How to autosize column width in exceljs

Admin 1
Viewed 19249

I must to autosize column width with exceljs. My excel must be dynamic and save in Excel only the columns that the user will provide in the request. To this case I provide this syntax:

workSheet.getRow(1).values = dto.columns;

which save column name on the first row with names provided in dto.columns.

But i must set width to each of the column, i try this:

for(let i=0; i <= dto.columns.length-1; i++) {
            workSheet.columns = [
                {key: dto.columns[i], width: Object.keys(dto.columns[i]).length}
            ]
        }

but this don't set me any with :

can someone tell me how can i create an autosize function to this problem?

thanks for any help

exceljs typescript excel javascript
4 Answers

You iterate the cells and check the length of each in such a way

    worksheet.columns.forEach(function (column, i) {
        var maxLength = 0;
        column["eachCell"]({ includeEmpty: true }, function (cell) {
            var columnLength = cell.value ? cell.value.toString().length : 10;
            if (columnLength > maxLength ) {
                maxLength = columnLength;
            }
        });
        column.width = maxLength < 10 ? 10 : maxLength;
    });
user_60127110

It works for me, you can do it like this:

private AdjustColumnWidth(worksheet) {
  worksheet.columns.forEach(column => {
    const lengths = column.values.map(v => v.toString().length);
    const maxLength = Math.max(...lengths.filter(v => typeof v === 'number'));
    column.width = maxLength;
  });
}
user_86726110

Ashish's answer is working. Thanks

To ignore serial no if you have in first cell:

refer:

worksheet.columns.forEach(function (column, i) {
    if(i!==0)
    {
        var maxLength = 0;
        column["eachCell"]({ includeEmpty: true }, function (cell) {
            var columnLength = cell.value ? cell.value.toString().length : 10;
            if (columnLength > maxLength ) {
                maxLength = columnLength;
            }
        });
        column.width = maxLength < 10 ? 10 : maxLength;
    }
});
user_159229180

My answer may be coming in a little bit too late, but i hope this helps out. The above examples work just fine. Just a little modification to ensure that all works well. To prevent overriding of column withs, this is what works for me

 worksheet.columns.forEach(function (column) {
              var dataMax = 0;
              column.eachCell({ includeEmpty: true }, function (cell) { 
                dataMax = cell.value?cell.value.toString().length:0;
                if(dataMax <= (column.header.length+2) ){
                    if(column.width > dataMax){
                        //retain its default width
                    } else {
                        column.width = column.header.length+3;
                    }
                } else {
                    column.width = dataMax+3;
                   column.header.length = dataMax+3;
                }
                dataMax = 0;
              })
              
            });
user_114983590
Related