You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Hannes Erven <h....@gmx.at> on 2011/08/16 13:39:23 UTC

Replace image in excel file (HSSF)?

Folks,


I need to batch-replace pictures embedded in Excel 2003 files; that is,
after the conversion the file should contain a different image, but with
otherwise the same properties (location, size, ...) as the original.

So far I have found that listing all affected HSSFPictures and
identifying those that should be replaced is no big deal. I haven't
found any way to modify the PictureData associated with the HSSFPicture.

With XSSF I'd probably just call getPackagePart().getOutputStream(), but
with HSSF?
Unfortunately, the result must absolutely be a XLS file.


Thank you for any hints!

Best regards,

	-hannes

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Query HSSFShapeGroup children

Posted by Hannes Erven <h....@gmx.at>.
Hi again,


as far as I see, the support for all those Escher drawing stuff in HSSF
is kind of limited -- it seems that HSLF is far more complete.

The images I wanted to change in the first place are always contained
within a ShapeGroup, but the HSSF Escher Aggregate does not account for
that and simple does not load any group's children. I tried to change
that, with the result that even only loading and saving a workbook
(without any other changes than the Escher loader) results in Excel
complaining about a corrupted file.

The BiffViewer tells me that POI loses many of the shape's properties,
like even the Text of a simple Textbox.


I guess it is time to take that to the dev list. I've already created an
enhancement request
https://issues.apache.org/bugzilla/show_bug.cgi?id=51683 where I have
also attached my patch/hack attempt.


Thank you everyone for this great library and your support!,

	-hannes

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Query HSSFShapeGroup children (was: Re: Replace image in excel file (HSSF)?)

Posted by Hannes Erven <h....@gmx.at>.
Hi Yegor!,


>         HSSFPatriarch drawing = sheet.createDrawingPatriarch();
>         for(HSSFShape sh : drawing.getChildren()){
>             if(sh instanceof HSSFPicture){
>                 HSSFPicture pic = (HSSFPicture)sh;
>                 pic.setPictureIndex(newPictureIndex);


many thanks for your reply.

I'm now struggling with actually finding the HSSFPictures in the
workbook.
The picture I am looking for in the workbooks is always grouped with
text boxes and other shapes and not a "root" shape.

The HSSFShapes I get from drawing.getChildren() are all HSSFGroups with
no children.
I've been digging through the source and to me it seems POI does not
load the child shapes of such groups... ???

I'll be looking into the file format description I found here
http://chicago.sourceforge.net/devel/docs/escher/ in the afternoon and
see if I can add that.


Thanks again,
best regards

	-hannes

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Re: Replace image in excel file (HSSF)?

Posted by Yegor Kozlov <ye...@dinom.ru>.
Download the latest build from trunk and try the following code:

        byte[] pictureData = null; // read from file
        HSSFWorkbook wb = new
                HSSFWorkbook(new FileInputStream("template.xls"));

        int newPictureIndex = wb.addPicture(pictureData,
HSSFWorkbook.PICTURE_TYPE_PNG);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFPatriarch drawing = sheet.createDrawingPatriarch();
        for(HSSFShape sh : drawing.getChildren()){
            if(sh instanceof HSSFPicture){
                HSSFPicture pic = (HSSFPicture)sh;
                pic.setPictureIndex(newPictureIndex);
            }
        }

Yegor

On Tue, Aug 16, 2011 at 3:39 PM, Hannes Erven <h....@gmx.at> wrote:
> Folks,
>
>
> I need to batch-replace pictures embedded in Excel 2003 files; that is,
> after the conversion the file should contain a different image, but with
> otherwise the same properties (location, size, ...) as the original.
>
> So far I have found that listing all affected HSSFPictures and
> identifying those that should be replaced is no big deal. I haven't
> found any way to modify the PictureData associated with the HSSFPicture.
>
> With XSSF I'd probably just call getPackagePart().getOutputStream(), but
> with HSSF?
> Unfortunately, the result must absolutely be a XLS file.
>
>
> Thank you for any hints!
>
> Best regards,
>
>        -hannes
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org