ExcelJS node: horizontalCentered & verticalCentered in page not working

Question

plz help me.

My flow:

Using a file Tempate -> Code (Exceljs) -> output New file

Template file:

Center on page:

horizontally : Checked
vertically : Checked

Source coder:

worksheet.pageSetup.horizontalCentered = true 
worksheet.pageSetup.verticalCentered = true

BUT File output:

horizontal : uncheck
vertical : uncheck

score 4 · Answer 1 · answered Dec 18 '19 at 06:13

4

Please tell me if this worked well or not.

// DO NOT SET ANOTHER pageSetup AFTER DECLARING THIS!

var worksheet =  workbook.addWorksheet('sheet', {
  pageSetup: {
    horizontalCentered: true,
    verticalCentered: true,
    paperSize: 9,
    orientation: 'portrait',
    margins: {
      left: 0.3149606, right: 0.3149606,
      top: 0.3543307, bottom: 0.3543307,
      header: 0.3149606, footer: 0.3149606
    }
  }
})

answered Dec 18 '19 at 06:13

Edi Imanto

2,119
1
11
17

Sorry, my case is using a template, not a new sheet. – Tính Ngô Quang Dec 18 '19 at 08:12
I have tested it with your code, but it still suffers – Tính Ngô Quang Dec 21 '19 at 08:39

score 2 · Answer 2 · answered Dec 20 '19 at 05:17

Please check below link and code.

https://github.com/Great-hijack/react-excel/blob/master/frontend/src/utils/export2excel.js

var sheet1 = workbook.addWorksheet('Download', { properties: { tabColor: { argb: '6B5B95' }, defaultRowHeight: 39 } });
// Page Setup for sheet1
sheet1.pageSetup.paperSize = 13; // B5 (JIS)
sheet1.pageSetup.orientation = 'landscape';
sheet1.pageSetup.horizontalCentered = true;
sheet1.pageSetup.verticalCentered = true;
sheet1.pageSetup.margins = {
    left: 0.7, right: 0.7,
    top: 0.3, bottom: 0.3,
    header: 0.3, footer: 0.3
};

I hope this is helpful for you.

This issue is fixed a few days before. You can check it in below link. https://github.com/exceljs/exceljs/pull/1073 — , Jan 27 '20 at 04:06

score 0 · Answer 3 · answered Dec 27 '19 at 14:00

Can you show the code, So that it will be easy to figure the problem.

But, While creating worksheet have you specified,

useStyles: true

var workbook = new Excel.stream.xlsx.WorkbookWriter({
    filename: filename.xlsx,
    useStyles: true
});

See below code, If it could be of any help -

var worksheet = workbook.addWorksheet(date);
    worksheet.columns = [{
            header: 'Col-1',
            key: 'col_1',
            width: 5
        },
       {
            header: 'Col-2',
            key: 'col_2',
            width: 5
        },
    ];

    for (let i in data) {
        worksheet.addRow(data[i]);
    }

    // set style for each cell
    worksheet.eachRow(function (Row, rowNum) {

        Row.eachCell(function (Cell, cellNum) {

            if (rowNum == 1) {
                Cell.alignment = {
                    vertical: 'center',
                    horizontal: 'center'
                }
            }else{
                Cell.alignment = {
                    vertical: 'middle',
                    horizontal: 'middle'
                }
            }
        })
    })

    workbook.commit()
        .then(function () {
            console.log('Excel export complete!');
            process.exit()
        });

ExcelJS node: horizontalCentered & verticalCentered in page not working

3 Answers3