Add a rows before the columns headers

[rihabbs]

Hi,
how can i add a rows before the column header?

[mfahmirukman]

As in cell by cell or like you want to write an entire row at once?

[vishnu-kyatannawar]

In the documentation I don't see any method to add row before or after a certain row.

[rihabbs]

an entire row at once

[rihabbs]

I need to set the headers on a different row, other than the first (A1)

[mfahmirukman]

@rihabbs @getsomecoke

then do:

1. Add the header you want to row other than 1, e.g you wanna add it to row 2 and skip row 1
   worksheet.getRow(2).values = ['Hello', 'World!', 'How', 'Are', 'You', 'Doing', 'There?']

a) Define the keys. (This process will put the key sequentially starting from column A, B, C and so on. If you want to custom it, refer to step 2b).
worksheet.columns = [
{
key: 'id'
// you dont need to add header, just define the what the key is called
}
{
key: 'name'
}
]

b) Define custom keys. For example you wanna put it into column B and D
worksheet.getColumn('B').key = 'id';
worksheet.getColumn('D).key = 'name'

3. Adding data into row (The way this works is basically the module will keep the number of next empty row. E.g. if you dont fill row 1, but fill row 2 with header, it'll keep row number 3 as the next empty one. If you add data with worksheet.addRow(), it's going from 3rd row and so on)
   array.forEach(function(item, index) {
worksheet.addRow({
id: item.id,
name: item.name
})
})

[rihabbs]

@mfahmirukman thanks for your help,
but how can i set the first row at for example
worksheet.getRow(1).values = ['Hello', 'World!', 'How', 'Are', 'You', 'Doing', 'There?']
and the columns start from the row 2.

[mfahmirukman]

@rihabbs Sorry, but I don't understand your question. Can you clarify what you meant?

Edit: Did you mean that you want to have your header at row 1. E.g. from A1 to E1 and you want to have your data starting from row 2. E.g. from A2 to E2, A3 to E3, A4 to E4, so on and so forth?

[rihabbs]

I need to do like this example

[mfahmirukman]

By that example, do this @rihabbs

/*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) {
  worksheet.addRow({
     idClient: item.idClient,
     name: item.name,
     tel: item.tel,
     adresse: item.adresse
  })
})

That's about it

[rihabbs]

@mfahmirukman thanks

[srajabhoj]

@mfahmirukman Thank you very much... you saved my time

[AhCamargo]

How do I create style in excel ?

[mdmasumbillah]

@mfahmirukman thanks for help. This is work when added the width.

sheet.columns = [
{ key: 'idClient', width: 10},
{ key: 'name', width: 20},
{ key: 'tel', width: 15},
{ key: 'adresse', width: 25}
]

[numetrickyle]

Not only is your 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 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 your example)

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

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

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

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

None of this gives me anything other than the final column key in my title cell (using Microsoft Excel).
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 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)

This seems a common enough use case that it would be nice to see the ability to add a table title built into the worksheet object, perhaps as a columnsTitle attribute; where, when present, column functionality is standard but starts at row two with the title value centered and merged across row one.

[jazminvalencia]

como se hace para ponerle mas alto a las filas ?

[sudheerkaushik]

como se hace para ponerle mas alto a las filas ?

prueba esto

row.height = 42.5;

https://github.com/exceljs/exceljs#rows

[gbhipolitoglyph]

By that example, do this @rihabbs

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

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

...

thanks! saved my time! :D
Although in my case, it still printed the headers in the first row too! so I have headers in both row 1 and row 4 (row 4 is where I want my header & data to start). Great thing the data started at row 5 already, not row 2!
Only thing left is to overwrite the headers printed at row 1.

So I just did this AFTER defining (or below the line) sheet.columns = [...]:

sheet.getRow(1).values = ['The value I want to put'];

and voila! Headers in row 1 overwritten!

Thanks! :D

[TusharX]

Not only is your 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 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 your example)

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

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

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

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

None of this gives me anything other than the final column key in my title cell (using Microsoft Excel).
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 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)

This seems a common enough use case that it would be nice to see the ability to add a table title built into the worksheet object, perhaps as a columnsTitle attribute; where, when present, column functionality is standard but starts at row two with the title value centered and merged across row one.

If anyone is having the same issue as the OP mentioned that sheet.columns's last or first header value is over writing the merged Cell content then declare the sheet.columns first before performing the merge logic that's how I fixed this issue.

  let workbook = new Excel.Workbook();
  let sheet = workbook.addWorksheet(`${reportType} Report`);

  sheet.columns = [
   header : "Name", key : "name",
   header : "Age", key : "age",
   header : "Language", key : "language",
  ];

  sheet.mergeCells('A1','N1');
  sheet.getCell('A1').value = banner;
  sheet.getCell('A1').font = {
    name: 'Arial Black',
    family: 4,
    color : { argb : '96c030' },
    size: 45,
    bold: true
  };
  

  sheet.getCell('A1').alignment = {
    vertical : "middle", horizontal : "center"
  }

  sheet.mergeCells('G2','I2');
  sheet.getCell('G2').value = "Powered by XYZ";
  sheet.getCell('G2').font = {
    name: 'Arial Black',
    family: 4,
    size: 10,
    bold: true
  };

  sheet.getRow(4).values = ["Name", "Age" "Language"];

  sheet.addRows(students);

[nicolomanni]

I found a more elegant and faster solution:

// After columns definition

// Insert an empty row at beginning
worksheet.spliceRows(1, 0, []) 

// Set title
worksheet.getCell('A1').value = 'New worksheet title'

// Optional merge and styles
worksheet.mergeCells('A1:E1') 
worksheet.getCell('A1').alignment = { horizontal: 'center' }