CODEWITHSUNDEEP
javascriptnode.jsexcelnpmxlsx
John
John

Reputation: 21

Dynamicly create columns using node xlsx

I am try to create an excel file from the data provided using npm xlsx. https://www.npmjs.com/package/xlsx

const players = [
    {
        "number": "34",
        "firstName": "Nate",
        "lastName": "Cole",
        "position": "Pitcher"
    },
    {
        "number": "5",
        "firstName": "Kyle",
        "lastName": "Carroll",
        "position": "Pitcher"
    }
];

Here is were I pick the Column Headers to use.

let worksheetColumns = [];

_.forEach(players, function (object) {
    _.forEach(object, function (value, key) {
        worksheetColumns.push(key);
    });
});

const uniqueColumns = _.uniq(worksheetColumns);

I can hard code columns and rows but I am looking to have the key be the Column headers and the values be the rows of data. 

const workbook = {
    SheetNames: ["Test Sheet"],
    Sheets: {
        "Test Sheet": {
            "!ref":"A1:Z5",
            A1: { t:'s', v: "column" },
            A2: { t:'s', v: "row" }
        }
    }
};

const wopts = { bookType:'xlsx', bookSST:false, type:'binary' };

XLSX.writeFile(workbook, 'output.xlsx', wopts);

Upvotes: 1

Views: 4230

Answers (1)

Nitesh Malviya
Nitesh Malviya

Reputation: 840

You can use exceljs to create xls and dynamically handle new columns. First, create an instance of the workbook as follows

var workbook = new Excel.Workbook();

Then create a sheet in the workbook

workbook.creator = "Me";
workbook.lastModifiedBy = "";
workbook.created = new Date();
workbook.modified = new Date();
workbook.lastPrinted = new Date();
workbook.properties.date1904 = true;
workbook.views = [
  {
    x: 0,
    y: 0,
    width: 10000,
    height: 20000,
    firstSheet: 0,
    activeTab: 1,
    visibility: "visible",
  },
];
var worksheet = workbook.addWorksheet("My Sheet");

Then create a table at column A1

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*/],
});

Now you can add columns dynamically

const table = worksheet.getTable("MyTable");
table.addColumn({
    name: "Column name",
});
table.commit();

Upvotes: 2

Related Questions