- 引用ExcelBuilder
- 创建workbook
var workbook = ExcelBuilder.createWorkbook();
- 创建worksheet
var worksheet = workbook.createWorksheet({name: 'Sheet 1'}); workbook.addWorksheet(worksheet);
- 加载数据
var data = [['Name','Gender','Age']]; data.push(['Zhang San','M', 24]); data.push(['Li Si','F', 18]); data.push(['Wang Wu','M', 22]); worksheet.setData(data);
- 下载
var blob = ExcelBuilder.createFile(workbook, {type:'blob'}); var url = URL.createObjectURL(blob); var a = document.createElement('A'); a.href = url; a.download = Date.now()+'.xlsx'; document.body.appendChild(a); a.click(); document.body.removeChild(a);
- 自动计算列宽
//定义每个单字节的宽度 var LETTER_WIDTH = 1.11; //定义每列最大宽度 var MAX_COLUMN_WIDTH = 80; //定义每列的最小宽度 var columns = [ {width: 10}, {width: 10}, {width: 10} ]; //添加数据时遍历每一行,计算最大宽度 var autoFitWidth = function(row) { $.each(row, function(i,str){ if(str!==null&&str!==undefined&&str!=='') { //英文占一个字节宽度,中文占两个 var match = str.toString().match(/[\\0-\\255]/ig); var en = match==null?0:match.length; var w = (str.length * 2 - en) * LETTER_WIDTH; columns[i].width = Math.min(Math.max(columns[i].width, w), MAX_COLUMN_WIDTH); } }); }; //遍历完数据后将列的设置添加到worksheet中 worksheet.setColumns(columns);
- 过滤特殊字符
//一些特殊字符会导致excel报错 var specialChar = /[\u0000-\u0008\u000b\u000c\u000d-\u001f]/ig; val = val.replace(specialChar,'');
- 自动换行,顶部对齐
//创建一种样式 var format1 = workbook.getStyleSheet().createFormat({ alignment:{ vertical:'top', wrapText: true } }); //填充数据,给单元格增加样式 var data = [ [ {value:'row1_col1\nline2\nline3','metadata':{'style':format1}}, {value:'row1_col2'}, 'row_col3' ], [ 'row2_col1','row2_col2','row2_col3' ] ]
- 全局默认样式
workbook.getStyleSheet().masterCellFormats[0].alignment ={ vertical:'top', wrapText: true };