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.

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.

• javascript
• node.js
• excel

• can you share some code as well? – mihai Commented Oct 24, 2018 at 15:04
• I haven't actually used this lib but couldn't you just add the dynamic column to worksheet.columns array? – Sujil Maharjan Commented Oct 24, 2018 at 17:54
• Have tried to add dynamically, doesn't take that column. I think, it's not allowing. Please visit "npmjs./package/exceljs#columns" for further details. – amir iqbal Commented Oct 25, 2018 at 5:54

3 Answers 3

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.mit();

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.