ADO.Net for Excel files - support different Excel versions?


I wrote a windows form application in C# that uses ADO.Net to read from an Excel file. I receive xls files but have to manually save them as xlsx files because I can't get the program to read xls files (I get "External table is not in the expected format"). As an aside, for broadest .net compatibility, I set each of the Projects (which create assemblies/dlls) for the application to .net framework 2.0.

I have 2 questions:

1. How can I get my program to read xls files when Excel 2007 is installed?

2. How can I support environments where Excel 2002 and Excel 2003 are installed - I assume different connection strings and have the users ONLY open xls files?

Code Snippet:

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

DbConnection connection = factory.CreateConnection();

connection.ConnectionString = "whatever";

connection.Open();

 

Connection strings I've tried:

// The first one worked for xlsx files w/ Excel 2007 installed:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFile.xls;Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;""

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;""

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myFile.xls;Extended Properties=""Excel 5.0;HDR=Yes;IMEX=1;""


Bill
0
metazonezoan
1/28/2009 2:26:47 AM
asp.net.object-datasource 16182 articles. 0 followers. Follow

2 Replies
1342 Views

Similar Articles

[PageSpeed] 42

 use this connection string and sample

        private void btnLoadExcel_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "Excel Files|*.xls;*.xlsx|All Files|*.*";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                string fileName = dlg.FileName;
                string connectionString = string.Format("Data Source={0};Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;", fileName);
                using(System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(connectionString))
                {
                    string query = "SELECT * FROM [Sheet1$]";
                    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query,cnn);
                    cnn.Open();
                    DataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    grdView.DataSource = ds.Tables[0];
                }
            }
        }

and diffrent versions are controlled via diffrent connectionStrings


سیستم آموزش مجازی
سیستم مدیریت کتابخانه دیجیتال
وستا
گزارش ساز
0
Mojtaabaa
1/28/2009 4:39:55 AM

Thanks for the reply.  I tried your code instead of using DbProviderFactory and DbConnection and  I can't get it to work - I've tried every suggestion so far.  I'm now focusing solely on opening an xls file (before, I saved the xls as an xlsx using Excel 2007 and that's working).  I even saved the xls file w/ Excel 2007 as Excel 97 - 2003 Workbook.  In addition, I opened the Excel file in notepad to see if it's in HTML format. 

CODE SNIPPET:

System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString);
connection.Open();

CONNECTION STRINGS AND EXCEPTIONS/ERRORS:

Note: The exceptions say that the source is the Microsoft Office Access Database Engine

// error: Could not find installable ISAM; reason: left out the double-quotes in Extended Properties
Data Source=C:\myFile.xls;Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;IMEX=1;HDR=YES

// error: External table is not in the expected format
Data Source=C:\myFile.xls;Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties="Excel 12.0;IMEX=1;HDR=YES";

// error: Unspecified error
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myFile.xls;Extended Properties="HTML Import";

// error: External table is not in the expected format
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFile.xls;Extended Properties="Excel 12.0 Xml;IMEX=1;HDR=YES";

// error: External table is not in the expected format
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myFile.xls;Extended Properties="Excel 8.0;HDR=NO;IMEX=1";

 


Bill
0
metazonezoan
1/28/2009 4:30:12 PM
Reply:

Web resources about - ADO.Net for Excel files - support different Excel versions? - asp.net.object-datasource

Ted Cruz’s Dad: God Staged an Intervention to Force Ted to Run for President: VIDEO
Righteous presidential wannabe Ted Cruz can rest easy because it would appear that God is on his side. That’s according to his hate-mongering ...

Seth Rogen & Amy Schumer’s Bud Light Super Bowl Commercial: “Just Wait Till You See Our Caucus”
We don’t know what isn’t funny in Seth Rogen and Amy Schumer ’s Super Bowl Bud Light faux political commercial where they talk about the size ...

Tata Motors In India Decides To Rename New Hatchback Called Zica
“Zica” seemed like such a nice name for a new zippy hatchback from India’s Tata Motors. It’s a cute name that isn’t (as far as we know) secretly ...

Yahoo!: CNBC’s Faber Questions Ebitda, Cramer Demands to Know About a Sale
Shares of Yahoo! ( YHOO ) are down $1.89, or 7%, at $27.17, as the Street mulls the company’s announcement yesterday afternoon of a lower-than-expected ...

Apple Watch officially launching in Greece, Bahrain, Kuwait, Oman & Qatar next week
Israel is not the only country getting an official Apple Watch rollout next week. Notices on Apple websites around the world indicate that the ...

North Korea plans to try again to orbit satellite
The Unha-3 rocket, the platform for North Korea's (sort of) successful satellite launch in 2012. Another launch has been announced for February. ...

Two Men Catch on Video the Moment They Are Nearly Struck by Lightning
YouTube Link Meanwhile in Australia, where people with charming accents are about to be killed by All of The Things, a storm is blowing in. ...

Cuba Gooding Jr. Names His 'American Crime Story' Co-Stars As One of His Favorites of All Time!
Cuba Gooding Jr . has a favorite co-star! – US Weekly What did Wiz Khalifa have to say about Kanye West ? – TMZ Brooklyn Beckham went on a ...

How to uninstall the Windows 10 'Recommended' update and go back to Windows 7 or 8.1
Now that Microsoft is forcibly downloading Windows 10 onto unsuspecting Windows 7 and 8.1 users’ PCs there’s going to be a lot of unhappy customers ...

Amazon will open over 300 brick-and-mortar bookstores — we visited the first one
In November, Amazon opened its first brick-and-mortar location, Amazon Books , in Seattle's upscale University Village mall. While originally ...

Resources last updated: 2/3/2016 5:00:16 PM