exceljs 表合并导出

exceljs 表合并导出

/**
 * 导出
 * sheetList 工作表 Array eg：['sheet1','sheet2']
 * dataList 数据 Array eg：[[{key:1,name:'李红',value:19}],[{h1:'',h2:''}]]
 * headerList 表头 Array eg：[{key:'键值',name:'名称',value:'数值'},{h1:'字段名1',h2:'字段名2'}]
 * mergeList 合并项 Array eg：[{name:String 合并的项的共同名称,start:Number 合并开始的 index,total:Number 合并共多少项},{}]
 * mergeLength 合并的列数 Array eg：[6,1] （只A1合并）
 * fileName 表名
 * */
import ExcelJS from "exceljs"; // 画表
// import saveAs from "file-saver"; // 导出

export const exportFunDefault = (sheetList, dataList, headerList, mergeList, mergeLength, fileName) => {
    const workbook = new ExcelJS.Workbook();
    let font = {
        name: '微软雅黑',
        size: 10,
        bold: true
    }
    // 添加工作表，可以添加多个工作表
    for (let i = 0; i < sheetList.length; i++) {
        let worksheet = workbook.addWorksheet(sheetList[i]);
        let columns = [], rows = [] //表头
        let data = dataList[i] //数据
        for (let j in headerList[i]) {
            columns.push({header: headerList[i][j], key: j})
        }
        worksheet.columns = columns
        let cols = {}

        for (let j = 0; j < data.length; j++) {
            worksheet.addRow(data[j], 'i')
        }
        let head = {}, index = 1
        for (let x in headerList[i]) {
            head[x] = {index: index}
            index++
        }
        for (let j in data[0]) {
            if (headerList[i][j]) {
                cols[head[j].index] = {'width': 18}
                if (data[0][j]) {
                    if (data[0][j].toString().charCodeAt(0) > 255) {
                        cols[head[j].index].width = data[0][j].toString().length * 2 + 10
                    } else cols[head[j].index].width = data[0][j].toString().length + 10
                }
            }
        }
        //默认宽
        colWidth(worksheet, cols)
        let arr = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P']
        // 合并单元格
        if (mergeList.length > 0) {
            for (let j in mergeList[i]) {
                if (mergeList[i][j].total > 1) {
                    for (let o = 0; o < mergeLength[i]; o++) {
                        // merge by start row, start column, end row, end column (equivalent to K10:M12)
                        // worksheet.mergeCells(10,11,12,13);
                        worksheet.mergeCells(mergeList[i][j].start + 1, o + 1, mergeList[i][j].start + mergeList[i][j].total, o + 1);
                    }
                }
            }
        }
        worksheet.getRow(1).font = font
        // 行居中
        rowCenter(worksheet, 1 + headerList[i].length, data.length)
    }

    /**
     *  设置start-end行单元格水平垂直居中/添加边框
     * @param arg_ws workSheet 参数
     * @param arg_start 开始行
     * @param arg_end 结束结束行
     */
    function rowCenter(arg_ws, arg_start, arg_end) {
        // eslint-disable-next-line no-undef,no-unmodified-loop-condition
        for (let i = arg_start; i <= arg_end; i++) {
            arg_ws.findRow(i).alignment = {vertical: 'middle', horizontal: 'center'}
            // eslint-disable-next-line no-irregular-whitespace
            // 循环 row 中的　cell，给每个 cell添加边框
            arg_ws.findRow(i).eachCell(function (cell, index) {
                cell.border = {
                    top: {style: 'thin'},
                    left: {style: 'thin'},
                    bottom: {style: 'thin'},
                    right: {style: 'thin'}
                }
            })
        }
    }

    // eslint-disable-next-line no-irregular-whitespace
    // 设置　start-end 列的宽度
    /**
     * 设置行宽
     * @param arg_ws workSheet
     * @param arg_cols 列数组
     */
    function colWidth(arg_ws, arg_cols) {
        for (const i in arg_cols) {
            arg_ws.getColumn(parseInt(i)).width = arg_cols[i].width
        }
    }

   // workbook.xlsx.writeBuffer().then((buffer) => {
   //     saveAs(
   //         new Blob([buffer], {type: "application/octet-stream"}), fileName + ".xlsx"
   //     );
   // });
   let buffer = workbook.xlsx.writeBuffer()
   // let ab = Buffer.from(buffer, "binary");
   // const blob = new Blob([ab], {
   //    type: 'xlsx'
   // });
   // return blob
   return buffer
}

 	/**
     * 导出数据处理
     * */
    async exportData(sheets) {
        let workbook = new ExcelJS.Workbook();
        if (!Array.isArray(sheets)) {
            sheets = [sheets];
        }
        // sheetData={sheet:'表名sheet1',data:[{name:'',value:0}],header:{name:'名称',value:'值'},fileName:'超限车辆排名'}
        for (let [sIndex, sheetData] of sheets.entries()) {
            let { sheet, data, header, fileName } = sheetData
            let worksheet = workbook.addWorksheet(sheet);

            // 表头
            worksheet.columns = lodash.map(header, (value, key) => {
                return { header: value, key }
            })
            // 数据行
            lodash.forEach(data, (value, key) => {
                worksheet.addRow(value, 'i')
            })

            // 设置表头字体样式
            worksheet.getRow(1).font = { name: '微软雅黑', size: 11, bold: true }
            // 默认每列的宽度
            let colWidth = 20
            if (data.length > 0) {
                // 设置表格宽度
                for (let key = 1; key < Object.keys(data[0]).length + 1; key++) {
                    worksheet.getColumn(+key).width = colWidth;
                }
            }

            for (let row = 1; row <= 1 + data.length; row++) {
                let Row = worksheet.findRow(row); // 获取行
                Row.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true } // 设置水平居中，垂直居中
                Row.eachCell((cell, index) => { // 添加单元格边框
                    cell.border = {
                        top: { style: 'thin' },
                        left: { style: 'thin' },
                        bottom: { style: 'thin' },
                        right: { style: 'thin' }
                    }
                })
            }
        }
        let buffer = workbook.xlsx.writeBuffer()
        return buffer
    }

文件导出参考：https://blog.csdn.net/weixin_44167504/article/details/135404374?spm=1001.2014.3001.5501

官方中文文档：https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md