How to set cell width when export .xlsx files with js-xlsx

Question

I am trying to set a fixed column/cell width to my exported excel files with js-xlsx.

EDIT:

Here is the source of js-xlsx: https://github.com/SheetJS/js-xlsx

Answer 1

I found a snippet the the write test here https://github.com/SheetJS/js-xlsx/blob/master/tests/write.js#L14-L19

For quick reference, where ws is your worksheet.

var wscols = [
    {wch:6},
    {wch:7},
    {wch:10},
    {wch:20}
];

ws['!cols'] = wscols;

Answer 2

Extending the question, if you need to set automatic width base on your content, you can write as following:

const worksheet = XLSX.utils.aoa_to_sheet(arrayOfArray);
worksheet['!cols'] = fitToColumn(arrayOfArray);

function fitToColumn(arrayOfArray) {
    // get maximum character of each column
    return arrayOfArray[0].map((a, i) => ({ wch: Math.max(...arrayOfArray.map(a2 => a2[i] ? a2[i].toString().length : 0)) }));
}

This function assumes your first row has most columns. It then tries to find the widest cell in each column by calculating content character length.

Answer 3

Similar to cell width, you can set the cell height in the following way

var wsrows =  [
                 {hpt: 12}, // row 1 sets to the height of 12 in points
                 {hpx: 16}, // row 2 sets to the height of 16 in pixels
               ];

ws['!rows'] = wsrows; // ws - worksheet

Hint: If your worksheet data is auto generated and you don't know how many rows and columns are getting populated then you could use the following way to find the number of rows and columns in the worksheet for doing cell width/height formatting.

var range = XLSX.utils.decode_range(ws['!ref']);
var noRows = range.e.r; // No.of rows
var noCols = range.e.c; // No. of cols

Answer 4

Nothing new, but explicitly using the width property makes it a bit easier to maintain:

ws['!cols'] = [{ width: 20 }, { width: 20 }, { width: 150 } ]; //set col. widths

Here is the full list of properties you can give to these ColInfo objects though, they give reasons why each width exists, but they state you should use width > wpx > wch, depending on the type of sheet you have and what is available for your use case. More can be read here: https://docs.sheetjs.com/

Answer 5

public exportAsExcelFile(json: any[], excelFileName: string): void {

   const header = Object.keys(json[0]); // columns name

   var wscols = [];
   for (var i = 0; i < header.length; i++) {  // columns length added
     wscols.push({ wch: header[i].length + 5 })
   }
   const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
   worksheet["!cols"] = wscols;
}

Answer 6

Automated width by any content:

  private fixWidth(worksheet: XLSX.WorkSheet) {
    const data = XLSX.utils.sheet_to_json<any>(worksheet)
    const colLengths = Object.keys(data[0]).map((k) => k.toString().length)
    for (const d of data) {
      Object.values(d).forEach((element: any, index) => {
        const length = element.toString().length
        if (colLengths[index] < length) {
          colLengths[index] = length
        }
      })
    }
    worksheet["!cols"] = colLengths.map((l) => {
      return {
        wch: l,
      }
    })
  }

Answer 7

I used @Icycool 's answer as great example. I also upvoted that one as it was very useful for me.

I enhanced it a bit with a configurable default width if the content is smaller than the title and added a buffer so that it doesn't look crammed together.

Added comments and expanded to increase readability

 return Object.keys(dataSource[0][0]).map((key) => {
  return ({
    //pick the data value that has the most content
    wch: Math
      .max(...dataSource[0]

        //Iterate over the column object and return a width for each one
        .map((dataObj: any) => {

          //Return the width as the number of chars with a buffer
          let keyWidth = key.toString().length
          let width = dataObj[key]?.toString().length ?? defaultColumnWidth
          width = width + columnWidthBuffer

          //use a default with if it's smaller than the title
          if (width < keyWidth) {
            width = keyWidth
          }
          return width
        })
      )
  })
});