1

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.

Sad-EyedLadyoftheLowlands
  • 129
  • 1
  • 12

1 Answers1

2

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 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.

Ryan M
  • 18,333
  • 31
  • 67
  • 74
Sébastien
  • 78
  • 5
  • Thank you. I see though that you are creating the chart programatically. This looks like it will work well, but I was hoping to be able to have designed the excel sheet before, and would only be missing the data. This way I can design a nice spreadsheet and have the charts be populated by data. I really just want to be able to have a chart that points to, say: all rows in column A and B on sheet "data". The x axis being all rows in col A and the y axis col B, for example... – Sad-EyedLadyoftheLowlands Oct 05 '20 at 21:18
  • 1
    Depending on the complexity of your data : https://trumpexcel.com/dynamic-chart-range/ – Sébastien Oct 05 '20 at 21:27
  • It seems so obvious now that I read that link. I think I had made this to be much more complicated than it needed to be. It works now. I have created a table with just one row of data so that at least it exists. A chart, on another sheet, now points to that table. My c# program writes to the sheet with the table and voila! The chart is dynamically produced. Thank you very much! – Sad-EyedLadyoftheLowlands Oct 05 '20 at 21:40