CODEWITHSUNDEEP
c#.netexcelvalidationspreadsheetgear
user1234
user1234

Reputation: 97

How to add a Data validation for a cell in excel using Spreadsheetgear - C# .NET

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

Upvotes: 2

Views: 4816

Answers (3)

user1234
user1234

Reputation: 97

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.

Upvotes: 1

James
James

Reputation: 69

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.

Upvotes: 1

Tim Andersen
Tim Andersen

Reputation: 3184

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");

Upvotes: 3

Related Questions