Apply validation to whole column instead of CellArea

Aspose.Cells Product Family
1

Hi,

I have the following code for not allowing any value other than decimal in the cells.
Now I need to apply this for a whole column.

How do I apply validation for a whole column instead of just the cellArea?

Code:

    Workbook wb = new Workbook("C:\\Users\\sg\\Desktop\\dataValTest.xlsx");
	Worksheet ws = wb.getWorksheets().get("ABC209");
	
	CellArea area = new CellArea();
	area.StartRow = 3;
	area.EndRow = 10;
	area.StartColumn = 1;
	area.EndColumn = 10;
	
	
	ValidationCollection validations = ws.getValidations();
	int index = validations.add(area);
	Validation validation = validations.get(index);
	validation.setType(ValidationType.DECIMAL);
	validation.setOperator(OperatorType.BETWEEN);
	validation.setFormula1(new Double(Double.MIN_VALUE).toString());
	validation.setFormula2(new Double(Double.MAX_VALUE).toString());
	validation.setErrorMessage("Please enter a valid integer or decimal number");
			
	wb.save("C:\\Users\\sg\\Desktop\\dataValOutput.xlsx");
2

@sandipanghosh111,

You may simply change the cell area fields’ values accordingly for your desired column where you need to apply data validation. See the following sample code that will create cell area based on whole B column.
e.g
Sample code:

.....
CellArea area = new CellArea();
	area.StartRow = 0;
	area.EndRow = 1048575;
	area.StartColumn = 1;
	area.EndColumn = 1;
3

Thanks. But there is another problem.

This is giving an error (Screenshot attached) when i open this excel file:
wb.save(“C:\Users\sg\Desktop\dataValOutput.xlsx”);

Screenshot (12).png (91.8 KB)

But changing the save format from xlsx to xls makes it work fine.

My JAVA class:

import com.aspose.cells.*;

public class DataValidationOnWholeColumn {

public static void main(String[] args) throws Exception {
	
	System.out.println("Aspose 19.10");
	License license = new License();
	license.setLicense("Aspose.Cells.Java.lic");
	 
	if (License.isLicenseSet()) {
	    System.out.println("License is Set!");
	}		
	
	Workbook wb = new Workbook();
    Worksheet ws = wb.getWorksheets().add("ABC");
   
    CellArea area = new CellArea();
    area.StartRow = 3;
    area.EndRow = 10;
    area.StartColumn = 1;
    area.EndColumn = 10;       
   
    ValidationCollection validations = ws.getValidations();
    int index = validations.add(area);
    Validation validation = validations.get(index);
    validation.setType(ValidationType.DECIMAL);
    validation.setOperator(OperatorType.BETWEEN);
    validation.setFormula1(new Double(Double.MIN_VALUE).toString());
    validation.setFormula2(new Double(Double.MAX_VALUE).toString());
    validation.setErrorMessage("Please enter a valid integer or decimal number");
    
    wb.save("C:\\Users\\i23615\\Desktop\\dataValOutput.xlsx");
	System.out.println("Done");
	
}

}
4

@sandipanghosh111,

Please provide your template file “dataValTest.xlsx”, we will check it soon.

5

Use this to test:

wb.save(“C:\Users\sg\Desktop\dataValOutput.xlsx”); //This creates prob.
wb.save(“C:\Users\sg\Desktop\dataValOutput.xls”);//This works fine.

The JAVA class file:

import com.aspose.cells.*;

public class DataValidationOnWholeColumn {

public static void main(String[] args) throws Exception {

System.out.println("Aspose 19.10");
License license = new License();
license.setLicense("Aspose.Cells.Java.lic");
 
if (License.isLicenseSet()) {
    System.out.println("License is Set!");
}		

Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().add("ABC");

CellArea area = new CellArea();
area.StartRow = 3;
area.EndRow = 10;
area.StartColumn = 1;
area.EndColumn = 10;       

ValidationCollection validations = ws.getValidations();
int index = validations.add(area);
Validation validation = validations.get(index);
validation.setType(ValidationType.DECIMAL);
validation.setOperator(OperatorType.BETWEEN);
validation.setFormula1(new Double(Double.MIN_VALUE).toString());
validation.setFormula2(new Double(Double.MAX_VALUE).toString());
validation.setErrorMessage("Please enter a valid integer or decimal number");

wb.save("C:\\Users\\sg\\Desktop\\dataValOutput.xlsx");
System.out.println("Done");

}

}
6

@sandipanghosh111,

I evaluated it further and found the above line is the culprit line. Actually MS Excel does not allow to enter maximum formula/value in scientific notations so it will give error, see the screenshot (sc_shot1.png (24.2 KB)
) for your reference. The above line will insert scientific notations in the maximum field. Even if you save to XLS, it will not work although you do not find any error when opening the file into MS Excel manually. Please enter correct value in numbers (and not in scientific notations), e.g
validation.setFormula2("999999");

it will work fine.

Should you have any further queries or other issue, feel free to write us back.

PS. We will correct the sample code in the document soon.

7

Thanks that worked.

8

And in the docs the same new Double of Double MAX Value is used

9

we already have seen it and told you above.

10

We have corrected the sample code in the document.