Bug 63665 - Drop down's options don't appear when clicking arrow in excel exported with Apache POI when there are many cell comments
Summary: Drop down's options don't appear when clicking arrow in excel exported with A...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 4.0.0-FINAL
Hardware: PC Mac OS X 10.1
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2019-08-14 12:50 UTC by David Ming
Modified: 2019-11-17 12:02 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
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) {