Bug 51459 - Image not added in HSSFWorkbook
Summary: Image not added in HSSFWorkbook
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.8-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2011-07-01 10:41 UTC by Martin Studer
Modified: 2011-07-01 10:47 UTC (History)
0 users

Excel file for reproducing the issue (196.50 KB, application/vnd.ms-excel)
2011-07-01 10:41 UTC, Martin Studer
Image file used for reproducing the issue (49.75 KB, image/jpeg)
2011-07-01 10:42 UTC, Martin Studer

Note You need to log in before you can comment on or make changes to this bug.
Description Martin Studer 2011-07-01 10:41:51 UTC
Created attachment 27235 [details]
Excel file for reproducing the issue

Trying to add an image to a worksheet does not actually work in the case of the attached Excel file (repro.xls). There is no error message - the image just won't end up on the worksheet. I could pin the problem down to the second worksheet (note, the image is to be added to first one). If I delete the second worksheet, it works. Originally, there were many more worksheets in the workbook and I tried deleting any of them in succession to detect where the issue is and it seems that exactly the remaining (worksheet 2 in repro.xls) is causing the grief.

The following is the code to reproduce the issue:

File f1 = new File("repro.xls");
        File f2 = new File("earthquake.jpg");

        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(f1));
        InputStream is = new FileInputStream(f2);
        byte[] bytes = IOUtils.toByteArray(is);
        int imageIndex = wb.addPicture(bytes, org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_JPEG);

        Name cname = wb.getName("Test");
        Sheet sheet = wb.getSheet(cname.getSheetName());

        AreaReference aref = new AreaReference(cname.getRefersToFormula());
        CellReference topLeft = aref.getFirstCell();
        CellReference bottomRight = aref.getLastCell();

        Drawing drawing = null;
        drawing = ((HSSFSheet)sheet).getDrawingPatriarch();
        if(drawing == null) {
            drawing = sheet.createDrawingPatriarch();

        CreationHelper helper = wb.getCreationHelper();
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setRow2(bottomRight.getRow() + 1);
        anchor.setCol2(bottomRight.getCol() + 1);

        drawing.createPicture(anchor, imageIndex);

        FileOutputStream fos = new FileOutputStream(f1, false);

I'm using POI 3.8-beta3 with a 64-bit JDK 1.6.0_20 on Windows 7 64-bit.
Comment 1 Martin Studer 2011-07-01 10:42:31 UTC
Created attachment 27236 [details]
Image file used for reproducing the issue
Comment 2 Nick Burch 2011-07-01 10:47:56 UTC
You seem to be trying to add to an existing drawing patriarch, which doesn't work for hssf (except in a very small number of cases)

If you want to add pictures, you need to start with a workbook with none in it, and then add them all in one go. See http://poi.apache.org/spreadsheet/quick-guide.html#Images

(We'd love for HSSF to be able to edit existing pictures, but it's a massive amount of work and alas no-one has been willing so far to do it...)