Read Excel files the easy way on Windows Server 2008 R2 64-bit

June 23, 2011 08:48 by Admin

Here’s a quick one. I once had to do some importing from an Excel-sheet in .NET 4.0, running on a 64-bit server. It is really trivial, so I’ll only post the two things that I had to look for (which apparently can be hard to find on the ‘net).

First of all, you’ll need the Microsoft Access Database Engine runtime for 64-bit:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

The connection string to use looks like this:

 

string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=\"Excel 12.0;HDR=YES;-\"", fileName);

The fileName variable must be an absolute path to an excel file.

 

 

The code to use it could look something like this:

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Items$]", connectionString);
 
DataSet ds = new DataSet(); 
adapter.Fill(ds); 
foreach (DataTable table in ds.Tables)
{
    Console.WriteLine(table.TableName);
} 
 
DataTable itemsDataTable = ds.Tables["Table"];

Tags:
Categories:
Actions: E-mail | Permalink | Comments (1) | Comment RSSRSS comment feed

Comments (1) -

June 23. 2011 09:39

Lasse Rasch

Great stuff... And yes, for some strange reason it is very hard to find information about this on google.

Another soulution to support Excel Import on 64 Bit systems is to use the Aspose.Cells component.

It is much more advanced than this approch, but also quite expensive.

For simple imports on 64 Bit systems, this soulution is great...

I have posted an example of how to do import without the use of Datatables below.


        public void Readexcel(string filepath, string sheetname)
        {
            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=\"Excel 12.0;HDR=YES\"", filepath);

            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            List<Project> Projects = new List<Project>();

            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM [" + sheetname + "$]";
                    connection.Open();

                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        int i = 1;

                        while (dr.Read())
                        {
                            try
                            {
                               string name = dr[0].ToString();
                                string phonenumber = dr[1].ToString();
                            }
                            catch (Exception Ex)
                            {
                                throw new Exception("Error reading from Excel Line number '" + i + "'", Ex);
                            }
                                i++;
                        }
                    }
                }
            }
        }

Lasse Rasch

Comments are closed