Export :- formula issue while referencing to other tab

Export :- formula issue while referencing to other tab

Posted by: Shalini89garg on 5 March 2018, 6:33 am EST

• 

  Shalini89garg

  • Post Options:
  • Link
    Copy

  Posted 5 March 2018, 6:33 am EST

  Hi,

  I am having both Row Header and columnheader in my application.
  
  When I am referencing formula from one sheet to another sheet its not working.
  
  Suppose i have applied formula as SUM(‘Test’!BH88:BI88), So it sould become SUM(‘Test’!BH89:BI89)while export because of headers.
  
  I am aware that this functionality is working in inspector sample.
  
  Can anyone please tell me that which attribute govern the funstionality of changing formula.

  I am using below code

  btnExport() {
  
  let excelIo = new GC.Spread.Excel.IO();
  
  var json = this.spread.toJSON({
  
  includeBindingSource: true,
  
  columnHeadersAsFrozenRows: true,
  
  ignoreFormula: false,
  
  ignoreStyle: false
  
  });
  
  let sheetsNames = Object.getOwnPropertyNames(json.sheets);
  
  sheetsNames.forEach(sheetname => {
  
  const option = {
  
  allowFilter: true,
  
  allowSort: true,
  
  allowResizeRows: true,
  
  allowResizeColumns: true,
  
  allowEditObjects: true
  
  };
  
  json.sheets[sheetname].protectionOptions = option;
  
  json.sheets[sheetname].isProtected = false;

      });
      json.highlightInvalidData = false;
      let currentSheet = this.spread.getActiveSheet()
      let currentSheetname = currentSheet.name();
      currentSheetname = currentSheetname + '.xlsx';
      excelIo.save(json, function (blob: any) {
          console.log(blob);
          FileSaver.saveAs(blob, currentSheetname);
      }, function (e: any) {
          //process error
          console.log(e);
      });
  }
  

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 6 March 2018, 11:13 am EST

  Hello,

  I am not able to replicate this issue at my end. I exported the Spread.Sheets with headers to excel and found that the formula adjusts automatically.
  
  In the attached sample you will find that the formula shown in Spread.Sheet for Cell(8, 2) is ‘Sum(C1:C8)’ however when exported and opened in Excel the formula it shows is (C2:C9).
  
  Please refer to the attachment.

  Thanks,
  
  Deepak Sharma

  ExportWithHeaders.zip