3

I have an Excel spreadsheet with a "Line with Markers" chart. The chart is sourced from data in a separate datasheet within the same workbook. If I open the workbook using ClosedXML SDK, and then change the datasheet only, it changes the data values to all zeros, and Y axes on the charts from (for this example) .92 through 1.10 to 0% through 100%, completely ignoring the fact that I set the axis to auto and specified the axis range be "linked to Source".

Strangely enough, in Excel, the chart still shows the data is linked, and clicking on the data range highlights the values to which they are linked, and they are correct. Still, the chart shows zeros

I'm at the point now, where it doesn't matter if its OpenXML, ClosedXML or any other tool, as long as it is in C#. I just need the charts to work.

Here are two example images of the same chart:

BEFORE: Chart Before

AFTER: Chart After

Here's my code, which takes a List of Lists that contain the values from the CSV response I've gotten from an API call, and then updates the given worsheet:

private bool UpdateSheetFrom2DList(string filename, string sheetName, List<List<string>> lstVals)
{
    bool rtn = false;
    try
    {
        var workbook = new XLWorkbook(filename);
        IXLWorksheet ws = workbook.Worksheet(sheetName);

        for (int y = 0; y < lstVals.Count; y++)
        {
            List<string> lstRow = lstVals[y];
            for (int x = 0; x < lstRow.Count; x++)
            {
                ws.Cell(y+1, x+1).Value = lstRow[x].Replace("\"", "");
            }
        }

        workbook.SaveAs(filename);
        rtn = true;
    }
    catch (Exception ex)
    {
        error = String.Format("EXCEPTION occurred updating {0} from XDBOA CSV: {1}", filename, ex.Message);
    }

    return rtn;
}

So, everything I've read indicates ClosedXML doesn't have this ability to modify chart axes, and so I have to use OpenXML. But I'm not having any luck with that either.

JLeRogue
  • 53
  • 1
  • 6

0 Answers0