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:
And the C# code:
protected void btnContinue_Click(object sender, EventArgs e)
string tempFileLocation = Path.GetTempFileName();
string connString =
+ @";Extended Properties=""Excel 8.0;HDR=YES;""";
OleDbConnection conn = new OleDbConnection(connString);
// Get the name of the first Excel worksheet
string worksheetName =
conn.GetSchema("Tables").Rows["TABLE_NAME"] as string;
OleDbCommand cmd =
new OleDbCommand(@"SELECT * FROM [" + worksheetName + @"]", conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
// 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:
you could do dt.Rows[“Email”] to get firstname.lastname@example.org . How cool is that!
All of this works on CSV files as well, you only have to tweak the connection string a little bit.