CODEWITHSUNDEEP
javascriptnode.jsexcel
amir iqbal
amir iqbal

Reputation: 51

Creating dynamic number of columns or header in excel in nodejs/javascript

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.

Upvotes: 5

Views: 9577

Answers (3)

Vikram Singh Shekhawat
Vikram Singh Shekhawat

Reputation: 762

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!");
        });

Upvotes: 1

Cyran
Cyran

Reputation: 1

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.

Upvotes: 0

Nitesh Malviya
Nitesh Malviya

Reputation: 840

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

Upvotes: 1

Related Questions