Skip to main content

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

Rich Talbot
  • Created
Date Posted:
Product: TIBCO Spotfire®

Problem:

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


Solution:

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

  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:
  1. Rename the .xlsx file to .zip
  2. Extract
  3. Open the xl > Worksheets folder
  4. Note the size of the sheet's xml contents (for example "sheet_name.xml")
As a workaround, there are a couple options:
  • Divide the data into multiple sheets so that each sheet XML size is less than 2GB and then join the data in Spotfire (See references How to Insert Rows / How to Add Rows)
or:
  • Convert the .xlsx to a comma separated .csv file and import that .csv to Spotfire
External: Excel specifications and limits Doc: How to Insert Rows (version 7.14 and lower) Doc: How to Add Rows (version 10.0 and higher)

Related articles

Comments

0 comments

Article is closed for comments.