openpyxl's theme1.xml uses xml-escaped characters for certain languages, confusing buggy Excel loaders (like the one in Spotfire)
The error in Spotfire and other programs that rely on Syncfusion.XlsIO, when loading an xls/xlsx created with pandas+openpyxl, is:
Error message: Unsupported Excel workbook version. Make sure that the file is saved in Excel 97 or newer
AggregateException at :
One or more errors occurred. (HRESULT: 80131500)
Stack Trace:
Aggregated exception:
ImportException at Spotfire.Dxp.Data:
Failed to execute data source query for data source ".....". (HRESULT: 80131500)
Stack Trace:
at Spotfire.Dxp.Data.DataSourceConnection.<GetPromptModels>d__37.MoveNext()
at Spotfire.Dxp.Data.Access.AddData.DataSourceAddDataFlow.ConfigureDataSourcesStep.<GetPromptRequests>d__7.MoveNext()
at Spotfire.Dxp.Data.Access.AddData.DataSourceAddDataFlow.ConfigureDataSourcesStep.<EnumerateModels>d__6.MoveNext()
ImportException at Spotfire.Dxp.Data:
Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection. (HRESULT: 80131500)
Stack Trace:
at Spotfire.Dxp.Data.Import.OutOfProcessDataSourceConnection.TryExecute(Action f)
at Spotfire.Dxp.Data.Import.OutOfProcessDataSourceConnection.TryExecute[T](Func`1 f)
at Spotfire.Dxp.Data.Import.OutOfProcessDataSourceConnection.<GetPromptModelsCore>d__13.MoveNext()
at Spotfire.Dxp.Data.DataSourceConnection.<GetPromptModels>d__37.MoveNext()
ImportException at Spotfire.Dxp.Data:
Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection. (HRESULT: 80131500)
Stack Trace:
at Spotfire.Dxp.Data.Import.Excel2DataSourceSettings.<OpenWorkbook>d__63`1.MoveNext()
at Spotfire.Dxp.Data.Import.Excel2DataSourceSettings.<ApplyHeuristics>d__60`1.MoveNext()
at Spotfire.Dxp.Data.Import.Excel2FileDataSource.<GetPromptModels>d__18`1.MoveNext()
at Spotfire.Dxp.Data.Import.DataSourceOutOfProcessConnection.CallbackConnection.<GetPromptModelsCore>d__4.MoveNext()
at Spotfire.Dxp.Data.Import.OutOfProcessDataSourceConnection.SubprocessImpl..ctor(String[] args)
ArgumentException at mscorlib:
Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection. (HRESULT: 80070057)
Stack Trace:
at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
at System.Collections.Generic.List`1.Reverse(Int32 index, Int32 count)
at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseThemeColors(XmlReader reader, Dictionary`2& dicThemeColors)
at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseThemeElements(XmlReader reader)
at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseThemes(XmlReader reader)
at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.ParseWorkbook(List`1& themeColors)
at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.ParseDocument(List`1& themeColors)
at Syncfusion.XlsIO.Implementation.ApplicationImpl.CreateWorkbook(Object parent, Stream stream, ExcelVersion version, ExcelParseOptions options)
at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(Stream stream, ExcelVersion version, ExcelParseOptions options)
at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(Stream stream, ExcelOpenType openType)
at Spotfire.Dxp.Data.Import.Excel2DataSourceSettings.<OpenWorkbook>d__63`1.MoveNext()
I tracked this down to the ASCII format theme1.xml generated in the OOXML, specifically lines like:
<a:font script="Jpan" typeface="MS Pゴシック"/>
A valid alternative is to use the UTF-encoded version, the way Excel does:
<a:font script="Jpan" typeface="MS Pゴシック"/>
I have a fix and will double check that the resulting documents load in Spotfire tomorrow. At that point I'll submit a PR.
Edited by ebrevdo