poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stephen Friedrich <stephen.friedr...@fortis-it.eu>
Subject Bug when adding picture to cloned sheet?!
Date Fri, 15 Jan 2010 15:15:36 GMT
Ma app generates XLS files like this

*         load static "template" XLS file (with formatting and auto-filters) containing a
single sheet

*         add logo to the first sheet

*         fill in data

*         write the result
This was working fine until I needed to fill multiple sheets (number of sheets being variable).
I tried to clone the first sheet as often as necessary.
However all I am getting is an IIOOBE:
Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 2, Size: 1
       at java.util.ArrayList.RangeCheck(ArrayList.java:546)
       at java.util.ArrayList.get(ArrayList.java:321)
       at org.apache.poi.hssf.model.DrawingManager2.getDrawingGroup(DrawingManager2.java:125)
       at org.apache.poi.hssf.model.DrawingManager2.allocateShapeId(DrawingManager2.java:71)
       at org.apache.poi.hssf.record.EscherAggregate.convertPatriarch(EscherAggregate.java:871)
       at org.apache.poi.hssf.record.EscherAggregate.convertUserModelToRecords(EscherAggregate.java:704)
       at org.apache.poi.hssf.record.EscherAggregate.getRecordSize(EscherAggregate.java:502)
       at org.apache.poi.hssf.model.Sheet.preSerialize(Sheet.java:1517)
       at org.apache.poi.hssf.usermodel.HSSFWorkbook.getBytes(HSSFWorkbook.java:1238)
       at org.apache.poi.hssf.usermodel.HSSFWorkbook.write(HSSFWorkbook.java:1158)
(using latest POI release, i.e. 3.6)

I tried various workarounds, but never got it working correctly.
Without adding the logo all works well.

See below for a self-contained example - the attached XLS template must be in the working
when running the code.
I tried digging into the POI source code, but quickly got lost.
Any ideas?


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.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;

 * Tests adding a picture with POI to an existing Excel in conjunction with cloning of sheets.
 * Fails with an exception (using POI 3.6) - but see comment below for attempted workarounds.
public class PictureTest {
    public static void main(String[] args) throws IOException {
        byte[] pngData = createPng();

        HSSFWorkbook workbook = loadWorkbook("poi-picture-test.xls");

        workbook.cloneSheet(0); // Causes IndexOutOfBoundsException on workbook.write()

        // If comment is removed there will be no exception, but a broken picture on second
        // workbook = reloadWorkbook(workbook);

        addPictureToAllSheets(workbook, pngData);

        // This was a workaround (comment the call above to addPictureToAllSheets() and enable
the call below).
        // It is working when the file is opened in Excel 2007.
        // However a user reported missing auto-filters when opened in Excel 2000:
        // addNewPictureToAllSheets(workbook, pngData);

        saveWorkbook(workbook, "poi-picture-result.xls");

    private static void addPictureToAllSheets(HSSFWorkbook workbook, byte[] pngData) {
        int pictureIndex = workbook.addPicture(pngData, HSSFWorkbook.PICTURE_TYPE_PNG);

        int sheetCount = workbook.getNumberOfSheets();
        for (int sheetIndex = 0; sheetIndex < sheetCount; ++sheetIndex) {
            addPicture(workbook, sheetIndex, pictureIndex);

    private static void addNewPictureToAllSheets(HSSFWorkbook workbook, byte[] pngData) {

        int sheetCount = workbook.getNumberOfSheets();
        for (int sheetIndex = 0; sheetIndex < sheetCount; ++sheetIndex) {
            int pictureIndex = workbook.addPicture(pngData, HSSFWorkbook.PICTURE_TYPE_PNG);
            addPicture(workbook, sheetIndex, pictureIndex);

    private static HSSFWorkbook reloadWorkbook(HSSFWorkbook workbook) throws IOException {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray());
        return new HSSFWorkbook(bais);

    private static void addPicture(HSSFWorkbook workbook, int sheetIndex, int pictureIndex)
        HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        Drawing drawing = sheet.createDrawingPatriarch();
        CreationHelper helper = workbook.getCreationHelper();
        ClientAnchor anchor = helper.createClientAnchor();


        Picture pict = drawing.createPicture(anchor, pictureIndex);


    private static HSSFWorkbook loadWorkbook(String xlsPath) throws IOException {
         FileInputStream fis = new FileInputStream(xlsPath);
        HSSFWorkbook workbook = new HSSFWorkbook(fis, true);
        return workbook;

    private static void saveWorkbook(HSSFWorkbook workbook, String filePath) throws IOException
        FileOutputStream fos = new FileOutputStream(filePath);
        try {
        finally {

    private static byte[] createPng() throws IOException {
        BufferedImage image = new BufferedImage(250, 40, BufferedImage.TYPE_INT_ARGB);
        Graphics2D g = (Graphics2D) image.getGraphics();
        g.setFont(new Font("SansSerif", Font.BOLD, 20));
        g.setStroke(new BasicStroke(3.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND));
        g.fillRect(0, 0, 300, 50);
        g.drawString("Fortis IT-Services GmbH", 8, 30);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ImageIO.write(image, "PNG", baos);
        byte[] data = baos.toByteArray();

        return data;

    private static void close(Closeable closeable) {
        try {
        catch (IOException ignore) {

View raw message