Last post Jun 14, 2021 08:19 AM by yij sun
May 20, 2021 04:23 AM|arctura|LINK
I need to extract reports into Excel using C#, these reports generated using Stored Procedure.
Everytime I called the SP, it will return 4 DR, and it will export to Excel into 4 worksheet.
Problem is I need to loop the DR and insert into excel worksheet row by row.
Is there anyway to export the whole DR into 1 worksheet without looping the DR row by row?
I need to generate report for 10 branches and it takes me about 1 hour to export report for all branches.
I need to export the data to a fix excel template not a new excel file..
Those example I saw online are mostly generate a new excel file and they still doing the data loop.
Can anyone teach me how to export the data to excel without looping row by row?
May 20, 2021 08:54 AM|yij sun|LINK
I think you can make that in other way, create a dataset/datatable that has is datasource the excel file.
Then bind the list to this dataset/databtable, after filling the list with your data, just call the update method to update the file.
More details,you could refer to below article:
Jun 10, 2021 06:05 AM|arctura|LINK
Hi yij sun
Thanks for asking, i have on hold the development for 2 weeks because got another urgent matter.
i'm back on the function.
just for your information on the project.
I have 5 branches, each branch need an individual excel report.
each excel file will have 7 worksheet for different report usage.
currently I call stored procedure for 5 times (5 branches), each calling will return 7 datareader.
I loop the datareader and export to excel file.
I manage to do that, but the problem is performance is very slow when there are a lot data within the month.
I want to use your suggestion but i manage to convert the datareader to datatable but not sure how to bind the datatable to excel without loop the data.
if looping the data to export, it won't help on the performance much.
do you have any idea what to do next?
Jun 10, 2021 08:31 AM|yij sun|LINK
To make the code faster you can use the StringBuilder (examples: http://www.dotnetperls.com/stringbuilder-vbnet[^]) or simply open the StreamReader and write out everything immediately instead of first storing it all in memory. You can use Write for each
row value and a WriteLine when done with a complete row.
More details,you could refer to below articles:
Jun 11, 2021 03:06 AM|arctura|LINK
thanks for the suggestion.
I have go thru the sample, so far I can see is export into CSV.
I can't do that cause in the excel template, there is 7 sheets.
2nd sheet to 7th sheet is all different data.
but 1st sheet is a design to print as sticker, there are some image on this sheet and the data is getting from 2nd sheet.
because of the 1st sheet, i dont think it can save as CSV.
Jun 14, 2021 08:19 AM|yij sun|LINK
As far as I think,it also could export to excel.
HttpResponse response = HttpContext.Current.Response;
response.ContentType = "application/vnd.ms-excel";