I know there are a lot of questions on how to write data from a dataset or datatable to excel, but they all seem to assume a new blank excel workbook. I have a special excel file containing multiple sheets with lots of formatting, formulas, and a chart. I just need to lay data over the appropriate portions of some sheets and let excel handle the rest.
So I need a way to insert data into an existing excel file, at a certain range (which can vary depending on the number of rows), on certain sheets. Currently I'm using the COM interop to write it all which is a performance nightmare and needs to be changed. So what would be the best way to accomplish this?
More info: We have a simple mvc app that lets the user submit a request for data. It kicks off this console app as a seperate process to get the data and build the excel file. So first we get the data which takes 10-15 seconds, then we write the data to excel. It's the actual writing of the values to the cell that appears to be taking all the time. Whether the formulas in the excel file are having an affect on the write I don't know, but that is what I'm trying to avoid.
Update: As it turns out the formulas were the primary hindarence on the speed of the writes. I set the Calculation property of the Excel Application class to XlCalculation.xlCalculationManual and it runs much faster now! This isn't the proper answer to my question but it's what worked.
Thank you to shahkalpesh who did point this out, and Cj!
Thank you, Jeff