[TOC] ## **要求** PHP要求 7.1或更高版本,office2007及以上  官方文档 https://phpspreadsheet.readthedocs.io/en/latest/topics/creating-spreadsheet/ https://phpoffice.github.io/PhpSpreadsheet/namespaces/phpoffice-phpspreadsheet.html [phpspreadsheet 中文文档（七）技巧和诀窍 - zh7314 - 博客园 (cnblogs.com)](https://www.cnblogs.com/zx-admin/p/11653863.html) [PhpSpreadsheet中文文档 | Spreadsheet操作教程实例 - 三水点靠木 (3water.com)](https://3water.com/article/bMTM17MTE5dLjI5) [phpspreadsheet中文手册\_php打开文件 - 码农教程 (manongjc.com)](http://www.manongjc.com/detail/64-hyybbwxtzlxkzxj.html) https://blog.csdn.net/DestinyLordC/article/details/84071456 ## **安装** ``` composer require phpoffice/phpspreadsheet ``` 效果 ``` D:\programming\phpstudy_pro\WWW\www.gyl.com>composer require phpoffice/phpspreadsheet Using version ^1.25 for phpoffice/phpspreadsheet ./composer.json has been updated Loading composer repositories with package information Updating dependencies (including require-dev) Package operations: 8 installs, 0 updates, 0 removals - Installing psr/http-factory (1.0.1): Downloading (100%) - Installing psr/http-client (1.0.1): Downloading (100%) - Installing markbaker/matrix (3.0.1): Downloading (100%) - Installing markbaker/complex (3.0.2): Downloading (100%) - Installing myclabs/php-enum (1.8.4): Downloading (100%) - Installing maennchen/zipstream-php (2.2.6): Downloading (100%) - Installing ezyang/htmlpurifier (v4.16.0): Downloading (100%) - Installing phpoffice/phpspreadsheet (1.25.2): Downloading (100%) ezyang/htmlpurifier suggests installing cerdic/css-tidy (If you want to use the filter 'Filter.ExtractStyleBlocks'.) ezyang/htmlpurifier suggests installing ext-tidy (Used for pretty-printing HTML) phpoffice/phpspreadsheet suggests installing ext-intl (PHP Internationalization Functions) phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for rendering PDF with PDF Writer) phpoffice/phpspreadsheet suggests installing dompdf/dompdf (Option for rendering PDF with PDF Writer) phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for rendering PDF with PDF Writer) phpoffice/phpspreadsheet suggests installing mitoteam/jpgraph (Option for rendering charts, or including charts with PDF or HTML Writers) Writing lock file Generating autoload files > @php think service:discover Succeed! > @php think vendor:publish File D:\programming\phpstudy_pro\WWW\www.gyl.com\config\trace.php exist! Succeed! ``` ## 实例化Spreadsheet对象 | | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | A1 | B1 | C1 | D1 | | 2 | A2 | | | | ``` <?php namespace app // 给类文件的命名空间起个别名 use PhpOffice\PhpSpreadsheet\Spreadsheet; //Xlsx类 保存文件功能类 use PhpOffice\PhpSpreadsheet\Writer\Xlsx; // 实例化 Spreadsheet 对象 $spreadsheet = new Spreadsheet(); ``` ## 获取活动工作薄(Worksheet类的对象) ``` $sheet = $spreadsheet->getActiveSheet();//Worksheet类实例 ``` ### Spreadsheet对象(活动工作薄)方法介绍 ``` // 2、获取单元格 $cell = $sheet->getCell('A1'); //方法1 $cell = $sheet->getCellByColumnAndRow(1,1); //方法2 // 3、给单元格赋值 $cellA->setValue('A1单元格内容'); // 3-1获取设置单元格，链式操作 $sheet->getCell('A3')->setValue('郭靖'); $sheet->getCellByColumnAndRow(1,4)->setValue('杨康'); // 3-2获取单元格 $cellA = $sheet->getCell('A1'); echo '值: ', $cellA->getValue(),PHP_EOL; echo '坐标: ', $cellA->getCoordinate(); //获取单元格坐标 // 4 Xlsx类 将电子表格保存到文件 $writer = new Xlsx($spreadsheet); $writer->save('1.xlsx'); // 单元格强化 /** * 参数说明 * 设置单元格 * setCellValue(参数1,参数2) * 参数1：单元格位置 * 参数2：单元格的值 * setCellValueByColumnAndRow(参数1,参数2,参数3) * 参数1：列位置 * 参数2：行位置 * 参数3：单元格的值 */ $sheet->setCellValue('A1','ID'); $sheet->setCellValue('B1','姓名'); $sheet->setCellValue('C1','年龄'); $sheet->setCellValue('D1','身高'); $sheet->setCellValueByColumnAndRow(1, 2, 1); $sheet->setCellValueByColumnAndRow(2, 2, '李雷'); $sheet->setCellValueByColumnAndRow(3, 2, '18岁'); $sheet->setCellValueByColumnAndRow(4, 2, '188cm'); $sheet->setCellValueByColumnAndRow(1, 3, 2); $sheet->setCellValueByColumnAndRow(2, 3, '韩梅梅'); $sheet->setCellValueByColumnAndRow(3, 3, '17岁'); $sheet->setCellValueByColumnAndRow(4, 3, '165cm'); ``` ### 单元格文字样式 ``` / ** * 单元格文字样式设置 */ // getStyle 获取单元格样式 // getFont 获取单元格文字样式 // setBold 设置文字粗细 // setName 设置文字字体 // setSize 设置文字大小 $sheet->getStyle('B2')->getFont()->setBold(true)->setName('宋体')->setSize(20); / ** * 单元格文字颜色 */ // getColor 获取坐标颜色 // setRGB设置字体颜色 // getRGB 获取字体颜色 // setARGB 设置字体颜色 // getARGB 获取字体颜色 $sheet->getStyle('B2')->getFont()->getColor()->setRGB('#AEEEEE'); $sheet->getStyle('B3')->getFont()->getColor()->setARGB('FFFF0000'); / ** *单元格格式 */ $sheet->setCellValue('A1','2019-10-10 10:10:10'); $sheet->setCellValue('A2','2019-10-10 10:10:10'); $sheet->getStyle('A2')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2); ``` ## Xlsx类 将电子表格保存到文件 ``` use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $writer = new Xlsx($spreadsheet); $writer->save('1.xlsx'); ``` #### 字体 第1行代码将A7至B7两单元格设置为粗体字，Arial字体，10号字；第2行代码将B1单元格设置为粗体字。 ~~~ $spreadsheet->getActiveSheet()->getStyle('A7:B7')->getFont()->setBold(true)->setName('Arial') ->setSize(10);; $spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); ~~~ #### 颜色 将文字颜色设置为红色。 ~~~ $spreadsheet->getActiveSheet()->getStyle('A4') ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); ~~~ #### 图片 可以将图片加载到Excel中。 ~~~ $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing->setName('Logo'); $drawing->setDescription('Logo'); $drawing->setPath('./images/officelogo.jpg'); $drawing->setHeight(36); ~~~ #### 列宽 将A列宽度设置为30（字符）。 ~~~ $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30); ~~~ 如果需要自动计算列宽，可以这样： ~~~ $spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); ~~~ 设置默认列宽为12。 ~~~ $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12); ~~~ #### 行高 设置第10行行高为100pt。 ~~~ $spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100); ~~~ 设置默认行高。 ~~~ $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15); ~~~ #### 对齐 将A1单元格设置为水平居中对齐。 ~~~ $styleArray = [ 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, ], ]; $worksheet->getStyle('A1')->applyFromArray($styleArray); ~~~ #### 合并 将A18到E22合并为一个单元格。 ~~~ $spreadsheet->getActiveSheet()->mergeCells('A18:E22'); ~~~ #### 拆分 将合并后的单元格拆分。 ~~~ $spreadsheet->getActiveSheet()->unmergeCells('A18:E22'); ~~~ #### 边框 将B2至G8的区域添加红色边框。 ~~~ $styleArray = [ 'borders' => [ 'outline' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK, 'color' => ['argb' => 'FFFF0000'], ], ], ]; $worksheet->getStyle('B2:G8')->applyFromArray($styleArray); ~~~ #### 工作表标题 设置当前工作表标题。 ~~~ $spreadsheet->getActiveSheet()->setTitle('Hello'); ~~~ #### 日期时间 设置日期格式。 ~~~ $spreadsheet->getActiveSheet() ->setCellValue('D1', '2018-06-15'); $spreadsheet->getActiveSheet()->getStyle('D1') ->getNumberFormat() ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2); ~~~ #### 换行 使用`\n`进行单元格内换行，相当于(ALT+"Enter")。 ~~~ $spreadsheet->getActiveSheet()->getCell('A4')->setValue("hello\nworld"); $spreadsheet->getActiveSheet()->getStyle('A4')->getAlignment()->setWrapText(true); ~~~ #### 超链接 将单元格设置为超链接形式。 ~~~ $spreadsheet->getActiveSheet()->setCellValue('E6', 'www.helloweba.net'); $spreadsheet->getActiveSheet()->getCell('E6')->getHyperlink()->setUrl('https://www.helloweba.net'); ~~~ #### 使用函数 使用SUM计算B5到C5之间单元格的总和。其他函数同理：最大数(MAX)，最小数(MIN)，平均值(AVERAGE)。 ~~~ $spreadsheet->getActiveSheet() ->setCellValue('B7', '=SUM(B5:C5)'); ~~~ #### 设置文档属性 可以设置Excel文档属性。 ~~~ $spreadsheet->getProperties() ->setCreator("Helloweba") //作者 ->setLastModifiedBy("Yuegg") //最后修改者 ->setTitle("Office 2007 XLSX Test Document") //标题 ->setSubject("Office 2007 XLSX Test Document") //副标题 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") //描述 ->setKeywords("office 2007 openxml php") //关键字 ->setCategory("Test result file"); //分类 ~~~ 此外，除了提供丰富的Excel文件处理接口外，PhpSpreadshee还提供了CSV，PDF，HTML以及XML等文件处理接口。 更多使用设置请参照官网文档：[https://phpspreadsheet.readthedocs.io/en/stable/](https://phpspreadsheet.readthedocs.io/en/stable/)。