1 /**
2 * Excel导出工具类，核心类
3 * @constructor
4 */
5 function ExcelUtils() {}
6
7 /**
8 * cell,row,sheet,workbook的xml
9 * @type {{tmplCellXML: string, tmplWorksheetXML: string, tmplWorkbookXML: string, uri: string}}
10 * ss:ExpandedColumnCount="256" 每页最多256列
11 * ss:ExpandedRowCount="10000000" 每页最多1百万行
12 */
13 ExcelUtils.paramXml = {
14 //uri: 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,',
15 uri: 'data:application/vnd.ms-excel;base64,',
16 tmplWorkbookXML: `<?xml version="1.0"?>
17 <?mso-application progid="Excel.Sheet"?>
18 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
19 xmlns:o="urn:schemas-microsoft-com:office:office"
20 xmlns:x="urn:schemas-microsoft-com:office:excel"
21 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
22 xmlns:html="http://www.w3.org/TR/REC-html40">
23 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
24 <Created>1996-12-17T01:32:42Z</Created>
25 <LastSaved>2006-02-17T13:16:06Z</LastSaved>
26 <Version>11.5606</Version>
27 </DocumentProperties>
28 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
29 <RemovePersonalInformation/>
30 </OfficeDocumentSettings>
31 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
32 <WindowHeight>4530</WindowHeight>
33 <WindowWidth>8505</WindowWidth>
34 <WindowTopX>480</WindowTopX>
35 <WindowTopY>120</WindowTopY>
36 <AcceptLabelsInFormulas/>
37 <ProtectStructure>False</ProtectStructure>
38 <ProtectWindows>False</ProtectWindows>
39 </ExcelWorkbook>
40 <Styles>
41 <Style ss:ID="Default" ss:Name="Normal">
42 <Alignment ss:Vertical="Bottom"/>
43 <Borders/>
44 <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
45 <Interior/>
46 <NumberFormat/>
47 <Protection/>
48 </Style>
49 <Style ss:ID="s32">
50 <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
51 <Borders>' +
52 <Border ss:Position="Bottom" ss:LineStyle="Dash" ss:Weight="1"/>
53 </Borders>' +
54 <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Bold="1"/>
55 <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
56 </Style>
57 <Style ss:ID="s34">
58 <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
59 <Borders>
60 <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
61 <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
62 <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
63 <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
64 </Borders>
65 <Font x:Family="Swiss" ss:Size="11" ss:Bold="1"/>
66 <Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
67 </Style>
68 <Style ss:ID="s35">
69 <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
70 <Font ss:FontName="宋体" x:CharSet="134" ss:Size="10"/>
71 </Style>
72 </Styles>
73 {worksheets}
74 </Workbook>
75 `,
76 // tmplWorksheetXML: '<Worksheet ss:Name="{nameWS}"><Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="22"ss:DefaultColumnWidth="156.75" ss:DefaultRowHeight="14.25">{column}{rows}</Table></Worksheet>',
77 tmplWorksheetXML: `<Worksheet ss:Name="{nameWS}">
78 <Table ss:ExpandedColumnCount="256" ss:ExpandedRowCount="10000000" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
79 {column}{rows}</Table></Worksheet>`,
80 tmplCellXML: `<Cell {attributeStyleID}{mergeAcross}><Data ss:Type="{nameType}">{data}</Data></Cell>`
81
82 };
83
84 /**用来缓存多个sheet的xml，便于一次导出**/
85 ExcelUtils.sheetsXml = [];
86
87 /**默认的Point**/
88 ExcelUtils.UserPointModel = DefaultPoint;
89
90 /**默认的列名**/
91 ExcelUtils.colNames = ['col01', 'col02'];
92
93 /**头信息格式[{text:xxx,colspan:1-9}]**/
94 ExcelUtils.addTableHeadJson = [];
95
96 /**
97 * 添加caption部门，可以添加多个
98 * @param headArr
99 * @returns {ExcelUtils}
100 */
101 ExcelUtils.addTableHead = function (headArr) {
102 ExcelUtils.addTableHeadJson = headArr;
103
104 return ExcelUtils;
105 };
106
107 /**
108 * 将数据转为Html的table
109 * @param tableModel
110 * @returns {*|jQuery.fn.init|jQuery|HTMLElement}
111 * version 1.0.0
112 * 状态：丢弃
113 * 原因：执行太慢
114 */
115 /*ExcelUtils.tableModelConvertToTable = function (tableModel) {
116 var starttime = new Date().getTime();
117 var tableId = Math.random().toString(36);
118 var $table = $('<table id="' + tableId + '"></table>');
119 try {
120 if (ExcelUtils.addTableHeadJson != null && ExcelUtils.addTableHeadJson != []) {
121 $.each(ExcelUtils.addTableHeadJson, function (index, elem) {
122 var $tr = $('<tr></tr>');
123 var $td = $('<td colspan=' + elem.colspan + '>' + elem.text + '</td>');
124 $tr.append($td);
125 $table.append($tr);
126 })
127 }
128 var $tr = $('<tr></tr>');
129 //添加首行列名字
130 $.each(tableModel.getColNames(), function (index, elem) {
131 var $td = $('<td>' + elem + '</td>');
132 $tr.append($td);
133 })
134 $table.append($tr);
135 //数据填充table
136 var stt = new Date().getTime();
137 $.each(tableModel.getPointList(), function (index, point) {
138 var $tr = $("<tr></tr>");
139 $.each(Object.keys(point), function (index, attr) {
140 var $td = $('<td>' + point[attr] + '</td>');
141 $tr.append($td);
142 })
143 $table.append($tr);
144 })
145 console.log("数据填充花费时间：" + (new Date().getTime() - stt))
146 // $("#mytable").append($table)
147 } catch (e) {
148 ExcelUtils.exceptionCall(e);
149 }
150 console.log("数据转为uHtml的table:" + (new Date().getTime() - starttime));
151 return $table;
152 };*/
153 /**
154 * 将数据转为Html的table
155 * @param tableModel
156 * @returns {*|jQuery.fn.init|jQuery|HTMLElement}
157 * version 1.0.1
158 */
159 ExcelUtils.tableModelConvertToTable = function (tableModel) {
160 var tableId = Math.random().toString(36);
161 var table = '<table id="' + tableId + '">{tr}</table>';
162 try {
163 if (!ExcelUtils.isEmpty(ExcelUtils.addTableHeadJson)) {
164 $.each(ExcelUtils.addTableHeadJson, function (index, elem) {
165 var tr = '<tr>{td}</tr>';
166 var td = '<td colspan=' + elem.colspan + ' rowspan=4>' + elem.text + '</td>'
167 tr = ExcelUtils.formatStr(tr, td);
168 table = ExcelUtils.formatStr(table, tr + '{tr}');
169 })
170 }
171 var tr = '<tr>{td}</tr>';
172 //添加首行列名字
173 $.each(tableModel.getColNames(), function (index, elem) {
174 var td = '<td>' + elem + '</td>';
175 tr = ExcelUtils.formatStr(tr, td + '{td}');
176 })
177 tr = ExcelUtils.formatStr(tr, '');
178 table = ExcelUtils.formatStr(table, tr + '{tr}');
179 //数据填充table
180 var strL = '';
181 $.each(tableModel.getPointList(), function (index, point) {
182 var tr = '<tr>{td}</tr>';
183 $.each(Object.keys(point), function (index, attr) {
184 var td = '<td>' + point[attr] + '</td>';
185 tr = ExcelUtils.formatStr(tr, td + '{td}');
186 })
187 tr = ExcelUtils.formatStr(tr, '');
188 strL += tr;
189 })
190 table = ExcelUtils.formatStr(table, strL);
191 } catch (e) {
192 ExcelUtils.exceptionCall(e);
193 }
194 var $table = $(table);
195
196 return $table;
197 };
198 /**
199 * 处理数据
200 * @param userPointFunction
201 * @param dataList
202 * @returns {[]}
203 */
204 ExcelUtils.getAxisData = function (userPointFunction, ...dataList) {
205 var pointList = [];
206 //$.each(dataList,function())
207 try {
208 if (dataList != null && dataList.length > 0) {
209 //初始化模型列表
210 $.each(dataList[0], function () {
211 pointList.push(new userPointFunction())
212 })
213 //填充数据
214 $.each(Object.keys(new userPointFunction()), function (index, attrName) {
215 $.each(dataList[index], function (index, elem) {
216 var point = pointList[index];
217 point[attrName] = elem;
218 })
219 })
220 return pointList;
221 } else {
222 throw new Error("数据数组不能为空");
223 }
224 } catch (e) {
225 ExcelUtils.exceptionCall(e);
226 }
227 };
228 /**
229 * 将workbookxml转为base64数据
230 * @param s
231 * @returns {string}
232 */
233 ExcelUtils.base64 = function (s) {
234 var str = window.btoa(decodeURI(encodeURIComponent(s)))
235
236 return str;
237 };
238
239 /**
240 * {xxx}数据替换
241 * @param s
242 * @param c
243 * @returns {*|void|string}
244 */
245 ExcelUtils.format = function (s, c) {
246 return s.replace(/{(\w+)}/g, function (m, p) {
247 return c[p];
248 })
249 };
250
251 /**
252 * @param sheetName
253 * @param table
254 * @returns {string}
255 */
256 ExcelUtils.formatStr = function (s, c) {
257 return s.replace(/{(\w+)}/g, function (m, p) {
258 return c;
259 })
260 };
261 /**
262 *将table对象转为Sheet
263 * @param sheetName
264 * @param table
265 * @returns {string}
266 */
267 ExcelUtils.tableConvertToSheet = function (sheetName, table) {
268 var ctx = "";
269 var worksheetsXML = "";
270 var rowsXML = "";
271 try {
272 var table = table[0];
273 var columnStr = '';
274 //设置单元格宽度
275 if (table.rows[ExcelUtils.addTableHeadJson.length].cells.length) {
276 for (var j = 0; j < table.rows[ExcelUtils.addTableHeadJson.length].cells.length; j++) {
277 columnStr += '<Column ss:Index="' + (j + 1) + '" ss:AutoFitWidth="0" ss:Width="156.75"/>'
278 }
279 }
280 //控制要导出的行数
281 for (var j = 0; j < table.rows.length; j++) {
282 if (!ExcelUtils.isEmpty(ExcelUtils.addTableHeadJson) && j < ExcelUtils.addTableHeadJson.length) {
283 rowsXML += '<Row ss:Height="26">';
284 } else {
285 rowsXML += '<Row ss:Height="20">';
286 }
287 for (var k = 0; k < table.rows[j].cells.length; k++) {
288 var dataValue = table.rows[j].cells[k].innerHTML;
289 var colspan = table.rows[j].cells[k].getAttribute("colspan");
290 var styleId = 's35';
291 if (!ExcelUtils.isEmpty(ExcelUtils.addTableHeadJson) && j < ExcelUtils.addTableHeadJson.length) {
292 styleId = 's32';
293 } else if (j == ExcelUtils.addTableHeadJson.length) {
294 styleId = 's34';
295 }
296 ctx = {
297 attributeStyleID: (styleId != '') ? 'ss:StyleID="' + styleId + '"' : '',
298 nameType: 'String',
299 data: dataValue,
300 mergeAcross: (colspan) ? ' ss:MergeAcross="' + (colspan - 1) + '"' : '' //合并单元格
301 };
302 styleId = "";
303 rowsXML += ExcelUtils.format(ExcelUtils.paramXml.tmplCellXML, ctx);
304 }
305 rowsXML += '</Row>'
306 }
307
308 ctx = {
309 rows: rowsXML,
310 nameWS: sheetName,
311 column: columnStr
312 };
313
314 worksheetsXML += ExcelUtils.format(ExcelUtils.paramXml.tmplWorksheetXML, ctx);
315 // rowsXML = "";
316 } catch (e) {
317 ExcelUtils.exceptionCall(e);
318 }
319
320 return worksheetsXML;
321 };
322
323 /**
324 * 判断字符和数组
325 * @param data
326 * @returns {boolean}
327 */
328 ExcelUtils.isEmpty = function (data) {
329 if (data === undefined)
330 return true;
331
332 var type = typeof data;
333
334 if (type === 'object') {
335 if (typeof data.length === 'number')
336 type = 'array';
337 else
338 type = 'object';
339 }
340
341 switch (type) {
342 case 'array':
343 if (data === undefined || data.length <= 0)
344 return true;
345 else
346 return false;
347 case 'string':
348 if (data === undefined || data.length() <= 0)
349 return true;
350 else
351 return false;
352 default:
353 throw new Error('Unknown type');
354 }
355 }
356
357 /**
358 *
359 * @param sheetName 单个sheet的名称
360 * @param userPointFunction 用户自定义的Point
361 * @param colNames 列名数组
362 * @param dataList 每列的数据数组
363 * @returns {ExcelUtils} 单个sheetXml
364 */
365 ExcelUtils.addSheet = function (sheetName, userPointFunction, colNames, ...dataList) {
366
367 try {
368 var pointList = ExcelUtils.getAxisData(userPointFunction, ...dataList);
369 var tableModel = new TableModel();
370 tableModel.setPointList(pointList);
371 tableModel.setColNames(colNames || ExcelUtils.colNames);
372 var $table = ExcelUtils.tableModelConvertToTable(tableModel);
373 var sheetXml = ExcelUtils.tableConvertToSheet(sheetName, $table);
374 ExcelUtils.sheetsXml.push(sheetXml);
375 } catch (e) {
376 ExcelUtils.exceptionCall(e);
377 }
378
379 return ExcelUtils;
380 };
381
382 /**
383 * 下载Excel
384 * @param fileName Excel名称
385 * @param workbookXML 整个ExcelXml
386 */
387 ExcelUtils.downExcel = function (fileName, workbookXML) {
388 try {
389 var link = document.createElement("A");
390 link.href = ExcelUtils.paramXml.uri + ExcelUtils.base64(workbookXML);
391 link.download = fileName || 'Workbook.xlsx';
392 link.target = '_blank';
393 document.body.appendChild(link);
394 link.click();
395 document.body.removeChild(link);
396 } catch (e) {
397 ExcelUtils.exceptionCall(e);
398 }
399 };
400
401 /**
402 * 清除数据，以及恢复默认值
403 */
404 ExcelUtils.clear = function () {
405 ExcelUtils.sheetsXml = [];
406 ExcelUtils.UserPointModel = DefaultPoint;
407 ExcelUtils.colNames = ['x轴', 'y轴'];
408 ExcelUtils.executeExceptionCall = ExcelUtils.defaultExceptionCall;
409 ExcelUtils.beforeExecute = ExcelUtils.defaultBeforeExecute;
410 ExcelUtils.afterExecute = ExcelUtils.defaultAfterExecute;
411 };
412
413 /**
414 * 导出函数，执行一些列的导出工作
415 * @param fileName
416 */
417 ExcelUtils.export = function (fileName) {
418 try {
419 var strXml = '';
420 $.each(ExcelUtils.sheetsXml, function (index, xml) {
421 strXml += xml;
422 })
423 var ctx = {
424 created: (new Date()).getTime(),
425 worksheets: strXml
426 };
427 var workbookXML = ExcelUtils.format(ExcelUtils.paramXml.tmplWorkbookXML, ctx);
428 ExcelUtils.downExcel(fileName, workbookXML);
429 } catch (e) {
430 ExcelUtils.exceptionCall(e);
431 ExcelUtils.end();
432 }
433
434 return ExcelUtils;
435 };
436
437 /**
438 * 默认异常回调执行函数
439 */
440 ExcelUtils.defaultExceptionCall = function (e) {
441 console.log("导出Excel出现异常:" + e);
442 };
443
444 /**
445 * 默认的异常回调函数
446 * @type {ExcelUtils.defaultExceptionCall|(function(*): void)}
447 */
448 ExcelUtils.executeExceptionCall = ExcelUtils.defaultExceptionCall;
449
450 /**
451 * 异常回调函数
452 */
453 ExcelUtils.exceptionCall = function (e) {
454 ExcelUtils.executeExceptionCall(e);
455 };
456
457 /**
458 * 设置自定义异常回调函数
459 */
460 ExcelUtils.setExceptionCall = function (fn) {
461 ExcelUtils.executeExceptionCall = fn;
462 return ExcelUtils;
463 };
464
465 /**
466 * 默认的导出前执行的任务
467 */
468 ExcelUtils.defaultBeforeExecute = function () {
469 console.log("Excel开始导出......");
470 };
471 /**
472 * 执行前执行函数
473 */
474 ExcelUtils.beforeExecute = ExcelUtils.defaultBeforeExecute;
475
476 /**
477 * 默认的导出完成后执行的任务
478 */
479 ExcelUtils.defaultAfterExecute = function () {
480 console.log("Excel导出结束......");
481 };
482 /**
483 * 导出后执行函数
484 * @param fn
485 */
486 ExcelUtils.afterExecute = ExcelUtils.defaultAfterExecute;
487
488 /**
489 * 导出前必须限制性此函数
490 * 开始预执行函数
491 * @returns {ExcelUtils}
492 */
493 ExcelUtils.start = function () {
494 ExcelUtils.beforeExecute();
495 return ExcelUtils;
496 };
497
498 /**
499 * 最后必须执行此函数
500 * 结束预执行函数
501 */
502 ExcelUtils.end = function () {
503 ExcelUtils.afterExecute();
504 //清除缓存数据
505 ExcelUtils.clear();
506 };
507
508 /**
509 * 设置导出前需要执行的任务
510 * @param fn
511 * @returns {ExcelUtils}
512 */
513 ExcelUtils.setBeforeExecute = function (fn) {
514 ExcelUtils.beforeExecute = fn;
515 return ExcelUtils;
516 };
517
518 /**
519 * 设置导出完成后需要执行的任务
520 * @param fn
521 * @returns {ExcelUtils}
522 */
523 ExcelUtils.setAfterExecute = function (fn) {
524 ExcelUtils.afterExecute = fn;
525 return ExcelUtils;
526 };
