Export multiple resultset into multple sheet in 1 template excel file

Question

User1948389125 posted

Hi all

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?

Answer 1

User1535942433 posted

Hi arctura,

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:

https://www.vbforums.com/showthread.php?617561-RESOLVED-Export-data-to-Excel-without-looping

Best regards,

Yijing Sun

Answer 2

User1948389125 posted

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?

Answer 3

User1535942433 posted

Hi arctura,

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:

https://www.codeproject.com/Questions/317486/export-datatable-to-Text-File-without-Looping-thro

https://stackoverflow.com/questions/8207869/how-to-export-datatable-to-excel

Best regards,

Yijing Sun

Answer 4

User1948389125 posted

Hi,

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.

Answer 5

User1535942433 posted

Hi arctura,

As far as I think,it also could export to excel.

HttpResponse response = HttpContext.Current.Response;
response.ContentType = "application/vnd.ms-excel";

Best regards,

Yijing Sun