English
English
In web development, data import and export functionalities are common requirements. Whether importing data from an Excel file into a database, or exporting data from a database to Excel, CSV, or other formats, mastering these techniques can greatly improve development efficiency. This article will explain how to implement these features in PHP and provide specific code examples.
When implementing data import functionality, we often need to handle Excel files. PHP provides several libraries to process Excel files, with PHPExcel being one of the most commonly used. First, we need to install and include the PHPExcel library.
<?php // Include PHPExcel library require_once 'PHPExcel/PHPExcel.php'; require_once 'PHPExcel/PHPExcel/IOFactory.php'; ?>
Next, we can use the following code to import data from an Excel file into a database:
<?php
// Read the Excel file
$inputFileName = 'data.xlsx';
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
// Get data from the worksheet
$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);
// Insert data into the database
$sql = "INSERT INTO table_name (column1, column2, column3) VALUES ('" . $rowData[0][0] . "', '" . $rowData[0][1] . "', '" . $rowData[0][2] . "')";
// Execute the SQL query
}
?>
The above code reads the data from the Excel file row by row and inserts it into the database.
When implementing data export functionality, we usually need to export the data to Excel or CSV files. For Excel export, we can still use the PHPExcel library. Below is an example of exporting data from a database to an Excel file:
<?php
// Create a PHPExcel object
$objPHPExcel = new PHPExcel();
// Add data to the worksheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Column1');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Column2');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Column3');
// Query the database to get data
$sql = "SELECT column1, column2, column3 FROM table_name";
$result = mysqli_query($conn, $sql);
// Write data to the Excel file
$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++;
}
// Export the Excel file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('data.xlsx');
?>
The above code fetches the data from the database and writes it row by row into an Excel file, then saves it as data.xlsx.
If you prefer to export the data as a CSV file, you can use the following code example:
<?php
// Set HTTP headers for CSV
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="data.csv"');
// Query the database to get data
$sql = "SELECT column1, column2, column3 FROM table_name";
$result = mysqli_query($conn, $sql);
// Output data as CSV
while ($row_data = mysqli_fetch_assoc($result)) {
echo $row_data['column1'] . ',' . $row_data['column2'] . ',' . $row_data['column3'] . "\n";
}
?>
The above code outputs the data in CSV format to the browser, and the user can choose to save it as a data.csv file.
Through the example code in this article, we have learned how to use PHP to implement data import and export functionalities. By using the PHPExcel library, we can easily handle data import/export for Excel files; at the same time, we also demonstrated how to export data as a CSV file. These techniques can help developers handle data efficiently and improve development productivity.
