|
实际工作场景中,经常需要将系统中数据导出为Excel,便于产品和运营查阅和二次处理。本文详细介绍了使用PHP导出数据的方法。
PHP操作Excel主要有两个第三方库,一个是 PHPExcel ,另外一个是 PhpSpreadsheet 。其中PhpSpreadsheet是PHPExcel的升级版本。
PHPExcel本身支持超链接、样式设置(字体、颜色、边框线、对齐等)、行高列宽设置、表格冻结、公式、合并单元格、多表格等特性。从官方文档得知,PHPExcel支持PHP5.2版本,代码质量和性能会低于PhpSpreadsheet。另外PHPExcel从2015年便不再维护,因此很难从社区增加新特性和处理历史缺陷。PHPExcel支持xls和xlsx两种格式,xls是Microsoft Excel 2003以下版本支持的文件,xlsx是Microsoft Excel 2007以后开始的。
PhpSpreadsheet是下一代的PHPExcel,支持PHP5.6及以上,可以处理Microsoft Excel和LibreOffice Calc。PhpSpreadsheet作为下一代的PHPExcel,目前得到社区持续维护。
基于本身业务特点,以及疑难问题的快速处理,最终选择了PHPExcel作为导出的基础库,并且采用xls文件格式。PHPExcel库因为历史久远,积累了很多其他用户踩过的坑,也便于我们处理类似疑难问题。
下文所述的方法是将内容导出为xls文件格式。
处理中-内容的处理
处理多sheet
核心方法:$obj_phpexcel->createSheet($i);
- $obj_phpexcel = new PHPExcel();
- $sheet_datas;//实际数据
- $sheet_name = 'test';
- foreach($sheet_datas as $i => $sheet_data) {
- $obj_phpexcel->createSheet($i);
- $obj_phpexcel->setActiveSheetIndex($sheet_index);
- $obj_phpexcel->getActiveSheet()->setTitle($sheet_name);
- }
复制代码
处理样式
核心方法: $obj_phpexcel->getActiveSheet()->getStyle(‘A1’)->applyFromArray($style_array);
getStyle的参数为实际坐标,比如A1,代表A列的第1行的单元格。applyFromArray的参数为样式配置。
写入数据
核心方法:$obj_phpexcel->getActiveSheet()->setCellValueExplicit(‘A1’, $value, $dtype);
dtype区分不同的数据类型,定义见PHPExcel\Cell\DataType.php
- /* Data types */
- const TYPE_STRING2 = 'str';
- const TYPE_STRING = 's';
- const TYPE_FORMULA = 'f';
- const TYPE_NUMERIC = 'n';
- const TYPE_BOOL = 'b';
- const TYPE_NULL = 'null';
- const TYPE_INLINE = 'inlineStr';
- const TYPE_ERROR = 'e';
复制代码
处理超链接
核心方法:$obj_phpexcel->getActiveSheet()->getCell(‘A1’)->getHyperlink()->setUrl($URL);
处理公式
核心方法:$obj_phpexcel->getActiveSheet()->setCellValue(‘A1’, ‘=SUM(A10:E9)’);
合并单元格
核心方法:$obj_phpexcel->getActiveSheet()->mergeCells(‘A1:C1’);
列宽和行高
- //列宽
- $obj_phpexcel->getActiveSheet()->getColumnDimension($column_label)->setWidth($value);
- //行高
- $obj_phpexcel->getActiveSheet()->getRowDimension($key)->setRowHeight($value);
复制代码
表格冻结
核心方法:$obj_phpexcel->getActiveSheet()->freezePane(‘D2’);
其他问题
实际值、像素值隐射
PHPExcel没办法将像素值,转化为Microsoft Excel实际的宽度和高度值。如果直接将像素值设置为Excel的实际值,会导致内容存在差异。经过测试得知,Excel实际值和像素值之间满足线性关系。知道问题原因,处理起来便很简单。
- private function _get_excel_real_width($value) {
- return $value/7;
- }
复制代码
横坐标处理
Excel的横坐标为A、B….、AA、AB类型,需要将PHP数组下标转化为Excel的横坐标。
- private function _get_sheet_col_label($index) {
- if (isset($this->_col_label_map[$index])) {
- return $this->_col_label_map[$index];
- }
- $abc = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
- for ($i=0; $i < 600; $i++) { //最高支持600列
- $a = (int)($i / 26);
- $b = $i % 26;
- $column_label = '';
- if ($a == 0) {
- $column_label = $column_label . substr($abc, $b, 1);
- } else {
- $a = $a -1;
- $column_label = substr($abc, $a, 1) . substr($abc, $b, 1);
- }
- $this->_col_label_map[($i)] = $column_label;
- }
- return $this->_col_label_map[$index];
- }
复制代码
默认的样式处理
Excel本身的表格框线无法通过PHPExcel设置,需要trick处理。另外Excel的样式属性与css样式属性也会有差异,需要注意下。
- //设置Excel默认框线
- $style_array = array(
- 'borders' => array(
- 'allborders' => array(
- 'style' => PHPExcel_Style_Border::BORDER_THIN,
- 'color' => array('rgb' => 'DDDDDD')
- )
- )
- );
复制代码 |
|