I was implementing a solution for importing xls files given by following this posts and this post. The following solution indeed works for sum excel files:
public XLSReader(string path, string worksheet = "Sheet1")
{
Dictionary<string, string> props = new Dictionary<string, string>();
props["Provider"] = "Microsoft.ACE.OLEDB.12.0";
props["Data Source"] = path;
props["Extended Properties"] = "Excel 12.0";
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair<string, string> prop in props)
{
sb.Append(prop.Key);
sb.Append('=');
sb.Append(prop.Value);
sb.Append(';');
}
string properties = sb.ToString();
using (OleDbConnection conn = new OleDbConnection(properties))
{
conn.Open();
ds = new DataSet();
//string columns = String.Join(",", columnNames.ToArray());
using (OleDbDataAdapter da = new OleDbDataAdapter(
"SELECT *" + /*columns +*/ " FROM [" + worksheet + "$]", conn))
{
da.Fill(ds);
dt = new DataTable[ds.Tables.Count];
ds.Tables.CopyTo(dt, 0);
}
Console.WriteLine(ds.Tables.Count + " Tables");
}
}
It is a class's constructor, and sum of the variables used here are defined on the class and only used here so sorry for any missing variable declarations.
The problem is, for some excel files it doesn't work (a link to such example is added at the end of the post), throwing me an 'External table is not in the expected format' eception. If I understand correctly, it depends on the excel's version and is influenced by the props's string and properties given to the OleDbConnetion.
- Do I understand correctly or is there another problem in the code?
- How do I alter this to generically load any excel file?
