How to Read Excel Files in C# without External Libraries
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;
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.
Source: linuxhint.com