PHPExcel替代⽅案PhpSpreadsheet
PHPExcel上⼀版本1.8.1于2015年发布。该项⽬已不再维护，可以使⽤，但是不建议再使⽤。所有⽤户都应该迁移到其直接后继者或其他替代⽅案。PhpSpreadsheet打破了兼容性，⼤⼤提⾼了代码库质量（命名空间，PSR合规性，最新PHP语⾔功能的使⽤等）。
compor安装：compor require phpoffice/phpspreadsheet
简单⼊门
<?php
require './vendor/autoload.php';
u PhpOffice\PhpSpreadsheet\Spreadsheet;
u PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$spreadsheet = new Spreadsheet();
try {
$sheet = $spreadsheet->getActiveSheet();
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
echo "==PhpSpreadsheet异常==";
var_dump($e);
die;
}
// 按照单元格写⼊
$sheet->tCellValue('A1','Hello World');
// 按照⾏列写⼊，注意⾏和列都是从1开始的
$sheet->tCellValueByColumnAndRow(1,2,'2⾏1列');
$writer = new Xlsx($spreadsheet);
try {
$writer->save('hello.xlsx');
照片识图} catch (\PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
echo "==Writer异常==";
var_dump($e);
die;
阳光帅哥}
⽣成的hello.xlsx如下
从内存中清除⼯作簿
$spreadsheet->disconnectWorksheets(); //如果没有这句，直接运⾏下⾯那句会报“内存泄漏”错误
unt($spreadsheet);
世界防治结核病日PhpSpreadsheet类⽆法读取和写⼊持久存储，但是提供了\PhpOffice\PhpSpreadsheet\Reader\IReader和
\PhpOffice\PhpSpreadsheet\Writer\IWriter实现了读和写
有两种⽅法可以将⽂件读⼊PhpSpreadsheet
1 ⾃动⽂件类型解析模式
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load("05featuredemo.xlsx");
如果您需要在阅读器上设置⼀些属性（例如，只读取数据）
$reader = \PhpOffice\PhpSpreadshee\IOFactory::createReaderForFile("05featuredemo.xlsx"); $reader->tReadDataOnly(true);
$reader->load("05featuredemo.xlsx");
2 显式模式
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");
$spreadsheet = $reader->load("hello.xlsx");
⾃动类型解析模式⽐显式模式稍慢。
阅读电⼦表格(.xlsx⽂件)
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load("05featuredemo.xlsx");
坐姿划船器设置单元格的数据
1 按照单元格写⼊
$sheet->tCellValue('A1','Hello World');
2 按照⾏列写⼊，注意⾏和列都是从1开始的
$sheet->tCellValueByColumnAndRow(1,2,'2⾏1列');
获取单元格的数据
1 按照单元格读取
$sheet->getCell('A1')->getValue()
2 按照⾏列写⼊，注意⾏和列都是从1开始的
$sheet->getCellByColumnAndRow($column,$row)->getValue()
例⼦：有⼀个students.xlsx，内容如下
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->tReadDataOnly(true);
$spreadsheet = $reader->load('students.xlsx');
$sheet = $spreadsheet->getActiveSheet();
$highestRow = $sheet->getHighestRow(); // 最⼤⾏数地产药材
非ie内核浏览器$highestColumn = $sheet->getHighestColumn(); // 最⼤列数
$highestColumn = "AB";
// 把下标变成数字，如A-Z分别对应1-26，AA对应27，AB对应28，以此类推
$highestColumnIndex = PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); if($highestRow <= 2){ // 因为students.xlsx表格数据是从第三⾏开始的
周记格式exit('Excel没有任何数据');
}
$data = array();
for($row = 3;$row <= $highestRow;$row++){
$tempData['name'] = $sheet->getCellByColumnAndRow(1,$row)->getValue();
$tempData['chine'] = $sheet->getCellByColumnAndRow(2,$row)->getValue();
$tempData['math'] = $sheet->getCellByColumnAndRow(3,$row)->getValue();
$tempData['English'] = $sheet->getCellByColumnAndRow(4,$row)->getValue();
$data[] = $tempData;
}
var_dump($data);
结果如下
如果要在students.xlsx添加⼀⾏，李四 80 85 90，怎么处理？这也就要引⼊下⾯的概念
从模板⽣成Excel⽂件（读取，修改，写⼊）
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('students.xlsx');
$sheet = $spreadsheet->getActiveSheet();//李四 80 85 90
$sheet->getCell('A7')->tValue('李四');
$sheet->getCell('B7')->tValue(80);
$sheet->getCell('C7')->tValue(85);
$sheet->getCell('D7')->tValue(90);
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('new_students.xlsx');
⽣成 new_students.xlsx 内容如下
监督管理制度⽂件下载
$file_name = date('YmdHis').mt_rand(1000,9999).".xlsx"; // ⽂件名,07Excel⽂件后缀xlsx,Excel03版
本⽂件xls
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');// 告诉浏览器输出07Excel⽂件// header('Content-Type: application/vnd.ms-excel'); // 告诉浏览器将要输出Excel03版本⽂件
header('Content-Disposition: attachment;filename=' . $file_name ); // 告诉浏览器输出⽂件名称
header('Cache-Control: max-age=0'); // 禁⽌缓存
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet,'Xlsx'); //Excel07版本：Xlsx，Excel03版本：Xls // 个⼈理解，等同于下⾯那句
// $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet); // Excel07版本：Xlsx，Excel03版本：Xls
$writer->save('php://output');
