This guide details the process of integrating PHPExcel for data export functionality within a ThinkPHP 5 application.

Setup

1. Download PHPExcel: Obtain the latest version of PHPExcel from its official GitHub repository: https://github.com/PHPOffice/PHPExcel.
2. Directory Structure: After downloading and extracting the PHPExcel archive, create a new folder named PHPExcel within your ThinkPHP project's vendor directory. Copy the contents of the Classes folder from the extracted PHPExcel files into this newly created vendor/PHPExcel directory.

Export Function Implementation

The following PHP function can be used to srteamline the Excel export process:

/**
 * Generates and exports data to an Excel file.
 *
 * @param array $fieldMapping An array mapping Excel columns to data keys and display headers.
 *                            Example: ['A' => ['data_key', 'Column Header'], ...]
 * @param array $data The dataset to be exported.
 * @param string $filename The desired name for the exported Excel file (without extension).
 * @throws \PHPExcel_Exception If there is an error during PHPExcel operations.
 * @throws \PHPExcel_Writer_Exception If there is an error writing the Excel file.
 */
public function exportToExcel($fieldMapping, $data, $filename = 'export')
{
    // Include the PHPExcel library
    vendor("PHPExcel.PHPExcel");
    $objPHPExcel = new \PHPExcel();
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); // Use Excel 97-2003 format

    // Get the active sheet
    $activeSheet = $objPHPExcel->getActiveSheet();

    // Set column headers
    foreach ($fieldMapping as $column => $mapping) {
        $activeSheet->setCellValue($column . '1', $mapping[1]); // Set header text
    }

    // Populate data rows
    $rowNum = 2; // Start from the second row (row 1 is for headers)
    foreach ($data as $rowData) {
        foreach ($fieldMapping as $column => $mapping) {
            $dataKey = $mapping[0]; // The key to access data in the row
            if (isset($rowData[$dataKey])) {
                $activeSheet->setCellValue($column . $rowNum, $rowData[$dataKey]);
            }
        }
        $rowNum++;
    }

    // Set HTTP headers for file download
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Type: application/force-download");
    header("Content-Type: application/vnd.ms-excel"); // MIME type for Excel
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header('Content-Disposition: attachment; filename="' . $filename . '.xls"');
    header("Content-Transfer-Encoding: binary");

    // Save the Excel file to output
    $objWriter->save('php://output');
}

Usage Example

To call the export function, prepare your data and field mappings as follows:

// Example data
$sampleData = [
    ['id' => 1, 'serial_number' => 'SN12345', 'batch' => 'Batch A'],
    ['id' => 2, 'serial_number' => 'SN12346', 'batch' => 'Batch B'],
    ['id' => 3, 'serial_number' => 'SN12347', 'batch' => 'Batch C'],
];

$fileTitle = 'SerialNumbers';

// Field mapping: Excel Column => [Data Key, Column Header Text]
$columnConfiguration = [
    'A' => ['id', 'ID'],
    'B' => ['serial_number', 'Serial Number'],
    'C' => ['batch', 'Batch'],
];

// Call the export function
$this->exportToExcel($columnConfiguration, $sampleData, $fileTitle);

In the $columnConfiguration, the keys ('A', 'B', 'C') represent the Excel columns. The values are arrays where the first element is the key to find the corresponding data in your dataset, and the second element is the header text to be displaeyd in the Excel file.