Excel export in web application

Question

1.00/5 (2 votes)

See more:

I am having a MVC application. I have to export data to excel from the application. The number of records may go upto lakhs. When we click the button, we get three options....
1. Open --> When i click open it is taking a long time to open the excel with the data.

Can anyone help on how this can be achieved in a quick manner?

What I have tried:

I am loading the data from Database in to a DataSet. Then i loop through the dataset and add it to a worksheet in the excel object.

Posted 5-Oct-17 0:21am

Prakash Sekhar

Add a Solution

Comments

Richard MacCutchan 5-Oct-17 7:27am

With that amount of data it will always take a long time.

Prakash Sekhar 5-Oct-17 7:33am

I know but just want to optimize it as much. Any other ways to approach this?

Dave Kreskowiak 5-Oct-17 8:54am

If you're using Excel Interop:

1) You cannot use in a web application. It may work fine on your DEV machine, but deploy it to a production web server and your code is going to fail at some point.

2) Interop is SLOW. There is no getting around this other than NOT using Interop. Use a library dedicated to this task, like the OpenXML SDK, or the ClosedXML SDK.

Richard Deeming 5-Oct-17 12:16pm

We can't tell you how to optimize code that we can't see. You either need to show us the relevant parts of your code, or explain how you're exporting the data to Excel.

In general, don't use Interop, and don't just send back HTML with a Content-Type header. Use a server-safe library to generate a real Excel file - for example:

* EPPlus[^];
* ClosedXML[^];
* The OpenXML SDK[^];

But, as Dave said, if it's a large file, there's nothing you can do to speed up the download - unless you can convince your users to upgrade their internet connection speed!

Prakash Sekhar 6-Oct-17 9:33am

I cannot show the code. I am using CLosed XML only. 10,000 records is taking 3 mins. but it has to be done in 20-25 secs max. Is there any option for this?

Richard Deeming 6-Oct-17 9:41am

Start by profiling everything to find out where the delay is - the database query; the code that creates the spreadsheet; the network connection between the server and the client; or the client PC struggling to open the file.

If the database is the bottleneck, analyse and optimise your query, and add any missing indexes. If you can't make it run fast enough, you might need to upgrade the server.

If it's the code that creates the spreadsheet, then optimise that to make it faster. If you can't make it fast enough, then you either need to show us the relevant parts of the code, or upgrade your server.

If it's the network connection from the server, then you'll need to upgrade that to a faster connection.

If it's the client's network connection, then they'll need to upgrade their connection. There's nothing you can do, short of reducing the amount of data you return.

If it's the client's PC struggling to open the file, then they'll need to upgrade their PC. There's nothing you can do, short of reducing the amount of data you return.

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)