Code snippet for querying excel files as DataTable
public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
string HDR = hasHeaders ? "Yes" : "No";
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
FileName +
";Extended Properties='Excel 12.0;HDR=" +
HDR + ";IMEX=1'";
DataSet output = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow schemaRow in schemaTable.Rows)
{
string sheet = Convert.ToString(schemaRow["TABLE_NAME"]);
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
cmd.CommandType = CommandType.Text;
DataTable outputTable = new DataTable(sheet);
outputTable.TableName = sheet;
output.Tables.Add(outputTable);
new OleDbDataAdapter(cmd).Fill(outputTable);
}
}
return output;
}