how to set the automatic width?

[cjlhll]

Hello, use it for the first time. I don't know how to set the col width. The content is hidden

[LuisEnMarroquin]

You can set the width of a column doing this

let newSheet = utils.json_to_sheet(yourData)
newSheet['!cols'].push({ width: 20 })

I created a library that does it automatically and uses this xlsx library, check out json-as-xlsx

[SheetJSDev]

The file formats do not have an option to recalculate width on read. You can use VBA for it by calling the sheet .Range("A:Z").EntireColumn.AutoFit OR you can set the wch property of the specific column array object to the desired number of characters as explained in the README

PS: our Pro Basic build solves the problem by iterating through the cells in each row, deducing Excel's calculated width based on the Maximum Digit Width algorithm, then taking the largest width

[chenlitchian]

@cjlhll

Please see my solution

1. get maximum width from the json data
2. set column width

let objectMaxLength = []; 
    for (let i = 0; i < json.length; i++) {
      let value = <any>Object.values(json[i]);
      for (let j = 0; j < value.length; j++) {
        if (typeof value[j] == "number") {
          objectMaxLength[j] = 10;
        } else {
          objectMaxLength[j] =
            objectMaxLength[j] >= value[j].length
              ? objectMaxLength[j]
              : value[j].length;
        }
      }
    }
    console.log(objectMaxLength);

    var wscols = [
      { width: objectMaxLength[0] },  // first column
      { width: objectMaxLength[1] }, // second column
      { width: objectMaxLength[2] }, //...
      { width: objectMaxLength[3] }, 
      { width: objectMaxLength[4] },
      { width: objectMaxLength[5] }, 
      { width: objectMaxLength[6] }, 
      { width: objectMaxLength[7] }, 
      { width: objectMaxLength[8] },
      { width: objectMaxLength[9] }
    ];

    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
    worksheet["!cols"] = wscols;

[dave-wind]

you can try see it https://dave-wind.github.io/excel/index.html

[iamandreadompe]

@chenlitchian i've edited your code for complete autofit with values (your versione) and headers

		for (let i = 0; i < json.length; i++) {
		let value = <any>Object.values(json[i]);
		for (let j = 0; j < value.length; j++) {
			if (typeof value[j] == "number") {
				objectMaxLength[j] = 10;
			} else {
				objectMaxLength[j] =
					objectMaxLength[j] >= value[j].length
						? objectMaxLength[j]
						: value[j].length;
			}
		}
		let key = <any>Object.keys(json[i]);
		for (let j = 0; j < key.length; j++) {
			objectMaxLength[j] =
				objectMaxLength[j] >= key[j].length
					? objectMaxLength[j]
					: key[j].length;
		}
	}

[andreElrico]

Creds: @chenlitchian + @dave-wind

This is esp useful when you are user header option that will change the order of your cols. (Please note this is typescript. Just remove the types from fun-args).

I ended up using:

  private autofitColumns(json: any[], worksheet: XLSX.WorkSheet, header?: string[]) {

    const jsonKeys = header ? header : Object.keys(json[0]);

    let objectMaxLength = []; 
    for (let i = 0; i < json.length; i++) {
      let value = json[i];
      for (let j = 0; j < jsonKeys.length; j++) {
        if (typeof value[jsonKeys[j]] == "number") {
          objectMaxLength[j] = 10;
        } else {

          const l = value[jsonKeys[j]] ? value[jsonKeys[j]].length : 0;

          objectMaxLength[j] =
            objectMaxLength[j] >= l
              ? objectMaxLength[j]
              : l;
        }
      }

      let key = jsonKeys;
      for (let j = 0; j < key.length; j++) {
        objectMaxLength[j] =
          objectMaxLength[j] >= key[j].length
            ? objectMaxLength[j]
            : key[j].length;
      }
    }

    const wscols = objectMaxLength.map(w => { return { width: w} });

    worksheet["!cols"] = wscols;

  }

[lewiswolf]

All great solutions, thanks. But value.length does not give the same column width as Excel's own auto-fit... is there any way around this? Is there something I'm missing?

function formatExcelCols(json) {
    let widthArr = Object.keys(json[0]).map(key => {
        return { width: key.length + 2 } // plus 2 to account for short object keys
    })
    for (let i = 0; i < json.length; i++) {
        let value = Object.values(json[i]);
        for (let j = 0; j < value.length; j++) {
            if (value[j] !== null && value[j].length > widthArr[j].width) {
                widthArr[j].width = value[j].length;
            }
        }
    }
    return widthArr
}

[SheetJSDev]

Column widths are nontrivial: https://docs.sheetjs.com/#column-properties (read this first)

As an auto-fit example, try setting A1 to "iiiiiiiiii" (10 lowercase letter i) and setting B1 to "wwwww" (5 lowercase letter w), then adjust the widths. The 10-character string is actually smaller than the 5-character string!

There are a few other complicating factors, like conditional formatting and tables ("action at a distance"), and the auto-filter box.

[JonArnfred]

One could also use the canvas method measureText() to get the width of a text string in px, see https://developer.mozilla.org/en-US/docs/Web/API/CanvasRenderingContext2D/measureText.

const text = "something";
const context = document.createElement("canvas").getContext('2d');
context.font = '12px arial';
const width = context.measureText(text).width;

[MohannadNaj]

For anyone whose looking for doing this for Array of Arrays (aoa), I'm sharing how I did it.

Not sure the check for null values is necessary but it was necessary in my case.

let aoa = [['array'], ['of'], ['arrays']]

let worksheet = XLSX.utils.aoa_to_sheet(aoa);

let objectMaxLength = []

aoa.map(arr => {
  Object.keys(arr).map(key => {
    let value = arr[key] === null ? '' : arr[key]

    if (typeof value === 'number')
    {
      return objectMaxLength[key] = 10
    }

    objectMaxLength[key] = objectMaxLength[key] >= value.length ? objectMaxLength[key]  : value.length
  })
})

let worksheetCols = objectMaxLength.map(width => {
  return {
    width
  }
})

worksheet["!cols"] = worksheetCols;

XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);

[SheetJSDev]

@JonArnfred the big issue with measureText is that the system-wide Calibri font (present in the various TTFs that ship with Office) do not have the same metrics as the "Calibri (Body)" that Excel uses by default.

@MohannadNaj within the aoa.map, arr is an array so you can simply forEach:

aoa.forEach(arr => {
  arr.forEach((value, key) => {
    let len = 0;
    switch(typeof value) {
      case "number": len = 10; break;
      case "string": len = value.length; break;
      case "object": if(value instanceof Date) len = 10; break; 
    }
    objectMaxLength[key] = Math.max(objectMaxLength[key], len);
  });
});

[henridev]

for those just passing a worksheet i found this to be the fasted way to get an autofit (using the lodash range function)

export function autofitColumns(worksheet: WorkSheet) {
  let objectMaxLength: ColInfo[] = [];
  const [startLetter, endLetter] = worksheet['!ref']?.replace(/\d/, '').split(':')!;
  const ranges = range(startLetter.charCodeAt(0), endLetter.charCodeAt(0) + 1);
  ranges.forEach((c) => {
    const cell = String.fromCharCode(c);
    const cellLength = worksheet[`${cell}1`].v.length + 1;
    objectMaxLength.push({ width: cellLength });
  });
  worksheet['!cols'] = objectMaxLength;
}

[sysoft]

ref: https://github.com/adambisek/string-pixel-width

npm install string-pixel-width

[lieunttuit]

Creds: @andreElrico , @sysoft I re-changed for my way and share it in case someone needed it.

npm install string-pixel-width

  const exportData [{name: 'foo', age: 12}, {name: 'bar', age: 12}]
  const time = new Dayjs().format('YYYYMMDDHHmmss')
  const workbook = XLSX.utils.book_new()
  const sheet = XLSX.utils.json_to_sheet(exportData)
  const wscols = _autoFitColumns(exportData, sheet)
  sheet['!cols'] = wscols
  XLSX.utils.book_append_sheet(workbook, sheet, 'Sheet1')
  XLSX.writeFile(workbook, `${time}.xlsx`)


  const _autoFitColumns = (json, worksheet, header) => {
    const jsonKeys = header || Object.keys(json[0])

    const objectMaxLength = []
    jsonKeys.forEach((key) => {
      objectMaxLength.push(
        pixelWidth(key, {
          size: 5,
        })
      )
    })

    json.forEach((data, i) => {
      const value = json[i]
      jsonKeys.forEach((key, j) => {
        const l = value[jsonKeys[j]]
          ? pixelWidth(value[jsonKeys[j]], {
              size: 5,
            })
          : 0
        objectMaxLength[j] = objectMaxLength[j] >= l ? objectMaxLength[j] : l
      })
    })

    return objectMaxLength.map((w) => {
      return { width: w }
    })
  }

[jacanon]

Thanks for the helpful answers! I ended up using this solution:

private autofitColumns(json: any[], worksheet: XLSX.WorkSheet) {

  let objectMaxLength: number[] = [];
  
  json.map(jsonData => {
     Object.entries(jsonData)
           .map(([, v], idx) => {
              let columnValue = v as string
              objectMaxLength[idx] = objectMaxLength[idx] >= columnValue.length ? objectMaxLength[idx] : columnValue.length
           })
  })
  
  const wscols = objectMaxLength.map((w: number) => ({width: w}))
  worksheet["!cols"] = wscols;

}

[Mykola-Veryha]

More examples:

var wscols = [
	{wch: 6}, // "characters"
	{wpx: 50}, // "pixels"
	,
	{hidden: true} // hide column
];

/* At 96 PPI, 1 pt = 1 px */
var wsrows = [
	{hpt: 12}, // "points"
	{hpx: 16}, // "pixels"
	,
	{hpx: 24, level:3},
	{hidden: true}, // hide row
	{hidden: false}
];

https://github.com/SheetJS/sheetjs/blob/10ae7c9fec50a5c857f2e61c7b610445c50b4048/tests/write.js#L21-L26

[gentunian]

for those just passing a worksheet i found this to be the fasted way to get an autofit (using the lodash range function)

export function autofitColumns(worksheet: WorkSheet) {
  let objectMaxLength: ColInfo[] = [];
  const [startLetter, endLetter] = worksheet['!ref']?.replace(/\d/, '').split(':')!;
  const ranges = range(startLetter.charCodeAt(0), endLetter.charCodeAt(0) + 1);
  ranges.forEach((c) => {
    const cell = String.fromCharCode(c);
    const cellLength = worksheet[`${cell}1`].v.length + 1;
    objectMaxLength.push({ width: cellLength });
  });
  worksheet['!cols'] = objectMaxLength;
}

How is this working? Just incrementing v.length by one?

[SheetJSDev]

File formats don't have a flag to recalculate widths on open, so any "automatic width" approach has to be calculated when generating the spreadsheet file.

The naive method of looking at string lengths is decent on average. For Calibri the degenerate cases involve "i" and "w": 6 "w" is wider than 8 "0" and 8 "0" is wider than 16 "i".

Since column widths are written before the string table, this will require a second workbook scan.

Open questions:

0. Given that the calculation can be done outside of the library, and there is no implementation advantage to integrating it in the library, should it be added?

1. If it is added, should this behavior be "opt-in" or "opt-out" (always calculate if no width is specified)?

2. If the behavior is opt-in, should this be implemented as a column property or relegated to an API function (XLSX.utils.sheet_calc_col_width)?

[dhanielsales]

I hope I can help :)

const data = [['aaaaa  aaaaa  aaaaaaaaaaa', 'bbbb', 'ccccc', 'dd']]
const minWidth = 10

const worksheet = XLSX.utils.aoa_to_sheet(aoa);

const howManyColumns = data.reduce((prev, curr) => {
  return curr.length > prev ? curr.length : prev
}, 0)

let currentWidht = minWidth
const worksheetCols = []

for (const index in Array.from({ length: howManyColumns })) {
  for (const curr of data) {
    currentWidht =
      curr[index].length > currentWidht ? curr[index].length : currentWidht
  }

  worksheetCols.push({
    width: currentWidht
  })

  currentWidht = minWidth
}

worksheet["!cols"] = worksheetCols;

XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);

[Argon2000]

for those just passing a worksheet i found this to be the fasted way to get an autofit (using the lodash range function)

export function autofitColumns(worksheet: WorkSheet) {
  let objectMaxLength: ColInfo[] = [];
  const [startLetter, endLetter] = worksheet['!ref']?.replace(/\d/, '').split(':')!;
  const ranges = range(startLetter.charCodeAt(0), endLetter.charCodeAt(0) + 1);
  ranges.forEach((c) => {
    const cell = String.fromCharCode(c);
    const cellLength = worksheet[`${cell}1`].v.length + 1;
    objectMaxLength.push({ width: cellLength });
  });
  worksheet['!cols'] = objectMaxLength;
}

Without using lodash, and searches all rows:

export function autoFitColumns(worksheet: WorkSheet) {
    const [startLetter, endLetter] = worksheet['!ref']?.replace(/\d/, '').split(':')!;
    let numRegexp = new RegExp(/\d+$/g);
    let start = startLetter.charCodeAt(0), end = endLetter.charCodeAt(0) + 1, rows = +numRegexp.exec(endLetter)[0];
    let ranges: number[] = [];
    for(let i = start; i < end; i++) {
        ranges.push(i);
    }
    let objectMaxLength: ColInfo[] = [];
    ranges.forEach((c) => {
        const cell = String.fromCharCode(c);
        let maxCellLength = 0;
        for(let y = 1; y <= rows; y++) {
            let cellLength = worksheet[`${cell}${y}`].v.length + 1;
            if(cellLength > maxCellLength) {
                maxCellLength = cellLength;
            }
        }
        objectMaxLength.push({ width: maxCellLength });
    });
    worksheet['!cols'] = objectMaxLength;
}

Thanks for the base code btw, works great!

[pleymor]

Same with more explicit variable names and removal of ranges:

/**
 * Set the width of all columns of the given worksheet,
 * to fit the content of the cells.
 */
export function autoFitColumns(worksheet: WorkSheet): void {
  const [firstCol, lastCol] = worksheet['!ref']?.replace(/\d/, '').split(':')

  const numRegexp = new RegExp(/\d+$/g)

  const firstColIndex = firstCol.charCodeAt(0),
    lastColIndex = lastCol.charCodeAt(0),
    rows = +numRegexp.exec(lastCol)[0]

  const objectMaxLength: ColInfo[] = []

  // Loop on columns
  for (let colIndex = firstColIndex; colIndex <= lastColIndex; colIndex++) {
    const col = String.fromCharCode(colIndex)
    let maxCellLength = 0

    // Loop on rows
    for (let row = 1; row <= rows; row++) {
      const cellLength = worksheet[`${col}${row}`].v.length + 1
      if (cellLength > maxCellLength) maxCellLength = cellLength
    }

    objectMaxLength.push({ width: maxCellLength })
  }
  worksheet['!cols'] = objectMaxLength
}

Usage:

const worksheet = XLSX.utils.json_to_sheet(jsonObject)
autoFitColumns(worksheet)

[centralToNowhere]

@pleymor

Same with more explicit variable names and removal of ranges:

/**
 * Set the width of all columns of the given worksheet,
 * to fit the content of the cells.
 */
export function autoFitColumns(worksheet: WorkSheet): void {
  const [firstCol, lastCol] = worksheet['!ref']?.replace(/\d/, '').split(':')

  const numRegexp = new RegExp(/\d+$/g)

  const firstColIndex = firstCol.charCodeAt(0),
    lastColIndex = lastCol.charCodeAt(0),
    rows = +numRegexp.exec(lastCol)[0]

  const objectMaxLength: ColInfo[] = []

  // Loop on columns
  for (let colIndex = firstColIndex; colIndex <= lastColIndex; colIndex++) {
    const col = String.fromCharCode(colIndex)
    let maxCellLength = 0

    // Loop on rows
    for (let row = 1; row <= rows; row++) {
      const cellLength = worksheet[`${col}${row}`].v.length + 1
      if (cellLength > maxCellLength) maxCellLength = cellLength
    }

    objectMaxLength.push({ width: maxCellLength })
  }
  worksheet['!cols'] = objectMaxLength
}

Usage:

const worksheet = XLSX.utils.json_to_sheet(jsonObject)
autoFitColumns(worksheet)

I think that using charCodeAt(0) is not very reliable cause in theory column name could be something like AA:1

    for (let row = 1; row <= rows; row++) {
      const cellLength = worksheet[`${col}${row}`].v.length + 1
      if (cellLength > maxCellLength) maxCellLength = cellLength
    }

and, for example, if you start from origin A3 then A1 does not exist -> worksheet[${col}${row}].v is undefined

[gersur]

Improving from https://github.com/SheetJS/sheetjs/issues/1473#issuecomment-1339690813 With code from https://stackoverflow.com/a/34910965/5989987 to generate array of column address

I've tested with cell address until BJ:2

  autoFitColumns(worksheet: WorkSheet): void {
    const objectMaxLength: ColInfo[] = [];

    const lastRow = XLSX.utils.decode_range(worksheet["!ref"]!).e.r;

    for (const column of _buildColumnsArray(worksheet)) {
      let maxCellLength = 0;

      for (let row = 1; row <= lastRow + 1; row++) {
        const cell = worksheet[`${column}${row}`];

        if (cell != null) {
          const cellLength = Math.ceil(cell.v.length * 1.1); // adjust column width multiplier
          if (cellLength > maxCellLength) maxCellLength = cellLength;
        }
      }

      objectMaxLength.push({ width: maxCellLength });
    }

    worksheet["!cols"] = objectMaxLength;
  }

  _buildColumnsArray(sheet: WorkSheet) {
    const alphaToNum = (alpha: string) => {
      let num = 0;
      for (let i = 0; i < alpha.length; i++) {
        num = num * 26 + alpha.charCodeAt(i) - 0x40;
      }

      return num - 1;
    };

    const numToAlpha = (num: number) => {
      let alpha = "";

      for (; num >= 0; num = num / 26 - 1) {
        alpha = String.fromCharCode((num % 26) + 0x41) + alpha;
      }

      return alpha;
    };

    const rangeNum = sheet["!ref"]!.split(":").map((val) => alphaToNum(val.replace(/[0-9]/g, "")));
    const res: string[] = [];

    for (let i = rangeNum[0]; i < rangeNum[1] + 1; i++) {
      res.push(numToAlpha(i));
    }

    return res;
  }

[rwieruch]

I extended the solution by @henridev (thanks for the foundation!) because their solution only checks the length of each cell for the header row. However, I want to have each column adapt to its widest cell content. Therefore we have to check each cell's content in a column and use the widest one to set the width.

import * as XLSX from 'xlsx/xlsx.mjs';
import range from 'lodash.range';

function getEndingDigit(inputString) {
  // Define a regular expression to extract the ending digits
  const regex = /\d+$/;

  // Use the test method to check if the inputString matches the pattern
  if (regex.test(inputString)) {
    // Use the match method to extract the ending digits from the inputString
    const match = inputString.match(regex);
    // Remove any non-digit characters before returning the result
    return match[0].replace(/\D/g, '');
  }

  // Return null if there are no ending digits
  return null;
}

function autofitColumns(worksheet) {
  let objectMaxLength = [];

  const [startLetter, endLetter] = worksheet['!ref']?.replace(/\d/, '').split(':');
  const ranges = range(startLetter.charCodeAt(0), endLetter.charCodeAt(0) + 1);

  ranges.forEach((c) => {
    const cellHeader = String.fromCharCode(c);

    const maxCellLengthForWholeColumn = Array.from(
      { length: getEndingDigit(worksheet['!ref']) - 1 },
      (_, i) => i
    ).reduce((acc, i) => {
      const cell = worksheet[`${cellHeader}${i + 1}`];

      // empty cell
      if (!cell) return acc;

      const charLength = cell.v.length + 1;

      return acc > charLength ? acc : charLength;
    }, 0);

    objectMaxLength.push({ wch: maxCellLengthForWholeColumn });
  });
  worksheet['!cols'] = objectMaxLength;
}

export const downloadAsExcel = async (element) => {
  // Extract Data (create a workbook object from the table)
  const workbook = XLSX.utils.table_to_book(element, { raw: true });

  // manipulate worksheet
  const ws = workbook.Sheets['Sheet1'];
  autofitColumns(ws);

  // Package and Release Data (`writeFile` tries to write and save an XLSB file)
  XLSX.writeFile(workbook, 'report.xlsx');
};