导出数据到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);
    1
    2
    3
    4
    5
    6
    7
    8
    $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的参数为样式配置。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
//样式配置
$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
    1
    2
    3
    4
    5
    6
    7
    8
    9
    /* 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’);

列宽和行高

1
2
3
4
//列宽
$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实际值和像素值之间满足线性关系。知道问题原因，处理起来便很简单。

1
2
3
private function _get_excel_real_width($value) {
	return $value/7;
}

横坐标处理

Excel的横坐标为A、B….、AA、AB类型，需要将PHP数组下标转化为Excel的横坐标。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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样式属性也会有差异，需要注意下。

1
2
3
4
5
6
7
8
9
//设置Excel默认框线
 $style_array = array(
      'borders' => array(
          'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
    			'color' => array('rgb' => 'DDDDDD')
          )
      )
  );

坚持原创技术分享，您的支持将鼓励我继续创作！