Sheetjs: 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