none
How to import Excel worksheet to .Net web application?

    Question

  • Backgroud:
    I have vb.Net Web application, which should allow end-users to browse Excel file and upload content to web application, and store to the SQL Server.
    This worked fine in Windows Server 2003/.Net Framework 2.0/VS2008.
    But it seem like critical feature is not working any more when I converted the application to Windos Server 2008R2/.Net Framework 4.0/VS2010.
    Problem seems to be Office Automation API that is not supported anymore by Microsoft. (Microsoft.Office.Interop.Excel) I seems to get error "Microsoft Excel cannot access the file"

    What I need:
    I need advice for best practise and code sample for import Excel worksheet to VB.Net web application.
    Method should be supported in Windows Server 2008R2 and hopefully in Windows Server 8 as well.

    So my environment consists of Windows Server 2008 R2, IIS, Visual Studio 2010, Web application.
    Example column of imported excel coluns are "Customer Id", "Customer Name", "Sales Amount".


    Kenny_I

    Wednesday, March 07, 2012 1:40 PM

Answers

  • Not using Office Automation is indeed the best practice. You have a few options available to extract data from Excel.

    Microsoft ACE OleDB: https://www.microsoft.com/download/en/details.aspx?id=13255

    Microsoft  SSIS (previously DTS): http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205

    NPOI: http://npoi.codeplex.com/

    Excel Package Plus: http://epplus.codeplex.com

    Aspose Cells.NET: http://aspose.com/

    SpreadsheerGear: http://www.spreadsheetgear.com


    My blog: blog.jessehouwing.nl

    Friday, March 09, 2012 8:25 AM

All replies

  • Hello,

    Try to install the following setup files and restart your machine and try again. I suspect the necessary dll's are not available in your machine to access the excel file.

    http://www.microsoft.com/download/en/details.aspx?id=3508

    http://www.microsoft.com/download/en/details.aspx?id=20479

    Let me know your findings after this installation.


    Kathir

    Wednesday, March 07, 2012 5:04 PM
  • Not using Office Automation is indeed the best practice. You have a few options available to extract data from Excel.

    Microsoft ACE OleDB: https://www.microsoft.com/download/en/details.aspx?id=13255

    Microsoft  SSIS (previously DTS): http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205

    NPOI: http://npoi.codeplex.com/

    Excel Package Plus: http://epplus.codeplex.com

    Aspose Cells.NET: http://aspose.com/

    SpreadsheerGear: http://www.spreadsheetgear.com


    My blog: blog.jessehouwing.nl

    Friday, March 09, 2012 8:25 AM