Why are the charts in Excel deleted if I export the data with' ExcelJS'?

Question

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

Answer 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.