Introduction
Reading from Excel is easy, but I just spent four hours banging my head into "why doesn't it work" only to find I wasn't referring to the file: I'd forgotten the ".xlsx" on the end, and all you get for that is an error report about installable ISAMs...not helpful.
Using the code
Easy: Call the method(s)!
string path = @"C:\Users\griff\Documents\My Spreadsheets\My SpreadSheet.xlsx";
Then:
myDataGridView.DataSource = LoadExcel(path);
Or:
myDataGridView.DataSource = GetExcelSheetNames(path).Select(s => new { SheetName = s }).ToArray();
I'll just explain the second one...
If you return a List of strings, and set that as the DataSource of a control, you will not see the actual strings: you will see how long the strings are instead! This is because the String class has only one property: Length, so the control shows just that. If String had a Text property, that would also be shown, but...
So what we do is use a Linq method to create a new anonymous class that has just one property: SheetName which is the string. This "fools" the control into showing what we wanted in the first place! Unfortunately, the Linq method returns an IEnumerable, which doesn't work directly as a DataSource either, so we need the ToArray (or ToList would work as well) to get round that as well...
The code
public static System.Data.DataTable LoadExcel(string path, string columns = "*", string sheet = "Sheet1$", bool verify=true)
{
if (verify)
{
if (!File.Exists(path)) throw new IOException("The input file does not exist: " + path);
if (!GetExcelSheetNames(path).Contains(sheet)) throw new ArgumentException("The requested sheet does not exist: " + sheet);
}
if (string.IsNullOrWhiteSpace(columns)) columns = "*";
System.Data.DataTable dt = new System.Data.DataTable();
using (OleDbConnection con = new OleDbConnection(GetExcelConnectionString(path)))
{
con.Open();
using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT {0} FROM [{1}]", columns, sheet), con))
{
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
da.Fill(dt);
}
}
}
return dt;
}
public static List<string> GetExcelSheetNames(string path)
{
List<string> sheetNames = new List<string>();
using (OleDbConnection con = new OleDbConnection(GetExcelConnectionString(path)))
{
con.Open();
using (DataTable sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
{
foreach (DataRow sheet in sheets.Rows)
{
if (sheet["TABLE_NAME"].ToString().Contains("$"))
{
sheetNames.Add(sheet["TABLE_NAME"].ToString());
}
}
}
}
return sheetNames;
}
private static string GetExcelConnectionString(string path)
{
return string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0", path);
}
History
2014-02-08 Original Version
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?