Sunday, May 29, 2011

C# Read Excel file (.xls) using JET OLEDB Provider

public static void extractExcelContent()
{
string path = "D:\shared\file.xls";
if (File.Exists(path))
{
//create the "database" connection string (FOR EXCEL)
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=\"" + path + "\";" +
"Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
//create the database query
string query = "SELECT * FROM [FirstExcelSheet$]";

//create a DataTable to hold the query results
DataTable dTable = new DataTable();

//create an OleDbDataAdapter to execute the query
using (OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString))
{
try
{
//fill the DataTable
dAdapter.Fill(dTable);
if (dTable.Rows.Count > 0)
{
foreach (DataRow row in dTable.Rows)
{
string fiscalDate = row["FiscalDate"].ToString();
string currency = row["Currency"].ToString();
double rate;
double.TryParse(row["Rate"].ToString(), out rate);
try
{
//do something
}
catch (Exception ex)
{
Console.Writeline("Error while updateExchangeRate: " + ex.ToString());
}

}
}
}
catch (InvalidOperationException e)
{
Console.Writeline(e.ToString());
}
dAdapter.Dispose();
}

//Optional: rename file
DateTime dtNow = DateTime.Now.AddDays(-1);
File.Move(path, path + dtNow.ToString("yyyy-MM-dd"));
}

}

No comments:

Related Posts Plugin for WordPress, Blogger...