Complete Guide to Reading Excel Files in C# Without Office.Interop Using OleDb

Technical Background and Problem Analysis

When processing Excel files in C# applications, many developers habitually use the Microsoft.Office.Interop.Excel library. However, this approach has significant limitations: first, it requires the target system to have Microsoft Office suite installed; second, in server environments or background automation processes, Office.Interop may cause permission and stability issues. As mentioned in the reference article about background automation scenarios, even with Application.Visible = false set, runtime errors such as "Exception has been thrown by the target of an invocation" may still occur.

Core Implementation of OleDb Solution

The solution based on OleDb provider accesses Excel files by establishing database connections, treating spreadsheets as data tables. The core advantage of this method lies in its lightweight nature and good cross-platform compatibility.

Connection string configuration is the key component of the entire solution:

var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";

Each parameter serves specific functions: Provider=Microsoft.ACE.OLEDB.12.0 specifies the use of Access Database Engine; IMEX=1 ensures mixed data type columns are always treated as text; HDR=NO indicates the first row is not treated as column headers; TypeGuessRows=0 and ImportMixedTypes=Text together prevent precision loss that may result from automatic data type inference.

Complete Data Extraction Workflow

Implementing complete Excel reading functionality requires following systematic steps:

var fileName = @"C:\ExcelFile.xlsx";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
using (var conn = new OleDbConnection(connectionString))
{
    conn.Open();

    var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";

        var adapter = new OleDbDataAdapter(cmd);
        var ds = new DataSet();
        adapter.Fill(ds);
    }
}

This implementation demonstrates several important technical aspects: dynamically obtaining information about all worksheets in the workbook through the GetOleDbSchemaTable method; using parameterized approach to construct SQL query statements for accessing specific worksheets; utilizing OleDbDataAdapter to fill query results into DataSet, providing structured support for subsequent data processing.

Comparative Analysis of Third-Party Library Solutions

In addition to the OleDb solution, the development community offers various specialized Excel processing libraries. CSharpJExcel provides lightweight reading capabilities specifically for Excel 97-2003 format (.xls), with its API designed to be simple and intuitive:

Workbook workbook = Workbook.getWorkbook(new System.IO.FileInfo(fileName));
var sheet = workbook.getSheet(0);
var content = sheet.getCell(colIndex, rowIndex).getContents();
workbook.close();

For newer Excel 2007/2010 formats (.xlsx), ExcelPackage offers native support based on Office Open XML standards:

using (ExcelPackage xlPackage = new ExcelPackage(existingFile))
{
  ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
  int iCol = 2;
  for (int iRow = 1; iRow < 6; iRow++)
    Console.WriteLine("Cell({0},{1}).Value={2}", iRow, iCol, worksheet.Cell(iRow, iCol).Value);
  Console.WriteLine("Cell({0},{1}).Formula={2}", 6, iCol, worksheet.Cell(6, iCol).Formula);
}

ExcelDataReader, as a comprehensive solution, can handle both .xls and .xlsx formats simultaneously, demonstrating outstanding performance in format compatibility. NPOI, as the .NET port of the Apache POI project, not only supports Excel files but also extends to Word and PowerPoint document processing. EPPlus provides more user-friendly API interfaces built upon ExcelPackage.

Technology Selection and Practical Recommendations

When choosing an appropriate Excel reading solution, developers need to consider multiple factors comprehensively. For simple data extraction tasks, especially scenarios requiring only reading without complex format operations, the OleDb solution becomes the preferred choice due to its system-level integration and lower resource overhead. When dealing with legacy .xls formats or pursuing extreme performance, CSharpJExcel offers professional-grade solutions. For modern .xlsx file processing and scenarios requiring complete Excel functionality support, ExcelPackage and EPPlus demonstrate clear advantages.

During actual deployment, attention should be paid to component dependencies in the target environment: the OleDb solution requires installation of corresponding Access Database Engine; while most third-party libraries are pure managed code implementations, offering better deployment convenience. In performance-sensitive applications, benchmark testing is recommended to select the most suitable solution for specific data scales and access patterns.