none
Need a Data Provider for Excel (2003, 2007, 2010) that will work on XP,Vista Win7 RRS feed

  • Question

  • I have written a C# Windows application that amoung other things reads data from Excel and Creates new Excel files.  My app currently Supports Excel 2003 and 2007 on XP or Vista.

    Now some of my users have Windows 7 & Excel 2010.  I have been asked to create a new version that supports this combination and at the same time supports the older ones.

    Currently my app uses Jet 4 or ACE 12 OLEDB data providers depending on the version of the Excel files but it seems that jet is nologer an option on Windows 7.

    Is there a data provider that I can use to read data from Excel 2003,2007,2010 that will work on XP, Vista and win 7.

    I don't mind having different sections of my code that use different data access for different versions but I want to avoid telling my clients that they have to install different components depending on what OS they have.

    Can someone tell me what might be the best solution to this?

    Thanks in anticipation.

    Tim

     

    Wednesday, November 10, 2010 11:55 PM

Answers

  • There aren't really any licensing issues with respect to Jet or ACE. The ACE OLEDB Provider is a bit trickier to deploy on 64-bit systems with Office 2007/2010 or ACE already installed. If the target machine has 64-bit Office or the 64-bit ACE OLEDB Provider already installed then that is the version you will have to use. Same applies to the 32-bit version since you cannot install both versions of ACE in the same environment.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by TimboS Thursday, November 18, 2010 10:38 PM
    Friday, November 12, 2010 1:13 PM
  • If you need only to read data then you can try xlReader Data Provider for Excel from my web site. It is implemented as .NET Managed provider (but with read-only capabilities) and does not require Jet, ACE or Excel installed. It works on binary level and reads files directly. It supports both 32 and 64-bit modes automatically, and works with all platforms including XP, Vista and Windows 7.


    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by liurong luo Thursday, November 18, 2010 10:09 AM
    • Unmarked as answer by TimboS Thursday, November 18, 2010 10:36 PM
    • Marked as answer by TimboS Thursday, November 18, 2010 10:40 PM
    Thursday, November 11, 2010 11:42 AM
    Moderator

All replies

  • If you need only to read data then you can try xlReader Data Provider for Excel from my web site. It is implemented as .NET Managed provider (but with read-only capabilities) and does not require Jet, ACE or Excel installed. It works on binary level and reads files directly. It supports both 32 and 64-bit modes automatically, and works with all platforms including XP, Vista and Windows 7.


    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by liurong luo Thursday, November 18, 2010 10:09 AM
    • Unmarked as answer by TimboS Thursday, November 18, 2010 10:36 PM
    • Marked as answer by TimboS Thursday, November 18, 2010 10:40 PM
    Thursday, November 11, 2010 11:42 AM
    Moderator
  • You should be able to use either Jet or the ACE OLEDB Provider. The Jet database engine is pre-installed on the systems you mentioned. The ACE OLEDB Provider would need to be installed if Office 2007/2010 is not installed or it has not been installed by another app.

    Remember there isn't as much flexibilty when creating a Workbook using data access code, since there are database requirements that are being applied.

      Public Sub CreateExcelADONET()
    
        Dim ConnectionString As String
    
        'For versions prior to 2007
        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\Test Files\ExcelWB.xls;Extended Properties=Excel 8.0"
    
        'Excel 2007
        'ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test Files\myExcel2007file.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=Yes"""
    
        Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
        ExcelConnection.Open()
    
        Dim SQLDDLCommand As String = "CREATE TABLE SheetName " & _
                        "([FileName] TEXT(50), " & _
                        "[Class] TEXT(50), " & _
                        "[Property] TEXT(50))"
    
        Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLDDLCommand, ExcelConnection)
        ExcelCommand.ExecuteNonQuery()
    
        ExcelConnection.Close()
    
      End Sub
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by liurong luo Thursday, November 18, 2010 10:09 AM
    • Unmarked as answer by TimboS Thursday, November 18, 2010 10:36 PM
    • Proposed as answer by Val MazurModerator Wednesday, May 16, 2012 8:18 PM
    Thursday, November 11, 2010 5:34 PM
  • My understanding is that Jet 4.0 is not be used with Windows 7 and certainly isn't working properly on my Windows 7 machine.  This is the problem I am trying to solve while still having an app that will work on Vista and XP as well.

    Thanks,

    Tim

    Friday, November 12, 2010 6:28 AM
  • As well as reading data I need to be able to create a new spreedsheet with specified column headings.  Which I currently do using a Create Table statement.  In future I need to avoid using Jet without creating other Licencing issues for my Clients.

    Thanks,

    Tim

    Friday, November 12, 2010 6:31 AM
  • I'm not sure what type of problem you are encountering but there should not be any issue using Jet 4.0 under Windows 7. With respect to .NET the only issue you have to be aware of is that there is no 64-bit Jet OLEDB Provider, so when running your app under a 64-bit version of Windows you need to make certain to set the Platform compile option to x86. This will configure your app to run in the 32-bit subsystem of 64-bit Windows.

    In any event, I would tend to recommend using the ACE OLEDB Provider since Jet has been deprecated.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, November 12, 2010 1:02 PM
  • There aren't really any licensing issues with respect to Jet or ACE. The ACE OLEDB Provider is a bit trickier to deploy on 64-bit systems with Office 2007/2010 or ACE already installed. If the target machine has 64-bit Office or the 64-bit ACE OLEDB Provider already installed then that is the version you will have to use. Same applies to the 32-bit version since you cannot install both versions of ACE in the same environment.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by TimboS Thursday, November 18, 2010 10:38 PM
    Friday, November 12, 2010 1:13 PM
  • How do I determine if ACE is already installed on a Machine and if it is 64Bit or 32Bit?  Can I detect this programatically and try to change the behaviour of my app acordingly?

    I suppose if my app is built for a 32Bit platform and the machine has 64Bit ACE or Excel 64Bit installed then my app can't use ACE with this s that true?

    Is there any difference in the connection string you use depending on if it is 64Bit or 32Bit?

    Thanks,

    Tim

    Monday, November 15, 2010 5:28 AM
  • The connection string is the same regardless of whether you are using 32-bit or 64-bit.

    Yes, you would likely have to target the version of Office and/or ACE OLEDB Provider that is installed on the target machine. I don't know of a way, at this time, to install both the 32-bit and 64-bit versions of ACE.

    The 32-bit ACE OLEDB DLL looks to be installed under (note this is an Office 2007 environment):

    C:\Program Files (x86)\Common Files\microsoft shared\OFFICE12

    I would suspect that the 64-bit version would be installed under:

    C:\Program Files\Common Files\Microsoft Shared\OFFICE12

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, November 16, 2010 2:27 PM
  • I have a strange behaviour when I use the ACE driver in order to insert data into a named range in Excel. The data is inserted, but the named range is not expanded to include the new rows. It only happens in some environments. For instance: Windows 7 64 bits, Office 2010 32 bits and ACE 32 bits, in this case the named range is not expanded. All items are updated to the latest service pack.
    Wednesday, November 17, 2010 2:08 PM
  • I have found a 3rd party api that looks the goods.

    Gembox Spreadsheet is .Net managed code and allows reading and writing of Excel files regardless of the version of excel the OS or the bitness.

    I have tested it and it works better than the OLE Db providers in my opinion.

    http://www.gemboxsoftware.com/

    • Marked as answer by TimboS Friday, December 3, 2010 1:37 AM
    • Unmarked as answer by Val MazurModerator Wednesday, May 16, 2012 8:18 PM
    Friday, December 3, 2010 1:37 AM