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

Question

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 enter image description here

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) enter image description here

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)

Somehow excel is not recognizing you inserts as part of the table. — Pynner
@Pynner Yes you're right! 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 replaces the value I set originally (Table1[Sales]) for $B$2 that is, the first cell of that column — eddy

dendarii dendarii · Accepted Answer · 2013-02-27T15:24:17

If the problem is that your table doesn't recognise the additional rows, try adding this after the last row delete:

tblData.Resize tblData.Range(1, 1).CurrentRegion

That should resize the table. Then hopefully your table operations should work.

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

1 Answers