Issue when exporting to excel if there is pivot table

Issue when exporting to excel if there is pivot table

Posted by: eric.marchand on 5 March 2024, 2:16 am EST

• 

  eric.marchand

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 5 March 2024, 2:16 am EST

  Hi

  I use to export the Excel IO lib “gc.spread.excelio” (version 16.x)

  var instance = new GC.Spread.Excel.IO();
  var value = instance.save(json, successCallBack, errorCallBack, options);

  when opening the file using excel I have some errors on pivot table cache. (I join the sjs file)

  • Enregistrements réparés: Rapport de tableau croisé dynamique dans la partie /xl/pivotCache/pivotCacheDefinition1.xml (Cache de tableau croisé dynamique)

  I have no issue using the designer, that seems to use other things in “gc.spread.sheets.io” [workbook.save(filetype=0=excel ], with some function that seems to do the job like _savePivotCache

  Is it recommended to use sheets.io features directly, instead of excel.io?

  ---

  pivot is created with this code

     let range = "=Datos!$A:$D";
     let pivotTable = activeSheet.pivotTables.add("PivotTable1", range, 0, 0, GC.Spread.Pivot.PivotTableLayoutType.tabular, GC.Spread.Pivot.PivotTableThemes.light3);
    // pivotTable.suspendLayout();
     pivotTable.add("Abreviatura", "Abreviatura", GC.Spread.Pivot.PivotTableFieldType.rowField);
     pivotTable.add("Nombre", "Nombre", GC.Spread.Pivot.PivotTableFieldType.rowField);
     pivotTable.add("Es Web", "Es Web", GC.Spread.Pivot.PivotTableFieldType.columnField);
     pivotTable.add("Orden", "Suma de Orden", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
    // pivotTable.resumeLayout();
    // activeSheet.resumePaint();

  thank in advance

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 5 March 2024, 11:57 pm EST

  Hi,

  I attempted to replicate the behavior on my end but was unsuccessful. Please refer to the attached sample.

  In the sample, after exporting using both the “excel.io” module and export methods of SpreadJS using “sheets.io” module, the Excel file opens without any errors, and the pivot table functions as expected.

  Sample: Sample.zip

  However, it is recommended to use import/export method of spreadJS using “sheets.io” module instead of “excel.io” because excelIO methods for importing and exporting are deprecated after version 16. Using excelIO for importing/exporting might cause unexpected behavior.

  If you face issue with export method of spreadJS using “sheets.io” module then To better assist you, could you share the following information:

  1. could you share a sample along with the steps to replicate the behavior you have observed, or modify the existing sample to replicate the behavior? This will enable me to investigate the problem more thoroughly.

  2. It would be helpful if you could provide a GIF or video illustrating the issue.

  3. Could you share the version of spreadJS and excel, you are using?

  Regards,

  Priyam

• 

  eric.marchand

  • Post Options:
  • Link
    Copy

  Posted 6 March 2024, 7:57 pm EST - Updated 6 March 2024, 7:59 pm EST

  Hi, thank you for the sample. I do not reproduce the issue with it
  
  So I inject my data and I have the issue (maybe a bad pivot and some computing with numbers or …)

  I use office 2021 on windows, spread js 16.2.6
  
  I record as mp4 the download and open with the excel errors (in french)

  thanks in advance

  by the way I will think about migrate also to sheet.io (when passing to 1.7)

  index.zip
  
  2024-03-07-09-48-57.zip

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 7 March 2024, 7:53 pm EST

  Hi,

  I was able to replicate the behavior in my end with excelIO and after investigation. I found that exporting excel file with sheet.io module seems work perfectly. Refer attached video.

  Video: exportWithSheetIO.zip

  So I recommended to use sheet.io export/import method because of many performance and optimization advantages over excelIO save/open method.

  Regards,

  Priyam

• 

  eric.marchand

  • Post Options:
  • Link
    Copy

  Posted 7 March 2024, 8:47 pm EST

  Hi, thank you for your time
  
  I will use sheet.io
  
  Regards