Table & Charting

Jul 28, 2011 at 7:58 PM

Hi, if a create a chart based on a table, the chart will be automatically adjusted when i add or delete rows from the table.

If change the table with ClosedXML the chart still refers to the old ranges.

            var listOfArr = new List<Int32[]>();
            listOfArr.Add(new Int32[] { 5, 10, 32 });
            listOfArr.Add(new Int32[] { 6, 12, 64 });
 
            var tblData = ws.Table("Table1"); 
            tblData.InsertRowsBelow(2);
            
            //Table exist A1:C5 - add R6 and R7
            ws.Cell(6, 1).InsertData(listOfArr);

Any change on the table will not be reflected by the chart anymore.

Any advise how i can adjust the chart so that it shows the new added data? Would this be possible anyhow by ClosedXML?

thx, Peter
Coordinator
Jul 28, 2011 at 8:06 PM

Charts are not supported yet so they're not updated even though you're adding a new row to the table. You'd have to implement a hack to get around it. If this is your only requirement then it shouldn't be too hard to do though.

Sorry.

Aug 6, 2011 at 7:06 AM

this is now my code to adjust the Series Formulas:

        Using document As SpreadsheetDocument = SpreadsheetDocument.Open(FullFileNameXLSX, True)
 
            Dim theSheet As Sheet = document.WorkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = "SngDevice").FirstOrDefault()
            Dim chartSheetPart As ChartsheetPart = CType(document.WorkbookPart.GetPartById(theSheet.Id), ChartsheetPart)
 
            Dim drawingsPart As DrawingsPart = chartSheetPart.GetPartsOfType(Of DrawingsPart).First
            Dim chartPart As ChartPart = drawingsPart.GetPartsOfType(Of ChartPart).First
            Dim chart As C.Chart = chartPart.ChartSpace.GetFirstChild(Of C.Chart)()
 
            'return all Chart.Formulas as IEnumerable
            For Each testFormula In chart.Descendants(Of DocumentFormat.OpenXml.Drawing.Charts.Formula)()
 
                If Rx.Regex.IsMatch(testFormula.Text, lS) Then  'not for Headers like Sheet1!$C$2
 
                    'e.g. Sheet1!$C$3:$C$42 => Sheet1!$C$3:$C$1002
                    testFormula.Text = Rx.Regex.Replace(input:=testFormula.Text, pattern:="\d+$", replacement:="$'" + maxRow,
                                                             options:=Rx.RegexOptions.IgnoreCase)
                End If
 
            Next
 
            drawingsPart.WorksheetDrawing.Save()
 
        End Using
Jan 18, 2012 at 9:35 PM
Edited Jan 18, 2012 at 9:38 PM

I have this same issue.   What did you have in mind for a workaround hack?

Do you have an ETA for chart support?

I was hoping maybe Excel's structured references would solve the issue but I haven't had any success with that.

(I just discovered ClosedXML BTW.  Fantastic library!)

Coordinator
Jan 18, 2012 at 9:54 PM
Edited Jan 18, 2012 at 9:56 PM

Charts are on the bottom of my list so don't wait for it. Right now I'm fixing bugs, next on the list are (in order) images on headers/footers, images anywhere, pivot tables (because I already have the skeleton to work on it), then charts. Sorry.

In the meantime you could try to create your chart using named ranges instead of hard coding them. That way ClosedXML should update the named ranges when you insert new rows and the charts should pick up on it.

Another option would be for you to pick up the ClosedXML code and modify it just enough to make the changes you want to the charts. Others have done it with pivot tables and comments (before I implemented them). Not saying it will be easy, but it's doable. Check out Pitterling's code above.

A last resort would be to modify the file with ClosedXML and then modify the chart with another library like the OpenXML SDK.

I wish I had better news for you =/

Nov 14, 2012 at 4:44 AM

mdeleon,

any possibility of an update on charts? it is really a needed feature.

Coordinator
Nov 14, 2012 at 5:07 AM

Not any time soon.

Apr 4, 2014 at 8:39 PM
Given that I'm probably going to go with the third option here: "A last resort would be to modify the file with ClosedXML and then modify the chart with another library like the OpenXML SDK." Would it work to get the open xml objects from the closed xml objects, something like:
        var workbook = new XLWorkbook();
        var worksheet = workbook.Worksheets.Add("Sample Sheet");
        WorksheetPart worksheetPart = getWorksheetPart(worksheet); 
where the method getWorksheetPart performed some sort of reflection on the worksheet object to access the underlying WorksheetPart? Or would that be a bad idea?
Feb 4, 2016 at 7:37 PM
I know this is an old discussion, but I have browsed the sources and saw the XLChart exists, but it is not publicly exposed:

IXLWorksheet has it commented out.

Can anyone say what is the status of its development? I am asking this because I want to use the feature only to change a datasource for an existing chart in an Excel file.

Thank you.