Hello,
thank you for your input. Unfortunately I can't use LoadFromXml(filename) overload because I need to work with streams - but LoadFromXml(stream) works - under certain curcumstances. So in principle, my problem is solved.
Just to give you a hint for improving your solution: as I stated in my initial post, I am using two-step algorithm for opening ANY given Excel file:
- Code: Select all
// 1. try "auto-detect"
try
{
workbook.LoadFromStream(inputStream);
return;
}
catch (Exception) { }
// 2. fallback: try all available Versions
foreach(ExcelVersion excelVersion in Enum.GetValues(typeof(ExcelVersion)))
{
try
{
workbook.LoadFromStream(inputStream, excelVersion);
return;
}
catch (Exception) { }
}
throw new Exception("Excel format is not supported");
As XML-based spreadsheets need to be opened using LoadFromXml(stream) obviously, I tried to add a third step at the end of my algorithm:
- Code: Select all
// 1. try "auto-detect"
try
{
workbook.LoadFromStream(inputStream);
return;
}
catch (Exception) { }
// 2. fallback: try all available Versions
foreach (ExcelVersion excelVersion in Enum.GetValues(typeof(ExcelVersion)))
{
try
{
workbook.LoadFromStream(inputStream, excelVersion);
return;
}
catch (Exception) { }
}
// 3. fallback: try load as XML
try
{
workbook.LoadFromXml(inputStream);
return;
}
catch (Exception) { }
throw new Exception("Excel format is not supported");
For some reason this doesn't work, LoadFromXml method fails with a NullReferenceException somewhere inside. It turns out that reordering the fallback steps solves my problem - trying LoadFromXml upfront enables me to open XML files PLUS it does not break opening all other Excel files. I can't see where the problem is, but I guess there's an issue with the position of the stream or the state of the workbook object after a failed attempt to call workbook.LoadFromStream(stream).
What works for me now is the following:
- Code: Select all
// 1. try load as XML
try
{
workbook.LoadFromXml(inputStream);
return;
}
catch (Exception) { }
// 2. fallback: try "auto-detect"
try
{
workbook.LoadFromStream(inputStream);
return;
}
catch (Exception) { }
// 3. fallback: try all available Versions
foreach (ExcelVersion excelVersion in Enum.GetValues(typeof(ExcelVersion)))
{
try
{
workbook.LoadFromStream(inputStream, excelVersion);
return;
}
catch (Exception) { }
}
throw new Exception("Excel format is not supported");
For my current problem no further action on your side is required, as I have the workaround in place with the "correct" order of opening attempts. However, in a perfect world all these steps should not be necessary and workbook.LoadFromStream(stream) would simply try all possible file formats INCLUDING XML based spreadsheets.
KR,
Gerhard
