分页导出 Java java分页导出excel

转载

陌陌香阁 2024-07-18 10:33:15

文章标签 分页导出 Java javascript 数据 xml List 文章分类 Java 后端开发

下载地址

链接：https://pan.baidu.com/s/1ce6oxhRMirMRmlSz8L8PLw
提取码：ws7c

版本

v2.0.0

进步

丢弃了jquery操作dom，由于大量耗费时间，效率严重低下，此版本改为字符替换代替dom操作。优点：

效率大大提高

可跨函数（ExcelUtils使用的都是静态属性，可超出代码块超出函数）分页（根据不同条件操作ExcelUtils工具进行添加其他sheet数据），最后一次导出

操作便捷，容易上手。

10万数据级别时间控制在5~10秒左右（具体根据数据对象的大小而定）

可自定义导出各个阶段的回调函数

缺点：

虽然百万级别数据能导出，但是仍然很吃力，下个版本将进行优化。

依然没有完全摆脱jquery的依赖，后期版本迭代将完全脱离jquery

代码

ExcelUtils.js

/**
 * author:gl
 * data:2020/7/2
 *js导出Excel
 */

/**
 * 默认的Point模型，用户遍历属性
 * @constructor
 */
function DefaultPoint() {
    this.x = null;
    this.y = null;
};

/**
 * table模型
 * @constructor
 */
function TableModel() {
    this.colNames = [];
    this.pointList = [];
};

/**
 * table模型所需函数
 * @type {{setPointList: TableModel.setPointList, getPointList: (function(): []), setColNames: TableModel.setColNames, getColNames: (function(): [])}}
 */
TableModel.prototype = {
    setColNames: function (names) {
        this.colNames = names;
    },
    getColNames: function () {
        return this.colNames;
    },
    setPointList: function (pointList) {
        this.pointList = pointList;
    },
    getPointList: function () {
        return this.pointList;
    }
};

/**
 * Excel导出工具类，核心类
 * @constructor
 */
function ExcelUtils() {}

/**
 * cell,row,sheet,workbook的xml
 * @type {{tmplCellXML: string, tmplWorksheetXML: string, tmplWorkbookXML: string, uri: string}}
 * ss:ExpandedColumnCount="256" 每页最多256列
 * ss:ExpandedRowCount="10000000" 每页最多1百万行
 */
ExcelUtils.paramXml = {
    //uri: 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,',
    uri: '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:o="urn:schemas-microsoft-com:office:office"
   xmlns:x="urn:schemas-microsoft-com:office:excel"
   xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
   xmlns:html="http://www.w3.org/TR/REC-html40">
   <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
    <Created>1996-12-17T01:32:42Z</Created>
   <LastSaved>2006-02-17T13:16:06Z</LastSaved>
   <Version>11.5606</Version>
  </DocumentProperties>
  <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
   <RemovePersonalInformation/>
  </OfficeDocumentSettings>
  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>4530</WindowHeight>
  <WindowWidth>8505</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <AcceptLabelsInFormulas/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s32">
        <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
        <Borders>' +
        <Border ss:Position="Bottom" ss:LineStyle="Dash" ss:Weight="1"/>
        </Borders>' +
        <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Bold="1"/>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s34">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font x:Family="Swiss" ss:Size="11" ss:Bold="1"/>
   <Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s35">
       <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
        <Font ss:FontName="宋体" x:CharSet="134" ss:Size="10"/>
  </Style>
 </Styles>
{worksheets}
</Workbook>
`,
    //    tmplWorksheetXML: '<Worksheet ss:Name="{nameWS}"><Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="22"ss:DefaultColumnWidth="156.75" ss:DefaultRowHeight="14.25">{column}{rows}</Table></Worksheet>',
    tmplWorksheetXML: `<Worksheet ss:Name="{nameWS}"> 
                                <Table ss:ExpandedColumnCount="256" ss:ExpandedRowCount="10000000" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
{column}{rows}</Table></Worksheet>`,
    tmplCellXML: `<Cell {attributeStyleID}{mergeAcross}><Data ss:Type="{nameType}">{data}</Data></Cell>`

};

/**用来缓存多个sheet的xml，便于一次导出**/
ExcelUtils.sheetsXml = [];

/**默认的Point**/
ExcelUtils.UserPointModel = DefaultPoint;

/**默认的列名**/
ExcelUtils.colNames = ['col01', 'col02'];

/**头信息格式[{text:xxx,colspan:1-9}]**/
ExcelUtils.addTableHeadJson = [];

/**
 * 添加caption部门，可以添加多个
 * @param headArr
 * @returns {ExcelUtils}
 */
ExcelUtils.addTableHead = function (headArr) {
    ExcelUtils.addTableHeadJson = headArr;

    return ExcelUtils;
};

/**
 * 将数据转为Html的table
 * @param tableModel
 * @returns {*|jQuery.fn.init|jQuery|HTMLElement}
 * version 1.0.0
 * 状态：丢弃
 * 原因：执行太慢
 */
/*ExcelUtils.tableModelConvertToTable = function (tableModel) {
    var starttime = new Date().getTime();
    var tableId = Math.random().toString(36);
    var $table = $('<table id="' + tableId + '"></table>');
    try {
        if (ExcelUtils.addTableHeadJson != null && ExcelUtils.addTableHeadJson != []) {
            $.each(ExcelUtils.addTableHeadJson, function (index, elem) {
                var $tr = $('<tr></tr>');
                var $td = $('<td colspan=' + elem.colspan + '>' + elem.text + '</td>');
                $tr.append($td);
                $table.append($tr);
            })
        }
        var $tr = $('<tr></tr>');
        //添加首行列名字
        $.each(tableModel.getColNames(), function (index, elem) {
            var $td = $('<td>' + elem + '</td>');
            $tr.append($td);
        })
        $table.append($tr);
        //数据填充table
        var stt = new Date().getTime();
        $.each(tableModel.getPointList(), function (index, point) {
            var $tr = $("<tr></tr>");
            $.each(Object.keys(point), function (index, attr) {
                var $td = $('<td>' + point[attr] + '</td>');
                $tr.append($td);
            })
            $table.append($tr);
        })
        console.log("数据填充花费时间：" + (new Date().getTime() - stt))
        // $("#mytable").append($table)
    } catch (e) {
        ExcelUtils.exceptionCall(e);
    }
    console.log("数据转为uHtml的table:" + (new Date().getTime() - starttime));
    return $table;
};*/
/**
 * 将数据转为Html的table
 * @param tableModel
 * @returns {*|jQuery.fn.init|jQuery|HTMLElement}
 * version 1.0.1
 */
ExcelUtils.tableModelConvertToTable = function (tableModel) {
    var tableId = Math.random().toString(36);
    var table = '<table id="' + tableId + '">{tr}</table>';
    try {
        if (!ExcelUtils.isEmpty(ExcelUtils.addTableHeadJson)) {
            $.each(ExcelUtils.addTableHeadJson, function (index, elem) {
                var tr = '<tr>{td}</tr>';
                var td = '<td colspan=' + elem.colspan + '>' + elem.text + '</td>'
                tr = ExcelUtils.formatStr(tr, td);
                table = ExcelUtils.formatStr(table, tr + '{tr}');
            })
        }
        var tr = '<tr>{td}</tr>';
        //添加首行列名字
        $.each(tableModel.getColNames(), function (index, elem) {
            var td = '<td>' + elem + '</td>';
            tr = ExcelUtils.formatStr(tr, td + '{td}');
        })
        tr = ExcelUtils.formatStr(tr, '');
        table = ExcelUtils.formatStr(table, tr + '{tr}');
        //数据填充table
        var strL = '';
        $.each(tableModel.getPointList(), function (index, point) {
            var tr = '<tr>{td}</tr>';
            $.each(Object.keys(point), function (index, attr) {
                var td = '<td>' + point[attr] + '</td>';
                tr = ExcelUtils.formatStr(tr, td + '{td}');
            })
            tr = ExcelUtils.formatStr(tr, '');
            strL += tr;
        })
        table = ExcelUtils.formatStr(table, strL);
    } catch (e) {
        ExcelUtils.exceptionCall(e);
    }
    var $table = $(table);

    return $table;
};
/**
 * 处理数据
 * @param userPointFunction
 * @param dataList
 * @returns {[]}
 */
ExcelUtils.getAxisData = function (userPointFunction, ...dataList) {
    var pointList = [];
    //$.each(dataList,function())
    try {
        if (dataList != null && dataList.length > 0) {
            //初始化模型列表
            $.each(dataList[0], function () {
                pointList.push(new userPointFunction())
            })
            //填充数据
            $.each(Object.keys(new userPointFunction()), function (index, attrName) {
                $.each(dataList[index], function (index, elem) {
                    var point = pointList[index];
                    point[attrName] = elem;
                })
            })
            return pointList;
        } else {
            throw new Error("数据数组不能为空");
        }
    } catch (e) {
        ExcelUtils.exceptionCall(e);
    }
};
/**
 * 将workbookxml转为base64数据
 * @param s
 * @returns {string}
 */
ExcelUtils.base64 = function (s) {
    var str = window.btoa(unescape(encodeURIComponent(s)))

    return str;
};

/**
 * {xxx}数据替换
 * @param s
 * @param c
 * @returns {*|void|string}
 */
ExcelUtils.format = function (s, c) {
    return s.replace(/{(\w+)}/g, function (m, p) {
        return c[p];
    })
};

/**
 * @param sheetName
 * @param table
 * @returns {string}
 */
ExcelUtils.formatStr = function (s, c) {
    return s.replace(/{(\w+)}/g, function (m, p) {
        return c;
    })
};
/**
 *将table对象转为Sheet
 * @param sheetName
 * @param table
 * @returns {string}
 */
ExcelUtils.tableConvertToSheet = function (sheetName, table) {
    var ctx = "";
    var worksheetsXML = "";
    var rowsXML = "";
    try {
        var table = table[0];
        var columnStr = '';
        //设置单元格宽度
        if (table.rows[ExcelUtils.addTableHeadJson.length].cells.length) {
            for (var j = 0; j < table.rows[ExcelUtils.addTableHeadJson.length].cells.length; j++) {
                columnStr += '<Column ss:Index="' + (j + 1) + '" ss:AutoFitWidth="0" ss:Width="156.75"/>'
            }
        }
        //控制要导出的行数
        for (var j = 0; j < table.rows.length; j++) {
            if (!ExcelUtils.isEmpty(ExcelUtils.addTableHeadJson) && j < ExcelUtils.addTableHeadJson.length) {
                rowsXML += '<Row ss:Height="26">';
            } else {
                rowsXML += '<Row ss:Height="20">';
            }
            for (var k = 0; k < table.rows[j].cells.length; k++) {
                var dataValue = table.rows[j].cells[k].innerHTML;
                var colspan = table.rows[j].cells[k].getAttribute("colspan");
                var styleId = 's35';
                if (!ExcelUtils.isEmpty(ExcelUtils.addTableHeadJson) && j < ExcelUtils.addTableHeadJson.length) {
                    styleId = 's32';
                } else if (j == ExcelUtils.addTableHeadJson.length) {
                    styleId = 's34';
                }
                ctx = {
                    attributeStyleID: (styleId != '') ? 'ss:StyleID="' + styleId + '"' : '',
                    nameType: 'String',
                    data: dataValue,
                    mergeAcross: (colspan) ? ' ss:MergeAcross="' + (colspan - 1) + '"' : '' //合并单元格
                };
                styleId = "";
                rowsXML += ExcelUtils.format(ExcelUtils.paramXml.tmplCellXML, ctx);
            }
            rowsXML += '</Row>'
        }

        ctx = {
            rows: rowsXML,
            nameWS: sheetName,
            column: columnStr
        };

        worksheetsXML += ExcelUtils.format(ExcelUtils.paramXml.tmplWorksheetXML, ctx);
        //  rowsXML = "";
    } catch (e) {
        ExcelUtils.exceptionCall(e);
    }

    return worksheetsXML;
};

/**
 * 判断字符和数组
 * @param data
 * @returns {boolean}
 */
ExcelUtils.isEmpty = function (data) {
    if (data === undefined)
        return true;

    var type = typeof data;

    if (type === 'object') {
        if (typeof data.length === 'number')
            type = 'array';
        else
            type = 'object';
    }

    switch (type) {
        case 'array':
            if (data === undefined || data.length <= 0)
                return true;
            else
                return false;
        case 'string':
            if (data === undefined || data.length() <= 0)
                return true;
            else
                return false;
        default:
            throw new Error('Unknown type');
    }
}

/**
 *
 * @param sheetName 单个sheet的名称
 * @param userPointFunction 用户自定义的Point
 * @param colNames 列名数组
 * @param dataList 每列的数据数组
 * @returns {ExcelUtils} 单个sheetXml
 */
ExcelUtils.addSheet = function (sheetName, userPointFunction, colNames, ...dataList) {
    try {
        var pointList = ExcelUtils.getAxisData(userPointFunction, ...dataList);
        var tableModel = new TableModel();
        tableModel.setPointList(pointList);
        tableModel.setColNames(colNames || ExcelUtils.colNames);
        var $table = ExcelUtils.tableModelConvertToTable(tableModel);
        var sheetXml = ExcelUtils.tableConvertToSheet(sheetName, $table);
        ExcelUtils.sheetsXml.push(sheetXml);
    } catch (e) {
        ExcelUtils.exceptionCall(e);
    }

    return ExcelUtils;
};

/**
 * 下载Excel
 * @param fileName Excel名称
 * @param workbookXML 整个ExcelXml
 */
ExcelUtils.downExcel = function (fileName, workbookXML) {
    try {
        var link = document.createElement("A");
        link.href = ExcelUtils.paramXml.uri + ExcelUtils.base64(workbookXML);
        link.download = fileName || 'Workbook.xlsx';
        link.target = '_blank';
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    } catch (e) {
        ExcelUtils.exceptionCall(e);
    }
};

/**
 * 清除数据，以及恢复默认值
 */
ExcelUtils.clear = function () {
    ExcelUtils.sheetsXml = [];
    ExcelUtils.UserPointModel = DefaultPoint;
    ExcelUtils.colNames = ['x轴', 'y轴'];
    ExcelUtils.executeExceptionCall = ExcelUtils.defaultExceptionCall;
    ExcelUtils.beforeExecute = ExcelUtils.defaultBeforeExecute;
    ExcelUtils.afterExecute = ExcelUtils.defaultAfterExecute;
};

/**
 * 导出函数，执行一些列的导出工作
 * @param fileName
 */
ExcelUtils.export = function (fileName) {
    try {
        var strXml = '';
        $.each(ExcelUtils.sheetsXml, function (index, xml) {
            strXml += xml;
        })
        var ctx = {
            created: (new Date()).getTime(),
            worksheets: strXml
        };
        var workbookXML = ExcelUtils.format(ExcelUtils.paramXml.tmplWorkbookXML, ctx);
        ExcelUtils.downExcel(fileName, workbookXML);
    } catch (e) {
        ExcelUtils.exceptionCall(e);
        ExcelUtils.end();
    }

    return ExcelUtils;
};

/**
 * 默认异常回调执行函数
 */
ExcelUtils.defaultExceptionCall = function (e) {
    console.log("导出Excel出现异常:" + e);
};

/**
 * 默认的异常回调函数
 * @type {ExcelUtils.defaultExceptionCall|(function(*): void)}
 */
ExcelUtils.executeExceptionCall = ExcelUtils.defaultExceptionCall;

/**
 * 异常回调函数
 */
ExcelUtils.exceptionCall = function (e) {
    ExcelUtils.executeExceptionCall(e);
};

/**
 * 设置自定义异常回调函数
 */
ExcelUtils.setExceptionCall = function (fn) {
    ExcelUtils.executeExceptionCall = fn;
    return ExcelUtils;
};

/**
 * 默认的导出前执行的任务
 */
ExcelUtils.defaultBeforeExecute = function () {
    console.log("Excel开始导出......");
};
/**
 * 执行前执行函数
 */
ExcelUtils.beforeExecute = ExcelUtils.defaultBeforeExecute;

/**
 * 默认的导出完成后执行的任务
 */
ExcelUtils.defaultAfterExecute = function () {
    console.log("Excel导出结束......");
};
/**
 * 导出后执行函数
 * @param fn
 */
ExcelUtils.afterExecute = ExcelUtils.defaultAfterExecute;

/**
 * 导出前必须限制性此函数
 * 开始预执行函数
 * @returns {ExcelUtils}
 */
ExcelUtils.start = function () {
    ExcelUtils.beforeExecute();
    return ExcelUtils;
};

/**
 * 最后必须执行此函数
 * 结束预执行函数
 */
ExcelUtils.end = function () {
    ExcelUtils.afterExecute();
    //清除缓存数据
    ExcelUtils.clear();
};

/**
 * 设置导出前需要执行的任务
 * @param fn
 * @returns {ExcelUtils}
 */
ExcelUtils.setBeforeExecute = function (fn) {
    ExcelUtils.beforeExecute = fn;
    return ExcelUtils;
};

/**
 * 设置导出完成后需要执行的任务
 * @param fn
 * @returns {ExcelUtils}
 */
ExcelUtils.setAfterExecute = function (fn) {
    ExcelUtils.afterExecute = fn;
    return ExcelUtils;
};

thread.js

var threadCount=0;
function Runnable(task){
    this.run=task;
};
 
function Thread(runnable){
    var sleep=0;
    var name="Thread-"+(threadCount++);
    var target=runnable;
    //私有变量，默认异常处理
    var uncaughtExceptionHanlder=function(e){
        console.log(e.message);
    };
    this.getName=function(){
        return name;
    };
    this.setName=function(threadName){
        name=threadName;
    };
    this.start=function(){
        setTimeout(this.run,sleep);
    };
    this.setSleep=function(value){
        sleep=value;
    };
    this.run = function () {
        if (target != null) {
            try {
                threadSleep(sleep);
                target.run();
            } catch (e) {
                uncaughtExceptionHanlder(e);
            }
            return;
        }
        console.log('Exception:{线程名称："' + name + '",错误定位:"thread.js",错误信息："target对象为null,没有可执行的任务."}');
    };
    this.setUncaughtExceptionHanlder=function(exception){
        uncaughtExceptionHanlder=exception;
    }
};
 
//线程暂停
function threadSleep(millions) {
  for (var t = Date.now(); Date.now() - t <= millions;);
};
 
//继承
Thread.prototype=new Runnable();

test.html

<!DOCTYPE html>
<html lang="en">

<head>
       
    <meta charset="UTF-8">
    <title></title>
    <script src="./ExcelUtils.js"></script>
    <script src="./thread.js"></script>
    <script src="https://cdn.staticfile.org/jquery/1.10.2/jquery.min.js">
</script>
</head>

<body>
    <label for="count">输入导出的记录数</label>
    <input id="count" />万
    <button id="export">导出</button>
    
<script>
           
  function btnClickHandle(){
        /**
   * addTableHead     text: 单元格内容 colspan: 该单元格合并几列
   * addSheet   第一个参数是sheet名称，
   * 第二个必须传一个与所要列数一直的对象函数，
   * 第三个参数，列名数组，
   * 第四个参数数据数组（数组顺序就是数据展示顺序）
   * 可链式添加
   */
  function UserPoint() {
    this.x = null;
    this.y = null;
    this.z = null;
    this.o = null;
  }
  function UserPoint2() {
    this.x = null;
    this.y = null;
    this.z = null;
  }

  let headArr1 = [{
    text: "设备名称：波音",
    colspan: 4
  }, {
    text: '时间：2222-22-22',
    colspan: 4
  }, {
    text: '数据类型:xxx',
    colspan: 4
  }];
  let headArr2 = [{
    text: "设备名称：宇宙飞船",
    colspan: 3
  }, {
    text: '时间：2222-22-22',
    colspan: 3
  }, {
    text: '数据类型：xxx',
    colspan: 3
  }];

 const count = document.querySelector('#count').value;
 let dataArr=[...new Array(count*10000).keys()]
  let runnable = new Runnable(() => {
    ExcelUtils
    .setBeforeExecute(() => {
      console.log("我开始执行了")
    })
    .setExceptionCall((e) => {
        console.log(e)
    })
    .setAfterExecute(() => {
      console.log("我结束执行了")
    })
    .start()
    .addTableHead(headArr1)
    .addSheet("波音数据", UserPoint, ['舱门老化', '羽翼新旧', '机油', '船重'],
        dataArr, dataArr, dataArr, dataArr)
    .addTableHead(headArr2)
    .addSheet("宇宙飞船数据", UserPoint2, ['出门', '飞行', '速度'],
        dataArr, dataArr, dataArr)
    .export("波音和宇宙飞船数据")
    .end();
  })
  
  let thread = new Thread(runnable);
  thread.start();
  }
     
   //绑定导出按钮     
    function bindEvent(){
    const exportBtn = document.querySelector('#export');
        exportBtn.addEventListener('click', btnClickHandle, false);
    }

    
    const init = () =>{
        bindEvent();
    };
        
    init();
    </script>
</body>       

</html>