Check valueType

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

  • 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
  • Replied 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
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels