导出数据到Excel的实用方案-Excel-EPS数据狗论坛

成长路上除了 发表于 2019-4-28 15:12:02

导出数据到Excel的实用方案

实际工作场景中，经常需要将系统中数据导出为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的参数为样式配置。
//样式配置
$style_array = array(
'alignment' => array(
   'horizontal' =>
         PHPExcel_Style_Alignment::HORIZONTAL_GENERAL       = 'general'
         PHPExcel_Style_Alignment::HORIZONTAL_LEFT          = 'left'
         PHPExcel_Style_Alignment::HORIZONTAL_RIGHT          = 'right'
         PHPExcel_Style_Alignment::HORIZONTAL_CENTER          = 'center'
         PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS = 'centerContinuous'
         PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY       = 'justify'
   'vertical' =>
         PHPExcel_Style_Alignment::VERTICAL_BOTTOM= 'bottom'
         PHPExcel_Style_Alignment::VERTICAL_TOP = 'top'
         PHPExcel_Style_Alignment::VERTICAL_CENTER= 'center'
         PHPExcel_Style_Alignment::VERTICAL_JUSTIFY = 'justify'
   'rotation' => (int)
   'wrap' => (boolean)
   'shrinkToFit' => (boolean)
   'indent' => (int)
)
'borders' => array(
   'allborders' => array(
         'style' =>
            PHPExcel_Style_Border::BORDER_NONE             = 'none';
            PHPExcel_Style_Border::BORDER_DASHDOT          = 'dashDot';
            PHPExcel_Style_Border::BORDER_DASHDOTDOT       = 'dashDotDot';
            PHPExcel_Style_Border::BORDER_DASHED          = 'dashed';
            PHPExcel_Style_Border::BORDER_DOTTED          = 'dotted';
            PHPExcel_Style_Border::BORDER_DOUBLE          = 'double';
            PHPExcel_Style_Border::BORDER_HAIR             = 'hair';
            PHPExcel_Style_Border::BORDER_MEDIUM          = 'medium';
            PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT    = 'mediumDashDot';
            PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT = 'mediumDashDotDot';
            PHPExcel_Style_Border::BORDER_MEDIUMDASHED    = 'mediumDashed';
            PHPExcel_Style_Border::BORDER_SLANTDASHDOT    = 'slantDashDot';
            PHPExcel_Style_Border::BORDER_THICK          = 'thick';
            PHPExcel_Style_Border::BORDER_THIN             = 'thin';
         'color' => array(
            'rgb' =>
               PHPExcel_Style_Color::COLOR_BLACK          = 'FF000000';
               PHPExcel_Style_Color::COLOR_WHITE          = 'FFFFFFFF';
               PHPExcel_Style_Color::COLOR_RED          = 'FFFF0000';
               PHPExcel_Style_Color::COLOR_DARKRED       = 'FF800000';
               PHPExcel_Style_Color::COLOR_BLUE          = 'FF0000FF';
               PHPExcel_Style_Color::COLOR_DARKBLUE       = 'FF000080';
               PHPExcel_Style_Color::COLOR_GREEN          = 'FF00FF00';
               PHPExcel_Style_Color::COLOR_DARKGREEN    = 'FF008000';
               PHPExcel_Style_Color::COLOR_YELLOW       = 'FFFFFF00';
               PHPExcel_Style_Color::COLOR_DARKYELLOW    = 'FF808000';
         )

   )
   'left' => // See 'allborders'
   'top' => // See 'allborders'
   'right' => // See 'allborders'
   'bottom' => // See 'allborders'
   'diagonal' =>
   'diagonaldirection' =>
)
'fill' => array(
   'type' =>
         PHPExcel_Style_Fill::FILL_NONE                      = 'none';
         PHPExcel_Style_Fill::FILL_SOLID                      = 'solid';
         PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR          = 'linear';
         PHPExcel_Style_Fill::FILL_GRADIENT_PATH             = 'path';
         PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN          = 'darkDown';
         PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY          = 'darkGray';
         PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID          = 'darkGrid';
         PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL    = 'darkHorizontal';
         PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS       = 'darkTrellis';
         PHPExcel_Style_Fill::FILL_PATTERN_DARKUP             = 'darkUp';
         PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL       = 'darkVertical';
         PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625          = 'gray0625';
         PHPExcel_Style_Fill::FILL_PATTERN_GRAY125          = 'gray125';
         PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN          = 'lightDown';
         PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY          = 'lightGray';
         PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID          = 'lightGrid';
         PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL    = 'lightHorizontal';
         PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS       = 'lightTrellis';
         PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP          = 'lightUp';
         PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL    = 'lightVertical';
         PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY       = 'mediumGray';
   'rotation' => (double)
   'startcolor' => // See 'borders' => 'allborders' => 'color'
   'endcolor' => // See 'borders' => 'allborders' => 'color'
   'color' => // See 'borders' => 'allborders' => 'color'
)
'font' => array(
   'name' =>
         'Arial'
         'Calibri'
         // etc.
   'bold' => (boolean)
   'italic' => (boolean)
   'superScript' => (boolean)
   'subScript' => (boolean)
   'underline' => (boolean)
   'strike' => (boolean)
   'size' => (float)
   'color' => // See 'borders' => 'allborders' => 'color'
)
'numberformat' =>
'protection' =>
)

写入数据
核心方法：$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')
      )
   )
);

clearlove 发表于 2019-5-3 08:58:35

谢谢分享

页: [1]

EPS数据狗论坛's Archiver

导出数据到Excel的实用方案