Bug 51332

Summary: HSSFPatriarch has problem when there are more than 1023 drawing objects
Product: POI Reporter: Sammy Chu <sammychuhf>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: The problematic Excel file
The reference Excel file

Description Sammy Chu 2011-06-07 08:51:56 UTC
Created attachment 27123 [details]
The problematic Excel file

When HSSFPatriarch store more than 1023 drawing objects with list validation enabled, POI will generate a problematic excel (xls) which will move the (total drawing objects % 1024) th drawing object to the validation cell when click on the validation enabled cell.

Here is the example code to produce this bug:
==========================================================================
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.util.CellRangeAddressList;

public final class TestCase {
    public static void main (String[] args) {
        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();
            InputStream iconFileStream = TestCase.class.getResourceAsStream("icon.png");
            // Convert to PNG image format in case the image is not in PNG
            BufferedImage iconImage = ImageIO.read(iconFileStream);
            ByteArrayOutputStream iconByteStream = new ByteArrayOutputStream();
            ImageIO.write(iconImage, "PNG", iconByteStream);
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

            // Use the same image for the demo
            int imgIndex = wb.addPicture(iconByteStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG);
            for (int i = 0; i < 1023; ++i) {
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 1, i, (short) 2, i + 1);
                anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
                patriarch.createPicture(anchor, imgIndex);
            }
            // The 1024+ th image
            for (int i = 0; i < 1; ++i) {
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 3, i, (short) 4, i + 1);
                anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
                patriarch.createPicture(anchor, imgIndex);
            }
            // Create validation for demo the bug
            DVConstraint listValidatorConstraint = DVConstraint.createExplicitListConstraint(new String [] { "Value 1" });
            HSSFDataValidation listValidator = new HSSFDataValidation(new CellRangeAddressList(0, 65535, 2, 2), listValidatorConstraint);
            sheet.addValidationData(listValidator);
            OutputStream fos = new FileOutputStream("problematic.xls");
            wb.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
==========================================================================

When you click on the column C of the generated Excel file, the picture in B1 will move to the current position and the validation does not work.
Comment 1 Nick Burch 2011-06-07 08:58:06 UTC
What does excel do if you generate a file with that many drawings in it? Does it store them all in the same record structure, or does it split them? Does it use different structures entirely?
Comment 2 Sammy Chu 2011-06-07 09:37:56 UTC
In our project, we generate those Excel with different images, comments and validation list for selecting input. When the drawing exceeds the 1023 limit, the validation list become problematic.

Sorry, I don't know what you mean about "store them all in the same record structure".

Thanks for your quick reply.
Comment 3 Sammy Chu 2011-06-07 09:43:59 UTC
The above code is just for illustrate the bug.
Comment 4 Nick Burch 2011-06-07 11:26:57 UTC
First up we need a file generated by excel with at least the problem number of images. Then someone needs to figure out what excel does differently, be it multiple records, different structures etc.
Comment 5 Sammy Chu 2011-06-08 03:07:03 UTC
Created attachment 27128 [details]
The reference Excel file

Hi, I just generated a similar Excel file using JExcelApi and adding list validation in Excel 2003 SP3 (Since JExcelApi does not support this feature).
Hope this can help to figure out the problem.
Comment 6 Yegor Kozlov 2011-06-22 13:50:25 UTC
Fixed in r1138465. 

I was able to reproduce the problem in Excel 2003, but not in Excel 2010 - the latest version of Excel seems to be more forgiving to flaws in input files.

It is an old bug, I think we have since POI-3.0 or so. The problem was in how POI generated internal IDs for the generated shapes. The bug can be reproduced with any types of shapes, i.e. if you replace Picture with Textbox you will have the same issue.

Each shape in Excel must have a unique low-level id. The problem is that Excel uses several different sequences of IDs to handle drawing objects - some start with 1024, others with 1, etc.  POI did not make any differences and used the same id for all. It worked well for most cases except when the number of shapes exceeded a certain limit.

Regards,
Yegor