I have used exceljs module in nodejs for exporting json data to excel. It's working fine, but the names of headers/columns have to be predefined before adding rows i.e., columns are fixed. After addition of rows, I can't add columns dynamically.
I have tried a number of modules available through npm but all of them have the same features.
So, is there any way or module that, at the time of manipulation of json data, can create a new column and add the required row.
If someone is still looking into this problem then I have a decent solution.
Instead of creating columns, you can create a table as follows in the worksheet.
worksheet.addTable({
name: "MyTable",
ref: "A1",
headerRow: true,
totalsRow: false,
style: {
theme: null,
showRowStripes: true,
showColumnStripes: true,
},
columns: [
{ name: "EmployeeID" },
{ name: "First Name" },
],
rows: [/* Enter initial rows if you want to add*/],
});
After adding a table to the column A1 of your worksheet you can add new columns dynamically
const table = worksheet.getTable("MyTable");
table.addColumn({
name: "Column name",
});
table.commit();
I tried directly pushing the new columns to the worksheet.columns but it is not working. I did a workaround and working well for me.
Note: You need to make the track of already added columns in the worksheet to get the next empty columns by column index.
Here is an example:
let workbook = new excel.Workbook(); //creating workbook
let worksheet = workbook.addWorksheet('Records'); //creating worksheet
const columns = [];
columns.push({header: 'Id', key: '_id', width: 30});
columns.push({header: 'Name', key: 'name', width: 30});
//Set Headers to WorkSheet Header
worksheet.columns = columns;
//Now insert some records if you want
worksheet.addRow({_id: "1", name: "Mitchell Starc"});
worksheet.addRow({_id: "2", name: "Ab de Villiers"});
//Update or add dynamic columns
//Get the empty columns from worksheet. You can get the empty columns number by using `columns` array length
//For this you have to track all inserted columns in worksheet
//This will return the next empty columns
let newColumn = worksheet.getColumn(columns.length + 1);
//Set new key header and all other required properties
newColumn.key = "profession";
newColumn.header = "Profession";
newColumn.width = 30;
//Add the new column to `columns` to track the added headers
columns.push(newColumn);
//Now you can insert rows with new columns
worksheet.addRow({_id: "3", name: "MS Dhoni", profession: "Cricket"});
workbook.xlsx.writeFile("records.xlsx")
.then(function () {
console.log("file saved!");
});
Now not sure if this worked 2 years ago but this worked for me
var columns=[]
x="data1"
y="data2"
Columns.push({ header: x, key: x })
Columns.push({ header: y, key: y})
worksheet.columns = Columns
You must use a separate variable to dynamically create the array of structs for it to work. if you use worksheet.columns=[] and worksheet.columns.push(..) it will fail.
