PHP development skills: How to implement data import and export functions

PHP development skills: How to implement data import and export functions, specific code examples are required

Importing and exporting data are very common functions in the Web development process. Whether you are importing data from an Excel file to a database, or exporting data from a database to Excel, CSV, or other formats, you need to master some development skills. This article will introduce how to use PHP to implement data import and export functions, and provide specific code examples.

1. Data import

When implementing the data import function, we often need to process Excel files. PHP provides some functions and libraries to process Excel files, the most commonly used is the PHPExcel library. First, we need to install and introduce the PHPExcel library.

// 引入PHPExcel库
require_once 'PHPExcel/PHPExcel.php';
require_once 'PHPExcel/PHPExcel/IOFactory.php';

Next, we can import the data in the Excel file into the database through the following code.

// 读取Excel文件
$inputFileName = 'data.xlsx';
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

// 获取工作表中的数据
$worksheet = $objPHPExcel->getActiveSheet();
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();

for ($row = 1; $row <= $highestRow; $row++) {
    $rowData = $worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, false);
    
    // 插入数据库
    $sql = "INSERT INTO table_name (column1, column2, column3) VALUES ('" . $rowData[0][0] . "', '" . $rowData[0][1] . "', '" . $rowData[0][2] . "')";
    // 执行SQL语句
}

The above code reads the data in the Excel file line by line and inserts it into the database.

2. Data export

When implementing the data export function, we usually export the data to Excel or CSV files. For Excel export, we can still use PHPExcel library. The following is a sample code to export data from the database to an Excel file.

// 创建PHPExcel对象
$objPHPExcel = new PHPExcel();

// 添加数据
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Column1');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Column2');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Column3');

// 查询数据库获取数据
$sql = "SELECT column1, column2, column3 FROM table_name";
$result = mysqli_query($conn, $sql);

$row = 2;
while ($row_data = mysqli_fetch_assoc($result)) {
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $row_data['column1']);
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $row_data['column2']);
    $objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $row_data['column3']);
    
    $row++;
}

// 导出Excel文件
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('data.xlsx');

The above code will write the data obtained from the database into the Excel file line by line and save it as data.xlsx.

For exporting to a CSV file, you can use the following code example.

// 设置HTTP响应头
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="data.csv"');

// 查询数据库获取数据
$sql = "SELECT column1, column2, column3 FROM table_name";
$result = mysqli_query($conn, $sql);

while ($row_data = mysqli_fetch_assoc($result)) {
    echo $row_data['column1'] . ',' . $row_data['column2'] . ',' . $row_data['column3'] . '
';
}

The above code outputs the data to the browser in CSV format, and the user can choose to save it as a data.csv file.

Summary

Through the sample code in this article, we have learned how to use PHP to implement data import and export functions. For data import, we used the PHPExcel library to read the Excel file and insert the data into the database; for data export, we used the PHPExcel library to export the data to an Excel file and use CSV format to output the data. These techniques can help us better handle data import and export tasks and improve development efficiency.

The above is the detailed content of PHP development skills: How to implement data import and export functions. For more information, please follow other related articles on the PHP Chinese website!