Opening Excel (.xlsx) file in TIBCO Spotfire client fails with error 'XmlException at System.Xml: Root element is missing'

Products	Versions
Spotfire Analyst	All

Description

When opening .xlsx files in the TIBCO Spotfire installed client, it may fail to load due to worksheet size with the following error.

ImportException at Spotfire.Dxp.Forms:
Unable to open file 'xxxx'

Stack Trace:
   at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.CreateConfiguredDataSourceFromFile(String filePath)
   at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.OpenFiles(IEnumerable`1 filePaths, String errorDialogTitle)

TargetInvocationException at Spotfire.Dxp.Framework:
Exception has been thrown by the target of an invocation. (HRESULT: 80131604)

Stack Trace:
   at Spotfire.Dxp.Framework.ApplicationModel.ProgressService.ExecuteWithProgressCancelable(String title, String description, ProgressOperation operation, Boolean showCancelButton)
   at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.CreateConfiguredDataSourceFromFile(String filePath)

ImportException at Spotfire.Dxp.Data:
Failed to execute data source query. (HRESULT: 80131500)

Stack Trace:
   at Spotfire.Dxp.Data.DataSourceConnection.<GetPromptModels>d__2.MoveNext()
   at Spotfire.Dxp.Data.DataSource.Connect(IServiceProvider serviceProvider, DataSourcePromptMode promptMode, Boolean updateInternalState, DataLoadSettings loadSettings)
   at Spotfire.Dxp.Data.DataSource.Connect(IServiceProvider serviceProvider, DataSourcePromptMode promptMode)
   at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.<>c__DisplayClass2.<CreateConfiguredDataSourceFromFile>b__0()
   at Spotfire.Dxp.Forms.Application.FormsProgressService.ProgressThread.DoOperationLoop()

ImportException at Spotfire.Dxp.Data:
Root element is missing. (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__24.MoveNext()
   at Spotfire.Dxp.Data.DataSourceConnection.<GetPromptModels>d__2.MoveNext()

XmlException at System.Xml:
Root element is missing. (HRESULT: 80131940)

Stack Trace:
   at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
   at Syncfusion.XlsIO.Implementation.XmlSerialization.WorksheetDataHolder.ParseWorksheetData(WorksheetImpl sheet, Dictionary`2 dictUpdateSSTIndexes)
   at Syncfusion.XlsIO.Implementation.WorksheetImpl.ParseData(Dictionary`2 dictUpdatedSSTIndexes)
   at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseWorksheets(Dictionary`2 dictUpdatedSSTIndexes)
   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__13`1.MoveNext()
   at Spotfire.Dxp.Data.Import.Excel2DataSourceSettings.<ApplyHeuristics>d__0`1.MoveNext()
   at Spotfire.Dxp.Data.Import.Excel2FileDataSource.<GetPromptModels>d__4`1.M

Issue/Introduction

Importing Excel files in Spotfire may fail due to worksheet XML size

Resolution

This can be caused by the XML of the individual worksheet in the .xlsx file being larger than 2GB. This is different than the overall size of the .xlsx file when saved on disk, which may still be smaller than 2GB (an .xlsx file with a size of ~300MB on disk may still have an uncompressed worksheet XML size of greater than 2GB). The Syncfusion modules, which Spotfire uses to read Excel files, has a size limitation of 2GB for each Worksheet XML. If a single worksheet XML size is over 2GB, then it fails to open in Spotfire with the error seen above.

The size of each worksheet XML can be checked using the following steps: