Complete Guide to Reading Excel Files with C# in MS Office-Free Environments

Introduction

In .NET application development, reading data from Excel files is a common requirement. However, when the target system lacks Microsoft Office installation, traditional Office interoperability approaches fail. This article systematically introduces multiple solutions for Office-free environments.

OleDB Connection Approach

Using OleDB providers represents one of the most direct methods for Excel file reading. This approach treats Excel files as databases accessible through standard ADO.NET interfaces.

Basic Implementation Code

string connectionString = 
    @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;" + 
    @"Extended Properties='Excel 8.0;HDR=Yes;'"
    
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    connection.Open()
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection)
    
    using (OleDbDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            object firstColumnValue = reader[0]
            Console.WriteLine(firstColumnValue.ToString())
        }
    }
}

Provider Selection Strategy

Different Excel file formats require corresponding OleDB providers:

• For Excel 97-2003 format (.xls files), use Microsoft.Jet.OLEDB.4.0
• For Excel 2007 and later versions (.xlsx files), install Microsoft Access Database Engine and use Microsoft.ACE.OLEDB.12.0 provider

Deployment Considerations

Special attention required when using ACE provider:

• Select appropriate Access Database Engine version matching application target platform (32-bit or 64-bit)
• Ensure correct runtime components are installed on target systems
• Address provider registration issues, particularly compatibility in mixed environments

Data Type Handling Challenges

OleDB approach presents several limitations in Excel data type processing:

• Data truncation may occur when single cell content exceeds 255 characters
• Mixed data type columns can cause type inference errors
• Automatic date and number format conversions may not meet expectations

Third-Party Library Alternatives

Beyond OleDB approach, specialized third-party libraries offer viable alternatives:

ExcelDataReader

A lightweight open-source library supporting both XLS and XLSX formats:

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        do
        {
            while (reader.Read())
            {
                for (int column = 0; column < reader.FieldCount; column++)
                {
                    Console.Write(reader.GetValue(column) + "\t")
                }
                Console.WriteLine()
            }
        } while (reader.NextResult())
    }
}

Additional Library Options

• CSharpJExcel: Specialized handling for Excel 97-2003 format
• ExcelPackage: Focused on Office Open XML format processing
• EPPlus: Feature-rich commercial-grade solution

Open XML SDK Advanced Solutions

For scenarios requiring fine-grained control or processing extremely large files, Microsoft Open XML SDK provides low-level access capabilities.

DOM Approach Processing

Suitable for complete memory loading of small to medium files:

static void ReadExcelFileDOM(string fileName)
{
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart ?? spreadsheetDocument.AddWorkbookPart()
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First()
        SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First()
        
        string text
        foreach (Row row in sheetData.Elements<Row>())
        {
            foreach (Cell cell in row.Elements<Cell>())
            {
                text = cell?.CellValue?.Text
                Console.Write(text + " ")
            }
        }
    }
}

SAX Approach for Large Files

Utilizes streaming reading to prevent memory overflow:

static void ReadExcelFileSAX(string fileName)
{
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart ?? spreadsheetDocument.AddWorkbookPart()
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First()
        
        using (OpenXmlReader reader = OpenXmlReader.Create(worksheetPart))
        {
            string text
            while (reader.Read())
            {
                if (reader.ElementType == typeof(CellValue))
                {
                    text = reader.GetText()
                    Console.Write(text + " ")
                }
            }
        }
    }
}

Performance and Compatibility Considerations

When selecting specific solutions, balance the following factors:

• File Size: DOM approach suitable for small files, SAX or streaming recommended for large files
• Deployment Complexity: OleDB requires runtime components, third-party libraries typically more lightweight
• Functional Requirements: Basic reading可以使用简单方案，复杂处理需要功能丰富的库
• Maintainability: Standard approaches (like OleDB) offer better long-term support

Best Practice Recommendations

• Always include exception handling and resource cleanup in production environments
• For critical applications, implement data validation and error recovery mechanisms
• Consider configuration-based management for connection strings and file paths
• Regularly test compatibility with different Excel versions

Conclusion

Reading Excel files in MS Office-free environments offers developers multiple reliable technical choices. The OleDB approach provides standard database-style access, third-party libraries offer lighter alternatives, while Open XML SDK delivers low-level control for advanced scenarios. Selecting the most appropriate technical solution based on specific application requirements, performance needs, and deployment environment represents the key to project success.