none
Reading complex excel data into a c# dataset RRS feed

  • Question

  • Hi,

    I am writing this code for reading data from complex excel sheets that may contain pivot table, formulas, multiple tables per sheet.

    I am unable to find a code or a plugin that I could use to dynamically read the data from a complex excel sheet.

    I would appreciate if anyone could help me find a tool/plugin or code that can perform the read dynamically.

    Thanks,

    Leena

    • Moved by Vicky SongMicrosoft employee Friday, August 31, 2012 2:54 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Thursday, August 30, 2012 9:34 PM

Answers

  • Hi Leena,

    I think you can use Microsoft Excel 2010 Primary Interop Assembly (PIA) API to handle this kind of excel data. For more detail, see http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, September 6, 2012 8:15 AM
    Moderator
  • It depends
    on what exactly you need to achieve<o:p></o:p>

    1. If your goal is to have an access to rich Excel functionality then you would need to use Excel Interop (Microsoft Office Developer Tools) that come with Visual
    Studio. This solution provides access to all Excel functionality, but it is not suitable for the web applications due to the fact that it is out-of-process mechanism.<o:p></o:p>

    2. If you only need to read values from Excel spreadsheets without recalculating formulas, you can use ACE OLDB provider that allows you to connect to Excel
    file using OledbConnection and treat it as database. You can find download for ACE installation here http://www.microsoft.com/en-us/download/details.aspx?id=13255 There are separate installations for 32- and 64-bit environments, so you would need to target specific platform. In a case if you would need .NET managed solution that works in both 32- and 64-bit environments, you can try managed provider from my web site, or some other third-party components.<o:p></o:p>



    Val Mazur Microsoft MVP
    http://www.xporttools.net

    Thursday, September 6, 2012 4:29 PM
    Moderator

All replies

  • Hi Leena,

    I am moving your issue to the ADO.NET DataSet forum so that you can get better support.

    Thanks.


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us

    Friday, August 31, 2012 2:54 AM
  • Hi Leena,

    I think you can use Microsoft Excel 2010 Primary Interop Assembly (PIA) API to handle this kind of excel data. For more detail, see http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, September 6, 2012 8:15 AM
    Moderator
  • It depends
    on what exactly you need to achieve<o:p></o:p>

    1. If your goal is to have an access to rich Excel functionality then you would need to use Excel Interop (Microsoft Office Developer Tools) that come with Visual
    Studio. This solution provides access to all Excel functionality, but it is not suitable for the web applications due to the fact that it is out-of-process mechanism.<o:p></o:p>

    2. If you only need to read values from Excel spreadsheets without recalculating formulas, you can use ACE OLDB provider that allows you to connect to Excel
    file using OledbConnection and treat it as database. You can find download for ACE installation here http://www.microsoft.com/en-us/download/details.aspx?id=13255 There are separate installations for 32- and 64-bit environments, so you would need to target specific platform. In a case if you would need .NET managed solution that works in both 32- and 64-bit environments, you can try managed provider from my web site, or some other third-party components.<o:p></o:p>



    Val Mazur Microsoft MVP
    http://www.xporttools.net

    Thursday, September 6, 2012 4:29 PM
    Moderator