Excel export from flexgrid generates corrupt file

Excel export from flexgrid generates corrupt file

Posted by: seesdcds on 14 September 2017, 12:03 pm EST

• 

  seesdcds

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 14 September 2017, 12:03 pm EST

  Regular (pure?) javascript, with the following libraries

  • jQuery 3.2.0
  • Bootstrap 3.3.7
  • Bootstrap Datepicker 1.6.4 (https://github.com/eternicode/bootstrap-datepicker)
  • Select2 4.0.3
  • JSZip 3.1.3
  • Wijmo 5.20163.259
    
    Excel 2016 (Office 365, current)

  Using the wijmo.grid.xlsx.FlexGridXlsxConverter.saveAsync function results in a file that Excel has to repair. On opening in excel, a dialogue pops up with “We found a problem with some content in ‘’. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.” <Yes/No buttons>.

  After clicking yes, it shows a dialogue with “Repairs to ”: Removed Records: Formula from /xl/worksheets/sheet1.xml part, after which the “repaired” data seems to be fine. It seems to happen sporadically on smaller result sets, but will always happen on larger result sets (500+ rows). Also, on larger result sets (which can take several seconds to generate and save) it seems that it is not saving asynchronously.

  I believe our 1-year period for updates is over, so what are the options to fix this?

  These are the grid options:

  resultsGrid = new wijmo.grid.FlexGrid('#resultsGrid', {
      autoSizeColumns: false,
      autoGenerateColumns: false,
      columns: [{
          binding: 'id',
          header: 'ID',
          visible: false
      }, {
          header: '',
          isReadOnly: true,
          width: 25,
          cssClass: "customer-details-link"
      }, {            
          binding: 'name',
          header: 'Name',
          isReadOnly: true,
          minWidth: 250,
          width: "*"
      }, {
          binding: 'bfoid',
          header: 'bFO ID',
          isReadOnly: true,
          width: 125
      }, {
          binding: 'earliestShipDate',
          header: 'Earliest Shipped',
          dataType: "Date",
          format: 'MM/dd/yyyy',
          align: 'right',
          isReadOnly: true,
          width: 150
      }, {
          binding: 'lastShipDate',
          header: 'Last Shipped',
          dataType: "Date",
          format: 'MM/dd/yyyy',
          align: 'right',
          isReadOnly: true,
          width: 150
      }, {
          binding: 'totalMoneySpent',
          header: 'Total Spent',
          dataType: 'Number',
          format: 'c',
          isReadOnly: true,
          width: 150
      }, {
          binding: 'mergedCustomers',
          header: 'Merges',
          isReadOnly: true,
          width: 150
      }],
      selectionMode: wijmo.grid.SelectionMode.Cell       
  });
  
  /* ... ajax to get data, then collection view is assigned to itemsSource ... */
  
  // Save the flexGrid asynchronously to an xlsx file.
  // Based on browser performance, something is not working asynchronously - it locks up the browser and the export status set via jQuery prior to this call is not displayed until it is finished 
  wijmo.grid.xlsx.FlexGridXlsxConverter.saveAsync(resultsGrid,
      {
          includeColumnHeaders: true,
          includeCellStyles: false
      }, // options
      fileName, // filename
      function (base64) { // onSaved
          // User can access the base64 string in this callback.
          //$('#export').href = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;' + 'base64,' + base64;
          $('#statusString').text("Export complete.");
          $('#exportButton').prop('disabled', false);
      },
      function (reason) { // onError
          // User can catch the failure reason in this callback.
          console.log('The reason of save failure is ' + reason);
          $('#exportButton').prop('disabled', false);
      }
  );
  

• 

  manish-gupta1

  • Post Options:
  • Link
    Copy

  Posted 14 September 2017, 12:03 pm EST

  Hi,
  
  We are sorry, we are unable to replicate issue at our end with provided information.
  
  Please refer to the attached sample and excel file those are used to replicate issue at our end.

  If issue persists, please modify the attached sample that implements the same.

  Thanks,
  
  Manish Kumar Gupta

  2017/05/FlexGrid_saveAsync.zip

• 

  seesdcds

  • Post Options:
  • Link
    Copy

  Posted 14 September 2017, 12:03 pm EST

  After some more in depth debugging, it looks like that some of our data comes across with an equal sign at the beginning of the field and another inside it, which doesn’t translate to a formula in excel - which explains the error about removing the formula.

  After googling, it looks like you can allow this by preceding the equal sign with a single quote (‘), but when exporting this way with Wijmo (such as appending =a= with a single quote, like "’=a=", the cell contents read as '=a= rather than =a=.

  The attached html file will produce the error message, with country using “=a=” in the data generation.

  2017/05/FlexGrid_saveAsync.html

• 

  manish-gupta1

  • Post Options:
  • Link
    Copy

  Posted 14 September 2017, 12:03 pm EST

  Hi,
  
  In the further investigation, we found that if correct formula is set in FlexGrid cell. On exporting, cells display correct values.
  
  Just for information, if you would like to display value in view also, please use FlexSheet control instead of FlexGrid.

  For your reference, please see the attached sample that implements the same.

  Thanks,
  
  Manish Kumar Gupta
  
  2017/05/FlexGrid_saveAsync-updated.zip

• 

  lorein0688

  • Post Options:
  • Link
    Copy

  Posted 20 November 2017, 1:51 am EST

  Well, in this case try to repair the Excel file. As it seems your Excel file gets corrupted and you need to repair and recover the corrupt Excel files. You can make use of the third party MS Excel Repair Tool, this is the best tool to repair and recover the corrupt Excel files and fix all sort of corruption and errors as well.

  Source: http://www.repairmsexcel.com