How to autosize column width in exceljs

Question

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

Ashish S · Answer 1 · 2023-05-13T10:33:46.750

21

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

    worksheet.columns.forEach(function (column, i) {
        let 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;
    });

edited May 13 '23 at 10:33

answered Sep 28 '20 at 07:31

Ashish S

638
6
14

what is the "eachCell" is this a property of some sort? – Asma Rahim Ali Jafri May 16 '21 at 17:23
1

@AsmaRahimAliJafri eachCell is similar to forEach for array in JS.....basically it iterates over each cell – Ashish S Jul 24 '21 at 07:54
but what is columns here in `worksheet.columns.forEach` how to use this please be more specific ! – Kunal Pandey Dec 11 '22 at 15:21
1

@KunalPandey columns is the array of columns in your sheet – Ashish S Dec 16 '22 at 14:10

score 8 · Answer 2 · answered Sep 22 '21 at 16:15

8

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;
  });
}

answered Sep 22 '21 at 16:15

Ariel R Condori Rodriguez

81
1
4

2

I found this works well and is more succinct than the other options. Adding a little padding based on the fact that I was using `autoFilter` and factoring in the header's width made it just about perfect: `column.width = Math.max(Math.max(...lengths.filter(v => typeof v === 'number')) + 2, column.header.length + 6);` – bsplosion Dec 07 '21 at 02:52

score 2 · Answer 3 · answered Feb 12 '23 at 19:14

If you find that other solutions aren't working when there's a numFmt involved, especially for Date values, here is a solution that uses some of the concepts previously suggested, as well as the SSF library to format cell values with numFmt before getting the length.

import { format } from 'ssf';

// Convert Date object to Microsoft serial date aka ms date aka OA date
const dateToSerial = (date: Date): number => {
  const timezoneOffset = date.getTimezoneOffset() / (60 * 24);
  const msDate = date.getTime() / 86400000 + (25569 - timezoneOffset);
  return msDate;
};

const autoFitColumn = (column: ExcelJS.Column) => {
  const numFmt = column.numFmt;
  let maxLength = 6;
  column.eachCell({ includeEmpty: true }, (cell: ExcelJS.Cell) => {
    let columnLength: number;
    if (numFmt && cell.value != undefined) {
      switch (cell.type) {
        case ExcelJS.ValueType.Date:
          const serialDate = dateToSerial(cell.value as Date);
          const formattedDate = format(numFmt, serialDate);
          columnLength = formattedDate.length;
          break;
        case ExcelJS.ValueType.Number:
          const formattedNumber = format(numFmt, cell.value as Number);
          columnLength = formattedNumber.length;
          break;
        default:
          const formatted = format(numFmt, cell.value);
          columnLength = formatted.length;
          break;
      }
    } else {
      columnLength = cell.text.length;
    }
    maxLength = Math.max(maxLength, columnLength);
  });
  column.width = maxLength + 2;
};

score 1 · Answer 4 · edited May 17 '21 at 21:34

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;
    }
});

score 1 · Answer 5 · answered Oct 25 '21 at 07:25

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;
              })
              
            });

column.header is undefined for me. – Neurothustra Jun 16 '22 at 15:30 — Neurothustra, Jun 16 '22 at 15:30

score 0 · Answer 6 · answered Jun 24 '23 at 17:33

This worked for me :

worksheet.columns.forEach((column) => {
    let maxLength = 0;
    column["eachCell"]({ includeEmpty: true }, (cell) => {
        const columnLength = cell.value ? cell.value.toString().length + 3 : 10;
        if (cell.type === ExcelJS.ValueType.Date) {
            maxLength = 20;
        }
        else if (columnLength > maxLength) {
            maxLength = columnLength + 3;
        }
    });
    column.width = maxLength < 10 ? 10 : maxLength;
});

How to autosize column width in exceljs

6 Answers6