Excel: Data, What-If Analysis, Data Table (Two-Way)

Excel: Data, What-If Analysis, Data Table (Two-Way)

Posted by: vince on 24 January 2021, 8:24 am EST

• 

  vince

  • Post Options:
  • Link
    Copy

  Posted 24 January 2021, 8:24 am EST

  I see in this forum that, as of 2017, the above Excel Two-Way Data Table functionality was not yet available in SpreadJS.

  I wonder if it’s there now. I just registered for the SpreadJS trial, and will start poking around.

  Also, I wonder if there is consulting help available for a project that will be making widespread use of Two-Day Data Tables in the financial and estate planning worlds.

  I’m a 38-year user of spreadsheets (starting with Lotus 1-2-3 Version 1A), so lots of experience with spreadsheet functions of all kinds.

  Thanks!

  Vince Lackner
  
  Pittsburgh, PA

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 25 January 2021, 10:30 am EST

  Hi Vince,

  This feature is currently not supported by SpreadJS. We have made an enahcement request. We will update you once we have any information regarding this issue. The internal ID for this issue will be SJS- 7402.

  Regards
  
  Avinash

• 

  vince

  • Post Options:
  • Link
    Copy

  Posted 25 January 2021, 4:08 pm EST

  Thank you, Avinash.

  I also saw the GrapeCity reply this morning on StackOverflow.

  It would be great if you could add the Data Table feature, which has been pending with GrapeCity for 3-4 years (per similar request posted on your forum in 2017).

  I was delighted to rediscover this spreadsheet feature, which I first discovered in Lotus 1-2-3 almost 38 year ago, Given the incredible power of these Data Tables, and given that you appear to be the premier developers of spreadsheet functionality for Javascript, I was hoping that it would be part of your suite.

  We are working with a Javascript developer who might be able to build this for us. If he can do it, any interest in some arrangement whereby our work would help you include Data Tables sooner rather than later?

  Best,
  
  Vince Lackner

• 

  mackenzie.albitz

  

  • Post Options:
  • Link
    Copy

  Posted 25 January 2021, 4:53 pm EST

  Hello Vince,

  I have an update, our team has implemented a dataTable function that is used to create the two-way-table: function dataTable(sheet, columnRange, rowRange, colInputCell, rowInputCell, formulaCell)

  Here is the entire dataTable function shared by our team:

  function dataTable(sheet, columnRange, rowRange, colInputCell, rowInputCell, formulaCell) {
              var colRg = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, columnRange);
              var rowRg = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, rowRange);
              var colCell = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, colInputCell);
              var rowCell = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, rowInputCell);
              var fCell = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, formulaCell);
  
              for (var i=colRg.col; i<colRg.col + colRg.colCount; i++) {
                  sheet.setValue(colCell.row, colCell.col, sheet.getValue(colRg.row, i));
                  for (j=rowRg.row; j<rowRg.row + rowRg.rowCount; j++) {
                      sheet.setValue(rowCell.row, rowCell.col, sheet.getValue(j, rowRg.col));
  
                      var v = sheet.getValue(fCell.row, fCell.col);
                      sheet.setValue(j, i, v);
                  }
              }
          }
  
  

  Here is an example of how you would create the data table using this function:

          function createDataTable() {
              var ss = GC.Spread.Sheets.findControl('ss');
              var sheet = ss.getActiveSheet();
              ss.suspendPaint();
              dataTable(sheet, 'G1:K1', 'F2:F6', 'B1', 'B2', 'B4');
              ss.resumePaint();
          }
  
  

  We attached a sample showing this as well using SpreadJS, please let us know if you have any questions!

  Best,
  
  Mackenzie

  Two-Way Data Table.zip