2

I would like to add a data validation for a cell in an excel to allow ONLY numeric values.

My code does the following, 

SpreadSheetGearHelper hlpr = new SpreadSheetGearHelper(excelFilePath);
cells = workbook.Worksheets[0].Cells;
hlpr.WorkSheet(0).Cells[string.Format("{0}:{0}", colName)].Validation.Add(SpreadsheetGear.ValidationType.WholeNumber, ValidationAlertStyle.Stop,
ValidationOperator.Between, "-9999999", "9999999");
hlpr.WorkSheet(0).Cells[string.Format("{0}:{0}", colName)].NumberFormat = "@";
hlpr.WorkSheet(0).Cells[string.Format("{0}:{0}", colName)].Validation.ErrorMessage = "Please enter a number";

But when I enter valid number within the range in excel it still says "Please enter a number".

Can someone please help me out with this

user1234
  • 97
  • 1
  • 9

3 Answers3

3

You're currently using the ValidationType.WholeNumber, which will only allow whole numbers such as 1, 2, 3 and not decimals such as 1.23. If you need to allow all numeric values and not just whole numbers you need to specify ValidationType.Decimal. Example:

using SpreadsheetGear;
...
IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.ActiveWorksheet;
IRange cells = worksheet.Cells;
cells["A1"].Validation.Add(ValidationType.Decimal, ValidationAlertStyle.Stop, 
    ValidationOperator.Between, "-9999999", "9999999");
Tim Andersen
  • 3,014
  • 1
  • 15
  • 11
  • Thank you for your reply Tim, but even when I change the ValidationType to Decimal - It still gives me the same error when I try to enter the number (45) in the cell "Please enter a number" – user1234 May 02 '16 at 21:34
  • Then at this point there's not enough information to provide you with an adequate answer. I tested a rough equivalent of your original case but which used ValidationType.Decimal and input was expectedly restricted to any numeric (whole or decimal) between -9999999 and 9999999, including 45. Please revise your question to include code which more clearly demonstrates the issue. It would also help to remove any references to undefined variables and objects (like "SpreadSheetGearHelper" or "colName") which we cannot easily reproduce on our end. I'll revise my response with a more general sample. – Tim Andersen May 02 '16 at 21:47
  • So I removed the objects such a colName to avoid confusion and hardcoded everything. My code looks like below hlpr.WorkSheet(0).Cells["C:C"].Validation.Add(ValidationType.Decimal, ValidationAlertStyle.Stop, ValidationOperator.Between, "-9999999", "9999999"); Still the same issue, I am clueless why this happens – user1234 May 03 '16 at 15:41
1

Take a look at this: Apply-Data-Validation-to-Excel-Cells-in-Csharp - CodeProject

        sheet.Range["C9"].DataValidation.AllowType = CellDataType.Decimal;
        sheet.Range["C9"].DataValidation.Formula1 = "-9999999";
        sheet.Range["C9"].DataValidation.Formula2 = "9999999";
        sheet.Range["C9"].DataValidation.CompareOperator = ValidationComparisonOperator.Between;
        sheet.Range["C9"].DataValidation.InputMessage = "Type a number between -9999999-9999999 in this cell.";

I am not familar with SpreadsheetGear but the solution in this article works fine at my side.

James
  • 69
  • 2
1

Fixed it.

The Number Format of the cell was set to text, and that was why it was giving me an error every time I had entered a number (Even though the data validation was set correctly). Hence I added the below line of code to change it to "General"

hlpr.WorkSheet(0).Cells[string.Format("{0}:{0}", colName)].NumberFormat = "General";

Thank you guys for your time and responses.

user1234
  • 97
  • 1
  • 9