RowCount exceptions

RowCount exceptions

Posted by: matthew on 29 June 2020, 9:28 am EST

• 

  matthew

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 29 June 2020, 9:28 am EST

  I’m on the trial version of the Spread.Net and I’m getting frustrated trying to get around the RowCount exceptions. I need to get a range from 2 cells in a string format, such as “C1” and “C501”. Normally, you would just create a range from those 2 cells and you’re all set. Unless that range exceeds the number of rows in the Worksheet. Then you only get a null range. Ok, so now I need to increase my rows by setting the RowCount higher to avoid these exceptions. I have a string cell address “C501”. Is there some utility where I can give it an address and it can tell me the row number? Or better yet, can I set a flag that tells the Worksheet to auto-grow when my row exceeds the RowCount?

  To me it makes no sense to return a null range. The range is valid even if the spreadsheet does not have values yet.

• 

  Ruchir.Agarwal

  

  • Post Options:
  • Link
    Copy

  Posted 29 June 2020, 11:34 pm EST

  Hello,

  It is by design behavior, that is, if you try to access a cell not present in the spread an exception will be thrown.
  
  Also, Spread does not offer auto growing the sheet when the row exceeds the RowCount.
  
  However, since each cell is identified with a Row and Column number you can retrieve the numerical value from it to get the row number and compare it before accessing the cell range. If the row index in cell range exceeds then you can set the RowCount property as needed.

  Regards,
  
  Ruchir

• 

  matthew

  • Post Options:
  • Link
    Copy

  Posted 29 June 2020, 11:48 pm EST

  Ok, so can you give an example for how you would take a string cell address and determine the row number, let’s use “C1002”, using your API?
  
  I’ve tried several ways within Spread.net and all I get back are nulls. The APIs I have tried ```
  
  Worksheet.Cells[“C1002”], Worksheet.Cells.Range(“C1002”)

• 

  Ruchir.Agarwal

  

  • Post Options:
  • Link
    Copy

  Posted 30 June 2020, 6:50 pm EST

  Hi,

  Please refer following code that has been prepared to demonstrate what I was suggesting. You can modify the code as per your requirements (like do the same for column etc)

  string cellAddress = "$C$501"; //check if cell address is not valid if (ReturnRowIndex(cellAddress) >=fpSpread1.ActiveSheet.RowCount) {      //grow spreadsheet      fpSpread1.ActiveSheet.RowCount = ReturnRowIndex("$C$501")+1; } //now access the cell range as needed. fpSpread1.ActiveSheet.Cells[501, 3].BackColor = Color.Red;

  Where the ReturnRowIndex function is defined something like:```
  
  public int ReturnRowIndex(string cellAddress)
  
  {
  
  int rowIndex = -1;

   string[] indexes = cellAddress.Split('$');
   try
   { rowIndex = Convert.ToInt32(indexes[2]); }
   catch (Exception ex)
   { return -1; }
  
   return rowIndex;
  

  }

  
  Thanks,

• 

  matthew

  • Post Options:
  • Link
    Copy

  Posted 1 July 2020, 12:18 am EST

  I had already implemented something like your suggestion, but I was assuming that there was an API that I had missed. I’ve been evaluating several spreadsheet controls over the past several months and this is the first one that does not seem to supply 2-way conversions (string address<->index).

  Thanks for your help.

• 

  Ruchir.Agarwal

  

  • Post Options:
  • Link
    Copy

  Posted 1 July 2020, 5:18 pm EST

  Hi Mathew,

  I have forwarded your request for the utility to the developers [SPNET-9343] and it might be added in the control in some future release, if they find it feasible.
  
  I will keep you updated with any solution/conclusion related to the request.

  Regards,
  
  Ruchir

• 

  Ruchir.Agarwal

  

  • Post Options:
  • Link
    Copy

  Posted 1 July 2020, 8:51 pm EST

  Hello Mathew,

  Can you please try to use the following code and see, if meets your requirement;```
  
  string cellAddress = “$C$501”;
  
  GrapeCity.CalcEngine.RangeReference range = default;
  
  if (GrapeCity.CalcEngine.FormulaEngine.TryParseA1Reference(cellAddress, 0, 0, ref range))
  
  {
  
  string address = GrapeCity.CalcEngine.FormulaEngine.UnparseA1Reference(range);
  
  //In order to get cell address only
  
  string address2 = GrapeCity.CalcEngine.FormulaEngine.UnparseA1Reference(range.TopLeft);
  
  }

  Ruchir