Creating Charts without referencing data in excel file

Mar 28, 2012 at 6:50 PM

I'm generating a report with numerous data sources and would like to generate a chart for some of this data. When setting up the chart series information, I've noticed that it requests a range of cells. Since my Excel document is being generated by server side programming, I'd like to skip the need to have a worksheet just to pull this data.

Is there any way (an example would be great) to set up the chart series without referencing Excel cells?

For instance:

Series1 = 1000, 1050, 2400, 1508, 999

I don't want to have to set up cells with these values and instead would like to set up the series manually.

Mar 28, 2012 at 7:20 PM

I've done a lot of searching through past discussions and have now come across a similar question that went without an answer, so even a simple "Yes, this is possible" or "No, this is not possible" answer would be most appreciated.

Mar 28, 2012 at 8:07 PM

Or, if this is not possible, is it possible to reference a range of cells in a different worksheet? I could then create a new worksheet in the code behind, reference the appropriate cells, then dispose of the temp worksheet.

Mar 28, 2012 at 9:57 PM

Ok... I've created a sample xlsx file that has a chart with a series that is not linked to any of the spreadsheet data. I then loaded it into a web script in an attempt to figure out how the series would look to EPPlus... however, it appears to be an empty string. Here is the code I use:

Dim file As FileInfo = New FileInfo(Server.MapPath(".") & "\temp.xlsx")
Dim p1 As ExcelPackage = New ExcelPackage(file)       
Dim ws1 As ExcelWorksheet = p1.Workbook.Worksheets(1)
For Each c1 As Chart.ExcelChart In ws1.Drawings           
For i As Integer = 0 To c1.Series.Count - 1               
Response.Write(c1.Series(i).Header & "<br>")               
Response.Write(c1.Series(i).Series & "<br>")           

I only see the Header (which is "Test"). The series, as I mentioned, comes out as an empty string.