dropdown Validation not working if it exceeds 50 rows in the Export To Excel

Question

I am generating Excel File(.xlsx) using apache poi jar (poi-ooxml-3.9.jar), I added dropdown validation for 10 columns in my excel file, If I generate the Excel File with 50 rows, drop down validation is working. If it exceeds more than 50 rows, drop down validation is not coming in the Excel File, When I open the excel File I get the message as "We found a problem with some content in fileName.xlsx. Do you want us to try to recover as much as we can ? If you trust the source of this workbook, click Yes ". when click on Yes, all the dropdown validation it is removing. Kindly need solution to fix this issue.

Answer 1

Do not create DataValidationConstraint for each single cell but only for each varying list you need. Then create DataValidation using those DataValidationConstraint for continuous CellRangeAddressList which are as big as possible and also are not all single cells.

Example creates ten different list validations for column 1 to 10 in rows 1 to 10000.

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddressList;

class DataValidationList {

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

  Workbook workbook = new XSSFWorkbook();  // or new HSSFWorkbook

  Sheet sheet = workbook.createSheet("Data Validation");
  DataValidationHelper dvHelper = sheet.getDataValidationHelper();

  for (int col = 0; col < 10; col++) {
   DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(
    new String[]{"Col "+(col+1)+" one","Col "+(col+1)+" two","Col "+(col+1)+" three"});
   CellRangeAddressList addressList = new CellRangeAddressList(0, 9999, 0, col);            
   DataValidation validation = dvHelper.createValidation(
    dvConstraint, addressList);

   if(validation instanceof XSSFDataValidation) {
    validation.setSuppressDropDownArrow(true);
    validation.setShowErrorBox(true);
   }
   else {
    validation.setSuppressDropDownArrow(false);
   }

   sheet.addValidationData(validation);
  }

  String filename;

  if(workbook instanceof XSSFWorkbook) {
   filename = "DataValidationList.xlsx";
  } else {
   filename = "DataValidationList.xls";
  }

  FileOutputStream out = new FileOutputStream(filename);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}