PhpSpreadsheet是一个纯PHP编写的组件库,它使用现代PHP写法,代码质量和性能比PHPExcel高不少,完全可以替代PHPExcel(PHPExcel已不再维护)。使用PhpSpreadsheet可以轻松读取和写入Excel文档,支持Excel的所有操作。
PhpSpreadsheet特性
- 支持读取.xls,.xlsx,.html,.csv等格式文件
- 支持写入导出.xls,.xlsx,.html,.csv,.pdf格式文件。
- 提供丰富的API,提供单元格样式设置、Excel表格属性设置、图表设置等等诸多功能
- 使用PhpSpreadsheet完全可以生成一个外观结构都满足你的Excel表格文件。
- 卓越的性能,尤其在PHP7上表现优异,比PHPExcel强大很多。
1、安装PhpOffice\PhpSpreadsheet
composer require phpoffice/phpspreadsheet
2、引入
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
3、使用(导出)
set_time_limit(0);
$spreadsheet = new Spreadsheet();
//合并单元格
$spreadsheet->getActiveSheet()->mergeCells('A1:E1');
//设置自动高度
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
//设置宽度
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(100);
//设置字体为粗体
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
//设置表头
$spreadsheet->getActiveSheet()->getCell('A1')->setValue('销售汇总单(' . $date . ')');
//设置自动换行
$spreadsheet->getActiveSheet()->getStyle('A3:I20')->getAlignment()->setWrapText(true);
//设置水平、垂直居中
$styleArray = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
],
];
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->getStyle('A1:E100')->applyFromArray($styleArray);
$title = ['序号', '商品名称', '规格'];
foreach ($title as $index => $item) {
$worksheet->setCellValueByColumnAndRow($index + 1, 2, $item);
}
$data = [
['no' => 1, 'product_name' => '苹果', 'package' => '1斤'],
['no' => 2, 'product_name' => '香蕉', 'package' => '2斤']
]
$row = 2; // 从第二行开始
foreach ($data as $item) {
$column = 1;
foreach ($item as $value) {
// 单元格内容写入
$worksheet->setCellValueByColumnAndRow($column, $row, $value);
$column++;
}
$row++;
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="销售汇总单('. date('Y-m-d') .')'.'.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
4、进价使用:合并复杂单元格(导出)
有时候经常会遇到合并单元格的情况,比如一个人有多个订单,则需要把此人用户信息合并,合并思路:
1、找到行最小粒度(单个订单)
2、先写入用户信息(1,1),然后循环该用户的订单,比如有3个订单,则设置单元格
记录订单开始行startRow = 1
(1,2) (1,3) (1,4) 分别为 第1个订单的订单号,下单时间,收货地址
(2,2) (2,3) (2,4) 分别为 第2个订单的订单号,下单时间,收货地址
(3,2) (3,3) (3,4) 分别为 第3个订单的订单号,下单时间,收货地址
记录订单开始行endRow = 3
3、合并(1,1)(2,1)(3,1),填充内容为用户信息
代码如下
$data = [
[
'id' => 1,
orders =>
['order_sn' => 101, 'order_time' => '20211019', 'order_address' => '***'],
['order_sn' => 102, 'order_time' => '20211019', 'order_address' => '***'],
['order_sn' => 103, 'order_time' => '20211019', 'order_address' => '***']
]
]
$row = 1;
foreach ($data as $k => $v) {
//先写入用户信息
$worksheet->setCellValueByColumnAndRow(1, $row, $v['user_id']);
$rowStart = $row;
//循环输出订单
foreach ($vvv['orders'] as $kk => $vv) {
$worksheet->setCellValueByColumnAndRow(2, $row, $vvvv['order_sn']);
$worksheet->setCellValueByColumnAndRow(3, $row, $vvvv['contactor']);
$worksheet->setCellValueByColumnAndRow(4, $row, $vvvv['contactor']);
//也可以使用下面
/*
$col = 2;
foreach ($vv as $kkk => $vvv) {
$worksheet->setCellValueByColumnAndRow($row, $col, $vvv);
$col++;
}
$col = 2;
*/
$worksheet->setCellValueByColumnAndRow(3, $row, $vvvv['contactor']);
$worksheet->setCellValueByColumnAndRow(4, $row, $vvvv['contactor']);
$row++;
}
//合并用户信息
$spreadsheet->getActiveSheet()->mergeCells('A' . $rowStart . ':A' . ($row - 1));
}
}
5、使用(导入)
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load('students.xlsx'); //载入excel表格
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$data = [];
for ($row = 1; $row <= $highestRow; ++$row) { //从第一行开始
$name = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //姓名
$age = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //年龄
$data[] = [
'age' => $age,
'name' => $name,
];
}