PhpSpreadsheet套件產生XLS或ODS檔

• 完全可以替代PHPExcel（PHPExcel已不再維護）
• 要使用PhpSpreadsheet需要滿足以下條件：
  • PHP5.6或更高版本
  • PHP的延伸模組php_zip、php_xml、php_gd2需要啟用
• 安裝

composer require phpoffice/phpspreadsheet

• Migration from PHPExcel
• 使用PhpSpreadsheet匯入&匯出Excel

與PHPExcel語法差異

• 引入函式庫

<?php
//Before
require_once 'PHPExcel.php';
//After
require_once 'vendor/autoload.php';

• 引入空間名稱(可以根據自己實際需要引入)

<?php
//After
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

• 創建

<?php
//Before
$phpexcel = new PHPExcel();
$sheet1 = $phpexcel->getActiveSheet()->setTitle("基本資料");
$phpexcel->setActiveSheetIndex(0);
$objWriter->save("php://output");
//After
$spreadsheet = new Spreadsheet();
$sheet1 = $spreadsheet->getActiveSheet()->setTitle("基本資料");
$spreadsheet->setActiveSheetIndex(0);
$objWriter->save("php://output");

• 匯出Excel檔案格式(檔案含特殊字須轉出xlsx格式xls會爆錯)

<?php
//Before
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename=人員基本資料.xlsx');
header('Cache-Control:max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($phpexcel,'Xlsx');
//After
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=人員基本資料.xlsx');
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');

• 匯出Ods檔案格式

<?php
//After
header('Content-Type: application/vnd.oasis.opendocument.spreadsheet');
header('Content-Disposition:attachment;filename=人員基本資料.ods');
header('Cache-Control:max-age=0');
$objWriter = IOFactory::createWriter($spreadsheet,'Ods');

• 儲存格寫法參考
  • 官網
• 其他資源
  • 國人自製套件 yidas/phpspreadsheet-helper
  • GitHub Link
  • Blog Link