Add rows before the column header using excel.js module + node

Question

I am trying to create xslx file using excel.js module in node.js. I was able to create the columns and add its values. But, I need to insert some rows before the columns headers, where I can have some descriptions. How can I do that?

Any help will be appreciated

The code I tried is

     var worksheet = workbook.getWorksheet(1);
    worksheet.mergeCells('A1:B1');
    var row = worksheet.getRow(1);
    row.getCell(1).value =' Hello, World!'; 
   // ... merged cells are linked 
    worksheet.getCell('A1').value = 'PI';
    worksheet.columns = [
        {header: 'Id', key: 'id', width: 10},
        {header: 'Name', key: 'name', width: 32},
        {header: 'complexity', key: 'complexity', width: 10},
        {header: 'hours', key: 'hours', width: 10},
        {header: 'cost', key: 'cost', width: 10}
    ];
     worksheet.addRow({name:'m', complexity: 'd', hours:5, cost: 7});

Did you find a solution to this problem? @Mariya – Willa Mar 01 '17 at 18:34 — Willa, Mar 01 '17 at 18:34
nope, I handled it with adding text to row with index – Mariya James Jun 03 '20 at 07:23 — Mariya James, Jun 03 '20 at 07:23

score 8 · Answer 1 · edited Jun 29 '20 at 18:16

Found answer at https://github.com/guyonroche/exceljs/issues/433

> @rihabbs: I need to do like this example [![enter image description here][1]][1] [1]: https://i.stack.imgur.com/9oRE4.png

> @mfahmirukman:

/*TITLE*/
sheet.mergeCells('C1', 'J2');
sheet.getCell('C1').value = 'Client List'

/*Column headers*/
sheet.getRow(9).values = ['idClient', 'Name', 'Tel', 'Adresse'];

/*Define your column keys because this is what you use to insert your data according to your columns, they're column A, B, C, D respectively being idClient, Name, Tel, and Adresse.
So, it's pretty straight forward */
sheet.columns = [
  { key: 'idClient'},
  { key: 'name'},
  { key: 'tel'},
  { key: 'adresse'}
]

/*Let's say you stored your data in an array called arrData. Let's say that your arrData looks like this */
arrData = [{
  idClient: 1,
  name: 'Rihabbs',
  tel: '0123456789',
  adresse: 'Home sweet home'
},
{
  idClient: 2,
  name: 'mfahmirukman',
  tel: '0123456789',
  adresse: 'Indonesia'
}
]
/* Now we use the keys we defined earlier to insert your data by iterating through arrData and calling worksheet.addRow()
*/
arrData.forEach(function(item, index) {
  sheet.addRow({
     idClient: item.idClient,
     name: item.name,
     tel: item.tel,
     adresse: item.adresse
  })
})

Where in the code the styles have been defined to make header background blue? — Alvin, Jul 06 '18 at 18:19

score 0 · Answer 2 · answered Jun 10 '19 at 18:15

The example provided by exceljs from @dim answer does not really work as described.

Not only is the example use-case a really bad example of what a real world use-case for this might look like, it simply does not work. What most people want in this situation is to have a table header as a merged and centered cell above the row of column labels where data may then be added by key. I have tried a lot of things to get the sheet.columns functionality to leave the first row of data alone but it always adds the column headers over whatever else I put there.

I have tried: adding title values before defining sheet.columns (as in the example)

adding the column headers as a raw list before defining sheet.columns (as in the example)

offsetting the merged title cell from the columns used in the table (as in the example) (why would anyone do this anyway?)

adding rows between title and column headers (as in the example) (again why?)

merging more than one row into title cell (as in the example)

None of this gives me anything other than the final column key in my title cell (using Microsoft Excel). It seems that the timing of defining sheet.columns has no bearing on the scope of the table being defined. I'm sure adding a title cell that falls outside of the columns of the actual table would work, but, as I stated, this is not very useful for most of the standard use-cases.

The workarounds I have found are these:

make the last column your sheet.columns a use the table title as it's key, do not include anything for it in the headers row, and ensure no table data comes in using that key (the downside being that the title is merged with an extra column outside of the table data, causing it to look a little funny and off center)
make your title cell on the second row, your headers on the third, and set sheet.getRow(1).hidden = true to hide the headers that inevitably appear on row one (the downside being your sheet starts on row two, for no obvious reason)

I started down the path of the first workaround, bookending empty columns on either end of the table to at least balance the title cell out, but it ended up looking too strange so I settled on using the second workaround.

score 0 · Answer 3 · answered Nov 02 '19 at 19:22

you can splice at 1st row with 0 removal and add row values

titleValues1 = ["colA1","colb1"];
titleValues2 = ["colA2","colb2"];

worksheet.spliceRows(1, 0, titleValues1,titleValues2);

Then get the rows and merge or manipulate like you want.

Hope this answers your (or any one in future) need.

score 0 · Answer 4 · answered Jun 16 '21 at 08:19

Taken from Dim's answer but I already had the code working before the requirement to add the header in. So, with as little change as possible it looks something like this:

fields = [{key:'a', width:12}, {key:'b', label:'2nd', width:15}, {key:'c', width:12}]
data = [{a:'a', b:'b', c:'c'},...]

// first add header info, then
worksheet.columns = fields
const titles = worksheet.addRow(this.fields.map(f => f.label || f.key))
// uncomment following line to freeze header and column title rows
// worksheet.views = [{state: 'frozen', ySplit: worksheet.lastRow._number}]
const dataRows = worksheet.addRows(data)

Add rows before the column header using excel.js module + node

4 Answers4