How Can I Directly Read Excel Files in C# Using OleDb?

Directly Reading Excel Files in C# Using OleDb

Many C# applications require the ability to read data from Excel files. Several libraries simplify this process, offering direct access to Excel data. The OleDb library is a popular open-source choice.

Here's how to use OleDb to read data from an Excel file:

string fileName = Path.Combine(Directory.GetCurrentDirectory(), "fileNameHere.xlsx"); //Improved path handling
string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={fileName};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\""; //Updated for newer Excel versions

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    connection.Open();
    OleDbDataAdapter adapter = new OleDbDataAdapter($"SELECT * FROM [{worksheetNameHere}$]", connection);
    DataSet ds = new DataSet();
    adapter.Fill(ds, "dataTable");

    DataTable data = ds.Tables["dataTable"];

    foreach (DataRow row in data.Rows)
    {
        string cellValue = row["columnName"].ToString();
        //Process cellValue
    }

    // Accessing data directly as strings:
    string cellValueDirect = data.Rows[0].Field<string>("columnName");
    //Process cellValueDirect

}

This improved code establishes a connection, selects data from the specified worksheet, and stores it in a DataTable. Individual cells are accessible via coordinates or column names. The using statement ensures proper resource cleanup. The connection string is updated to support newer Excel versions (xlsx) and includes HDR=YES and IMEX=1 for better handling of headers and data types.

The example iterates through rows and demonstrates how to retrieve cell values as strings using both ToString() and Field<string>()</string>. OleDb provides a straightforward method for importing Excel data into your C# applications for analysis and manipulation.

The above is the detailed content of How Can I Directly Read Excel Files in C# Using OleDb?. For more information, please follow other related articles on the PHP Chinese website!