Working with Excel files in .NET without using COM

Read and write Excel files with C# using OLEDB on x86 and x64 platforms

Excel is a common format for storing tabular data. It is a powerful tool from Microsoft Office package.

Since it is often format for string data, it is often required to work with this data from the third party applications. Office itself provides libraries for working with Excel files along with libraries for other products from Microsoft Office package. 

Unfortunately, Microsoft Office does not come for free, plus it is not a good practice to have Office installed on server where you might run Web applications. Luckily, Windows comes with Microsoft.Jet.OLEDB.4.0 driver which provides access to Excel files but there is only 32bit version, so in case you application is compiled for x64 platform, you will not be able to use it.

If you want to compile your application for x64 you have to install Microsoft Access Database Engine 2010 Redistributable or Microsoft Access Database Engine 2016 Redistributable which comes with both x86 and x64 support. It is free and available from Microsoft download website.

  <connectionStrings>
    <add name="connection.excel.system" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\sample.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';"/>
    <add name="connection.excel.office" connectionString="Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\temp\sample.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';"/>
  </connectionStrings>
    
  • HDR=Yes; indicates that the first row contains column names, not data. HDR=No; indicates the opposite.
  • IMEX=1; tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. This option will also set readonly for Excel file, so if you are about to write to Excel file, set this option to 0

Writing Excel file

            var connectionString =  ConfigurationManager.ConnectionStrings["connection.excel.system"];

            using (OleDbConnection conn = new OleDbConnection(connectionString.ConnectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;

                cmd.CommandText = "CREATE TABLE [Customers] (id INT, [Full Name] VARCHAR, [Email Address] VARCHAR, [Date Created] DATE);";
                cmd.ExecuteNonQuery();

                cmd.CommandText = $"INSERT INTO [Customers](id,[Full Name],[Email Address],[Date Created]) VALUES (23,'Dejan','test@test.com','{DateTime.Now.ToString("yyyy-MM-dd")}');";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "UPDATE [Customers] SET [Full Name] = 'Dejan Stojanovic' WHERE id = 23;";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "DROP TABLE [Customers];";
                cmd.ExecuteNonQuery();

                conn.Close();
            }
    
Note

Unfortunately DELETE is not supported, only INSERT and UPDATE are supported. As an alternative you can set nulls for the row you want to delete or insert existing rows in a new sheet by skipping the one you want to delete and then delete old sheet and rename the temporary one

Reading Excel file

            using (OleDbConnection conn = new OleDbConnection(connectionString.ConnectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;

                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Customers] WHERE id=23", connectionString.ConnectionString);
                DataSet dataSet = new DataSet();

                adapter.Fill(dataSet,"Customers");

                conn.Close();
            }
    

Binding directly to DataTable instance is not possible, so even though you might have only one table in you result, you have to bind to DataSet with OleDbDataAdapter and access the table object from tables collection of DataSet class instance.

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan Stojanovic is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform includion ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

JavaScript

read more

SQL/T-SQL

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article