[PHP] Use phpspreadsheet to output data to Excel file

In this article, we will introduce the method of using phpspreadsheet to output data to an Excel file for your reference.

Before we used PHP to export Excel data, we used the PHPExcel library, but phpoffice has officially announced that PHPExcel has been abandoned and is no longer maintained. It is recommended to use phpspreadsheet, as shown in the figure below

We can install phpspreadsheet through composer

composer require phpoffice/phpspreadsheet

Use phpspreadsheet to export student information to the browser code example

/**
 * 输出到浏览器(需要设置header头)
 * @param string $fileName 文件名
 * @param string $fileType 文件类型
 */
function excelBrowserExport($fileName, $fileType) {

    //文件名称校验
    if(!$fileName) {
        trigger_error('文件名不能为空', E_USER_ERROR);
    }

    //Excel文件类型校验
    $type = ['Excel2007', 'Xlsx', 'Excel5', 'xls'];
    if(!in_array($fileType, $type)) {
        trigger_error('未知文件类型', E_USER_ERROR);
    }

    if($fileType == 'Excel2007' || $fileType == 'Xlsx') {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$fileName.'.xlsx"');
        header('Cache-Control: max-age=0');
    } else { //Excel5
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$fileName.'.xls"');
        header('Cache-Control: max-age=0');
    }
}

$data = [
    [1, 'jack', 10],
    [2, 'mike', 12],
    [3, 'jane', 21],
    [4, 'paul', 26],
    [5, 'kitty', 25],
    [6, 'yami', 60],
];

$title = ['id', 'name', 'age'];

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

//设置工作表标题名称
$worksheet->setTitle('工作表格1');

//表头
//设置单元格内容
foreach ($title as $key => $value) {
    $worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
}

$row = 2; //从第二行开始
foreach ($data as $item) {
    $column = 1;

    foreach ($item as $value) {
        $worksheet->setCellValueByColumnAndRow($column, $row, $value);
        $column++;
    }
    $row++;
}


$fileName = '学生信息';
$fileType = 'Xlsx';

//1.下载到服务器
//$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//$writer->save($fileName.'.'.$fileType);

//2.输出到浏览器
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); //按照指定格式生成Excel文件
excelBrowserExport($fileName, 'Xlsx');
$writer->save('php://output');

The export result is as shown in the figure :

Related tutorials: PHP video tutorial

The above is the detailed content of [PHP] Use phpspreadsheet to output data to Excel file. For more information, please follow other related articles on the PHP Chinese website!