excelJS wrapText in Table columns

Question

Is there a way to get wrapText working for columns in a table? I'm trying the following and it's not registering in excel.

const columnsStyle = { font: { name: 'Arial Black', size: 12 } };
const columns = [
            { name: 'PO#', style: columnsStyle, alignment: { wrapText:true } }
];
 ws.addTable({
                name: 'name',
                ref: `A${lastRowNum}`,
                columns: columns,
                rows: rows,
            });

I've also tried putting 'alignment' in the 'style' object but it results in an error when opening in excel. (Table looks different and has no wrap text and excel says there's an error in trying to create it)

const columnsStyle = { font: { name: 'Arial Black', size: 12 }, alignment: { wrapText:true } }; const columns = [ { name: 'PO#', style: columnsStyle, } ];

I also want to get all cells in these columns of the table wrapped too. Does anybody have any idea on how to do this? I looked through the documentation several times and couldn't find anything concrete.

Note: I know I can do ws.getCell(ref).alignment = {wrapText:true} and so I tried looking at the table object to see if I could get a reference of all the cells in it, loop through them, and set the alignment. But I was not able to get cells in the table.

Answer 1

You can set alignment by row or by column.

Loop through all rows and set alignment and text wrapping:

var sheet = workbook.addWorksheet(sheetName, { pageSetup: { paperSize: 9, orientation: 'landscape' } });

// Add data to sheet here, e.g. sheet.addRow

let rowIndex = 1;
for (rowIndex; rowIndex <= sheet.rowCount; rowIndex++) {
    sheet.getRow(rowIndex).alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
}

Set alignment and text wrapping of column 5 and 7:

sheet.getColumn(5).alignment = { vertical: 'middle', wrapText: true };
sheet.getColumn(7).alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };

Also, if you are wrapping text you probably want the cells to be text. You can set this in the column header style using numFmt: '@'. Like this:

const columnHeaders = [
    { header: 'Column 1 Title', key: 'COL_ONE', width: 10, style: { numFmt: '@' } },
    { header: 'Column 2 Title', key: 'COL_TWO', width: 32, style: { numFmt: '@' } },
]

sheet.columns = columnHeaders;

Answer 2

I implemented a workaround by detecting what rows the table was contained in, and then looping through every row, and then every row's cell. Then finally setting the wrapText on every cell.

//Record where the table starts
const firstTableRowNum = SOME_NUM;

//Create the table
ws.addTable({
    name: 'someTable',
    ref: `A${firstTableRowNum}`,
    columns: columns,
    rows: rows,
});

//Record table's last row
let lastRowNum = ws.lastRow.number;
const lastTableRowNum = lastRowNum;

//Loop through all table's row
for (let i=firstTableRowNum; i<= lastTableRowNum; i++) {
    const row = ws.getRow(i);

    //Now loop through every row's cell and finally set alignment
    row.eachCell({includeEmpty: true}, (cell => {
        cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
    }));
}

Can anyone see of a more efficient way to do this? I'd love to have done it another way while just creating the table's columns. But it seems like the API doesn't allow for that.