search

natergj / excel4node

Node module to allow for easy Excel file creation
MIT License
1.38k stars 214 forks source link

Advance string value not override in tab #261

Closed ashjha1 closed 5 years ago

ashjha1 commented 5 years ago

Describe the bug I have 2 spreadsheet where on 1st heading and 2nd sheet heading are using advance string (with different color) but both sheet show same data

To Reproduce

function genXls(res) {
    // Require library
    var xl = require('excel4node');

    // Create a new instance of a Workbook class
    var wb = new xl.Workbook();
    var heading = wb.createStyle({
        font: {
            bold: true,
            size: 9,
            vertAlign: 'center'
        },
        alignment: {
            horizontal: 'center',
            vertical: 'center'
        },
        border: {
            left: {
                style: "thin"
            },
            right: {
                style: "thin"
            },
            top: {
                style: "thin"
            },
            bottom: {
                style: "thin"
            }
        },
    });

    var body = wb.createStyle({
        font: {
            bold: false,
            size: 9
        },
        alignment: {
            horizontal: 'left'
        },
        border: {
            left: {
                style: "thin"
            },
            right: {
                style: "thin"
            },
            top: {
                style: "thin"
            },
            bottom: {
                style: "thin"
            }
        },
    });

    var total = wb.createStyle({
        font: {
            bold: true,
            size: 9
        },
        alignment: {
            horizontal: 'left'
        },
        border: {
            left: {
                style: "thin"
            },
            right: {
                style: "thin"
            },
            top: {
                style: "thin"
            },
            bottom: {
                style: "thin"
            }
        },
    });

    var skyBlu = {
        fill: {
            type: 'pattern',
            patternType: 'solid',
            fgColor: '#B4C6EB'
        }
    }

    var drkGry = {
        fill: {
            type: 'pattern',
            patternType: 'solid',
            fgColor: '#CFCDCE'
        }
    }

    var lytGrn = {
        fill: {
            type: 'pattern',
            patternType: 'solid',
            fgColor: '#C6E1B2'
        },
        font: {
            bold: true
        }
    }

    var lytYlw = {
        fill: {
            type: 'pattern',
            patternType: 'solid',
            fgColor: '#FFD866'
        },
        font: {
            bold: true
        }
    }
    var cm = (w) => w * 3.6363;

    // Add Worksheets to the workbook
    var ws = wb.addWorksheet('Ashu', {
        'sheetFormat': {
            'defaultColWidth': 20,
            'defaultRowHeight': 11.8867
        },
    });

    // Add Worksheets to the workbook
    var ws1 = wb.addWorksheet('Tosh', {
        'sheetFormat': {
            'defaultColWidth': 20,
            'defaultRowHeight': 11.8867
        },
    });

    const Worksheets = [ws,ws1];

    for (let i = 0; i < Worksheets.length; i++) {
        const worksheet = Worksheets[i];
        worksheet.row(2).setHeight(60.2271);
        worksheet.column(1).setWidth(cm(0.61));
        worksheet.column(2).setWidth(cm(2.30));
        worksheet.column(3).setWidth(cm(2.30));
        worksheet.column(4).setWidth(cm(1.56));
        worksheet.column(5).setWidth(cm(5.34));
        worksheet.column(6).setWidth(cm(10.37));
        worksheet.column(7).setWidth(cm(5.98));
        worksheet.column(8).setWidth(cm(4.31));
        worksheet.column(9).setWidth(cm(4.07));
        worksheet.column(10).setWidth(cm(4.39));
        worksheet.column(11).setWidth(cm(4.00));
        worksheet.column(12).setWidth(cm(4.58));
        worksheet.column(13).setWidth(cm(3.84));
        worksheet.column(14).setWidth(cm(3.84));
        worksheet.column(15).setWidth(cm(4.34));
        worksheet.column(16).setWidth(cm(8.33));
        worksheet.column(17).setWidth(cm(8.33));
        worksheet.column(18).setWidth(cm(8.33));
        worksheet.column(19).setWidth(cm(6.30));
        worksheet.column(20).setWidth(cm(6.35));
        worksheet.column(21).setWidth(cm(5.58));
        worksheet.column(22).setWidth(cm(4.60));
        worksheet.column(23).setWidth(cm(4.55));
        worksheet.column(24).setWidth(cm(4.55));
        worksheet.column(25).setWidth(cm(4.55));
        worksheet.column(26).setWidth(cm(4.47));
        worksheet.column(27).setWidth(cm(4.47));
        worksheet.cell(1, 8, 1, 22, true)
        .string([{
            bold: true,
            size: 9,
        }, 'Microsite Digital Metrics (reported per store)*', {
            color: 'FF0000',
            value: '(*Data complete through ________)'+i
        }])
        .style({
            alignment: {
                horizontal: 'center',
                vertical: 'center'
            },
            fill: {
                type: 'pattern',
                patternType: 'solid',
                fgColor: '#B4C6EB'
            }
        });

    worksheet.cell(1, 23, 1, 27, true)
        .string('Marketing Automation Program Metrics')
        .style(heading)
        .style(drkGry);

    worksheet.cell(2, 2).string('Parent ID').style(heading);
    worksheet.cell(2, 3).string('Brand').style(heading);
    worksheet.cell(2, 4).string('StoreID').style(heading);
    worksheet.cell(2, 5).string('Location').style(heading);
    worksheet.cell(2, 6).string('Store').style(heading);
    worksheet.cell(2, 7).string('Promo Period').style(heading);
    worksheet.cell(2, 8).string('DIGITAL MEDIA START').style(heading).style(skyBlu);
    worksheet.cell(2, 9).string('Current Digital\n package for this\n store').style(heading).style(skyBlu);
    worksheet.cell(2, 10).string('Total Unique Traffic for\n this store').style(heading).style(skyBlu);
    worksheet.cell(2, 11)
        .string('# of Times store is\n returned in dealer\n locator search\n results (Avg per\n Month)')
        .style(heading).style(skyBlu);

    worksheet.cell(2, 12).string('Bounce Rate (avg %) for\n this store microsite').style(heading).style(skyBlu);
    worksheet.cell(2, 13).string('# of Times store\n has been clicked to\n call').style(heading).style(skyBlu);
    worksheet.cell(2, 14).string('# of Times store\n has been clicked to\n get directions')
        .style(heading)
        .style(skyBlu);
    worksheet.cell(2, 15).string('# of Leads Collected\n on this Microsite').style(heading).style(skyBlu);
    worksheet.cell(2, 16)
        .string('Product that is #1 Based on Total Page\n Views on this Microsite')
        .style(heading).style(skyBlu);

    worksheet.cell(2, 17).string('Product that is #2 Based on Total Page\n Views on this Microsite').style(heading).style(skyBlu);
    worksheet.cell(2, 18).string('Product that is #3 Based on Total Page\n Views on this Microsite').style(heading).style(skyBlu);
    worksheet.cell(2, 19).string('* Click Through Rate (CTR avg %)\n - Search & Digital Ads').style(heading).style(skyBlu);
    worksheet.cell(2, 20)
        .string('* Impressions Delivered - this\n store (optimized digital\n packages)').style(heading).style(skyBlu);
    worksheet.cell(2, 21)
        .string('* Impressions Delivered - this\n store (digital display geo-\ntargeted)')
        .style(heading).style(skyBlu);

    worksheet.cell(2, 22)
        .string('# of Savings Passes\n downloaded by\n consumers for this\n store from Microsite\n (directly)')
        .style(heading).style(skyBlu);

    worksheet.cell(2, 23)
        .string('# of Emails sent to\n consumers for this\n store')
        .style(heading)
        .style(drkGry);

    worksheet.cell(2, 24)
        .string('# of Buying Guides\n downloaded by\n consumers for this\n store from Marketing\n Automation')
        .style(heading)
        .style(drkGry);

    worksheet.cell(2, 25)
        .string('# of Savings Passes\n downloaded by\n consumers for this\n store from Marketing\n Automation')
        .style(heading)
        .style(drkGry);

    worksheet.cell(2, 26)
        .string('Email Open rate (%) for\n this store')
        .style(heading)
        .style(drkGry);

    worksheet.cell(2, 27)
        .string('Email Click rate (%) for\n this store')
        .style(heading)
        .style(drkGry);

    for (let i = 3; i < 9; i++) {

        worksheet.cell(i, 2).string(`SSB0609`).style(body);
        worksheet.cell(i, 3).string(`Serta`).style(body);
        worksheet.cell(i, 4).string(`SE0625`).style(body);
        worksheet.cell(i, 5).string(`Ossipee, New Hampshire`).style(body);
        worksheet.cell(i, 6).string(`Baron's Major Brands, 465 Route 16`).style(body);
        worksheet.cell(i, 7).string(`Year to Date`).style(body);

        worksheet.cell(i, 8).string(`MEMORIAL DAY`).style(body);
        worksheet.cell(i, 9).string(`Silver Upgrade`).style(body);
        worksheet.cell(i, 10).string('-----------').style(body);
        worksheet.cell(i, 11).number(59).style(body).style({
            numberFormat: '#,##0'
        });
        worksheet.cell(i, 12).number(86.64 / 100).style(body).style({
            numberFormat: '#,##0.00\%'
        });
        worksheet.cell(i, 13).number(0).style(body).style({
            numberFormat: '#,##0'
        });
        worksheet.cell(i, 14).number(2).style(body).style({
            numberFormat: '#,##0'
        });
        worksheet.cell(i, 15).number(1).style(body).style({
            numberFormat: '#,##0'
        });
        worksheet.cell(i, 16).string(`Perfect Sleeper Hectman Firm`).style(body);
        worksheet.cell(i, 17).string(`Perfect Sleeper Adamson Firm`).style(body);
        worksheet.cell(i, 18).string(`iComfort hybrid Applause II Plush`).style(body);
        worksheet.cell(i, 19).number(0.06 / 100).style(body).style({
            numberFormat: '#,##0.00\%'
        });
        worksheet.cell(i, 20).number(522612).style(body).style({
            numberFormat: '#,##0'
        });
        worksheet.cell(i, 21).number(2885504).style(body).style({
            numberFormat: '#,##0'
        });
        worksheet.cell(i, 22).number(0).style(body).style({
            numberFormat: '#,##0'
        });
        worksheet.cell(i, 23).number(0).style(body).style({
            numberFormat: '#,##0'
        });
        worksheet.cell(i, 24).number(0).style(body).style({
            numberFormat: '#,##0'
        });
        worksheet.cell(i, 25).number(0).style(body).style({
            numberFormat: '#,##0'
        });
        worksheet.cell(i, 26).number(0.00 / 100).style(body).style({
            numberFormat: '#,##0.00\%'
        });
        worksheet.cell(i, 27).number(0.00 / 100).style(body).style({
            numberFormat: '#,##0.00\%'
        });

    }

    worksheet.cell(9, 2, 9, 27)
        .style(body)
        .style(lytGrn);

    worksheet.cell(10, 2, 10, 27)
        .style(body)
        .style(lytYlw);

    worksheet.cell(9, 7).string('Year to Date Total');
    worksheet.cell(9, 8).string('-------------').style(total);
    worksheet.cell(9, 9).string('-------------').style(total);

    worksheet.cell(9, 10).formula('SUM(J3:J8)').style(total).style({
        numberFormat: '#,##0'
    });
    worksheet.cell(9, 11).formula('SUM(K3:K8)').style(total).style({
        numberFormat: '#,##0'
    });
    worksheet.cell(9, 12).string('-------------').style(total);
    worksheet.cell(9, 13).formula('SUM(M3:M8)').style(total).style({
        numberFormat: '#,##0'
    });
    worksheet.cell(9, 14).formula('SUM(N3:N8)').style(total).style({
        numberFormat: '#,##0'
    });
    worksheet.cell(9, 15).formula('SUM(O3:O8)').style(total).style({
        numberFormat: '#,##0'
    });

    worksheet.cell(9, 16).string('-------------').style(total);
    worksheet.cell(9, 17).string('-------------').style(total);
    worksheet.cell(9, 18).string('-------------').style(total);

    worksheet.cell(9, 19).string('-------------').style(total);
    worksheet.cell(9, 20).formula('SUM(T3:T8)').style(total).style({
        numberFormat: '#,##0'
    });
    worksheet.cell(9, 21).formula('SUM(U3:U8)').style(total).style({
        numberFormat: '#,##0'
    });
    worksheet.cell(9, 22).formula('SUM(V3:V8)').style(total).style({
        numberFormat: '#,##0'
    });
    worksheet.cell(9, 23).formula('SUM(W3:W8)').style(total).style({
        numberFormat: '#,##0'
    });
    worksheet.cell(9, 24).formula('SUM(X3:X8)').style(total).style({
        numberFormat: '#,##0'
    });
    worksheet.cell(9, 25).formula('SUM(Y3:Y8)').style(total).style({
        numberFormat: '#,##0'
    });
    worksheet.cell(9, 25).string('------------').style(total);
    worksheet.cell(9, 26).string('-------------').style(total);

    worksheet.cell(10, 7).string('Year to Date Average');

    worksheet.cell(10, 8).string('-------------').style(total);
    worksheet.cell(10, 9).string('-------------').style(total);
    worksheet.cell(10, 10).formula('IFERROR(AVERAGE(J3:J8),0)').style(total).style({
        numberFormat: '#,##0.00'
    });
    //IF(ISERROR(AVERAGE(J3:J8)),0,AVERAGE(AVERAGE(J3:J8))
    worksheet.cell(10, 11).formula('AVERAGE(K3:K8)').style(total).style({
        numberFormat: '#,##0.00'
    });
    worksheet.cell(10, 12).formula('AVERAGE(L3:L8)').style(total).style({
        numberFormat: '#,##0.00\%'
    });
    worksheet.cell(10, 13).formula('AVERAGE(M3:M8)').style(total).style({
        numberFormat: '#,##0.00'
    });
    worksheet.cell(10, 14).formula('AVERAGE(N3:N8)').style(total).style({
        numberFormat: '#,##0.00'
    });
    worksheet.cell(10, 15).formula('AVERAGE(O3:O8)').style(total).style({
        numberFormat: '#,##0.00'
    });

    worksheet.cell(10, 16).string('-------------').style(total);
    worksheet.cell(10, 17).string('-------------').style(total);
    worksheet.cell(10, 18).string('-------------').style(total);

    worksheet.cell(10, 19).formula('AVERAGE(S3:S8)').style(total).style({
        numberFormat: '#,##0.00\%'
    });
    worksheet.cell(10, 20).formula('AVERAGE(T3:T8)').style(total).style({
        numberFormat: '#,##0.00'
    });
    worksheet.cell(10, 21).formula('AVERAGE(U3:U8)').style(total).style({
        numberFormat: '#,##0.00'
    });
    worksheet.cell(10, 22).formula('AVERAGE(V3:V8)').style(total).style({
        numberFormat: '#,##0.00'
    });
    worksheet.cell(10, 23).formula('AVERAGE(W3:W8)').style(total).style({
        numberFormat: '#,##0.00'
    });
    worksheet.cell(10, 24).formula('AVERAGE(X3:X8)').style(total).style({
        numberFormat: '#,##0.00'
    });
    worksheet.cell(10, 25).formula('AVERAGE(Y3:Y8)').style(total).style({
        numberFormat: '#,##0.00'
    });
    worksheet.cell(10, 26).formula('AVERAGE(Z3:Z8)').style(total).style({
        numberFormat: '#,##0.00\%'
    });
    worksheet.cell(10, 27).formula('AVERAGE(AA3:AA8)').style(total).style({
        numberFormat: '#,##0.00\%'
    });

    }

    wb.write('ExcelFile.xlsx', res);

}

Expected behavior It should show different on both tab

Environment (please complete the following information):

natergj commented 5 years ago

this should be resolved via https://github.com/natergj/excel4node/pull/276 and included in 1.7.1 release