2

I want to set column width equal to maximum length of the content of that column when exporting .xlsx files with js-xlsx

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

I tried with above. 

let sheet = workBook.Sheets.Sheet1;
let wscols = [];
const colNode = table.tHead.rows[0].childNodes; // getting the column names from the table rendered on the page
const colNumber = colNode.length;
for(let i = 0; i < colNumber; i++){
    wscols.push({
        wch: colNode[i].innerText.length * 1.28
    })
}
sheet['!cols'] = wscols;

I'm able to set the width by fetching the name of column from the table rendered on the screen. But problem I'm getting is that suppose the name of column name is "Title" and its rows have some value like "Australia" then length of column name is less and the whole column width are not properly set.

Actual enter image description here

Expected enter image description here

Kikit
  • 81
  • 2
  • 11
  • Loop through your data and try to find out if there are texts with bigger character count than the column titles, if yes use those values to set `wch` parameter – codtex Jun 28 '19 at 07:57
  • 1
    that will be overkill since there are millions of data. – Kikit Jul 01 '19 at 13:55

1 Answers1

1
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;

}

From https://github.com/SheetJS/sheetjs/issues/1473#issuecomment-580648494

Geovani Anholete
  • 387
  • 1
  • 5