Problems with inserting pictures, textboxes, rectangles or ellipses into an existing XLSX document with POI

[Jens Götz <be...@jensgoetz.de>]

Hello,

I got two problems when I use POI 3.7 or 3.8beta2 to open and edit an existing XLSX document (see attachment) created with Microsoft Office 2010. First, after using the following code to add additional images to an existing sheet, all existing shapes in the sheet are erased:

    File templateWorkbookFile = ApachePoiReport
        .getFileFromClassLoader(templateWorkbookFileName);
    
    if (templateWorkbookFile == null) {
        throw new IllegalStateException(
            "Error - spreadsheetFile is null.");
    }
    
    // open template workbook
    InputStream templateWorkbookFileInputStream = 
        new FileInputStream(templateWorkbookFile);
    XSSFWorkbook workbook = 
        new XSSFWorkbook(templateWorkbookFileInputStream);
    XSSFSheet sheet = workbook.getSheet(sheetName);
    
    // create 300 pictures
    XSSFDrawing drawingPatriach = sheet.createDrawingPatriarch();
            
    // get picture file
    File imageFile = getFileFromClassLoader(imageFileName);
            
    if (imageFile == null) {
        throw new IllegalStateException("Error - imageFile is null.");
    }
            
    for (int row = 4, column = 0; row < 304; row++) {
        InputStream imageFileInputStream = 
            new FileInputStream(imageFile);
        byte[] imageData = new byte[(int)imageFile.length()];
        imageFileInputStream.read(imageData);
        imageFileInputStream.close();
        int pictureIndex = workbook.addPicture(imageData,
            Workbook.PICTURE_TYPE_JPEG);
                
        XSSFClientAnchor anchor = new XSSFClientAnchor();
        anchor.setCol1(column);
        anchor.setRow1(row);
                
        XSSFPicture picture = 
            drawingPatriach.createPicture(anchor, pictureIndex);
                
        // auto-size picture relative to its top-left corner
        picture.resize();
    }

Second, after using the following code to add textboxes, rectangles or ellipses to an existing sheet and saving the XLSX document, when opening the XLSX document with Microsoft Excel 2010, it is telling me that it needs to repair the document and erases all inserted shapes from the document:

    // insert 900 textboxes and format it
    Font textBoxFont = workbook.createFont();
    textBoxFont.setFontName("Arial");
    textBoxFont.setFontHeightInPoints((short) 8);
    textBoxFont.setColor(IndexedColors.RED.getIndex());
            
    for (int row = 4, column = 1; row < 904; row++) {
        XSSFCreationHelper creationHelper = workbook.getCreationHelper();
        XSSFClientAnchor anchor = creationHelper.createClientAnchor();
        anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
        anchor.setCol1(column);
        anchor.setRow1(row);
        anchor.setDx2(1023); // 100 %
        anchor.setDy2(255); // 100 %
        XSSFTextBox textbox = 
            drawingPatriach.createTextbox(anchor);
        
        textbox.setNoFill(true);
        textbox.setLineStyle(HSSFShape.LINESTYLE_NONE);
        
        XSSFRichTextString textboxText = new XSSFRichTextString(
            textfield);
        textboxText.applyFont(textBoxFont);
        textbox.setText(textboxText);
    }
    
    // insert 2000 rectangles
    for (int row = 4, column = 12; row < 2004; row++) {
        XSSFClientAnchor anchor = new XSSFClientAnchor();
        anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
        anchor.setCol1(column);
        anchor.setRow1(row);
        anchor.setDx2(1023); // 100 %
        anchor.setDy2(255); // 100 %
        XSSFSimpleShape rectangle = 
            drawingPatriach.createSimpleShape(anchor);
        rectangle.setShapeType(ShapeTypes.RECT);
        rectangle.setFillColor(Color.RED.getRed(), 
            Color.RED.getGreen(), 
            Color.RED.getBlue());
        rectangle.setLineStyle(HSSFShape.LINESTYLE_SOLID);
        rectangle.setLineStyleColor(Color.BLUE.getRed(), 
            Color.BLUE.getGreen(), 
            Color.BLUE.getBlue());
        }
        
        // insert 2000 circles
        for (int row = 4, column = 13; row < 2004; row++) {
            XSSFClientAnchor anchor = new XSSFClientAnchor();
            anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
            anchor.setCol1(column);
            anchor.setRow1(row);
            anchor.setDx2(1023); // 100 %
            anchor.setDy2(255); // 100 %
            XSSFSimpleShape rectangle = ((XSSFDrawing)drawingPatriach)
                .createSimpleShape((XSSFClientAnchor)anchor);
            rectangle.setShapeType(ShapeTypes.ELLIPSE);
            rectangle.setFillColor(Color.RED.getRed(), 
                Color.RED.getGreen(), 
                Color.RED.getBlue());
            rectangle.setLineStyle(HSSFShape.LINESTYLE_SOLID);
            rectangle.setLineStyleColor(Color.BLUE.getRed(), 
                Color.BLUE.getGreen(), 
                Color.BLUE.getBlue());
        }

Does anyone know if these are known issues or might I do something wrong?


Best regards,

Jens Götz