Limiting cells to only numeric values in Apache POI

Question

We are using Apache POI library to create the excel sheets.

How can we restrict the cells to accept only numeric values ? Is there any class that restricts to only numbers in Apache POI library ?

Thanks
Rama Krishna

check this link:http://stackoverflow.com/questions/15248284/using-poi-how-to-set-the-cell-type-as-number — soorapadman, Jun 06 '16 at 15:01
The example what you are referring does restriction while adding the data. But I want the user not to enter the characters in the created excel sheet. How do we restrict this at code level ? — Rama Krishna. G, Jun 06 '16 at 15:14

score 8 · Accepted Answer · edited Nov 09 '17 at 11:34

Perhaps, I should have asked my question like how to add Data Validation in Excel using Apache POI.

But here is the code to do so.

I guess this can help someone. It worked for me. You need to be careful with Cell Range.

XSSFWorkbook wb = new XSSFWorkbook(); 
XSSFSheet sheet = wb.createSheet("SomeSheet");

Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
XSSFDataValidationHelper dvHelper = new SSFDataValidationHelper(sheet);

XSSFDataValidationConstraint dvConstraint = 
    (XSSFDataValidationConstraint)
    dvHelper.createNumericConstraint(
        XSSFDataValidationConstraint.ValidationType.DECIMAL,
        XSSFDataValidationConstraint.OperatorType.BETWEEN,
        String.valueOf(Float.MIN_VALUE),
        String.valueOf(Float.MAX_VALUE)
    );

// Cell range is important here. 
CellRangeAddressList addressList = new CellRangeAddressList(
        0, 2, 1, 3);
// 0 - starting row, 2 - ending row
// 1 - starting col, 3 - ending col

XSSFDataValidation validation =(XSSFDataValidation)dvHelper.createValidation(
        dvConstraint, addressList);
validation.setSuppressDropDownArrow(false);
validation.setShowErrorBox(true);

CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
cell.setCellStyle(style);

sheet.addValidationData(validation);

cell.setCellValue(20);

Limiting cells to only numeric values in Apache POI

1 Answers1

Linked