5 Simple Ways to Read Excel Sheets in C#

Reading Excel files is a common requirement for developers, particularly those working on data-driven applications or business tools where data might frequently come from spreadsheets. Microsoft Excel is a powerful tool for data management and manipulation, making it essential to know how to interact with its files programmatically, especially using languages like C#. Here, we will explore five straightforward methods to read Excel sheets using C#, each with its benefits and use-cases.

Method 1: Using EPPlus Library

EPPlus is an open-source library that provides an easy interface for manipulating Excel files. Here's how you can read an Excel sheet with EPPlus:

• Install EPPlus from NuGet Package Manager.
• Import the necessary namespaces.
• Use the code snippet below to read data:

using OfficeOpenXml;
using System.IO;

public void ReadExcelWithEPPlus(string filePath)
{
    FileInfo file = new FileInfo(filePath);
    using (ExcelPackage package = new ExcelPackage(file))
    {
        // Get the first worksheet
        ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

        // Read data
        for (int row = 1; row <= worksheet.Dimension.Rows; row++)
        {
            for (int col = 1; col <= worksheet.Dimension.Columns; col++)
            {
                var cellValue = worksheet.Cells[row, col].Value?.ToString();
                Console.WriteLine($"Cell at [{row},{col}] value: {cellValue}");
            }
        }
    }
}

📝 Note: Remember to dispose of the ExcelPackage instance properly to release file locks.

Method 2: Using ClosedXML

ClosedXML is another popular library for dealing with Excel files in .NET. It's easy to use and provides many Excel-related operations:

• Install ClosedXML via NuGet.
• Include the required namespaces.
• Read Excel data like this:

using ClosedXML.Excel;

public void ReadExcelWithClosedXML(string filePath)
{
    using (var workbook = new XLWorkbook(filePath))
    {
        var worksheet = workbook.Worksheet(1);
        
        foreach (var row in worksheet.Rows())
        {
            foreach (var cell in row.Cells())
            {
                Console.WriteLine($"Cell: {cell.Address.ToString()}, Value: {cell.Value}");
            }
        }
    }
}

Method 3: Using OleDb

The OleDb library within .NET allows interaction with Excel files much like a database, perfect for scenarios where you need to perform complex queries:

• Set up the connection string with Excel's provider.
• Execute a SQL query to fetch data.

using System.Data.OleDb;

public void ReadExcelWithOleDb(string filePath)
{
    string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'", filePath);
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        string query = "SELECT * FROM [Sheet1$]";
        OleDbCommand command = new OleDbCommand(query, conn);
        OleDbDataReader reader = command.ExecuteReader();
        
        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                Console.WriteLine(reader[i]);
            }
        }
    }
}

Method 4: Using Microsoft Interop

Microsoft Office Interop libraries allow direct interaction with Excel applications. This method is useful when you need to modify or read data programmatically from an Excel instance:

• Ensure Microsoft.Office.Interop.Excel is installed via NuGet.
• Initialize an Excel application, open a workbook, and read the content:

using Excel = Microsoft.Office.Interop.Excel;

public void ReadExcelWithInterop(string filePath)
{
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
    Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    Excel.Range xlRange = xlWorksheet.UsedRange;
    
    int rowCount = xlRange.Rows.Count;
    int colCount = xlRange.Columns.Count;
    
    for (int i = 1; i <= rowCount; i++)
    {
        for (int j = 1; j <= colCount; j++)
        {
            Console.WriteLine(xlRange.Cells[i, j].Value);
        }
    }
    
    xlWorkbook.Close();
    xlApp.Quit();
}

📝 Note: COM interop objects like Excel applications can remain active in memory; it's crucial to properly close and release them.

Method 5: Using DocumentFormat.OpenXml

This method involves directly reading Excel files through the Office Open XML format:

• Install DocumentFormat.OpenXml from NuGet.
• Read Excel cells like XML nodes:

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public void ReadExcelWithOpenXml(string filePath)
{
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
    {
        WorkbookPart workbookPart = doc.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
        
        OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
        while (reader.Read())
        {
            if (reader.ElementType == typeof(Row))
            {
                reader.ReadFirstChild();
                do
                {
                    if (reader.ElementType == typeof(Cell))
                    {
                        Cell cell = (Cell)reader.LoadCurrentElement();
                        Console.WriteLine(cell.InnerText);
                    }
                } while (reader.ReadNextSibling());
            }
        }
    }
}

📝 Note: This method can be quite intricate due to the XML format but is very powerful for precise data extraction.

In conclusion, reading Excel sheets in C# offers developers several robust options, each suited to different needs. From open-source libraries like EPPlus and ClosedXML, which provide straightforward manipulation capabilities, to more direct interaction methods like OleDb for querying or Microsoft Interop for real-time interaction, developers can choose based on performance, ease of use, and specific application requirements. DocumentFormat.OpenXml gives you fine-grained control over the XML data, perfect for when detailed parsing is necessary. Understanding these methods ensures you can handle Excel files effectively in your .NET applications, providing flexibility in managing and processing data from spreadsheets.

What are the performance considerations for different Excel reading methods?

+

Performance varies with each method. EPPlus and ClosedXML are typically fast due to optimized handling. OleDb might be slower for large datasets but allows SQL-like queries. Microsoft Interop can be slow due to Excel application interactions, and OpenXml, while precise, requires careful parsing.

Can I use these methods to write data back to Excel?

+

Yes, all the mentioned libraries and methods can also write data to Excel files. EPPlus and ClosedXML have straightforward APIs for writing, while OleDb and Microsoft Interop allow data manipulation before saving.

Which method supports older Excel file formats like .xls?

+

Microsoft Interop and OleDb are better suited for working with older Excel file formats like .xls. EPPlus, ClosedXML, and OpenXml primarily work with the newer .xlsx format.