2

I succeeded in reading the existing Excel file and putting the data in the cell location I wanted. But after we put the data in, all the ExcelCharts in the existing Excel file disappeared. I'm still looking for a solution, but I can't solve it.

workbook.xlsx.readFile(__dirname + "/../assets/ExcelFile/testFile.xlsx")
 .then(function() {
    var worksheet = workbook.getWorksheet(8); 
      for(var i=1; i<data.length+1; i++)
     {
       for(var j=1; j<data[i-1].length+1; j++)
       {
         if(data[i-1][0] == "Slaes rate of electricity")
         {
           Price = data[i-1][2].replace(/,/gi,"");
           worksheet.getRow(12).getCell(7).value = Number(Price);
         }
         else if (data[i-1][0] == "Average coal HHV")
         {
           Price = data[i-1][2].replace(/,/gi,"");
           worksheet.getRow(15).getCell(7).value = Number(Price);
         }
        else if (data[i-1][0] == "Average price of coal")
         {
           Price = data[i-1][2].replace(/,/gi,"");
           worksheet.getRow(16).getCell(7).value = Number(Price);
         }
      }
    }
    return workbook.xlsx.writeFile(__dirname + "/../assets/ExcelFile/Test_"+getTimeStamp()+".xlsx");

 })
 .then(function() {
   res.send("true");
 })
 .catch(function(error) {
   console.dir(error);
   res.send(error);
 })
labongtoo
  • 31
  • 3
  • Looks like ExcelJS doesn't support charts: https://github.com/exceljs/exceljs/issues/307 – msbit Oct 22 '20 at 01:30
  • Is there another technology I can refer to for development? I must have a chart in Excel. Please. – labongtoo Oct 22 '20 at 02:00
  • On a related issue (https://github.com/exceljs/exceljs/issues/141) they mention https://www.npmjs.com/package/xlsx-chart so you could take a look at that (though it appears to only do charts). – msbit Oct 22 '20 at 11:47
  • Thank you for your answer. I tried using xlsx-chart, but I'm looking for a way to put the chart in the desired cell location. Can you help me? – labongtoo Oct 23 '20 at 00:27
  • Not beyond what I've already provided. There may be other libraries you can find to help with that, these should give you a start at least. Good luck! – msbit Oct 23 '20 at 00:55
  • Did you found a solution for this? – Luis Melendez Feb 18 '21 at 05:44

1 Answers1

1

Not sure if this is still an issue. For those who end up here because they are facing the same issue, there is a solution.

The Excel file is just a zipped container. ExcelJS does not read all the data in this container. In order to keep your charts, you need to copy the chart related elements from the original container to the container that gets created by writing the file with ExcelJS:

  • the folder /xl/charts/ and all of its underlying content
  • the folder /xl/drawings/ and all of its underlying content (charts are referred to from a drawing)
  • the respective entries in [Content_Types].xml (for each chart there are three files, all of which need to be referred to from within the content types file, e.g., <Override PartName="/xl/charts/chart1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawingml.chart+xml" />)
  • ensure that xl/worksheets/_rels/sheet[x].xml.rels contains a relationship to the drawing of interest like <Relationship Id="rId999" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing1.xml"/>
  • ensure that xl/worksheets/sheet[x].xml has a reference to the related item: <drawing r:id="rId999" /> (you can put this as last element within the <worksheet> tag)

(here [x] refers to the sheet of interest)

Note that the above assumes you have a source file containing your charts. If this is not the case, I suggest to create a one-off template to obtain the actual XML (files) for xl/charts and xl/drawings and use these to dynamically create the respective entries in your Excel file.

I've successfully applied this in a project, using ADM-ZIP for NodeJS to (de)compress the Excel file.

SjoerdD
  • 11
  • 1