This project has moved and is read-only. For the latest updates, please go here.

Chart doesn't update when adding new rows to an existing Excel table (not without having to use named ranges)

Dec 20, 2012 at 10:37 AM

I'm really new to the use of closedXMl and Excel too(at least for this purpose) so sorry if I'm asking silly questions.

I know that closedXML doesn't support charts yet so the only thing that came to mind to get around this was to create my chart using an excel table . That way I thought ClosedXML would update the ranges when I inserted new rows and the chart would pick up on it. Well , it didn't. At least not when I add the rows from code using the closedXML library.

What is curious is that adding new rows from inside excel automatically updates the chart but if I want to get that same result from code, I have to use OFFSET formula along with named ranges and then set the chart source data to these named ranges. That's why I'd like to know if if there is anything wrong with the code I use to insert new rows:

    Dim ruta As String = Server.MapPath("~/Templates/MyTemplate.xlsx")
    Dim wb As New XLWorkbook(ruta)
    Dim ws = wb.Worksheet(1)

    Dim tblData = ws.Table("Table1")

    Dim year As Integer = 2000
    For i As Integer = 1 To 13

        With tblData.DataRange.LastRow()
            .Field("Year").SetValue(year)
            .Field("Sales").SetValue(CInt(Math.Floor((2000 - 500 + 1) * Rnd())) + 500)
        End With
        tblData.DataRange.InsertRowsBelow(1)
        year = year + 1
    Next

    tblData.LastRow.Delete()

As you can see the code is very simple and so is the template , that consists of only two columns :"Year"(table1[Year]) and "Sales"(Table1[Sales]

I don't think this has anything to do with my template because as I told you, adding new rows directly from excel works as expected and it is only when I generate the table from code that the chart series doesn't include the new row that were added 

http://img560.imageshack.us/img560/9782/pic01ow.jpg

Being necessary to manually add the new ranges(Sheet1!Table1[Sales] and Sheet1!Table1[Year]) as it only includes the first row(the one added by default when you insert a table) 

http://img811.imageshack.us/img811/4237/pic02gl.jpg

Any help would be much appreciated

P.S. Here is a link to a rar containing the full code as well as the excel template(\Templates\MyTemplate.xlsx)


Dec 20, 2012 at 7:13 PM
Edited Dec 20, 2012 at 7:19 PM

Your observation is absolutely right. You have to take care of this issue yourself. If you are doing that within Excel, they update the datasource for the chart automatically. But if you are changing the datasource underneath, the chart series still points to the old range. Here is how i do that with OpenXML (not ClosedXML) and a Regex.

Basically i'm changing all Series Datasources Ending Rows, e.g. from existing 42 to 1002 --> 'e.g. Sheet1!$C$3:$C$42 = Sheet1!$C$3:$C$1002

Imports Rx = System.Text.RegularExpressions
 
Imports C = DocumentFormat.OpenXml.Drawing.Charts
Imports DocumentFormat.OpenXml.Drawing
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

                'Sheet1!$C$2      --> No Match                 'Sheet1!$C$3:$C$7 --> Match: Sheet1!$C$2:$C$
               Dim lS As String
                lS = String.Empty                 lS = String.Concat(lS, "^")                 lS = String.Concat(lS, "(\S+\!\$.+\$\d+\:\$.+\$)")                         '. = any char but linefeed                 lS = String.Concat(lS, "\d+")                 lS = String.Concat(lS, "$")
               Dim maxRow As String = CStr(myarray.count)   ' you have to adjust this number
 
                '----- OPEN XML ----------------------------------
                ' Open the document for editing. - OpenXML
                Using document As SpreadsheetDocument = SpreadsheetDocument.Open("your_file_name.xlsx", True)
 
                    Dim theSheet As Sheet = document.WorkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = "Your_Worksheet_Name").FirstOrDefault()
                    Dim chartSheetPart As ChartsheetPart = CType(document.WorkbookPart.GetPartById(theSheet.Id), ChartsheetPart)
 
                    Dim chartpart1 = chartSheetPart.DrawingsPart.ChartParts.First
 
                    'return all Chart.Formulas as IEnumerable
                    For Each testFormula In chartpart1.ChartSpace.Descendants(Of C.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
 
                    chartpart1.ChartSpace.Save()
 
                End Using

br, Peter
Dec 21, 2012 at 10:29 PM
Edited Dec 21, 2012 at 10:34 PM

Thanks for you reply Pitterling , but I really need to keep this as simple as possible and as far as I know OpenXML won't help me much in that sense :'( 

Didn't you really have any idea how to solve this using closedXML?? I would be awesome if you or anyone else out there could help me with this problem that has had me stuck for so long!!

Dec 22, 2012 at 9:05 AM

ClosedXML does not support Charts.

Please have a look here http://closedxml.codeplex.com/discussions/266966

Either you do with OpenXML or you are using Excel itself.

Dec 22, 2012 at 6:56 PM

Yes you're right! ClosedXML doesn't support charts , I know that. In fact I found that same discussion when I was looking for a tool to export data.   That's why I want know if there's any way to force Excel to keep Table1[Sales]as a reference to the 'Sales' column.

I don't understand why when I set the value for the chart series Excel recognizes Table1[Sales] as what it is (the 'Sales' column) but then when I want to check this value, much to my surprise, Excel has replaced the value I set originally (Table1[Sales]) for $B$2 that is, the first cell of that column

Dec 23, 2012 at 7:51 PM

Excel does not reference to Table[Column], it references to absolut Cell references. A chart series has actually 2 references (1st=Title --> that one points to the column header e.g. $B$2 ,2nd=the Series or better values of the series).

If a 2nd reference exactly matches a Table[Column] reference it will be recognized and maintained in the future. You have to make sure, that they exactly match. Once you have done this you can open the file with Excel and Chart-Series and Tablecolumn are magically connected.