c sharp

How to Read Excel Files in C# without External Libraries

Excel files are a common occurrence in the world of data and development. When working in C#, you might come across an instance where you need to process the Excel files without using an external library.

In this tutorial, we will explore how you can read an Excel file without taking advantage of the β€œ.NET” external libraries such as NPOI, EPPlus, or ClosedXML.

NOTE: Although it is technically possible, we recommend that you take advantage of external libs as they offer more performance tuning and are more capable than what is outlined in this post.

Using OLE DB

The most universal method that we can use to read the excel files in C# without external libraries is using a provider.

In this case, we can use the OLE DB provider which is a unified data access mechanism in C# that allows us to work with various data sources, including excel files.

This provider enables us to read the excel files without using an external library as it is part of the System.Data namespace.

Sample Excel File

Before we cover the process of reading the excel files using the OLE DB provider, let us setup a basic Excel file for demonstration purposes.

In our case, we have a basic excel file that has three columns as follows:

username ip_address agent
cclaque0 70.46.218.208 Mozilla/5.0 (Windows NT 6.0; WOW64; rv:24.0) Gecko/20100101 Firefox/24.0
isaunper1 110.148.187.193 Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/537.14 (KHTML, like Gecko) Chrome/24.0.1292.0 Safari/537.14
apepler2 59.0.168.37 Mozilla/5.0 (Windows NT 6.3; rv:36.0) Gecko/20100101 Firefox/36.0
cfermer3 18.196.197.180 Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_3) AppleWebKit/535.22 (KHTML, like Gecko) Chrome/19.0.1047.0 Safari/535.22
emonketon4 124.39.248.197 Mozilla/6.0 (Macintosh; I; Intel Mac OS X 11_7_9; de-LI; rv:1.9b4) Gecko/2012010317 Firefox/10.0a4

Reading an Excel File with OLE DB

Once we have the excel file ready, we can create a new C# application and import our required namespaces. In our case, we require the System.Data namespaces.

using System.Data.OleDb;
using System;


Once we import the required namespaces, we can proceed and read the data that is stored in the file as follows:

[cc lang="c#"  width="100%" height="100%" escaped="true" theme="blackboard" nowrap="0"]
using System;
using System.Data.OleDb;

namespace ExcelNoLib
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string path = "C:\\sample\\linuxhint\\sample.xlsx";
            string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={path};Extended Properties='Excel 12.0 Xml;HDR=YES;'";

            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand("SELECT * FROM [data$]", connection);

                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    Console.WriteLine("ID\tIP Address\tUser Agent");

                    while (dr.Read())
                    {
                        var id = dr["id"].ToString();
                        var ip = dr["ip_address"].ToString();
                        var user_agent = dr["agent"].ToString();

                        Console.WriteLine($"{id}\t{ip}\t{user_agent}");
                    }
                }
            }
        }
    }
}

In the given code, we start by defining the path to the excel file that we wish to read. In this case, it is a simple excel file with multiple rows.

Next, we define the connection string that allows us to tell C# that we are interested in using the OLE DB provider. In our case, we need the Microsoft.ACE.OLEDB.12.0. You can check more about the connection string in the documentation.

NOTE: This requires you to have the Microsoft Access Database Engine installed on your machine. Similarly, ensure that the target build platform is compatible with the install MSACCESS DB Engine.

For example, if you have the 64 bit version installed, ensure that you are building the project to that platform.

Download the Microsoft Access Database Engine in the following link:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

In the next section of the code, we open the OleDbConnection and execute an SQL-Like query to read the data from the β€œdata$” sheet. Ensure that this is the name of the available sheet in your Excel file.

We then create a loop that reads all the data from all the rows of the excel file. We also add a header row to the output which makes the output more readable.

This should read the excel file as shown in the following example:

There you have it!

Conclusion

In this post, we covered how you can read the Excel files in C# without using the external libraries. We hope that this tutorial helped you.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list