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

Watch out !! – Steve B coming to India for IndiMix2010

Steve B’s opening keynote at IndiMIX 2010 –http://www.microsoftindimix.com/default.aspx