How to read .xls/.csv document using DocumentFormat.OpenXml.Packaging.SpreadsheetDocument class?
Can read .xlsx file perfectly. But having FileFormatException in this line when xls file is given.
_spreadsheetDocument = SpreadsheetDocument.Open(FileStream, false);
Open XML only process office 2007 and upper files .docx, .xlsx and .pptx.
And it is not compatible for office 97-2003 documents like .doc, .xls and .ppt. So you should try something else.
Look at this one: Reading Excel files from C#
It may help!
Update:
One of the useful answers from above link is this:
Dictionary<string, string> props = new Dictionary<string, string>();
props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
props["Data Source"] = repFile;
props["Extended Properties"] = "Excel 8.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();
DataSet ds = new DataSet();
string columns = String.Join(",", columnNames.ToArray());
using (OleDbDataAdapter da = new OleDbDataAdapter(
"SELECT " + columns + " FROM [" + worksheet + "$]", conn))
{
DataTable dt = new DataTable(tableName);
da.Fill(dt);
ds.Tables.Add(dt);
}
}
You'll need to use Jet or some other driver to read the contents of an xls file, as it is not XML. Additionally, you may want to consider using something like Linq to Excel to query the contents, it works great.
