Facing an issue while opening .xlsx using MS-Excel in which APACHE POI XSSFDataValidationHelper is used for numeric and date validation

I used Apache POI 3.17, XSSFDataValidationHelper in java to add validation for data like dates and numeric values in .xlsx file, but whenever I am opening the file using MS-Excel it shows

“We found a problem with some content in <Excel_filename>. Do you want us to try recovering the file as much as we can? If you trust the source of this workbook, then click Yes”

When i checked for this error, I got, this happens when the file is partially corrupted or completely corrupted.

This problem only appear on those .xlsx files where i used any sort of data validation like I mentioned above but also this problem won't happen if I open that .xlsx file in linux environment.

P.S : I am closing workbook and FileOutputStream at the end, have created atleast one sheet under the workbook (obviously)

PFB the code snippet for data validation :-

`XSSFDataValidationHelper dataValidationHelper = new XSSFDataValidationHelper(sheet);

		XSSFDataValidationConstraint numberValidationConstraint =
				(XSSFDataValidationConstraint)
						dataValidationHelper.createNumericConstraint(
								XSSFDataValidationConstraint.ValidationType.DECIMAL,
								XSSFDataValidationConstraint.OperatorType.BETWEEN,
								String.valueOf(Double.MIN_VALUE),
								String.valueOf(Double.MAX_VALUE)
						);

		CellRangeAddressList addressList = new CellRangeAddressList(
				2, 2000, columnCounter, columnCounter);
		XSSFDataValidation numberValidation =(XSSFDataValidation)dataValidationHelper.createValidation(
				numberValidationConstraint, addressList);
		numberValidation.setSuppressDropDownArrow(false);
		numberValidation.setShowErrorBox(true);
		numberValidation.createErrorBox("Invalid data","Only numbers are allowed");
		sheet.addValidationData(numberValidation);`

You cannot use Double.MIN_VALUE and Double.MAX_VALUE in Excel data validation. Excel has more strict restrictions for numeric cell values. You cannot store 1.7976931348623157E308 in a Excel cell, nor you can store 4.9E-324. In Excel cells only 15 significant digits can be stored. So the smallest number you can store is -9.99999999999999E307 and the biggest number you can store is 9.99999999999999E307. So you would must use that numbers to limit the range of possible numbers.

DataValidationConstraint numberValidationConstraint = dataValidationHelper.createNumericConstraint(
DataValidationConstraint.ValidationType.DECIMAL,
DataValidationConstraint.OperatorType.BETWEEN,
//String.valueOf(Double.MIN_VALUE),
//String.valueOf(Double.MAX_VALUE)
"-9.99999999999999E307",
"9.99999999999999E307"
);

But I suspect the goal is allowing only numeric values. This also can be achieved using a custom formula constraint having the formula

=ISNUMBER(OFFSET($A$1,ROW()-1,COLUMN()-1))

Complete example:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;
class CreateExcelDataValidation {
public static void main(String[] args) throws Exception {
//Workbook workbook = new HSSFWorkbook();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
int columnCounter = 0;
int fromRow = 2;
int toRow = 2000;
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
/*
DataValidationConstraint numberValidationConstraint = dataValidationHelper.createNumericConstraint(
DataValidationConstraint.ValidationType.DECIMAL,
DataValidationConstraint.OperatorType.BETWEEN,
//String.valueOf(Double.MIN_VALUE),
//String.valueOf(Double.MAX_VALUE)
"-9.99999999999999E307",
"9.99999999999999E307"
);
*/
DataValidationConstraint numberValidationConstraint = dataValidationHelper.createCustomConstraint(
"ISNUMBER(OFFSET($A$1,ROW()-1,COLUMN()-1))"
);
CellRangeAddressList addressList = new CellRangeAddressList(fromRow, toRow, columnCounter, columnCounter);
DataValidation numberValidation = dataValidationHelper.createValidation(numberValidationConstraint, addressList);
numberValidation.setShowErrorBox(true);
numberValidation.createErrorBox("Invalid data","Only numbers are allowed");
sheet.addValidationData(numberValidation);
FileOutputStream out = null;
if (workbook instanceof HSSFWorkbook) {
out = new FileOutputStream("CreateExcelDataValidation.xls");
} else if (workbook instanceof XSSFWorkbook) {
out = new FileOutputStream("CreateExcelDataValidation.xlsx");
}
workbook.write(out);
workbook.close();
out.close();
}
}