Good afternoon,
I have a .net API that will receive a request from the client to generate an excel sheet. So far, so good. I receive the request, query the database, get the data, and write it to a sheet in an excel workbook.
It's actually the excel part I'm struggling with. I want to have a pre-made excel sheet with charts referring to a table that doesn't exist yet. It's this table that will be written upon request. I do not know in advance how many rows there will be, so this must adjust dynamically. I can't seem to figure out how to create a chart and have it point to data that doesn't exist yet.
For a simple example the c# program will write the following:
Car Brand | Total Honda 12 Dodge 24 BMW 6
This will be written on a second sheet, and on the first sheet is a bar chart that will now nicely display this data. The intention is after the c# program writes the data, I can then close the stream, and return the excel sheet to the client. When they view it on their end, the first sheet will have a populated chart.
I am more used to VBA, but I think what you are looking for is explained here :
http://csharp.net-informations.com/excel/csharp-excel-chart.htm
After the code generates an Excel worksheet, it adds some data, then create a chart object by retrieving the data in a hard coded way. If you know where you start to copy the values on your second page, and that the data is contiguous, then you may use range.end like here to get to the end, and throw this in your chart, located in the first sheet. So the chart would not be waiting to point to a dynamically created list, but rather created after the list is being populated (pre-made, but in your code). If it is a matter of one column of strings and one column of numbers, I think that this method may be convenient.