Vinoth
Vinoth

Reputation: 1113

How to write array in excel sheet using excel4node

I am creating an excel file by using Excel4node package. by using this code

// Require library
var excel = require('excel4node');

// Create a new instance of a Workbook class
var workbook = new excel.Workbook();

// Add Worksheets to the workbook
var worksheet = workbook.addWorksheet('Sheet 1');
var worksheet2 = workbook.addWorksheet('Sheet 2');

// Create a reusable style
var style = workbook.createStyle({
  font: {
    color: '#FF0800',
    size: 12
  },
  numberFormat: '$#,##0.00; ($#,##0.00); -'
});

// Set value of cell A1 to 100 as a number type styled with paramaters of style
worksheet.cell(1,1).number(100).style(style);

// Set value of cell B1 to 300 as a number type styled with paramaters of style
worksheet.cell(1,2).number(200).style(style);

// Set value of cell C1 to a formula styled with paramaters of style
worksheet.cell(1,3).formula('A1 + B1').style(style);

// Set value of cell A2 to 'string' styled with paramaters of style
worksheet.cell(2,1).string('string').style(style);

// Set value of cell A3 to true as a boolean type styled with paramaters of style but with an adjustment to the font size.
worksheet.cell(3,1).bool(true).style(style).style({font: {size: 14}});

workbook.write('Excel.xlsx');

by using this code creating an excel sheet now what I want is. I want to write the array in the excel sheet.

worksheet.getCell('A1').value = 's.no';

by using the code. it is writing the data to the sheet but it is writing the data by cell by cell. it takes to much of time to write the array in excel sheet

    data=[{s.no:1,Name:'xxx',Age:'22'},
{s.no:2,Name:'yyy',Age:'12'},
    {s.no:3,Name:'zzz',Age:'32'}]

I want to write the array in the excel sheet.

 workbook.write('Excel.xlsx',data);

I given like this but this also not working. can anyone resolve this.

Upvotes: 6

Views: 8784

Answers (3)

MD SHAYON
MD SHAYON

Reputation: 8063

const array_elements = [{
    fullname: "name 1",
    game1point: 10,
    game2point: 12
  },
  {
    fullname: "name 2",
    game1point: 15,
    game2point: 17
  },
]



let startRow = 3;
for (let i = 0; i < array_elements.length; i++) {
  // FULLNAME - FIRST COLUMN
  worksheet.cell(startRow + i, 1).string(array_elements[i].fullname);

  // SECOND COLUMN
  worksheet.cell(startRow + i, 2).number(array_elements[i].game1point);


  // THIRD COLUMN
  worksheet.cell(startRow + i, 3).number(array_elements[i].game2point);


  gameCol++;

}

Upvotes: 0

Shane Ryan
Shane Ryan

Reputation: 1

What I would do is create an excel4node layer. Something to the effect of:

// the type arg is the cell write method you want to use such as string, number, or formula
const writeCell(wb, ws, row, col, value, type, style){
  ws.cell(row, col)[type](value).style(wb.createStyle(style))
}

const xl = require('excel4node')
const wb = xl.createWorkbook()

// TODO: track your row and column indices with arrays. let's assume that we are 
// in the outer array and the inner array or row array is called "data"
// this would write out a row of data
data.forEach((d, idx) => {
  writeCell(wb, ws, rowIdx, idx + 1, d.value, d.type, d.style)
}

Upvotes: 0

M ghanom
M ghanom

Reputation: 11

why you don't use

sheet.cell(row , col ).string(`your value`).style(`your style`);

Upvotes: 0

Related Questions