How to convert data to Excel format in php

In web development, it is often necessary to export data to Excel format so that users can manage and use it more conveniently. As a web development language, PHP also provides very convenient functions to export Excel. The method of exporting arrays to Excel is very practical and can quickly convert data in the website into Excel format.

1. Excel export class

PHP provides many excel export classes, among which the more common and practical ones are PHPExcel and PhpSpreadsheet. PHPExcel is an earlier open source library and has stopped updating in the latest version, so we use PhpSpreadsheet for demonstration here.

2. Demo demonstration

<?php
require_once &#39;vendor/autoload.php&#39;; // 引入类文件

use PhpOffice\PhpSpreadsheet\Spreadsheet; // 导入类名称
use PhpOffice\PhpSpreadsheet\Writer\Xlsx; // 导入类名称

// 数据处理
$data = array(
    array(&#39;John&#39;, &#39;Doe&#39;, 28, &#39;Male&#39;),
    array(&#39;Lisa&#39;, &#39;Smith&#39;, 34, &#39;Female&#39;),
    array(&#39;Mark&#39;, &#39;Johnson&#39;, 42, &#39;Male&#39;)
);

// 创建对象
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// 写入数据
$sheet->setCellValue('A1', 'First Name');
$sheet->setCellValue('B1', 'Last Name');
$sheet->setCellValue('C1', 'Age');
$sheet->setCellValue('D1', 'Gender');

$row = 2;

foreach ($data as $rowdata) {
    $sheet->setCellValue('A' . $row, $rowdata[0]);
    $sheet->setCellValue('B' . $row, $rowdata[1]);
    $sheet->setCellValue('C' . $row, $rowdata[2]);
    $sheet->setCellValue('D' . $row, $rowdata[3]);
    $row++;
}

// 导出Excel
$filename = "demo.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;

The above code demonstrates converting the data in a two-dimensional array $data into Excel format and exporting it. The specific process is as follows:

1. Introduce the PhpSpreadsheet class library, and import the Spreadsheet and Xlsx classes;
2. Define the data $data that needs to be exported;
3. Create the Spreadsheet object and obtain Current worksheet;
4. Write data into cells;
5. Export Excel through Xlsx class.

Through the above code demonstration, we can draw the following conclusions:

1. The use of PhpSpreadsheet is very simple, you only need to perform data operations in the conventional way of Excel operation;
2. Use the header to return the exported file to the browser for download;
3. You can use PHPExcel and PhpSpreadsheet to process more complex Excel styles.

3. Summary

Using PHP to convert arrays to Excel is a very practical function that allows us to manage and export data more quickly. In UI design, we usually use the simplest and most flexible way to present data, and then use PHP to optimize the Excel format. This not only saves the time and cost of the Web front-end, but also allows engineers to focus more on data processing. During the development process, you need to select and master the corresponding Excel export class library according to the actual situation in order to better handle various data scenarios.

The above is the detailed content of How to convert data to Excel format in php. For more information, please follow other related articles on the PHP Chinese website!