export large amount of data to Excel

Posted on 2006-03-20
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.
Question by:tz478
    LVL 7

    Accepted Solution

    LVL 4

    Assisted Solution

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
    One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on could not pull conte…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video discusses moving either the default database or any database to a new volume.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now