programming

Reading an Excel File Using C# with Code Examples

•Published on

MS Excel is one of the most widely used tools for organizing and analyzing data in business, education, and research. It provides a simple way for people to work with structured information such as sales reports, employee rosters, and financial statements. But when it comes to building software that consumes this information, developers need a reliable method to read Microsoft Excel files programmatically and, often, to transfer this entire file data into a SQL Server database.

In the C# read Excel file ecosystem, one of the most efficient solutions is IronXL, a .NET library that enables developers to load, read the Excel file, and manipulate Excel documents without depending on Microsoft Office or Interop. This article will walk you through reading Excel files in C# using IronXL without the need to have Excel installed, covering everything from basic file loading with column headers to advanced data processing techniques.

Why Choose IronXL for Reading Excel Files?

There are several libraries available for handling Excel in C#, such as NPOI, ClosedXML, and EPPlus. While these are powerful, IronXL offers distinct advantages that make it an attractive choice:

  • No Office Installation Required: IronXL works independently of Microsoft Office, avoiding compatibility issues.
  • Simple API: The library provides a clean, developer-friendly API that requires less boilerplate code.
  • Multi-format Support: Reads and writes .XLSX, .XLS, and .CSV files.
  • Performance Optimized: Handles large Excel files efficiently.
  • Cross-platform: Compatible with .NET Framework, .NET Core, .NET 5/6+, and works on Windows, Linux, and macOS.

With these features, IronXL makes reading Excel files straightforward and reliable.

Installing IronXL

Before we start coding, you’ll need to install IronXL. The easiest way is via NuGet Package Manager or by downloading the DLL:

Using Package Manager Console

Install-Package IronXL.Excel

Using .NET CLI

dotnet add package IronXL.Excel

Once installed, import the namespace into your project:

using IronXL;

Basic Example: Reading an Excel File

Let’s begin with a simple example. Suppose we have an Excel file named Products.xlsx with the following data:

The following C# code reads the data:

using System;
using IronXL;
class Program
{
    static void Main()
    {
        // Load the workbook
        WorkBook workbook = WorkBook.Load("Products.xlsx");
        // Get the first worksheet
        WorkSheet sheet = workbook.DefaultWorkSheet;
        // Read specific cell values
        string product = sheet["A2"].StringValue;
        int quantity = sheet["B2"].IntValue;
        double price = sheet["C2"].DoubleValue;
        Console.WriteLine($"Product: {product}, Quantity: {quantity}, Price: {price}");
    }
}

Explanation:

  • WorkBook.Load loads the Excel file into memory.
  • DefaultWorkSheet returns the first worksheet.
  • Cells are accessed using either Excel-style addresses (“A2”) or indices.
  • IronXL automatically converts the value into the correct type (string, int, double).

This is the simplest way to extract values directly from an Excel file.

Iterating Through Rows and Columns

Reading single cells is fine for small files, but real-world Excel files usually contain large datasets. IronXL makes it easy to loop through rows and columns.

using System;
using IronXL;
using System.Linq;
class Program
{
    static void Main()
    {
        WorkBook workbook = WorkBook.Load("Products.xlsx");
        WorkSheet sheet = workbook.DefaultWorkSheet;
        Console.WriteLine("Product List:");
        // Skip header row and iterate over the rest
        foreach (var row in sheet.Rows.Skip(1))
        {
            string product = row.Columns[0].StringValue;
            int quantity = row.Columns[1].IntValue;
            double price = row.Columns[2].DoubleValue;
            Console.WriteLine($"{product} - {quantity} units - ${price}");
        }
    }
}

Here, each row is represented as a collection of columns. You can easily access values using row.Columns[index]. The use of Skip(1) ensures that the header row is ignored.

Reading Excel Files by Range

IronXL also allows you to work with a range of cells instead of looping through the entire sheet. This is useful when you only want a subset of data.

var range = sheet["A2:C4"];
// var reader
foreach (var cell in range)
{
    Console.Write($"{cell.Value}\t");
    if (cell.ColumnIndex == 2) Console.WriteLine();
}

This code selects a block of cells from A2 to C4 and iterates over them.

Reading CSV Files with IronXL

IronXL is not limited to .XLSX and .XLS; it also supports .CSV files. Reading a CSV file is just as easy:

WorkBook csvWorkbook = WorkBook.LoadCSV("Data.csv");
WorkSheet csvSheet = csvWorkbook.DefaultWorkSheet;
foreach (var row in csvSheet.Rows)
{
    Console.WriteLine($"{row.Columns[0].StringValue}, {row.Columns[1].StringValue}");
}

This flexibility makes IronXL a one-stop solution for different file formats.

Advanced Data Processing

Often, developers don’t just read data; they need to analyze it. IronXL integrates seamlessly with LINQ for filtering and aggregation.

Example: Calculate Total Sales

double totalSales = 0;
foreach (var row in sheet.Rows.Skip(1))
{
    int quantity = row.Columns[1].IntValue;
    double price = row.Columns[2].DoubleValue;
    totalSales += quantity * price;
}
Console.WriteLine($"Total Sales: ${totalSales}");

Example: Filter Expensive Products

var expensiveProducts = sheet.Rows.Skip(1)
    .Where(r => r.Columns[2].DoubleValue > 500);
foreach (var row in expensiveProducts)
{
    Console.WriteLine($"High Value: {row.Columns[0].StringValue}");
}

These techniques show how IronXL allows you to transform raw Excel data into meaningful insights.

Handling Empty or Invalid Cells

Real-world Excel files may contain missing or invalid data. IronXL makes it easy to check for empty cells.

string value = sheet["D2"].IsEmpty ? "N/A" : sheet["D2"].StringValue;
Console.WriteLine(value);

This prevents runtime errors and ensures robust applications.

Handling Merged Cells in Excel

When working with real-world Excel files, it’s common to encounter a merged cell. For example, headers might span multiple columns, or summary rows might combine several cells into a single cell. If you don’t account for merged cells, you might get unexpected results when reading data.

IronXL makes it easy to check and work with merged cells:

WorkBook workbook = WorkBook.Load("Report.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

// Access a merged cell at A1
var cell = sheet["A1"];
// Check if it's merged
if (cell.IsMergedCell)
{
    Console.WriteLine("Cell A1 is part of a merged cell.");
    
    // Get the merged cell range
    var mergedRange = cell.MergedRangeAddress;
    Console.WriteLine($"Merged Range: {mergedRange}");
    
    // Print the shared value of the merged cell
    Console.WriteLine($"Merged Value: {cell.Value}");
}

Key Notes:

  • IsMergedCell indicates whether a cell is merged.
  • MergedRangeAddress gives the full range of the merged cell (e.g., A1:C1).
  • All merged cells share the same value, so reading from one gives you the merged cell content.

Saving Processed Data Back to Excel

After reading and processing, you might want to save the results back to an Excel file.

WorkBook report = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet summary = report.CreateWorkSheet("Summary");

summary["A1"].Value = "Total Sales";
summary["B1"].Value = totalSales;
report.SaveAs("Report.xlsx");

This demonstrates how IronXL handles both reading and writing tasks seamlessly.

Performance Tips

When working with large Excel files:

  1. Use row iteration instead of individual cell lookups.
  2. Consider CSV format for bulk imports, as it loads faster.
  3. Apply LINQ queries instead of manual loops where possible.

IronXL is optimized, but these practices ensure maximum efficiency.

Licensing and Trial

IronXL provides a fully functional**** free trial, which includes all features but adds a watermark to saved files. For production use, developers can purchase a commercial license. This makes it easy to experiment before committing.

Conclusion

Reading Excel files in C# is a common requirement across industries, from finance to retail to education. With IronXL, this task becomes significantly easier. The library provides a clean API, multi-format support, and high performance, making it ideal for developers of all levels.

We explored:

  • Loading Excel and CSV files.
  • Reading cells, rows, and ranges.
  • Handling structured data.
  • Filtering and aggregating with LINQ.
  • Saving results back into Excel.

By leveraging IronXL, you can transform C# applications into powerful data-processing tools without relying on Microsoft Office. Whether you are building reporting systems, data importers, or analytics dashboards, IronXL will help you unlock the full potential of Excel integration. Unlock the full potential of IronXL with a free trial.