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

凌霄玉阶非所愿

已于 2022-12-29 11:23:44 修改

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

分类专栏：前端 JS 文章标签： javascript excel json

于 2022-12-02 10:59:38 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_42064877/article/details/128144551

前端同时被 2 个专栏收录

37 篇文章

订阅专栏

10 篇文章

订阅专栏

本文介绍了一种使用JavaScript实现的将大量数据批量导出到Excel的方法，通过创建多个工作表来解决单个工作表行数限制的问题。适用于需要导出大量数据到Excel表格的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//导出方法
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
//<ss:Column ss:Width="120"/> 增加列宽
//jsonData为json数据集; wbname:文件名;appname:Excel
function tablesToExcel(jsonData, wbname, appname) {
    var url = 'data:application/vnd.ms-excel;base64,',
        tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
            + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
            + '<Styles>'
            + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
            + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
            + '</Styles>'
            + '{worksheets}</Workbook>'
        , tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table><ss:Column ss:Width="120"/><ss:Column ss:Width="120"/><ss:Column ss:Width="120"/><ss:Column ss:Width="120"/><ss:Column ss:Width="120"/><ss:Column ss:Width="120"/><ss:Column ss:Width="120"/>{rows}</Table></Worksheet>'
        , tmplCellXML = '<Cell><Data ss:Type="{nameType}">{data}</Data></Cell>'
        , 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 += '<Row>' +
                '<Cell><Data ss:Type="String">病案号</Data></Cell>' +
                '<Cell><Data ss:Type="String">患者姓名</Data></Cell>' +
                '<Cell><Data ss:Type="String">入院日期</Data></Cell>' +
                '<Cell><Data ss:Type="String">出院日期</Data></Cell>' +
                '<Cell><Data ss:Type="String">住院天数</Data></Cell>' +
                '<Cell><Data ss:Type="String">病种名称</Data></Cell>' +
                '<Cell><Data ss:Type="String">缺失类型</Data></Cell></Row>';
        }
        rowsXML += '<Row>';
        for (var key in jsonData[i]) {
            ctx = {
                nameType: 'String',
                data: jsonData[i][key] || "-"
            };
            rowsXML += format(tmplCellXML, ctx);
        }
        rowsXML += '</Row>';
        if (i > 0 && (i / 60000) % 1 === 0) {
            pil++;
            ctx = { rows: rowsXML, nameWS: '测试导出' + i };
            worksheetsXML += format(tmplWorksheetXML, ctx);
            rowsXML = "";
            rowsXML += '<Row>' +
                '<Cell><Data ss:Type="String">病案号</Data></Cell>' +
                '<Cell><Data ss:Type="String">患者姓名</Data></Cell>' +
                '<Cell><Data ss:Type="String">入院日期</Data></Cell>' +
                '<Cell><Data ss:Type="String">出院日期</Data></Cell>' +
                '<Cell><Data ss:Type="String">住院天数</Data></Cell>' +
                '<Cell><Data ss:Type="String">病种名称</Data></Cell>' +
                '<Cell><Data ss:Type="String">缺失类型</Data></Cell></Row>';
        }
    }
    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万条数据增加一个标题、工作区