Mar 8, 2016 1 min read

Javascript导出Excel

引用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
};

fyn