javascript - How to set the width of the entire column based on the content when export .xlsx files with js-xlsx? - Stack Overfl

admin管理员组

文章数量:1312800

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

Expected

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

Expected

• javascript
• xlsx
• js-xlsx
• sheetjs

Share Improve this question Follow edited Jun 25, 2019 at 12:19 Kikit asked Jun 25, 2019 at 12:14 KikitKikit 11144 silver badges1212 bronze badges 2

• 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 Commented Jun 28, 2019 at 7:57
• 1 that will be overkill since there are millions of data. – Kikit Commented Jul 1, 2019 at 13:55

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 3

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./SheetJS/sheetjs/issues/1473#issuement-580648494

This worked for me to customize the cell width for the 1st column and then passing the default width for rest of the cells. You can also set row height for your workbook by modifying ws['!rows']

/* Create worksheet from HTML DOM TABLE */
let wb = XLSX.utils.book_new();
let ws = XLSX.utils.table_to_sheet(document.getElementById("your_DOM_ID"));

var wscols = [];
var cols_width = 16; // Default cell width
wscols.push({
  wch: 32
}); // Set 1stColumn @32 character wide
for (var i = 0; i < 70; i++) {
  // Increase/Decrease condition_value based on the nmbr of columns you've on your excel sheet
  wscols.push({
    wch: cols_width
  });
}
ws['!cols'] = wscols;

ws['!rows'] = [{
    hpx: 20
  }, // "pixels" in row1
  {
    hpx: 20
  } // "pixels" in row2
];
/* Export to file (start a download) */
XLSX.utils.book_append_sheet(wb, ws, 'sheet1');
XLSX.writeFile(wb, "Your_ExcelSheet.xlsx");

本文标签：