nodejs 導(dǎo)出 Excel

安裝依賴(lài)

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

修改文件

• 在導(dǎo)出 xlsx 文件中 表格展示內(nèi)容樣式錯(cuò)誤 請(qǐng)修改下列文件部分內(nèi)容路召。

• 在 node_modules/xlsx-style下的xlsx.js文件中别洪，把 write_ws_xml_data()替換成下列這個(gè)，導(dǎo)出excel沒(méi)有樣式淹仑，直接修改方法显押。

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("");
}

使用 導(dǎo)出Excel 功能

• ExcelData ：[{},{},{}] —— 需要自定義單元格樣式 或者 合并單元 使用下列格式
  let ExcelData = [{
  value : String // 單元格內(nèi)容
  style ：Object //單元格樣式
  colSpan ： Number //以當(dāng)前單元格為起點(diǎn) 合并列的單元格
  rowSpan ： Number //以當(dāng)前單元格為起點(diǎn) 合并行的單元格
  }];
• workSheet ：需要自定義行高或者列表 合并單元等等
  let workSheet = {
  "!merges" : [ {
  s: { //s為開(kāi)始 c: 1,//開(kāi)始列 r: 0//可以看成開(kāi)始行,實(shí)際是取值范圍 },
  e: { // e結(jié)束 c: 4,//結(jié)束列 r: 0//結(jié)束行 }
  }],
  "!cols" : [{wch:10},{wch:10}],
  "!rows" : [{hpx:20},{hpx:20}],
  };

// 使用 導(dǎo)出Excel 功能
const excel = async () => {
    let ExcelHeaders = ['標(biāo)題1','標(biāo)題2','標(biāo)題3','標(biāo)題4'];
    let ExcelData = [
        [1,11,111,1111],
        [2,22,222,2222],
        [3,33,333,3333],
        [4,44,444,4444],
    ];

    // 創(chuàng)建工作簿
    const buffer = await new ExportExcel()
        .createWorkBook(
            [
                { ExcelHeaders,ExcelData, sheetName: '車(chē)輛入場(chǎng)通知單', workSheet : {} }, // sheet1
                // { ExcelData, sheetName: '車(chē)輛入場(chǎng)通知單', workSheet : {} }, // sheet2
            ]
        );
    // const filename = `${DateUtils.format(new Date(), 'yyyyMMdd')}.xlsx`;
    // await fs.writeFileSync(`./${filename}`, buffer, { flag: 'w' });
}

功能實(shí)現(xiàn)詳細(xì)代碼

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


// 導(dǎo)出并生成Excel
class ExportExcel {

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

    /** 默認(rèn)樣式 */
    defaultStyle = {
        ...this.BorderStyle,
        alignment: {
            /// 自動(dòng)換行
            wrapText: true,
            // 內(nèi)容在單元格  居中
            horizontal: "center",
            vertical: "center",
        },
        font: {
            name: "宋體",
            sz: 12, // 字體大小
            color: {auto: 1},
            bgColor : '#fff'
        },
    };

    /**
     * 默認(rèn)標(biāo)題樣式
     */
    defaultTitleStyle = {
        ...this.defaultStyle,
        font: {
            name: "宋體",
            sz: 12,
            color: {auto: 1},
            bold : true,  // 字體加粗
        },
    };

    /**
     * 將對(duì)象數(shù)組按指定屬性順序轉(zhuǎn)為二維數(shù)組
     * @param objects 對(duì)象數(shù)組
     * @param props 對(duì)象的屬性列表,屬性可寫(xiě)為數(shù)組,數(shù)組只包含兩個(gè)值,
     *          第一個(gè)值為對(duì)象屬性名,第二個(gè)值為過(guò)濾函數(shù)
     */
    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;
    }

    /**
     * 設(shè)置顯示數(shù)據(jù)樣式顯示
     * @param RowData  數(shù)據(jù)
     * @param isBold   顯示內(nèi)容是否加粗
     * @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 單元格內(nèi)容
         style ：Object   單元格樣式
         colSpan ： Number  以當(dāng)前單元格為起點(diǎn)  合并列的單元格
         rowSpan ： Number  以當(dāng)前單元格為起點(diǎn)  合并行的單元格
       }
     ]
     workSheet = {
     "!merges" : [ {
       s: { //s為開(kāi)始 c: 1,//開(kāi)始列 r: 0//可以看成開(kāi)始行,實(shí)際是取值范圍 },
       e: { // e結(jié)束 c: 4,//結(jié)束列 r: 0//結(jié)束行 }
       }];
   }
     */
    createSheet = async ({ExcelHeaders = [], ExcelData = [] , workSheet = {}}) => {
        const data = [];
        if(ExcelHeaders.length){
            const headers = await this.setRowData(ExcelHeaders, true );
            data.push(headers);
        }

        // 表格數(shù)據(jù)內(nèi)容
        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};
    };

    /**
     * 創(chuàng)建 Excel 工作簿
     * @param data Array 可創(chuàng)建一個(gè)或者多個(gè) sheet
     * @returns {Promise<*>}
     */
    createWorkBook = async (data = []) => {
        //  創(chuàng)建一個(gè)工作簿
        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);
            }
        }
        // 返回寫(xiě)入數(shù)據(jù)  buffer
        return XLSX_STYLE.write(workBook, {type: 'buffer'});
    };

}

export {ExportExcel};