PhpSpreadsheet是一个纯PHP类库，使你能够读写Excel、LibreOffic Calc等这样的表格格式。
https://phpspreadsheet.readthedocs.io/en/develop/
列从1开始算，行从1开始算
$sheet->setCellValueByColumnAndRow(1,1,'特别说明');
composer require phpoffice/phpspreadsheet 版本号
默认情况会提示找不到库，上composer找是有的，是因为还没有稳定版，所以要指定版本 1.0.0beta
The following software is required to develop using PhpSpreadsheet:
PHP version 5.6 or newer
PHP extension php_zip enabled
PHP extension php_xml enabled
PHP extension php_gd2 enabled (if not compiled in)
默认使用ZipArchive来压缩保存
注意读写权限
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');
$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');
默认保存到执行php的根目录，以thinkphp为例index.php在
D:\wwwroot\thinkphp\public
，那么文件就保存在这
注：如果不想保存到文件，可以传入
php://output
或
php://stdout
直接输出（例如html，输出网页）
通过模板来生成文件
全用代码写太累，可以用模板来修改，但是对于动态数据，还是要由代码生成
//通过工厂模式创建内容
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('template.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->getCell('A1')->setValue('John');
$worksheet->getCell('A2')->setValue('Smith');
//通过工厂模式来写内容
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('write.xls');
为了防止内存泄露，建议用完手动清理
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
根据索引获取英文列
其中A=0
Cell::stringFromColumnIndex($pColumn)
$worksheet->getCell('A1')->setValue('John');
$sheet->setCellValue('A1', 'Hello World !');
$sheet->setCellValueByColumnAndRow($columnIndex, $rowIndex, $value);
合并单元格
* Set merge on a cell range by using numeric cell coordinates.
* @param int $pColumn1 Numeric column coordinate of the first cell (A = 0)
* @param int $pRow1 Numeric row coordinate of the first cell
* @param int $pColumn2 Numeric column coordinate of the last cell (A = 0)
* @param int $pRow2 Numeric row coordinate of the last cell
* @throws Exception
* @return Worksheet
$sheet->mergeCellsByColumnAndRow($pColumn1, $pRow1, $pColumn2, $pRow2)
$sheet->getStyleByColumnAndRow(0, 1, $columnIndex, $rowIndex)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyleByColumnAndRow(1, 1, 18, count($todayRank) + 2)->applyFromArray([
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
直接输出下载
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
//header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
header('Content-Disposition: attachment;filename="01simple.xlsx"');//告诉浏览器输出浏览器名称
header('Cache-Control: max-age=0');//禁止缓存
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
自动计算列宽
//注意$fromCol，$toCol是string类型，例如A、B、C、D
function autoFitColumnWidthToContent($sheet, $fromCol, $toCol) {
if (empty($toCol) ) {//not defined the last column, set it the max one
$toCol = $sheet->getColumnDimension($sheet->getHighestColumn())->getColumnIndex();
for($i = $fromCol; $i <= $toCol; $i++) {
$sheet->getColumnDimension($i)->setAutoSize(true);
$sheet->calculateColumnWidths();
https://phpspreadsheet.readthedocs.io/en/develop/references/function-list-by-name/
https://phpspreadsheet.readthedocs.io/en/develop/topics/calculation-engine/#function-reference
$worksheet->setCellValue('A12', '=DMIN(A4:E10,"Profit",A1:A2)');
$retVal = $worksheet->getCell('A12')->getCalculatedValue();
// $retVal = 225
单元格变可点击的超链
$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('https://www.example.com');
如果需要在表格内跳转，则
$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl("sheet://'Sheetname'!A1");
