Check valueType

Check valueType

Posted by: fsegui on 1 June 2022, 12:55 am EST

• 

  fsegui

  • Post Options:
  • Link
    Copy

  Posted 1 June 2022, 12:55 am EST

  Hi,
  
  I’m using Spread.NET to load in backgroup an Excel file and using it for various reasons.
  
  I need to check if a specific cell is:

  • an error (DIV/0, NAME, REFERENCE…)
  • a numeric value
  • a logical value

  For error, I’ve tried with the IsErrFunctionInfo but it always returns false

  
  workbookSet = GrapeCity.Spreadsheet.Win.Factory.CreateWorkbookSet();
  GrapeCity.Spreadsheet.IWorkbook workbook  = workbookSet.Workbooks.Open(@"excelFile.xlsx");          
  GrapeCity.Spreadsheet.IRange test = workbook.Worksheets[1].Cells[25, 9];
  FarPoint.CalcEngine.IsErrFunctionInfo isErrFunctionInfo = new FarPoint.CalcEngine.IsErrFunctionInfo();
  object[] parameters = new object[1];
  parameters[0] = test.Value;
  var test2 = isErrFunctionInfo.Evaluate(parameters);
  
  

  Is there a way to cast a cell value to match with this enum GrapeCity.CalcEngine.CellValueType ?

  Thanks

• 

  avnish.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 1 June 2022, 5:20 pm EST

  Hi,

  There is no method available to map an IRange value to the CellValueType but you can write your own method. You can check the type of the FormattedValue of the IRange and then map the type to the corresponding CellValueType as shown in the code below.

  static CellValueType GetCellValueType(IRange cell) {
              if (cell.Count > 1 || cell.Value is null) return CellValueType.Empty;
              var valType = cell.FormattedValue.GetType();
              switch (valType.FullName) {
                  case "GrapeCity.CalcEngine.CalcError":
                      return CellValueType.Error;
                  case "System.Boolean":
                      return CellValueType.Logical;
                  case "System.String":
                      return CellValueType.Text;
                  case "System.Double":
                      return CellValueType.Number;
                  case "System.DateTime":
                      return CellValueType.DateTime;
                  case "System.TimeSpan":
                      return CellValueType.TimeSpan;
              }
              return CellValueType.Object;
          }
  
  

  If you face any issues, just let us know.

  Regards
  
  Avnish