none
(ClosedXML) Chart doesn't update when adding new rows to an existing Excel table

    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 

    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) 

    Any help is appreciated!

    Thanks in advance.

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


    • Moved by Mike Feng Monday, December 24, 2012 7:10 AM (From:Visual Basic)
    Saturday, December 22, 2012 8:16 AM

All replies

  • 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 

    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) 

    Any help is appreciated!

    Thanks in advance.

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

    Saturday, December 22, 2012 8:03 AM
  • Can you use a Dynamic range?

    http://www.ozgrid.com/Excel/DynamicRanges.htm


    Ryan Shuell

    Saturday, December 22, 2012 6:14 PM
  • Can you use a Dynamic range?

    http://www.ozgrid.com/Excel/DynamicRanges.htm

    Well ,I could, but I'd prefer not to since they have caused me a lot of trouble in the past when I  needed to add new columns with the same style of the first ones I had in my template.

    For that reason I'd like to know if it is possible to work with ClosedXML and Excel templates but without having to use named ranges , that is , only with Excel tables and the names they use to reference to a whole column ( table1[Year] and Table1[Sales])


    • Edited by edalo Saturday, December 22, 2012 7:20 PM
    Saturday, December 22, 2012 7:20 PM
  • Hi edalo,

    Welcome to the MSDN forum.

    I’m afraid that your topic about closed XML (third party library) is out of scope here. I will move this thread to off-topic forum. It seems that you have post one thread in this link: http://closedxml.codeplex.com/discussions

    Please wait for the solution in this thread.

    Sorry for any incontinences and have a nice day.


    Mark Liu-lxf
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 24, 2012 7:09 AM
  • Hi Edalo,

    Thank you for posting in the MSDN Forum.

    As far as I'm concerned, closedXML is a 3rd party library and is not supported by this forum.

    For the usage of closedXML, I suggest you repost it on http://closedxml.codeplex.com/ or contact the library owner.

    Sorry for any inconvenience, and much appreciate for your understanding.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 24, 2012 11:37 AM