I had to use the script below to read in values from an Excel sheet using C# recently. The script itself is generic enough but it has one benefit - you don't need to know the name of the sheet you want to import. This can be very beneficial if you have people supplying you with Excel sheets as everyone has their own way of saving sheet names.
The code in this example will take your Excel sheet and read in the first 5 columns. It will also loop through any other sheet and read in the first 5 columns in those too.
//declare at the start of your code
using System.Data.OleDb;
//declare connectors and set the data table to nothing
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myexcelsheet.xls;Extended Properties=Excel 8.0;";
objConn = new OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array because we might not know what the sheet name is.
// will loop through all sheets
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
// Loop through all of the sheets (remove the for loop and use excelSheets[0].ToString() in
// connector below to pick just the first excel sheet)
for (int j = 0; j < excelSheets.Length; j++)
{
string ExcelQuery;
System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection(connString);
ExcelQuery = "Select * from [" + excelSheets[j].ToString() + "]"; // from Sheet1";
System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection);
ExcelConnection.Open();
System.Data.OleDb.OleDbDataReader ExcelReader;
ExcelReader = ExcelCommand.ExecuteReader();
string col1, col2, col3, col4, col5;
while (ExcelReader.Read())
{
col1 = (ExcelReader.GetValue(0)).ToString();
col2 = (ExcelReader.GetValue(1)).ToString();
col3 = (ExcelReader.GetValue(2)).ToString();
col4 = (ExcelReader.GetValue(3)).ToString();
col5 = (ExcelReader.GetValue(4)).ToString();
}
ExcelReader.Close();
ExcelConnection.Close();
}One problem you might have is that you might want to read in a number from a field. This is ok if the number doesn't have a comma in it (eg: 5 or 100) but if the number is 1000+ and excel has formatted the number then you need to make sure you remove the comma before you can do any number calculations. To do this on Col1 for example you would need to do the following within your ExcelReader loop:
//html decode the value from the excelsheet to a comma we can replace using C#
col1 = Server.HtmlDecode((ExcelReader.GetValue(0)).ToString());
col1 = col1.Replace(",", "");
//you can double check this value is a number by trying to parse it
int col1Int;
if (int.TryParse(col1 , out col1Int) == false)
{
//handle what to do if not a number here
//for this example I set the number to 0
col1 = 0;
}That last bit about the numbers had me scratching my head for a while so I hope it helps solve any issues you are having while reading in from Excel sheets.
Member discussion