EDN Admin
Well-known member
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
Im really new to the use of closedXMl and Excel too(at least for this purpose) so sorry if Im asking silly questions.
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
I know that closedXML doesnt support charts yet so the only thing that came to mind to get around this was to create my chart using an http://chandoo.org/wp/2009/09/10/data-tables/ " target="_blank" style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; text-decoration:initial; color:#4a6b82; background-color:transparent 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 didnt. At least not when I add the rows from code using the closedXML library.
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
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. Thats
why Id like to know if if there is anything wrong with the code I use to insert new rows:
<pre class="prettyprint lang-vb 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()[/code]
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
As you can see the code is very simple and so is the template , that consists of only two columns :<strong style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; background-color:transparent "Year" (table1[Year])
and <strong style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; background-color:transparent "Sales" (Table1[Sales]
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
I dont 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 doesnt include the new row that were added
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
<img alt="" src="http://social.msdn.microsoft.com/Forums/getfile/211774
<span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif Being necessary to manually add the new ranges(<strong style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif Sheet1!Table1[Sales] <span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif and <strong style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif Sheet1!Table1[Year] <span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif )
as it only includes the first row(the one added by default when you insert a table)
<span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif <img alt="" src="http://social.msdn.microsoft.com/Forums/getfile/211776
<p style="font-size:13px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; font-family:Segoe UI,Lucida Grande,Verdana,Arial,Helvetica,sans-serif; line-height:20px
Any help is appreciated!
<p style="font-size:13px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; font-family:Segoe UI,Lucida Grande,Verdana,Arial,Helvetica,sans-serif; line-height:20px
Thanks in advance.
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
<span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline P.S. Here is a http://sdrv.ms/YlnosS " target="_blank" style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; text-decoration:initial; color:#4a6b82 link <span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline to
a rar containing the full code as well as the excel template(TemplatesMyTemplate.xlsx)
<br/>
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
View the full article
Im really new to the use of closedXMl and Excel too(at least for this purpose) so sorry if Im asking silly questions.
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
I know that closedXML doesnt support charts yet so the only thing that came to mind to get around this was to create my chart using an http://chandoo.org/wp/2009/09/10/data-tables/ " target="_blank" style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; text-decoration:initial; color:#4a6b82; background-color:transparent 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 didnt. At least not when I add the rows from code using the closedXML library.
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
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. Thats
why Id like to know if if there is anything wrong with the code I use to insert new rows:
<pre class="prettyprint lang-vb 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()[/code]
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
As you can see the code is very simple and so is the template , that consists of only two columns :<strong style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; background-color:transparent "Year" (table1[Year])
and <strong style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; background-color:transparent "Sales" (Table1[Sales]
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
I dont 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 doesnt include the new row that were added
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
<img alt="" src="http://social.msdn.microsoft.com/Forums/getfile/211774
<span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif Being necessary to manually add the new ranges(<strong style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif Sheet1!Table1[Sales] <span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif and <strong style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif Sheet1!Table1[Year] <span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif )
as it only includes the first row(the one added by default when you insert a table)
<span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; color:#2a2a2a; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif <img alt="" src="http://social.msdn.microsoft.com/Forums/getfile/211776
<p style="font-size:13px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; font-family:Segoe UI,Lucida Grande,Verdana,Arial,Helvetica,sans-serif; line-height:20px
Any help is appreciated!
<p style="font-size:13px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; font-family:Segoe UI,Lucida Grande,Verdana,Arial,Helvetica,sans-serif; line-height:20px
Thanks in advance.
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
<span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline P.S. Here is a http://sdrv.ms/YlnosS " target="_blank" style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; text-decoration:initial; color:#4a6b82 link <span style="margin:0px; padding:0px; border:0px; font-size:14px; font:inherit; vertical-align:baseline to
a rar containing the full code as well as the excel template(TemplatesMyTemplate.xlsx)
<br/>
<p style="font-size:14px; font:inherit; vertical-align:baseline; list-style-type:none; color:#2a2a2a; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif
View the full article
