How do I increase performance of excel generation

Question

0.00/5 (No votes)

See more:

Hi Guys

I am currently generating reports in excel through vb.net.

Currently the process that i am using is i get the data from the database and store the data returned into a datatable

i then loop through each record in the datatable and add each column in each record to the excel sheet

Problem is when the report contains large data e.g. 120 000 + records reports take rather long to generate is there a better way to speed up the performance

What I have tried:

dim dt as new datatable("Vehicles")

dt = GetVehicledata()

for each dtrow in dt.rows
xSheet.Cells(RowDataStart, 4).value = DtRow("REGNUM").ToString
xSheet.Cells(RowDataStart, 5).value = DtRow("DRIVER").ToString
xSheet.Cells(RowDataStart, 6).value = DtRow("STARTDATE").ToString
xSheet.Cells(RowDataStart, 7).value = DtRow("STARTTIME").ToString
xSheet.Cells(RowDataStart, 8).value = DtRow("ENDTIME").ToString
next

Posted 24-Jan-17 5:06am

IsiR_19

Updated 24-Jan-17 7:55am

Add a Solution

3 solutions

Solution 3

Assuming the code is meaningful, there is a huge saving.
In this code, RowDataStart is a constant, this imply that you can jump directly to last vehicle and write the row in Excel sheet only once.

Posted 24-Jan-17 7:55am

Patrice T

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2017-01-24T05:34:00

Solution 1

If you're using Office Interop (set a reference to the Office PIA's) there is nothing you can do to speed it up. Office Interop is notoriously slow.

Your alternative is to generate the Excel file directly using the "OpenXML SDK", or "ClosedXML SDK", or similar.

Posted 24-Jan-17 5:34am

Dave Kreskowiak

Andy Lanng · Accepted Answer · 2017-01-24T05:36:00

Depending on the types of values (if there are any formulas etc) then you could write your data to an array and then paste that to a range.

The array must always be 2d, even if one of those dimensions is only 1 in length; That doesn't matter in your case as you will have a 2d array to paste