nodejs使用xlsx和xlsx-style导出Excel文件

nodejs 导出 Excel

---

安装依赖

• npm install xlsx --save
• npm install xlsx-style --save
• npm install fs --save

---

修改文件

• 在导出 xlsx 文件中 表格展示内容样式错误 请修改下列文件部分内容。

• 在 node_modules/xlsx-style下的xlsx.js文件中，把 write_ws_xml_data()替换成下列这个，导出excel没有样式，直接修改方法。

function write_ws_xml_data(ws, opts, idx, wb) {
 var o = [], r = [], range = safe_decode_range(ws['!ref']), cell, ref, rr = "", cols = [], R, C,rows = ws['!rows'];
 for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
 for(R = range.s.r; R <= range.e.r; ++R) {
   r = [];
   rr = encode_row(R);
   for(C = range.s.c; C <= range.e.c; ++C) {
     ref = cols[C] + rr;
     if(ws[ref] === undefined) continue;
     if((cell = write_ws_xml_cell(ws[ref], ref, ws, opts, idx, wb)) != null) r.push(cell);
   }
   if(r.length > 0){
     params = ({r:rr});
     if(rows && rows[R]) {
       row = rows[R];
       if(row.hidden) params.hidden = 1;
       height = -1;
       if (row.hpx) height = row.hpx;
       else if (row.hpt) height = row.hpt;
       if (height > -1) { params.ht = height; params.customHeight = 1; }
       if (row.level) { params.outlineLevel = row.level; }
     }
     o[o.length] = (writextag('row', r.join(""), params));
   }
 }
 if(rows) for(; R < rows.length; ++R) {
   if(rows && rows[R]) {
     params = ({r:R+1});
     row = rows[R];
     if(row.hidden) params.hidden = 1;
     height = -1;
     if (row.hpx) height = row.hpx;
     else if (row.hpt) height = row.hpt;
     if (height > -1) { params.ht = height; params.customHeight = 1; }
     if (row.level) { params.outlineLevel = row.level; }
     o[o.length] = (writextag('row', "", params));
   }
 }
 return o.join("");
}

使用 导出Excel 功能

• ExcelData ：[{},{},{}] —— 需要自定义单元格样式 或者 合并单元 使用下列格式
  let ExcelData = [{
  value : String // 单元格内容
  style ：Object //单元格样式
  colSpan ： Number //以当前单元格为起点 合并列的单元格
  rowSpan ： Number //以当前单元格为起点 合并行的单元格
  }];
• workSheet ：需要自定义行高或者列表 合并单元等等
  let workSheet = {
  “!merges” : [ {
  s: { //s为开始 c: 1,//开始列 r: 0//可以看成开始行,实际是取值范围 },
  e: { // e结束 c: 4,//结束列 r: 0//结束行 }
  }],
  “!cols” : [{wch:10},{wch:10}],
  “!rows” : [{hpx:20},{hpx:20}],
  };

// 使用 导出Excel 功能
const excel = async () => {
    let ExcelHeaders = ['标题1','标题2','标题3','标题4'];
    let ExcelData = [
        [1,11,111,1111],
        [2,22,222,2222],
        [3,33,333,3333],
        [4,44,444,4444],
    ];

    // 创建工作簿
    const buffer = await new ExportExcel()
        .createWorkBook(
            [
                { ExcelHeaders,ExcelData, sheetName: '车辆入场通知单', workSheet : {} }, // sheet1
                // { ExcelData, sheetName: '车辆入场通知单', workSheet : {} }, // sheet2
            ]
        );
    // const filename = `${DateUtils.format(new Date(), 'yyyyMMdd')}.xlsx`;
    // await fs.writeFileSync(`./${filename}`, buffer, { flag: 'w' });
}

功能实现详细代码

import * as xlsx from 'xlsx';
import * as XLSX_STYLE from 'xlsx-style';
import * as fs from 'fs';


// 导出并生成Excel
class ExportExcel {

    /**
     * 边框样式
     */
    BorderStyle = {
        border: {
            color: {auto: 1},
            top: {style: 'thin'},
            bottom: {style: 'thin'},
            left: {style: 'thin'},
            right: {style: 'thin'}
        }
    };

    /** 默认样式 */
    defaultStyle = {
        ...this.BorderStyle,
        alignment: {
            /// 自动换行
            wrapText: true,
            // 内容在单元格  居中
            horizontal: "center",
            vertical: "center",
        },
        font: {
            name: "宋体",
            sz: 12, // 字体大小
            color: {auto: 1},
            bgColor : '#fff'
        },
    };

    /**
     * 默认标题样式
     */
    defaultTitleStyle = {
        ...this.defaultStyle,
        font: {
            name: "宋体",
            sz: 12,
            color: {auto: 1},
            bold : true,  // 字体加粗
        },
    };

    /**
     * 将对象数组按指定属性顺序转为二维数组
     * @param objects 对象数组
     * @param props 对象的属性列表,属性可写为数组,数组只包含两个值,
     *          第一个值为对象属性名,第二个值为过滤函数
     */
    static objectsToRows(objects, props) {
        const rows = [];
        for (let i = 0; i < objects.length; i++) {
            const row = [];
            const item = objects[i];
            for (const key of props) {
                if (key instanceof Array) {
                    row.push(key[1](item[key[0]]));
                } else {
                    row.push(item[key]);
                }
            }
            
            rows.push(row);
        }
        return rows;
    }

    /**
     * 设置显示数据样式显示
     * @param RowData  数据
     * @param isBold   显示内容是否加粗
     * @returns {*}
     */
    setRowData(RowData = [],isBold = false,index = 0){
        for (let i = 0; i < RowData.length; i++) {
            if(RowData[i] instanceof Array){
                this.setRowData(RowData[i],isBold,(i + index));
            }
            else if(RowData[i] instanceof Object){
                if(!this.merges){
                    this.merges = [];
                }
                let {value = '', style = {}, colSpan = 0, rowSpan = 0} = RowData[i];
                RowData[i] = {v: value, s: {...this.defaultStyle, ...style}};

                if(rowSpan > 0){
                    this.merges.push({
                        s: {c: i,r: index},
                        e: {c: i,r: (index + rowSpan) }
                    })
                }
                if(rowSpan === 0 && colSpan > 0){
                    this.merges.push({
                        s: {c: i,r: index},
                        e: {c: (colSpan + i),r: index }
                    })
                }
            }
            else {
                if(isBold){
                    RowData[i] = {v: RowData[i] || '', s: this.defaultTitleStyle};
                }else {
                    RowData[i] = {v: RowData[i] || '', s: this.defaultStyle};
                }
            }
        }
        return RowData;
    }


    /** 
     * 
     ExcelData = [
     {
         value : String 单元格内容
         style ：Object   单元格样式
         colSpan ： Number  以当前单元格为起点  合并列的单元格
         rowSpan ： Number  以当前单元格为起点  合并行的单元格
       }
     ]
     workSheet = {
     "!merges" : [ {
       s: { //s为开始 c: 1,//开始列 r: 0//可以看成开始行,实际是取值范围 },
       e: { // e结束 c: 4,//结束列 r: 0//结束行 }
       }];
   }
     */
    createSheet = async ({ExcelHeaders = [], ExcelData = [] , workSheet = {}}) => {
        const data = [];
        if(ExcelHeaders.length){
            const headers = await this.setRowData(ExcelHeaders, true );
            data.push(headers);
        }

        // 表格数据内容
        let rowData = await this.setRowData(ExcelData, false, data.length );
        data.push(...rowData);
        let sheet = xlsx.utils.aoa_to_sheet(data, {cellDates: true, cellStyles: true});

        // 表单合并情况
        sheet["!merges"] = this.merges || [];

        // 表格每列显示的列宽
        let cols = [];
        if(workSheet['cols']){
            for(let i = 0; i < workSheet['cols'].length; i++){
                cols.push({wch: workSheet['cols'][i] || 10})
            }
            workSheet['!cols'] = cols;
        }else {
            for(let i = 0; i < ExcelHeaders.length; i++){
                cols.push({wch: ExcelHeaders[i] || 10})
            }
        }
        sheet["!cols"] = cols;

        // 表格显示  行高
        let rows = [];
        if(ExcelHeaders.length) {
            rows.push({hpx: 30});
        }
        for(let i = 0; i < ExcelData.length; i++){
            rows.push({hpx: 24})
        }
        sheet['!rows'] = rows;

        return {...sheet, ...workSheet};
    };

    /**
     * 创建 Excel 工作簿
     * @param data Array 可创建一个或者多个 sheet
     * @returns {Promise<*>}
     */
    createWorkBook = async (data = []) => {
        //  创建一个工作簿
        const workBook = xlsx.utils.book_new();
        for(let i = 0; i < data.length; i++){
            let sheet = await this.createSheet(data[i]);
            if(sheet){
                let sheetName = `sheet${i}`;
                if(data[i].sheetName){
                    sheetName = data[i].sheetName;
                }
                xlsx.utils.book_append_sheet(workBook, sheet, sheetName);
            }
        }
        // 返回写入数据  buffer
        return XLSX_STYLE.write(workBook, {type: 'buffer'});
    };

}

export {ExportExcel};