Java-Layui使用layExcel导出复杂excel

Java-Layui使用layExcel导出复杂excel

安装

这里使用Vue前端框架来演示Excel导出功能，可通过npm进行安装，代码如下：

$ npm i lay-excel

转载：https://blog.csdn.net/jiciqiang/article/details/129392965

示例

pushExport () {
    let exportData = [];
    let lineNull = new Array(26).fill("");
    // 第1行
    let line1 = new Array(26).fill("");
    line1[0] = this.formData.airline;
    line1[1] = this.formData.iata;
    line1[2] = this.formData.track;
    line1[23] = this.formData.airline + '-' + this.formData.track;
    exportData.push(line1);
    // 第2行
    exportData.push(lineNull);
    // 第3行
    let line3 = new Array(26).fill("");
    let line3Info1 = this.formData.consignor.name;
    line3[0] = line3Info1;
    line3[15] = this.toUpper(this.formData.airlineInfo.ename);
    exportData.push(line3);
    // 第4行
    exportData.push(lineNull);
    // 第5行
    let line5 = new Array(26).fill("");
    let line5Info = this.formData.consignor.name;
    line5[0] = line5Info;
    exportData.push(line5);
    // 第6行
    let line6 = new Array(26).fill("");
    line6[0] = this.formData.meta.agent;
    line6[22] = this.backPayment();
    exportData.push(line6);
    // 第7行
    exportData.push(lineNull);
    // 第8行
    let line8 = new Array(26).fill("");
    line8[0] = this.formData.iataInfo.airport + ',' + this.formData.iataInfo.country;
    exportData.push(line8);
    // 第9行
    exportData.push(lineNull);
    // 第10行
    let line10 = new Array(26).fill("");
    line10[0] = this.formData.destIata;
    line10[1] = this.formData.airlineInfo.code;
    line10[8] = this.formData.dests.length >0 ? this.formData.dests[0].iata : '';
    line10[9] = this.formData.dests.length > 0 ? this.formData.dests[0].airlineInfo.code : '';
    line10[13] = this.formData.dests.length> 1 ? this.formData.dests[1].iata : '';
    line10[16] = this.formData.dests.length > 1 ? this.formData.dests[1].airlineInfo.code : '';
    line10[18] = this.formData.charge.currency;
    line10[21] = this.formData.charge.payment;
    line10[24] = this.formData.meta.carriage;
    exportData.push(line10);
    // 第11行
    let line11 = new Array(26).fill("");
    line11[0] = this.toUpper(this.formData.destIataInfo.airport);
    line11[7] = this.formData.flight;
    line11[9] = this.formatTimeToStr(this.formData.departure,'yyyy-MM-dd');
    line11[13] = this.formData.meta.insurance;
    exportData.push(line11);
    // 第12行
    exportData.push(lineNull);
    // 第13行
    let line13 = new Array(26).fill("");
    line13[0] = this.subInfo == null ? this.formData.meta.mark1 : this.subInfo.meta.mark1;
    exportData.push(line13);
    // 第14行
    exportData.push(lineNull);
    // 第15行
    let line15 = new Array(26).fill("");
    line15[0] = this.formData.goods.packages;
    line15[1] = this.formData.goods.gross;
    line15[8] = this.backChargeWt(this.formData);
    line15[11] = this.formData.charge.price;
    line15[14] = this.formData.charge.total;
    line15[22] = this.toUpper(this.formData.goods.descEn);
    exportData.push(line15);
        // 第16行
    let line16 = new Array(26).fill("");
    line16[0] = this.formData.meta.mark2;
    exportData.push(line16);
        // 第17行
    exportData.push(lineNull);
        // 第18行
    let line18 = new Array(26).fill("");
    let line18Info1 = this.backBatchs(this.formData);
    line18[22] = line18Info1;
    exportData.push(line18);
        // 第19行
    let line19 = new Array(26).fill("");
    line19[0] = this.formData.goods.packages;
    line19[1] = this.formData.goods.gross;
    line19[14] = this.formData.charge.total;
    exportData.push(line19);
        // 第20行
    let line20 = new Array(26).fill("");
    line20[11] = 'AWC: ' ;
    line20[13] = this.formData.charge.awc;
    line20[17] = 'MYC: ';
    line20[20] = this.formData.charge.myc;
    exportData.push(line20);
        // 第21行
    let line21 = new Array(26).fill("");
    line21[0] = this.formData.charge.total;
    line21[17] = 'MSC: ';
    line21[20] = this.formData.charge.msc;
    exportData.push(line21);
        // 第22行
    exportData.push(lineNull);
        // 第23行
    exportData.push(lineNull);
        // 第24行
    let line24 = new Array(26).fill("");
    line24[0] = this.formData.charge.total;
    exportData.push(line24);
        // 第25行
    let line25 = new Array(26).fill("");
    line25[12] = this.formData.meta.shipping;
    exportData.push(line25);
        // 第26行
    let line26 = new Array(26).fill("");
    line26[0] = this.formData.charge.money;
    exportData.push(line26);
        // 第27行
    let line27 = new Array(26).fill("");
    line27[11] = this.formData.meta.executed;
    line27[16] = this.formData.meta.place;
    line27[22] = this.formData.meta.signature;
    exportData.push(line27);
        // 第28行
    exportData.push(lineNull);
        // 第29行
    exportData.push(lineNull);
        // 第30行
    let line30 = new Array(26).fill("");
    line30[23] = this.formData.airline + "-" + this.formData.track;
    exportData.push(line30);


    // 字体
    this.fontCenterWeight(14, layExcel,exportData, 'A1:B1');
    this.fontLeftWeight(14, layExcel,exportData, 'C1:J1');
    this.fontCenterWeight(14, layExcel,exportData, 'X1:Z1');
    this.fontLeftWeight(8, layExcel,exportData, 'A3:M6');
    this.fontCenterWeight(12, layExcel,exportData, 'P3:Z6');
    this.fontCenterWeight(12, layExcel,exportData, 'A8:Z8');
    this.fontCenterWeight(12, layExcel,exportData, 'A10:M10');
    this.fontCenterWeight(11, layExcel,exportData, 'N10:U10');
    this.fontCenterWeight(12, layExcel,exportData, 'V10:Z10');
    this.fontLeftWeight(11, layExcel,exportData, 'A11:Z11');
    this.fontLeftWeight(12, layExcel,exportData, 'A13:Z13');
    this.fontCenterWeight(12, layExcel,exportData, 'A15:V15');
    this.fontLeftWeight(10, layExcel,exportData, 'W15:Z17');
    this.fontLeftWeight(12, layExcel,exportData, 'A16:V18');
    this.fontLeftWeight(10, layExcel,exportData, 'W18:Z19');
    this.fontCenterWeight(12, layExcel,exportData, 'A19:V19');
    this.fontCenterWeight(12, layExcel,exportData, 'A20:J27');
    this.fontRightWeight(12, layExcel,exportData, 'L20:M22');
    this.fontLeftWeight(12, layExcel,exportData, 'N20:Q22');
    this.fontRightWeight(12, layExcel,exportData, 'R20:T22');
    this.fontLeftWeight(12, layExcel,exportData, 'U20:W22');
    this.fontCenterWeight(12, layExcel,exportData, 'X20:Z22');
    this.fontCenterWeight(12, layExcel,exportData, 'L23:Z27');
    this.fontCenterWeight(14, layExcel,exportData, 'A30:Z30');

    
    // 跨行跨列
    var mergeConf = layExcel.makeMergeConfig([
        ['C1', 'J1'],
        ['X1', 'Z1'],
        ['A3', 'M3'],
        ['P3', 'Z3'],
        ['A5', 'M5'],
        ['P5', 'Z5'],
        ['A6', 'M6'],
        ['P6', 'Z6'],
        ['A7', 'G7'],
        ['I7', 'M7'],
        ['A8', 'M8'],
        ['B10', 'H10'],
        ['N10', 'O10'],
        ['V10', 'X10'],
        ['Y10', 'Z10'],
        ['A11', 'G11'],
        ['H11', 'I11'],
        ['J11', 'M11'],
        ['N11', 'P11'],
        ['A12', 'Z12'],
        ['A13', 'Z13'],
        ['B15', 'C15'],
        ['I15', 'J15'],
        ['L15', 'M15'],
        ['O15', 'V15'],
        ['W15', 'Z17'],
        ['A16', 'V18'],
        ['W18', 'Z19'],
        ['B19', 'C19'],
        ['O19', 'V19'],
        ['A20', 'F20'],
        ['G20', 'J20'],
        ['L20', 'M20'],
        ['N20', 'Q20'],
        ['R20', 'T20'],
        ['U20', 'W20'],
        ['A21', 'F21'],
        ['A21', 'F22'],
        ['G21', 'J21'],
        ['N21', 'Q21'],
        ['R21', 'T21'],
        ['U21', 'W21'],
        ['G22', 'J22'],
        ['N22', 'Q22'],
        ['R22', 'T22'],
        ['U22', 'W22'],
        ['A23', 'F23'],
        ['G23', 'J23'],
        ['A24', 'F24'],
        ['G24', 'J24'],
        ['A25', 'F25'],
        ['G25', 'J25'],
        ['M25', 'Z25'],
        ['A26', 'F27'],
        ['G26', 'J26'],
        ['G27', 'J27'],
        ['L27', 'P27'],
        ['Q27', 'V27'],
        ['W27', 'Z27'],
        ['A28', 'F28'],
        ['G28', 'J28'],
        ['W28', 'Z28'],
        ['A29', 'F29'],
        ['G29', 'J29'],
        ['X30', 'Z30'],
    ]);
    // 行宽
    var colConf = layExcel.makeColConfig({
        A: 28,
        B: 28,
        C: 20,
        D: 8,
        E: 8,
        F: 8,
        G: 40,
        H: 22,
        I: 25,
        J: 25,
        K: 8,
        L: 22,
        M: 22,
        N: 8,
        O: 16,
        P: 16,
        Q: 16,
        R: 16,
        S: 16,
        T: 8,
        U: 8,
        V: 8,
        W: 36,
        X: 36,
        Y: 36,
        Z: 36,
    });
    // 行高
    var rowConf = layExcel.makeRowConfig({
        1: 20,
        2: 10,
        3: 55,
        4: 10,
        5: 60,
        6: 55,
        7: 20,
        8: 20,
        9: 10,
        10: 20,
        11: 20,
        12: 20,
        13: 45,
        14: 10,
        15: 23,
        16: 40,
        17: 45,
        18: 40,
        19: 25,
        20: 25,
        21: 25,
        22: 25,
        23: 25,
        24: 25,
        25: 25,
        26: 25,
        27: 20,
        28: 20,
        29: 20,
        30: 20,
    });
    layExcel.exportExcel(exportData, '账单.xlsx', 'xlsx',{
        extend: {
        '!merges': mergeConf,
        '!cols': colConf,
        '!rows': rowConf,
        '!margins':  {left:0.38, right:0.0, top:1.1,bottom:0.16,header:0.5,footer:0.09} // 页边距
        }
    });
    
    },
    // 文字居中靠左
    fontLeftWeight(fontSize, layExcel, exportData, fontStyle) {
    layExcel.setExportCellStyle(exportData, fontStyle, {
        s: {
            alignment: {
                wrapText: true, // 单元格内容换行
                vertical: 'center',
            },
            font: { sz: fontSize, bold: true },
        }
    });
    },
    // 文字居中靠右
    fontRightWeight(fontSize, layExcel, exportData, fontStyle) {
    layExcel.setExportCellStyle(exportData, fontStyle, {
        s: {
            alignment: {
                wrapText: true, // 单元格内容换行
                horizontal: 'right',
                vertical: 'center',
            },
            font: { sz: fontSize, bold: true },
        }
    });
    },
    // 文字居中
    fontCenterWeight(fontSize, layExcel, exportData, fontStyle) {
    layExcel.setExportCellStyle(exportData, fontStyle, {
        s: {
            alignment: {
                wrapText: true, // 单元格内容换行
                horizontal: 'center',
                vertical: 'center',
            },
            font: { sz: fontSize, bold: true },
        }
    });
  },