Javascript导出Excel

  1. 引用ExcelBuilder
  2. 创建workbook
    var workbook = ExcelBuilder.createWorkbook();
  3. 创建worksheet
    var worksheet = workbook.createWorksheet({name: 'Sheet 1'});
    workbook.addWorksheet(worksheet);
  4. 加载数据
    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);
  5. 下载
    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
    };

     

 

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注