Auto Column Width

[iwanjunaid]

Is there a way to set column width based on cell's content length ?

thank you

[Toub]

+1
Would be very helpful!

[CassianoNeves]

+1

[richardaum]

+1

[hmtuan196]

+1

[StephenErstad-zz]

+1

[rhernandez-itemsoft]

+1

[mmattosr]

look #36

[lnmput]

+1

[CrescentCraftorium]

Hopfully this because something built into ExcelJS down the road, but in the meantime this is how I solved the issue:

const wb = new Excel.Workbook()
const ws = wb.addWorksheet('TestSheet')

ws.columns = [
  { header: 'Column1', key: 'col1'},
  { header: 'ColumnTwo', key: 'col2'},
  { header: 'Col3', key: 'col3'},
  { header: 'TheGreatColFour', key: 'col4'},
  { header: 'JustCol5', key: 'col5'},
]

ws.columns.forEach(column => {
  column.width = column.header.length < 12 ? 12 : column.header.length
})

This allows you to have a minimum width (12) or base the column width off the number of characters in the header string. Sadly, unless you're using a mono typeface this isn't going to be exactly accurate, but it'll get you a lot closer.

If you need to base it off of contents instead of header length, you can always loop through the rows to find the longest string length and base the width off of that.

[PashaFrontEnd]

there is an array with values ​​if I add the row all well, all values ​​are exported to excel, but I can not build the column ((( who can do??

[karlvr]

I've had some luck with the still rough but maybe more accurate approach below in TypeScript:

/** Determined this value based upon experimentation */
export const PIXELS_PER_EXCEL_WIDTH_UNIT = 7.5

export function autoSize(sheet: excel.Worksheet, fromRow: number) {
	const canvas = document.createElement('canvas')
	const ctx = canvas.getContext('2d')
	if (!ctx) {
		return
	}

	const maxColumnLengths: Array<number> = []
	sheet.eachRow((row, rowNum) => {
		if (rowNum < fromRow) {
			return
		}

		row.eachCell((cell, num) => {
			if (typeof cell.value === 'string') {
				if (maxColumnLengths[num] === undefined) {
					maxColumnLengths[num] = 0
				}

				const fontSize = cell.font && cell.font.size ? cell.font.size : 11
				ctx.font = `${fontSize}pt Arial`
				const metrics = ctx.measureText(cell.value)
				const cellWidth = metrics.width
				
				maxColumnLengths[num] = Math.max(maxColumnLengths[num], cellWidth)
			}
		})
	})

	for (let i = 1; i <= sheet.columnCount; i++) {
		const col = sheet.getColumn(i)
		const width = maxColumnLengths[i]
		if (width) {
			col.width = width / PIXELS_PER_EXCEL_WIDTH_UNIT + 1
		}
	}
}

[ameliarahman]

This worked well for me :

`// Auto width column
const workbook = new Excel.Workbook();
const sheet1 = workbook.addWorksheet('sheet1');

  for (let i = 0; i < sheet1.columns.length; i += 1) { 
    let dataMax = 0;
    const column = sheet1.columns[i];
    for (let j = 1; j < column.values.length; j += 1) {
      const columnLength = column.values[j].length;
      if (columnLength > dataMax) {
        dataMax = columnLength;
      }
    }
    column.width = dataMax < 10 ? 10 : dataMax;
  }`

[mllrdev-miguelsobong]

+1

[cerinoligutom]

+1

[boyepanthera]

This worked well for me :

`// Auto width column
const workbook = new Excel.Workbook();
const sheet1 = workbook.addWorksheet('sheet1');

  for (let i = 0; i < sheet1.columns.length; i += 1) { 
    let dataMax = 0;
    const column = sheet1.columns[i];
    for (let j = 1; j < column.values.length; j += 1) {
      const columnLength = column.values[j].length;
      if (columnLength > dataMax) {
        dataMax = columnLength;
      }
    }
    column.width = dataMax < 10 ? 10 : dataMax;
  }`

Very nice explanation, but when I tried this has a performance issue, my worksheet has lots of rows /columns running into thousands. Any help on optimising my response time.

[weihsth]

A bit different solution and in TypeScript:

private _autosizeColumnCells({columns}: Worksheet) {
    let dataMax: number[];
    let max: number;
    columns.forEach((column: Column) => {
        dataMax = [];
        column.eachCell({includeEmpty: false}, (cell: Cell) => {
            dataMax.push(cell.value?.toString().length || 0);
        });
        max = Math.max(...dataMax);
        column.width = max < 10 ? 10 : max;
    });
}`

[pattersongarth]

worksheet.columns.forEach(function (column) {
  var dataMax = 0;
  column.eachCell({ includeEmpty: true }, function (cell) {
    dataMax = cell.value?cell.value.toString().length:0;
  })
  column.width = dataMax < 10 ? 10 : dataMax;
});

[hellobq]

worksheet.columns.forEach(function (column) {
  var dataMax = 0;
  column.eachCell({ includeEmpty: true }, function (cell) {
    dataMax = cell.value?cell.value.toString().length:0;
  })
  column.width = dataMax < 10 ? 10 : dataMax;
});

although the cells can be expanded normally, this calculation method seems to be inaccurate.
do you know the default unit of width? cm or wch?

[mvermadev]

A little bit diff way to make it reusable for all worksheets.

`class Exporter{
widthConfig(data)
{
data.forEach(function(column){
var dataMax = 0;
column.eachCell({ includeEmpty: true }, function(cell){
var columnLength = cell.value.length;
if (columnLength > dataMax) {
dataMax = columnLength;
}
})
column.width = dataMax <= 10 ? 25 : dataMax;
console.log('column.width: ', column.width)
});

}

export(){
this.widthConfig(worksheet.columns)
}
}`

[MeisQuietude]

Feel free to use my solution

/**
 * Autofit columns by width
 *
 * @param worksheet {ExcelJS.Worksheet}
 * @param minimalWidth
 */
const autoWidth = (worksheet, minimalWidth = 10) => {
    worksheet.columns.forEach((column) => {
        let maxColumnLength = 0;
        column.eachCell({ includeEmpty: true }, (cell) => {
            maxColumnLength = Math.max(
                maxColumnLength,
                minimalWidth,
                cell.value ? cell.value.toString().length : 0
            );
        });
        column.width = maxColumnLength + 2;
    });
};

[ttcy]

Inspired by @MeisQuietude solution for autoHeight

const autoHeight = (worksheet) => {
    const lineHeight = 12 // height per line is roughly 12
    worksheet.eachRow((row) => {
        let maxLine = 1
        row.eachCell((cell) => {
            maxLine = Math.max(cell.value.split('\n').length - 1, maxLine)
        })
        row.height = lineHeight * maxLine
    })
}

Caveat: Performance will be slow due to the iteration of between rows and cells!

[aditya9970]

I Use this logic for resizing the height & should also work with merged cells.
public static autoCellHeight(workSheet : Worksheet , rowNumberMin ? : number , rowNumberMax ? : number) {
let columnWidths = workSheet.columns.map((column) => column.width);
if(rowNumberMin && rowNumberMax) {
for(let i = (rowNumberMin || 1); i <= (rowNumberMax || workSheet.lastRow.number); i++) {
let multiplier = 1;
let mergedCells = 0;
let mergedCellWidth = 0;
let mergedContentWidth = 0;
workSheet.getRow(i).eachCell({ includeEmpty: false },(cell, colNumber)=> {
if(cell.isMerged) {
// @ts-ignore
if(cell._mergeCount) {
// @ts-ignore
mergedCells = cell._mergeCount + 1;
mergedContentWidth = cell.value?.toString().length || 0;
}
mergedCellWidth = mergedCellWidth + columnWidths[colNumber - 1];
mergedCells = mergedCells - 1;
if(mergedCells === 0) {
multiplier = Math.max(Math.ceil(mergedContentWidth / mergedCellWidth) , multiplier);
mergedCells= 0;
mergedCellWidth= 0;
mergedContentWidth = 0;
}
} else {
multiplier = Math.max((cell.value ?
Math.ceil((cell.value.toString().length + 1) / columnWidths[colNumber - 1]) : 1), multiplier);
}
});
workSheet.getRow(i).height = (workSheet.getRow(i).height || 15.75) * multiplier;
}
}
}
if anyone can give me what scaler can be used for different font sizes for arial that would be really helpful

[anton-advasoftware]

Here is the answer I was looking for, hope it would be helpful for someone.
range.format.autofitColumns();
https://learn.microsoft.com/en-us/javascript/api/excel/excel.rangeformat?view=excel-js-preview#excel-excel-rangeformat-autofitcolumns-member(1)

For height you can see https://learn.microsoft.com/en-us/javascript/api/excel/excel.rangeformat?view=excel-js-preview#excel-excel-rangeformat-autofitrows-member(1)

[rnwelsh]

Here is the answer I was looking for, hope it would be helpful for someone. range.format.autofitColumns(); https://learn.microsoft.com/en-us/javascript/api/excel/excel.rangeformat?view=excel-js-preview#excel-excel-rangeformat-autofitcolumns-member(1)

For height you can see https://learn.microsoft.com/en-us/javascript/api/excel/excel.rangeformat?view=excel-js-preview#excel-excel-rangeformat-autofitrows-member(1)

Unfortunately, that is the Office Add-ins JavaScript API, which will only work inside an instance of Excel. Autofitting columns is an internal API to Excel program itself, and people have been trying to figure out how to gracefully implement it within the Office Open XML spec for years ( what everyone besides Microsoft has to grapple with to create XLSX files ). The best solution to do this (still kindof a hack) has been around for a long time, but It only works when the spreadsheet is opened by excel, and only sometimes... you have to set these attributes on the columns of a worksheet : {BestFit : true, CustomWidth : true} There are caveats to this since this is kindof a backwards way of achieving autofit. See:
how to use "bestfit" attribute to autofit column width - MSDN
also
ECMA-376 - Office Open XML - 1st Edition

[RomanVavryn]

@MeisQuietude I'm using Angular, so I adapted your solution to use it in service like a method:

import * as ExcelJS from 'exceljs';

@Injectable()
export class ExportService {
// code
    private setHeadersWidth(worksheet: ExcelJS.Worksheet): void {
        const minColumnLength: number = 10;
        worksheet.eachColumnKey((column: ExcelJS.Column): void => {
            let columnLength = 0;
            column.eachCell((cell: ExcelJS.Cell): void => {
                columnLength = Math.max(columnLength, minColumnLength, cell?.value ? cell.value?.toString().length : 0);
            });
            column.width = columnLength + 2;
        });
    }
// code
}

[eberridge]

When there's a numFmt involved, especially for Date values, the solutions given above can produce very inaccurate results. Below is my 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;
};

[vladPopovschii]

When there's a numFmt involved, especially for Date values, the solutions given above can produce very inaccurate results. Below is my 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;
};

This works really great!
Only a suggestion, add cases for rich text and formula, format on those type of values throws error:
This is my addition (i didn't find a solution of how to get value of formula after calculation):

        case ValueType.RichText:
          const richTextValue = (cell.value as CellRichTextValue).richText.map(prop('text')).join('');
          columnLength = richTextValue.length;
          break;
        case ValueType.Formula:
          break;
      

[Geriano]

+1

[andersonespindola]

This works for me!
You can replace the "columns" and "rows" variables with your column and row arrays

    /**
     * Calculate columns width.
     */
    const columnWidths = columns.map(
      (column, columnIndex) => {
        /**
         * Max width for each column.
         */
        const maxContentWidth = rows.reduce((maxWidth, row) => {
          const cellValue = row[columnIndex]
          const cellWidth = cellValue ? String(cellValue).length : 0
          return Math.max(maxWidth, cellWidth)
        }, column.name.length)

        /**
         * Add a extra space.
         */
        return maxContentWidth + 2
      }
    )

    /**
     * Apply width.
     */
    columnWidths.forEach((width, columnIndex) => {
      worksheet.getColumn(columnIndex + 1).width = width
    })

[yossicahen]

+1