PHP Excel插件之PHP_XLSXWriter使用笔记

Posted on | In

PHP_XLSXWriter介绍

该库为PHP操作Excel库，设计为轻量级的，并具有较小的内存使用量。

优点

  • 设计较为轻量，内存使用量较小，写入/读取比PHPExcel性能高
  • 支持货币/日期/数字单元格格式，简单公式
  • 支持基本的单元格样式
  • 支持创建多个子表格
  • 支持导出超过10万行的大表格
  • 支持PHP 5.2.1+

缺点

  • 仅支持 Office 2007+ xlsx格式的Excel表，不支持2003版本xls格式
  • 仅支持简单的基本功能，功能没有PHPExcel丰富
  • 设置单元格格式等方法较为复杂难用
  • 写入Excel表格时只能一行一行写入，不能指定某一单元格进行写入
  • 不支持对已存在的文件追加写入(不支持修改文件)，只能创建新文件进行写入，已在的文件会覆盖

项目地址

PHP_XLSXWriter项目地址

主要使用方法及参数解释

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
/**
 * 设置表头列宽、列格式等
 * @param string $sheet_name 工作表名称
 * @param array $header_types 每列值=>每列类型，ex:['姓名' => 'string, '年龄' => 'integer']
 * @param array $col_options 列样式数组
 */
public function writeSheetHeader($sheet_name, array $header_types, $col_options = null);

/**
 * 逐行写入
 * @param string $sheet_name 工作表名称
 * @param array $row 每行的值 ex:['张三', 18]
 * @param string $row_options 行样式数组
 */
public function writeSheetRow($sheet_name, array $row, $row_options = null);

/**
 * 合并单元格
 * ex:如果合并A2:C4, 代码则 markMergedCell(1, 0, 3, 2)
 * @param string $sheet_name 工作表名称
 * @param integer $start_cell_row 开始行数(从0开始，第一行传入0)
 * @param integer $start_cell_column 开始列数(从0开始，A列传入0)
 * @param integer $end_cell_row 结束行数(从0开始，第一行传入0)
 * @param integer $end_cell_column 结束列数(从0开始，A列传入0)
 */
public function markMergedCell($sheet_name, $start_cell_row, $start_cell_column, $end_cell_row, $end_cell_column);

/**
 * 写入保存成文件
 * @param string $filename 文件路径名称
 */
public function writeToFile($filename);

格式参数及可使用的值

简单格式

简单格式 格式代码
string @
integer 0
date YYYY-MM-DD
datetime YYYY-MM-DD HH:MM:SS
time HH:MM:SS
price #,##0.00
dollar [$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00
euro #,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]

基本单元格格式

样式 允许的值
font Arial, Times New Roman, Courier New, Comic Sans MS, 宋体
font-size 8,9,10,11,12 …
font-style bold, italic, underline, strikethrough or multiple ie: ‘bold,italic’
border left, right, top, bottom, or multiple ie: ‘top,left’
border-style thin, medium, thick, dashDot, dashDotDot, dashed, dotted, double, hair, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot
border-color #RRGGBB, ie: #ff99cc or #f9c
color #RRGGBB, ie: #ff99cc or #f9c
fill #RRGGBB, ie: #eeffee or #efe
halign general, left, right, justify, center
valign bottom, center, distributed

示例

简单写入示例

1
2
3
4
5
6
7
8
9
10
$writer = new XLSXWriter();
$data = [
    ['姓名', '性别', '年龄'],
    ['张三', '男', 18],
    ['李四', '男', 20]
];
//逐行写入
$writer->writeSheet($data);
//保存文件
$writer->writeToFile($savePath . 'test.xlsx');

简单写入效果图

写入Excel样式示例

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
$writer = new XLSXWriter();
$sheetName = '表格';
//设置每列类型、suppress_row为true时代表不写入第一行、widths为每列宽度数组
$writer->writeSheetHeader($sheetName, ['' => 'string', '' => 'string', '' => 'string', '' => 'string', '' => '0.00', '' => 'string'], 
['suppress_row' => true, 'widths' => [5, 28, 17, 9, 16, 31]]);
//设置表头
$writer->writeSheetRow($sheetName, ['【XXX-XX】'],
['font' => '宋体', 'font-size' => 18, 'font-style' => 'bold', 'halign' => 'left', 'height' => 30, 'width' => 48.01]);
$writer->markMergedCell($sheetName, 0, 0, 0, 2);//合并单元格A1:C1 (开始行,开始列,结束行,结束列)
$writer->writeSheetRow($sheetName, ['XX费用明细'],
['font' => '宋体', 'font-size' => 14,'font-style' => 'bold', 'halign' => 'center', 'valign' => 'center', 'height' => 30, 'width' => 100.77]);
$writer->markMergedCell($sheetName, 1, 0, 1, 5);//合并单元格A2:F2
//样式类型
$style1 = ['font' => '宋体', 'halign' => 'center', 'valign' => 'center', 'font-size' => 12, 'height' => 25, 'border'=>'left,right,top,bottom', 'border-style' => 'thin'];
$style2 = ['font' => '宋体', 'halign' => 'center', 'valign' => 'center', 'font-size' => 12, 'height' => 25, 'fill' => '#99CCFF', 'border'=>'left,right,top,bottom', 'border-style' => 'thin'];
$style3 = ['font' => '宋体', 'halign' => 'center', 'valign' => 'center', 'font-size' => 12, 'height' => 25, 'fill' => '#99CCFF', 'color' => '#ff0000', 'border'=>'left,right,top,bottom', 'border-style' => 'thin'];
$style4 = ['font' => '宋体', 'halign' => 'left', 'font-size' => 12, 'font-style'=>'bold', 'height' => 25];
$style5 = ['height' => 25, 'border-style' => 'thin',
    ['font' => '宋体', 'halign' => 'left', 'font-size' => 12, 'border'=>'bottom'],
    ['font' => '宋体', 'halign' => 'left', 'font-size' => 12, 'border'=>'left,bottom'],
    ['font' => '宋体', 'halign' => 'left', 'font-size' => 12, 'border'=>'bottom'],
    ['font' => '宋体', 'halign' => 'left', 'font-size' => 12, 'border'=>'bottom'],
    ['font' => '宋体', 'halign' => 'left', 'font-size' => 12, 'border'=>'left,right,top,bottom', 'font-style'=>'bold', 'halign' => 'center', 'valign' => 'center'],
    ['font' => '宋体', 'halign' => 'left', 'font-size' => 12, 'border'=>'left,right,top,bottom']];
$writer->writeSheetRow($sheetName, ['项目', '', '收费标准', '数量', '金额(RMB)', '备注'], $style1);
$writer->markMergedCell($sheetName, 2, 0, 2, 1);//合并单元格A3:B3
$writer->writeSheetRow($sheetName, ['垂直居中', 'XXXX费', 'XX元/单', '', '', ''],
['font' => '宋体', 'halign' => 'center', 'valign' => 'distributed', 'font-size' => 12, 'height' => 25, 'border'=>'left,right,top,bottom', 'border-style' => 'thin']);
$writer->markMergedCell($sheetName, 3, 0, 22, 0);//合并单元格A4:A23
$writer->writeSheetRow($sheetName, ['', 'XY费', 'XX元/单', '', '', ''], $style1);
$writer->writeSheetRow($sheetName, ['', 'X税点（x%）', '按X%收取', '', '', ''], $style3);
$writer->writeSheetRow($sheetName, ['', '测试', '', '', '', ''], $style1);
$writer->writeSheetRow($sheetName, ['', 'XX税点（X%）', '', '', '', ''], $style1);
$writer->writeSheetRow($sheetName, ['', 'XX汇总金额', '', '', '', ''],
[$style1, $style1, $style1, $style1, array_merge($style1, ['font-style'=>'bold',]), $style1, 'height' => 25]);
$writer->writeSheetRow($sheetName, ['', 'XX费', '实报实销', '', '', ''], $style1);
$writer->writeSheetRow($sheetName, ['', 'X税点（X%）', '按X%收取', '', '', ''], $style3);
$writer->writeSheetRow($sheetName, ['', '垫XX费', 'X元/X', '', '', ''], $style1);
$writer->writeSheetRow($sheetName, ['', '', 'XX', '', '', ''], $style1);
$writer->markMergedCell($sheetName, 11, 1, 12, 1);//合并单元格B12:B13
$writer->writeSheetRow($sheetName, ['', 'XX费', '', '', '', ''], $style1);
$writer->writeSheetRow($sheetName, ['', '', 'XX', '', '', ''], $style1);
$writer->markMergedCell($sheetName, 13, 1, 14, 1);//合并单元格B14:B15
$writer->writeSheetRow($sheetName, ['', 'XX', '', '', '', ''], $style3);
$writer->writeSheetRow($sheetName, ['', '', '', '', '', ''], $style1);
$writer->writeSheetRow($sheetName, ['', '', '', '', '', ''], $style1);
$writer->markMergedCell($sheetName, 16, 1, 17, 1);//合并单元格B17:B18
$writer->writeSheetRow($sheetName, ['', '', '', '', '', ''], $style1);
$writer->writeSheetRow($sheetName, ['', '', '', '', '', ''], $style1);
$writer->markMergedCell($sheetName, 18, 1, 19, 1);//合并单元格B19:B20
$writer->writeSheetRow($sheetName, ['', 'xx', '', '', '', ''], $style2);
$writer->writeSheetRow($sheetName, ['', 'xx总金额', '', '', '', ''], $style5);
$writer->writeSheetRow($sheetName, ['', '', '总计', '', '', ''], $style5);
$writer->writeSheetRow($sheetName, ['xx：', '', '审核：', '', 'xx确认：', ''], $style4);
$writer->writeSheetRow($sheetName, ['xx信息：', '', '', '', '', ''], $style4);
$writer->markMergedCell($sheetName, 24, 0, 24, 4);//合并单元格A25:E25
$writer->writeSheetRow($sheetName, ['xx有限公司', '', '', '', '', ''], $style4);
$writer->markMergedCell($sheetName, 25, 0, 25, 4);//合并单元格A26:E26
$writer->writeSheetRow($sheetName, ['xx支行', '', '', '', '', ''], $style4);
$writer->markMergedCell($sheetName, 26, 0, 26, 4);//合并单元格A27:E27
$writer->writeSheetRow($sheetName, ['银 行帐 号：123456', '', '', '', '', ''], $style4);
$writer->markMergedCell($sheetName, 27, 0, 27, 4);//合并单元格A28:E28
$writer->writeToFile('test.xlsx');

写入效果图

以上文章有误请发送至联系邮箱，下一次介绍另一款高性能PHP操作Excel的工具使用方法