Bug 63665

Summary: Drop down's options don't appear when clicking arrow in excel exported with Apache POI when there are many cell comments
Product: POI Reporter: David Ming <873613171>
Component: HSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal    
Priority: P2    
Version: 4.0.0-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Mac OS X 10.1   

Description David Ming 2019-08-14 12:50:44 UTC
I export excel using Apache POI with drop down and many cell comments in it. When generating less cell comments, drop down's options appear for choosing when clicking drop down arrow, but when generating much cell comments, the drop down's options don't appear.

I do test with latest Apache POI version - 4.1.0.

From my test result, when commenting out cell comments generation part, drop down's options appear for choosing when clicking drop down arrow and when setting variable rowNum to 15, the drop down's options also appear. When setting variable rowNum to 25, then it will generate much more cell comments, then drop down's options don't appear. And if setting rowNum to be greater than 25, options also don't appear. So I infer this problem is related to number of comments generated. I am not sure if this is an Apache POI issue or there is anything I write incorrectly in code. These numbers I give to rowNum are just for test, you can give any rowNum to try. Please help me to figure out this problem. Thanks a lot.

The code I am running as below:

    HSSFWorkbook wb=new HSSFWorkbook();
    HSSFSheet sheet=wb.createSheet("my sheet");

    // create cell and add comments
    int rowNum = 25;
    int columnNum = 50;
    HSSFPatriarch p=sheet.createDrawingPatriarch();
    for (int i=0; i<rowNum; i++) {
        HSSFRow row = sheet.createRow(i);

        for (int j=0; j<columnNum; j++) {
            HSSFCell cell = row.createCell(j);
            cell.setCellValue(new HSSFRichTextString((i+1)+","+(j+1)));

            if (i != 0 || j != 0) {
                HSSFComment comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
                comment.setString(new HSSFRichTextString("comment for cell: " + (i+1) +","+(j+1)));

    // add drop down
    String hiddenSheetName = "hiddenSheet";
    HSSFSheet hiddenSheet = wb.createSheet(hiddenSheetName);
    wb.setSheetHidden(wb.getSheetIndex(hiddenSheet), true);
    HSSFRow hiddenRow = null;
    HSSFCell hiddenCell = null;
    String[] menuItems = {"Yes", "No"};
    for (int i = 0; i < menuItems.length; i++)
        hiddenRow = hiddenSheet.createRow(i);
        hiddenCell = hiddenRow.createCell(0);
    HSSFName namedCell = wb.createName();
    String formulaId = "formulaId";
    namedCell.setRefersToFormula(hiddenSheetName + "!A$1:A$" + menuItems.length);
    HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
    DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaId);
    CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
    HSSFDataValidation validation = (HSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);

    FileOutputStream out = null;
        out=new FileOutputStream("exportExcelTest.xls");
    } catch (Exception e) {