phpspreadsheet读写xlsx文件

程序 精帖
0 727
caixiaoxu
2018-08-31


PhpSpreadsheet是一个纯PHP类库,使你能够读写Excel、LibreOffic Calc等这样的表格格式。 
https://phpspreadsheet.readthedocs.io/en/develop/

列从0开始算,行从1开始算 $sheet->setCellValueByColumnAndRow(0,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来压缩保存 注意读写权限

简单示例
  1. require ‘vendor/autoload.php‘;
  2. use PhpOffice\PhpSpreadsheet\Spreadsheet;
  3. use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
  4. $spreadsheet = new Spreadsheet();
  5. $sheet = $spreadsheet->getActiveSheet();
  6. $sheet->setCellValue(‘A1‘, ‘Hello World !‘);
  7. $writer = new Xlsx($spreadsheet);
  8. $writer->save(‘hello world.xlsx‘);

默认保存到执行php的根目录,以thinkphp为例index.php在D:\wwwroot\thinkphp\public,那么文件就保存在这 注:如果不想保存到文件,可以传入php://output或php://stdout直接输出(例如html,输出网页)

通过模板来生成文件

全用代码写太累,可以用模板来修改,但是对于动态数据,还是要由代码生成

  1. //通过工厂模式创建内容
  2. $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load(‘template.xlsx‘);
  3. $worksheet = $spreadsheet->getActiveSheet();
  4. $worksheet->getCell(‘A1‘)->setValue(‘John‘);
  5. $worksheet->getCell(‘A2‘)->setValue(‘Smith‘);
  6. //通过工厂模式来写内容
  7. $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, ‘Xls‘);
  8. $writer->save(‘write.xls‘);
释放内存

为了防止内存泄露,建议用完手动清理

  1. $spreadsheet->disconnectWorksheets();
  2. unset($spreadsheet);
单元格根据索引获取英文列

其中A=0 Cell::stringFromColumnIndex($pColumn)

设置值
  1. $worksheet->getCell(‘A1‘)->setValue(‘John‘);
  2. $sheet->setCellValue(‘A1‘, ‘Hello World !‘);
  3. $sheet->setCellValueByColumnAndRow($columnIndex, $rowIndex, $value);
宽度设置

$this->getColumnDimension($columnIndex)->setWidth($width); 还可以让其自适应(不靠谱,建议自行设置) $sheet->calculateColumnWidths();

直接输出下载
  1. header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet‘);//告诉浏览器输出07Excel文件
  2. //header(‘Content-Type:application/vnd.ms-excel‘);//告诉浏览器将要输出Excel03版本文件
  3. header(‘Content-Disposition: attachment;filename="01simple.xlsx"‘);//告诉浏览器输出浏览器名称
  4. header(‘Cache-Control: max-age=0‘);//禁止缓存
  5. $writer = new Xlsx($spreadsheet);
  6. $writer->save(‘php://output‘);
自动计算列宽
  1. function autoFitColumnWidthToContent($sheet, $fromCol, $toCol) {
  2. if (empty($toCol) ) {//not defined the last column, set it the max one
  3. $toCol = $sheet->getColumnDimension($sheet->getHighestColumn())->getColumnIndex();
  4. }
  5. for($i = $fromCol; $i <= $toCol; $i++) {
  6. $sheet->getColumnDimension($i)->setAutoSize(true);
  7. }
  8. $sheet->calculateColumnWidths();
  9. }
函数formula

https://phpspreadsheet.readthedocs.io/en/develop/references/function-list-by-name/ https://phpspreadsheet.readthedocs.io/en/develop/topics/calculation-engine/#function-reference

  1. $worksheet->setCellValue(‘A12‘, ‘=DMIN(A4:E10,"Profit",A1:A2)‘);
  2. $retVal = $worksheet->getCell(‘A12‘)->getCalculatedValue();
  3. // $retVal = 225

可参考suanli项目

转发自:http://www.cnblogs.com/leestar54/p/7786806.html


回帖