一、介绍
本文介绍 phpoffice/phpexcel
和 box/spout
两个包,比较的话,后者性能是比前者好的(参考 PHP解析大型Excel表格的库:box/spout),但是比较遗憾,后者不支持XLS的处理。(补充:下载了phpexcel最新版本,测试一下并不差什么)
二、安装
composer require phpoffice/phpexcel
composer require box/spout
注意的是,官网挂了,文档很难找。 现在安装的应该为 3.0 版本,而网上查找的一些使用方法为 2+ 版本的使用方式。语法上有些差别。
三、PHPExcel
- 读取表格
/** * 读取表格 - 只读取当前sheet, 支持xlsx、xls * @param string $path 文件路径 * @param int|null $titleLength 读取的列数 */ public function read(string $path, ?int $titleLength = null) { $excel = \PHPExcel_IOFactory::load($path); $worksheet = $excel->getActiveSheet(); // 获取有值的定位 A1、B1、A2、B2等 $arr = $worksheet->getCellCollection(); $data = $row = []; foreach ($arr as $item) { $columnLetter = $item[0]; if ($titleLength !== null && ord($columnLetter) - 64 > $titleLength) { continue; } if ($columnLetter == 'A') { // 过滤空行 if (count(array_filter($row)) > 0) { $data[] = $row; } $row = []; } // 当一个细胞中出现不同字体时,使用getValue会得到分隔的对象,而不是一个值,所以使用 getFormattedValue $row[] = trim((string) $worksheet->getCell($item)->getFormattedValue()); } return $data; }
- 导出表格
/** * 导出 * TODO 未处理列大于26 AA、AB */ public function export(string $title, array $data) { $objPHPExcel = new PHPExcel(); // 重命名工作sheet $objPHPExcel->getActiveSheet()->setTitle($title); // 设置文档信息,这个文档信息windows系统可以右键文件属性查看 // $objPHPExcel->getProperties()->setCreator("作者") // ->setLastModifiedBy("最后更改者") // ->setTitle("文档标题") // ->setSubject("文档主题") // ->setDescription("文档的描述信息") // ->setKeywords("设置文档关键词") // ->setCategory("设置文档的分类"); $sheet = $objPHPExcel->setActiveSheetIndex(0); if (count($data) < 100) { $data = array_pad($data, 100, []); } else { $data = array_pad($data, count($data) + 10, []); } foreach ($data as $rowIndex => $row) { $ascii = 65; $sheet->getRowDimension($rowIndex + 1)->setRowHeight(18); // 设置行高 foreach ($row as $cellValue) { $letter = chr($ascii++); $cell = $letter . ($rowIndex + 1); $style = $sheet->getStyle($cell); // 设置值 $sheet->setCellValue($cell, $cellValue); $style->getFont()->setName('微软雅黑'); $style->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); if ($rowIndex === 0) { $sheet->getColumnDimension($letter)->setWidth(18); $style->getFont()->setBold(true); } } } header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="'.$title.'.xls"'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; }
四、Spout
-
读取表格
<?php require 'vendor/autoload.php'; use Box\Spout\Reader\Common\Creator\ReaderEntityFactory; $excelPath = '/tmp/test.xlsx'; // 读取表格,这里还可以 createCSVReader $reader = ReaderEntityFactory::createXLSXReader(); $reader->open($excelPath); // 获取sheets $sheets = $reader->getSheetIterator(); // 获取第一个sheet $sheets->rewind(); $sheet = $sheets->current(); $table = $sheet->getRowIterator(); foreach ($table as $row) { var_dump($row->toArray()); } $reader->close();
-
导出表格
注意要有目录权限
<?php require_once 'vendor/autoload.php'; use Box\Spout\Writer\Common\Creator\WriterEntityFactory; $data = [ [1,2,3], ['a', 'b', 'c'] ]; createExcel($data, '/tmp/tt.xlsx'); function createExcel(&$data, $filePath) { $writer = WriterEntityFactory::createXLSXWriter($filePath); $writer->openToFile($filePath); foreach ($data as &$row) { foreach ($row as &$item) { $item = WriterEntityFactory::createCell($item); } $row = WriterEntityFactory::createRow($row); } $writer->addRows($data); $writer->close(); }