Reading Excel Files in C# with Free and Open Source Libraries

Do you ever find yourself struggling to read Excel files directly in your C# applications? If so, you’re not alone! Many developers face the challenge of extracting data from Excel spreadsheets without relying on manual processes. The good news is that there are efficient and free methods to achieve this using open-source libraries. In this blog post, we will explore how you can read Excel files (.xls) directly from a C# program, eliminating the need for tedious manual steps.

The Problem: Manual Processing of Excel Files

Traditionally, many developers opt for a manual approach by exporting Excel sheets into a Unicode text format and then parsing the resulting tab-delimited files. While this works, it’s not the most efficient method. You may find it cumbersome, especially if you need to repeat this process frequently or handle multiple files.

Fortunately, there are better ways to read Excel files programmatically. Let’s dive into the solution!

The Solution: Using OLE DB to Read Excel Files

One of the simplest methods to read Excel files in C# is using the OLE DB (Object Linking and Embedding Database) provider. With OLE DB, you can connect to Excel files as if they were databases and execute SQL queries to retrieve the data you need. Here’s how to do it step-by-step:

Step 1: Set Up Your Connection

Before reading the data, you need to set up a connection to your Excel file. Here’s an example of how to format your connection string:

var fileName = string.Format("{0}\\fileNameHere", Directory.GetCurrentDirectory());
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

• fileName: This variable holds the path to your Excel file.
• connectionString: This is the connection to the Excel file that includes the provider and data source information.

Step 2: Fetch the Data

Now that you have established the connection, you can retrieve data from a specified worksheet using an OleDbDataAdapter. Below is the code to fetch all data from the worksheet:

var adapter = new OleDbDataAdapter("SELECT * FROM [workSheetNameHere$]", connectionString);
var ds = new DataSet();

// Fill the DataSet with the fetched data
adapter.Fill(ds, "anyNameHere");
DataTable data = ds.Tables["anyNameHere"];

• Replace workSheetNameHere with the actual name of the sheet you want to read data from.

Step 3: Use LINQ for Data Manipulation

Once you have the data in a DataTable, you can use LINQ (Language Integrated Query) to manipulate and query the data easily. If you want to filter out any rows where a specific field is empty, here’s how you can do that:

var data = ds.Tables["anyNameHere"].AsEnumerable();

var query = data.Where(x => x.Field<string>("phoneNumber") != string.Empty).Select(x =>
    new MyContact
    {
        firstName = x.Field<string>("First Name"),
        lastName = x.Field<string>("Last Name"),
        phoneNumber = x.Field<string>("Phone Number"),
    });

Summary of Key Points

To summarize, reading Excel files in C# using an OLE DB provider is straightforward and eliminates the need for manual file handling. Here are the key points to remember:

• Set up a proper connection string using the OLE DB provider.
• Fetch data from Excel using the OleDbDataAdapter.
• Leverage LINQ to filter and manipulate the extracted data.

By following these steps, you can create a seamless integration for reading Excel files directly in your C# applications.

Conclusion

Efficiently reading Excel files in C# doesn’t have to be a hassle. With the approach we’ve outlined, you can automate the process, save time, and focus on what really matters in your application development.

Now that you’re equipped with the knowledge to eliminate manual steps in reading Excel files, go ahead and give it a try in your projects!