5 Ways to Pull Data from Excel in C

Working with Excel data programmatically offers a wealth of opportunities for automation, data analysis, and reporting in software applications. C#, with its robust libraries and compatibility with COM (Component Object Model), provides multiple avenues to interact with Excel files. In this post, we'll explore five distinct methods to pull data from Excel using C#, each tailored to different needs and skill levels.

Method 1: Using Microsoft Office Interop

Microsoft.Office.Interop.Excel allows developers to control Excel from C# applications through COM interop, providing direct access to Excel’s functionalities:

• Install: Add the COM reference for Microsoft Excel Object Library.
• Initialize Excel:

Application excel = new Application();
excel.Visible = false;

• Open Workbook:

Workbook workbook = excel.Workbooks.Open(@“C:\Path\To\ExcelFile.xlsx”);

• Read Data:

Worksheet worksheet = workbook.Sheets[1];
Range usedRange = worksheet.UsedRange;
object[,] values = usedRange.Value2;

⚠️ Note: The Interop method can lead to Excel instance running in the background, potentially leading to resource issues on shared systems.

Method 2: Excel Data Reader Library

The ExcelDataReader library is an open-source option for reading Excel files without requiring Microsoft Excel installed:

• Install: Use NuGet Package Manager to add ExcelDataReader.
• Open Workbook:

var stream = File.Open(“ExcelFile.xlsx”, FileMode.Open, FileAccess.Read);
var excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

• Read Data:

var result = excelReader.AsDataSet(new ExcelDataSetConfiguration() {
    UseColumnDataType = true
});

Method 3: EPPlus

EPPlus offers an efficient way to manipulate Excel files, including pulling data:

• Install: Add the EPPlus NuGet package.
• Open Workbook:

var package = new ExcelPackage(new FileInfo(“ExcelFile.xlsx”));

• Read Data:

var worksheet = package.Workbook.Worksheets[1];
var startRow = worksheet.Dimension.Start.Row;
var endRow = worksheet.Dimension.End.Row;
var startColumn = worksheet.Dimension.Start.Column;
var endColumn = worksheet.Dimension.End.Column;

for (int row = startRow; row <= endRow; row++)
{
    for (int col = startColumn; col <= endColumn; col++)
    {
        var cellValue = worksheet.Cells[row, col].Value;
        Console.WriteLine(cellValue);
    }
}

Method 4: ADO.NET

If your Excel data resembles a database table, using ADO.NET can be effective:

• Connection String:

string connectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ExcelFile.xlsx;Extended Properties=‘Excel 12.0 Xml;HDR=Yes;’”;

• Read Data:

using (var conn = new OleDbConnection(connectionString))
{
    conn.Open();
    string query = “SELECT * FROM [Sheet1$]”;
    using (var command = new OleDbCommand(query, conn))
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader[0]);
        }
    }
}

Method 5: ClosedXML

ClosedXML provides a wrapper around the Open XML SDK, allowing data manipulation in a more user-friendly way:

• Install: Add the ClosedXML NuGet package.
• Open Workbook:

var workbook = new XLWorkbook(“ExcelFile.xlsx”);
var worksheet = workbook.Worksheet(1);

• Read Data:

foreach (var row in worksheet.Rows())
{
    foreach (var cell in row.Cells())
    {
        Console.WriteLine(cell.Value);
    }
}

💡 Note: Each method has its strengths; choose the one that aligns best with your application requirements, licensing needs, and Excel file format compatibility.

In summary, extracting data from Excel files in C# can be achieved through various methods, each with its own set of features, strengths, and potential limitations. From the power and familiarity of Microsoft Office Interop, the lightweight nature of ExcelDataReader, the full-featured capabilities of EPPlus, to the database-like querying of ADO.NET, or the simplicity of ClosedXML, C# developers have several tools at their disposal. Each method enables developers to automate the process of data extraction, thereby saving time, reducing errors, and enhancing application functionalities.

What’s the difference between using Interop and other libraries?

+

Interop requires Excel to be installed on the system where the application runs, making it less portable. Other libraries like ExcelDataReader or EPPlus don’t require Excel, making them more versatile for server-side or cross-platform environments.

Can EPPlus handle .xls files?

+

EPPlus primarily deals with the newer Office Open XML format (.xlsx), although it has some support for .xls files. For older formats, consider using other libraries or converting the files.

Which method is best for handling large datasets?

+

For very large datasets, consider using ADO.NET or EPPlus, as they can handle bulk reading more efficiently. Interop might struggle with memory and performance.