Excel connection string that does not have to be version specific

Question

I am trying to connect my MSTest Unit tests to Excel files (so that they can be data driven.)

I have tried using csv files and they work except when I need the values to be strings.

I would like to use Excel, but all of the connection strings have a Extended Properties='Excel xx.0; where xx is a version number.

Where I work we have different versions of Office installed. I have 2013 and that is all, others have 2010 only.

Is there a Connection string to connect to excel as a data source without needing to use a specific version?

Here is an example of what I have tried that does not work:

[DataSource("System.Data.OleDB", @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=MyExcelFile.xlsx; Extended Properties='Excel 12.0;HDR=yes';", "Sheet1$", DataAccessMethod.Sequential)]

You might consider ExcelDataReader http://exceldatareader.codeplex.com/ instead. I used the ACE driver in a project and had too many issues, particularly when it came to 32 vs 64-bit Office on the user's machine. I switched to ExcelDataReader and have been very happy with it. — BrandonLWhite, Sep 26 '13 at 15:16
You might also want to look into http://epplus.codeplex.com/, its a nice library for reading/writing to a xlsx file, you will run into much fewer problems with that lib than you will run into using the sql way of reading/writing an excel file. — Samuel, Sep 26 '13 at 17:40

BrandonLWhite · Accepted Answer · 2013-09-26T17:17:49.593

2

Here's an ACE connection string I used in a project before switching to ExcelDataReader:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;IMEX={1}

It seemed to work with xlsx and xls files from multiple versions of Excel. You can drop the IMEX and/or add HDR as you see fit. Though it specifies version 8.0, it seems to work fine with files saved in any version from 8.0 onward.

If you are steadfast in your decision to use the ACE driver, then give this a shot and see if it does the job for you. But if all you are doing is essentially parsing Excel files as input into your app, then I suggest ExcelDataReader (http://exceldatareader.codeplex.com/) as a better option. It works quite well, handles xls and xlsx, can give you the results in a DataSet/DataTable if you desire, and doesn't depend on Office or the standalone ACE driver being installed. It's available on NuGet too.

Here's a simple example of what it would look like in your code:

DataSet LoadDataSet()
{
    using (Stream stream = File.OpenRead("MyExcelFile.xlsx"))
    {
        using (Excel.IExcelDataReader excelReader = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream))
        {
            return excelReader.AsDataSet();
        }
    }
}

edited Sep 26 '13 at 17:17

answered Sep 26 '13 at 15:25

BrandonLWhite

1,866
1
23
26

Do you know how to use exceldatareader to make a connections string? – Vaccano Sep 26 '13 at 17:06
I added an example. Good luck! – BrandonLWhite Sep 26 '13 at 17:18
Alas, your Ace Example does not work for me, and your example of ExcelDataReader does not use a connection string. (I am trying to set up a DataSourceAttribute for a data driven MSTest.) I appreciate your help, but neither fo these options got it working for me. +1 for a great effort though. – Vaccano Sep 26 '13 at 17:25
Ah, I understand. You are not manipulating the DataSet in code, but rather declaring a connection string as an attribute. So if you use "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=MyExcelFile.xlsx; Extended Properties='Excel 8.0;HDR=yes';" it doesn't work with files that you have? – BrandonLWhite Sep 26 '13 at 18:03
I Had to use Excel 12.0 but that worked for Office 2010 and 2013 so I am set. Thanks your your help! – Vaccano Sep 26 '13 at 18:51

Excel connection string that does not have to be version specific

1 Answers1