Getting cell using A1 notation

Getting cell using A1 notation

Posted by: guy.g on 25 October 2018, 3:05 am EST

• 

  guy.g

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 25 October 2018, 3:05 am EST

  Hi, is it possible to get a cell row and col index by its A1 notation.
  
  For example pass “B3” and get col: 1, row: 2.
  
  Wijmo flexsheet has this function (wijmo.xlsx.Workbook.tableAddress)
  
  Do this exist in SpreadJS?

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 25 October 2018, 7:36 pm EST

  Hello,

  SpreadJS does not have anything like that built in.
  
  You can give name to range with AddCustomName method and access that range with same name. For example:

  
  $(document).ready(function () {
              var spread = new Gc.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
              var sheet = spread.getActiveSheet();
  
              sheet.setColumnCount(10);
              sheet.setRowCount(10);
  
              sheet.setValue(0, 0, 1);
              sheet.setValue(0, 1, 2);
              sheet.setValue(0, 2, 3);
          
              sheet.addCustomName("customName3", "=$A$1:$A$6", 0, 0);
  
              var cname = sheet.getCustomName("customName3");
              var range = cname.getExpression().getRange();
  
              var startRowIndex = range.row + 1;
              var endRowIndex = range.row + range.rowCount;
              var startColIndex = range.column + 1;
              var endColIndex = range.col + range.columnCount;
  
              alert("Start Row: " + startRowIndex + " Start Col: " + startColIndex)
  
          });
  
  

  Thanks,
  
  Deepak Sharma

• 

  guy.g

  • Post Options:
  • Link
    Copy

  Posted 27 October 2018, 5:26 pm EST

  I am not building the xlsx file from within SpreadJS, I just load a pre-maid file and need to change some cells’ value by the A1 notation I am getting from the server.
  
  So I understand that the function you guys wrote for Wijmo is not available in SpreadJS. I guess I’ll have to write this one on my own…

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 28 October 2018, 9:05 pm EST

  Hello,

  The Excel files imported with NamedRanges work fine with SpreadJS. The given name of a range is used a custom function in SpreadJS. Please test the attached Excel file, importing it to our online demo and check how the custom formula is used in cell B1 for the named range.
  
  https://www.grapecity.com/en/demos/spread/JS/InspectorSample/

  Thanks,
  
  Deepak Sharma
  
  NamedRange.zip

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 31 October 2018, 9:03 pm EST

  Hello,

  We found one method which you can use to get the column and row index from the formula within SpreadJS’ CalcEngine. You can use formulaToRanges() method to do the same. You can use the code as follows:

  
  var spread = GC.Spread.Sheets.findControl(document.getElementById("ss"));
              var sheet = spread.getSheet(0);
              var cellRanges = GC.Spread.Sheets.CalcEngine.formulaToRanges(sheet, 'B3');        
              console.log("Row Index of cell B3 is " + cellRanges[0].ranges[0].row);
              console.log("Column Index of cell B3 is " + cellRanges[0].ranges[0].col);
             
  
  

  To know more about this method please visit:
  
  http://help.grapecity.com/spread/SpreadSheets11/webframe.html#SpreadJS~GC.Spread.Sheets.CalcEngine~formulaToRanges.html

  I hope it helps

  Thanks,
  
  Deepak Sharma