Cyberiafreak

"Fortunate are those who take the first steps.” ― Paulo Coelho

Export grid/any data to Excel in .NET (Various options & Tradeoff)

These days, the export options for any data grid (Winform/WPF/Webform) becomes essential for the most of the business scenario. Just imagine you fired a query with complex search filters etc and pulled a list of records which you’d like to share/send across through email for further offline purpose…Considering this, we should provide an export facility through which client achieve this easily. As you know, there are various ways are there. I’ve listed few of them which will help you to take quicker decision on this.

Option 1) Using Interop.dll – also primary interop assemblies (PIAs)
    Pros:    
        -Will get full control on the excel sheet creation and decoration such as applying styles, colors, charts, grouping…etc
                 -Developer friendly & easy
    Cons:
        -Dependency may break if the future version changes, currently we can target for Office 2003
        -Full installation might be required as ‘Install On Demand’ features of Office Apps would cause the code to break
        -Requires MS-Office in the client computer. Read more about this
here
Option 2) .CSV option
    Pros:
        -Easy to generate and solves the requirement
        -No need to have MS-Office installed in the client computer, notepad/ excel viewer is suffice to view the generated .CSV files
        -For the plain grid export, this would be suffice
    Cons:
        -We can’t create visually appealing sheets with formatting,colors etc
Option 3) Using Microsoft JET Oledb 4.0 Provider
    Pros:
        -Doesn’t require MS-Office installed in the client computer
        -Gives Xls output
    Cons:
        -We can’t create visually appealing sheets with formatting,colors etc
        -Require MDAC re-distributable installed in the client machine- say ‘Microsoft Data Access Components (MDAC) 2.8’
Read from
here
Option 4) Third party components
    Pros:
        -Less effort
    Cons:
        -It involve evaluation, cost and learning curve.       
Option 5) Create XML & XSL and import it into Excel
    Pros:
        -Doesn’t require MS-Office products installed in client computer
    Cons:   
        -Cannot create Xls as a direct output
        -Require lot of effort – creating XML from the data source and XSL for the data
        -While importing the user have to map the XML and XSL to view the data in the Excel
Read from
here

Let me know if find any other way to achieve the export option..

Advertisements

May 20, 2010 - Posted by | Uncategorized

1 Comment »

  1. The best and easy way i see is…. generate html file of the grid and then rename .html file to .xls, by using html one can preserve the formatting changes like color, cell merge, font and so on…

    Like

    Comment by Jayaveer | May 21, 2010 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: