本文实例讲述了基于PHPexecl类生成复杂的报表表头。分享给大家供大家参考，具体如下：
以前一直有需求，能把Execl里面的数据导入数据库，并且把数据库里面的数据导出到Execl中。
|
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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
|
require_once dirname(__FILE__) . '/../Classes/PHPExcel/IOFactory.php';class PHPExeclCore extends PHPExcel_IOFactory{ public static function SummerCreateExecl($Head,$data) { self::SummerCreateExeclHead($Head,$data,"Excel2007"); } public static function SummerReadExecl($dir) { if(!file_exists($dir)) { echo "Execl Not Exist"; } else { $PHPExeclObj = self::load($dir); $sheetCount = $PHPExeclObj->getSheetCount(); //得到Execl中包含的Sheet工作簿的数量 for($i=0;$i<$sheetCount;$i++) { $ActiveSheet = $PHPExeclObj->getSheet($i); $highestRow = $ActiveSheet->getHighestRow(); // 取得总列数 $allColumn = $ActiveSheet->getHighestColumn(); //通过嵌套循环来读取sheet工作簿里面的内容 for($Col='A';$Col<$allColumn;$Col++) { for($Row=1;$Row<$highestRow;$Row++) { $Data[$Col][$Row] = $ActiveSheet->getCell($Col.$Row)->getValue(); } } } } return $Data; } /* * 将数据写入到数据表中 * $Data Array 表示要插入进Execl数据 * $RuleData Array 表示数据格式的规则数组 * $i int 表示从第几行起的插入数据 * **/ public static function SummerInsertDateToExecl($sheet,$Head,$Data,$n=3,$RuleData=array()) { $SimpleHead = self::getHead($Head); $row = $n; foreach($Data as $key=>$valueArr) { $m = 0; foreach($valueArr as $k=>$v) { $StartCol = PHPExcel_Cell::stringFromColumnIndex($m).$row; $sheet->getCell($StartCol)->setValue($v); $sheet->getStyle($StartCol)->getAlignment()->applyFromArray( array( 'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => TRUE, ) ); if(isset($SimpleHead[$k]['col'])) { $m = $m + $SimpleHead[$k]['col']-1; $endCol = PHPExcel_Cell::stringFromColumnIndex($m).$row; $sheet->mergeCells($StartCol.":".$endCol); } $m++; $type = false; if(isset($SimpleHead[$k]['type'])) { $type = $SimpleHead[$k]['type']; $AllowArray = $SimpleHead[$k]['allowarray']; } //设置单元格的数据验证 if($type) { switch ($type) { case 'list': self::setSelectionRange($sheet, $StartCol,$AllowArray); break; case 'range': self::setValueRange($sheet, $StartCol,$AllowArray); break; } } } $row ++ ; } } /* * 生成Execl单元格备注 * $sheet 当前的工作簿对象 * $Cell 需要设置属性的单元格 * $content 备注内容 * */ private static function setComment($sheet,$Cell,$content) { $sheet->getComment($Cell)->setAuthor('4399om'); $objCommentRichText = $sheet->getComment($Cell)->getText()->createTextRun('4399om:'); $objCommentRichText->getFont()->setBold(true); $sheet->getComment($Cell)->getText()->createTextRun("\r\n"); $sheet->getComment($Cell)->getText()->createTextRun($content); $sheet->getComment($Cell)->setWidth('100pt'); $sheet->getComment($Cell)->setHeight('100pt'); $sheet->getComment($Cell)->setMarginLeft('150pt'); $sheet->getComment($Cell)->getFillColor()->setRGB('EEEEEE'); } /* * 现在单元格的有效数据范围，暂时仅限于数字 * $sheet 当前的工作簿对象 * $Cell 需要设置属性的单元格 * $ValueRange array 允许输入数组的访问 */ private static function setValueRange($sheet,$Cell,$ValueRange) { //设置单元格的的数据类型是数字，并且保留有效位数 $sheet->getStyle($Cell)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00); $ValueRange = explode(",",$ValueRange); //开始数值有效访问设定 $objValidation = $sheet->getCell($Cell)->getDataValidation(); $objValidation->setType( PHPExcel_Cell_DataValidation:: TYPE_WHOLE ); $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation:: STYLE_STOP ); $objValidation->setAllowBlank(true); $objValidation->setShowInputMessage( true); //设置显示提示信息 $objValidation->setShowErrorMessage( true); //设置显示错误信息 $objValidation->setErrorTitle('输入错误'); //错误标题 $objValidation->setError('请输入数据范围在从'.$ValueRange[0].'到'.$ValueRange[1].'之间的所有值'); //错误内容 $objValidation->setPromptTitle('允许输入'); //设置提示标题 $objValidation->setPrompt('请输入数据范围在从'.$ValueRange[0].'到'.$ValueRange[1].'之间的所有值'); //提示内容 $objValidation->setFormula1($ValueRange['0']); //设置最大值 $objValidation->setFormula2($ValueRange['1']); //设置最小值 } private static function OutinputHeader($objWriter) { $fileName = str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="'.$fileName.'"'); header("Content-Transfer-Encoding: binary"); header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: no-cache"); $objWriter->save('php://output'); exit; } //数据控制，设置单元格数据在一个可选方位类 private static function setSelectionRange($sheet,$Cell,$rangeStr,$Title="数据类型") { $objValidation = $sheet->getCell($Cell)->getDataValidation(); $objValidation -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST) -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_STOP) -> setAllowBlank(true) -> setShowInputMessage(true) -> setShowErrorMessage(true) -> setShowDropDown(true) -> setErrorTitle('输入的值有误') -> setError('您输入的值不在下拉框列表内.') -> setPromptTitle('"'.$Title.'"') -> setFormula1('"'.$rangeStr.'"'); } /* * 构建表头 * */ public static function RecursionCreateExecl($head,$data) { $PHPExecl = new PHPExcel(); $objWriter = self::createWriter($PHPExecl, 'Excel2007'); $PHPExecl->getProperties()->setCreator("4399om") ->setLastModifiedBy("Summer") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); $PHPExecl->setActiveSheetIndex(0); $sheet = $PHPExecl->getActiveSheet(); self::HandleHeadToNode($sheet, $head,1,0,0); self::SummerInsertDateToExecl($sheet,$head,$data,4); self::OutinputHeader($objWriter); } private static function HandleHeadToNode($sheet,$Head,$beginRow,$col,$StartCol) { foreach($Head as $key=>$cells) { $row = $beginRow; //表示行 $beginCol = PHPExcel_Cell::stringFromColumnIndex($col).$row; $sheet->getCell($beginCol)->setValue($cells['value']); //设置表格样式 $sheet->getStyle($beginCol)->getAlignment()->applyFromArray( array( 'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => TRUE, ) ); $sheet->getStyle($beginCol)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKGREEN); //设置单元格的宽度 if(isset($cells['width'])) { $Cell = $sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($col)); $Cell->setWidth($cells['width']); } //哥元素打上标记 if(isset($cells['Content'])) { self::setComment($sheet, $beginCol, $cells['Content']); } $merge = false; //合并单元格 if(isset($cells['col'])) { $col += $cells['col']-1; $merge = true; } if(isset($cells['row'])) { $row += $cells['row']-1; $merge = true; } if($merge) { $endCol = PHPExcel_Cell::stringFromColumnIndex($col).$row; $sheet->mergeCells($beginCol.":".$endCol); } $row ++; $col ++; //表示有存在孩子节点 if(isset($cells['children']) && is_array($cells['children'])){ $cols = $StartCol; if(!self::IsExistChildren($cells['children'])) { $cols = $col-2; $StartCol = $col; } self::HandleHeadToNode($sheet,$cells['children'],$row,$cols,$StartCol); }else{ $StartCol = $col; } } } //判断自己的孩子节点中是否存在孙子节点 private static function IsExistChildren($Data) { foreach($Data as $key=>$value) { if(isset($value['children']) && is_array($value['children'])) { return true; } } return false; } //获取底层数据 private static function getHead($Head,&$Node=array()) { foreach($Head as $key=>$value) { if(isset($value['children']) && is_array($value['children'])) { self::getHead($value['children'],$Node); } else { $Node[] = $value; } } return $Node; }}$Head = array( array('value'=>'姓名','col'=>2,'row'=>2,'width'=>20,'type'=>'list','allowarray'=>'PHP开发工程师,PHP开发'), array('value'=>'第一天','col'=>2,'row'=>1,'width'=>20,'Content'=>'2014-12-29号', 'children'=> array( array('value'=>'上午','col'=>1,'width'=>20,'type'=>'range','allowarray'=>'10,100'), array('value'=>'下午','width'=>20), ), ), array('value'=>'第二天','col'=>2,'row'=>1,'width'=>20, 'children'=> array( array('value'=>'上午','width'=>20), array('value'=>'下午','width'=>20), ), ),);$data = array( array('PHP开发工程师','12','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'), array('PHP开发工程师','25','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'), array('PHP开发工程师','50','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'), array('PHP开发工程师','99','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'), array('PHP开发工程师','10','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'), );$Node = PHPExeclCore::RecursionCreateExecl($Head,$data); |
得到的效果也基本符合需求：
希望本文所述对大家PHP程序设计有所帮助。
