Getting Data from an Excel File in ASP.NET

Let’s say I have a FileUpload control for the user to select an excel file. I then want to access the data in the excel file and be able to do stuff like row[“column”] to access the value of a specific item within a row.

This solution does not rely on MS Excel to be installed on the server. It uses the Microsoft.Jet.OLEDB.4.0 data source. Which I think is the same one that is used to access a MS Access database.

Now to the code. This is how the UI would look like:

image

And the C# code:

protected void btnContinue_Click(object sender, EventArgs e)
{
    string tempFileLocation = Path.GetTempFileName();
    this.fuCSV.SaveAs(tempFileLocation);
    string connString =
        @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
        + tempFileLocation
        + @";Extended Properties=""Excel 8.0;HDR=YES;""";

    OleDbConnection conn = new OleDbConnection(connString);
    conn.Open();

    // Get the name of the first Excel worksheet
    string worksheetName =
        conn.GetSchema("Tables").Rows[0]["TABLE_NAME"] as string;
    OleDbCommand cmd =
        new OleDbCommand(@"SELECT * FROM [" + worksheetName + @"]", conn);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    DataTable dt = new DataTable();
    da.Fill(dt);

    // Do cool stuff here

}

The “HDR=YES” part of the connection string enables you to access the fields inside a row using the column header as an index. For data like:

Name

Email

Pedro

pedro@somewhere.net

Bob

bob@somewhere.net

 

you could do dt.Rows[0][“Email”] to get pedro@somewhere.net . How cool is that!

All of this works on CSV files as well, you only have to tweak the connection string a little bit.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s