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

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

> @rihabbs: I need to do like this example

> @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
  })
})

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.

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.

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)