We help IT Professionals succeed at work.

export large amount of data to Excel

tz478
tz478 asked
on
Medium Priority
346 Views
Last Modified: 2012-06-21
I have a web app which needs to allow users to save dataset to a Excel file to their desktop.  There are many solutions online to export dataset to excel.  Does anybody know, from performance point of view, which way is better when dataset is very large, (close to Excel limit - about 63000 rows)?  

Any suggestions and comments will be highly appreciated.
Comment
Watch Question

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
I used OLEDB connection to connect to Excel and it works without Excel being installed on the machine. Excel ( I used Office 2003) has limitations for columns number 256 and rows number 65,536. So even successfully created file on a server wouldn't open on the client Excel.

Connection string for this solution is:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text

TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.

HTH,
Jigit
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.