A Rest API Solution for Data Validation in Excel

While working with Microsoft Excel, cell validation is one of the important features that you can use for data validation to restrict the type of data or the values that users enter into a cell, to make sure all data entries are accurate and consistent.

I this article I will explain how to do Data Validation in Excel using Aspose.Cells Cloud. We will add data validation, update data validation rule and remove all data validations from a worksheet. I am using cURL in the sample code, but you can use the SDK of your favorite programming language in your specific project. Aspose.Cells Cloud is a REST API that can be used with any language: .NET, Java, PHP, Ruby, Python, Node.js and many more. You can use it with any language or platform that supports REST.

Here we go, first thing first signup with aspose.cloud to get App SID and App key and get Json Web Token for api call authentication.

Add a data validation to a cell

Add a validation at index(A1) in a worksheet Sheet1.

//Add validation by Index in the worksheet
curl -X PUT "https://api.aspose.cloud/v3.0/cells/aspose_Test.xlsx/worksheets/Sheet1/validations?range=A1:A1&folder=Temp" 
-H "accept: application/json" 
-H "authorization: Bearer [Access_Token]"

Add/Update data validation rule

Add a custom data validation rule, YES or NO. Aspose.Cells Cloud API supports all types of data validation rules.

//Add custom validation rule by Index in the worksheet
curl -X POST "https://api.aspose.cloud/v3.0/cells/aspose_Test.xlsx/worksheets/Sheet1/validations/0?folder=Temp" 
-H "accept: application/json" 
-H "authorization: Bearer [Access_Token]" 
-H "Content-Type: application/json" -d "{ \"AreaList\": [ { \"EndColumn\": 0, \"EndRow\": 0, \"StartColumn\": 0, \"StartRow\": 0 } ], \"Formula1\": \"=(OR(A1=\\\"Yes\\\",A1=\\\"No\\\"))\", \"Type\": \"Custom\", \"IgnoreBlank\": true}"

Delete all data validations

Delete all data validations from the worksheet.

//Delete all validation from the worksheet
curl -X DELETE "https://api.aspose.cloud/v3.0/cells/aspose_Test.xlsx/worksheets/Sheet1/validations?folder=Temp"
-H "accept: application/json" 
-H "authorization: Bearer [Access_Token]"

Happy Coding! Read more.