ClosedXML - charts missing after saving

Question

I'm using ClosedXML to change the values of some cells in a XLSX file. These cells are used to generate a chart on another sheet.

I'm not touching the chart at all with ClosedXML. In fact, I'm not even touching the sheet that contains it, I'm just changing the values of some data cells, like this:

var workbook = new XLWorkbook(@"C:\path\to\my\file.xlsx");
var dataWorksheet = workbook.Worksheet("data");
var cell = dataWorksheet.Cell(1, 2);
cell.Value = 30;
workbook.Save();

However, when I re-open the file in Excel, the chart that was on the other sheet (which I haven't touched at all) simply disappears, leaving the sheet empty.

I know ClosedXML doesn't support the CREATION of charts, but is there a way to avoid LOSING them when saving?

Create an issue on Github.com and attach the file. I'll see if I can do a PR to at least preserve the chart. — Francois Botha
@FrancoisBotha: sorry if I can't test it at this time (I was in a hurry, so I had to switch the project to use the the Excel Interop API instead), but thanks a lor for your fix. If you post it as an answer I'll happily accept it "on faith" :) — Master_T

Helder Sepulveda Helder Sepulveda · Accepted Answer · 2018-04-13T17:24:30

Unfortunately that is a limitation on ClosedXML there are a few "chart" requests on the project page:
https://github.com/ClosedXML/ClosedXML/issues?utf8=%E2%9C%93&q=is%3Aissue+chart

But fear not the XLSX format is very simple! it's just a bunch of files all zipped.
We can still use ClosedXML to edit that Worksheet save it as a temp file and then replace the sheet on the original file, here is my code:

using ClosedXML.Excel;
using Ionic.Zip;
using System.IO;
using System.Linq;

namespace ExcelTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string file = @"..\..\file.xlsx";
            ClosedXMLTest(file);
        }

        static void ClosedXMLTest(string file)
        {
            string outFile = "fileClosedXML.xlsx";

            var workbook = new XLWorkbook(file);
            var dataWorksheet = workbook.Worksheet("data");
            for (int i = 1; i < 10; i++)
                dataWorksheet.Cell(i, i).Value = i;
            workbook.SaveAs(outFile);

            ReplaceSheet(outFile, file, "xl/worksheets/sheet1.xml");
        }

        static void ReplaceSheet(string outputFile, string inputFile, string sheetName)
        {
            using (var ozip = new ZipFile(outputFile))
            using (var izip = new ZipFile(inputFile))
            {
                var osheet = ozip.Entries.Where(x => x.FileName == sheetName).FirstOrDefault();
                var tempS = new MemoryStream();
                osheet.Extract(tempS);

                var isheet = izip.Entries.Where(x => x.FileName == sheetName).FirstOrDefault();
                izip.RemoveEntry(isheet);
                izip.AddEntry(isheet.FileName, tempS.ToArray());
                izip.Save();
            }
        }
    }
}

I have the full project here: https://github.com/heldersepu/csharp-proj/tree/master/ExcelTest

ClosedXML - charts missing after saving

1 Answers