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.
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?
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.
The above code is just for illustrate the bug.
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.
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.
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