How to define formula in Designer for all table rows

How to define formula in Designer for all table rows

Posted by: stephen.young on 1 March 2018, 2:44 am EST

• 

  stephen.young

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 1 March 2018, 2:44 am EST

  Hi,

  I am trying to use Designer to create an ssjson file that I then import using fromJSON before binding to data using setDataSource. I have a mixture of data-bound cells and formulas in the columns of my data table. When I call setDataSource the sheet is populated with all rows and the table grows but the formulas are only applied to the first row of the table. I can set the formula in code using sheet.tables.findByName(“Table1”).setColumnDataFormula but I have lots of columns with complex formulas and would rather maintain it in the template. I see a dataAreaFormula property in the ssjson schema but this appears to be ignored?

  Thanks,

  Steve

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 7 March 2018, 12:39 am EST

  Hello Steve,

  It looks you are setting the data source for the Table only. In this case the formula will work for only the unaffected existing rows/cells. In order to get the formula working for Table you would need to either use the setColumnDataFormula or setColumnFormula methods. You can also reference table data in formulas outside of the table area. For example, activeSheet.getCell(7,1).formula(“SUM(Table1[SubTotal])”);

  Thanks,
  
  Deepak Sharma

• 

  stephen.young

  • Post Options:
  • Link
    Copy

  Posted 11 March 2018, 8:58 pm EST

  Thanks for your reply Deepak. Is there a reason why dataAreaFormula in the ssjson schema appears to do nothing? This would seem like the right way to do it but it is ignored.

  Thanks,

  Steve

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 13 March 2018, 2:13 am EST

  Hi Stephen,

  It looks like an internal property. I have asked the development team for more information on this. I will let you know as soon as I get an update on this.

  Thanks,
  
  Deepak Sharma

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 22 March 2018, 2:22 am EST

  Hello,

  This has been submitted as an feature request for future version of SpreadJS. Currently there is no ETA for this.

  Thanks,
  
  Deepak Sharma

• 

  stephen.young

  • Post Options:
  • Link
    Copy

  Posted 26 March 2018, 6:58 pm EST

  Thanks for the update. That’s good to know.

  Steve

• 

  stephen.young

  • Post Options:
  • Link
    Copy

  Posted 4 December 2018, 9:34 pm EST

  Hi,

  I have tried dataAreaFormula in the ssjson is more recent versions and it almost works. It is correctly using the formula in the table columns, the only problem is that it skips the first data row of the table. So the first row of the table is blank/has no formulas but the rest of the bound rows work correctly.

  Any update on the status of this development?

  Thanks,

  Steve

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 5 December 2018, 11:00 pm EST

  Hi Steve,

  I have asked for an update on this enhance request from the concerned team . I will let you know as soon as I hear anything on this.
  
  The tracking id for this is 238049.

  Thanks,
  
  Deepak Sharma

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 6 December 2018, 4:09 pm EST

  Hi Steve,

  As per development team, this feature has not been added to SpreadJS till now and there are no plans to implement it in near future. However the status of this request is still active.

  Sorry for the inconvenience.

  Thanks,
  
  Deepak Sharma