[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

export large amount of data to Excel

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.
2 Solutions
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.


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now