js导出excel表格并生成多sheet（更改列宽）

//导出方法
function exclePort() {
    $.ajax({
        data: {
            ajaxMethod: 'ajaxExcelPort',  //自己封装的方法，调用后台方法
            fhspid: top.modemp.FCURRENTCODE,
            FDATES: $('#FDATES').val(),
            FDATEE: $('#FDATEE').val(),
            fmrdid: $("#Medicalrecord").val(),
            FID: $("#fidlink").text()
        },
        type: "post",
        dataType: 'json',
        cache: false,
        async: false,
        success: function (data) {
            var jsonData = data.dt;
            if (jsonData != null) {
                tablesToExcel(jsonData, '测试导出' + nowtime() + '.xls', 'Excel');
            }
        }
    });
}
 
 
 
//获取时间戳
function nowtime() {//将当前时间转换成yyyymmdd格式
    var mydate = new Date();
    var str = "" + mydate.getFullYear();
    var mm = mydate.getMonth() + 1
    if (mydate.getMonth() > 9) {
        str += mm;
    }
    else {
        str += "0" + mm;
    }
    if (mydate.getDate() > 9) {
        str += mydate.getDate();
    }
    else {
        str += "0" + mydate.getDate();
    }
    return str;
}
 
 
 
//导出excel包含多个sheet
// 增加列宽
//jsonData为json数据集; wbname:文件名;appname:Excel
function tablesToExcel(jsonData, wbname, appname) {
    var url = 'data:application/vnd.ms-excel;base64,',
        tmplWorkbookXML = ''
            + 'Axel Richter{created}'
            + ''
            + ''
            + ''
            + ''
            + '{worksheets}'
        , tmplWorksheetXML = '{rows}'
        , tmplCellXML = '{data}'
        , base64 = function (s) {
            return window.btoa(unescape(encodeURIComponent(s)))
        }
        , format = function (s, c) {
            return s.replace(/{(\w+)}/g, function (m, p) {
                return c[p];
            })
        }
 
    var ctx = "";
    var workbookXML = "";
    var worksheetsXML = "";
    var rowsXML = "";
 
    var pil = 0;
    for (var i = 0; i < jsonData.length; i++) {
        if (i == 0) {
            rowsXML += '' +
                '病案号' +
                '患者姓名' +
                '入院日期' +
                '出院日期' +
                '住院天数' +
                '病种名称' +
                '缺失类型';
        }
        rowsXML += '';
        for (var key in jsonData[i]) {
            ctx = {
                nameType: 'String',
                data: jsonData[i][key] || "-"
            };
            rowsXML += format(tmplCellXML, ctx);
        }
        rowsXML += '';
        if (i > 0 && (i / 60000) % 1 === 0) {
            pil++;
            ctx = { rows: rowsXML, nameWS: '测试导出' + i };
            worksheetsXML += format(tmplWorksheetXML, ctx);
            rowsXML = "";
            rowsXML += '' +
                '病案号' +
                '患者姓名' +
                '入院日期' +
                '出院日期' +
                '住院天数' +
                '病种名称' +
                '缺失类型';
        }
    }
    ctx = { rows: rowsXML, nameWS: '测试导出' };
    worksheetsXML += format(tmplWorksheetXML, ctx);
    rowsXML = "";
    ctx = { created: (new Date()).getTime(), worksheets: worksheetsXML };
    workbookXML = format(tmplWorkbookXML, ctx);
    var link = document.createElement("A");
    link.href = url + base64(workbookXML);
    link.download = wbname || 'Workbook.xls';
    link.target = '_blank';
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
}

注意：

因excel导出的条数限制,百度发现excel2003版最多导出6万多条,excel2007版最多导出10万多条.

因要导出超过十万条数据，所以以xcel2003为例子，每6万条数据增加一个标题、工作区