You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Jon Brisbin <jo...@npcinternational.com> on 2009/06/03 15:30:59 UTC
Problems adding logo image to spreadsheet
I've not found a lot of documentation on how to add images to
spreadsheets using POI and the examples I have found don't seem to
work like I expect. I could really use some help here.
I'm trying to add a company logo graphic (which I pull from our
intranet via Java URL object) to a HSSFWorkbook I create. My code
looks like this:
int picIndex = workbook.addPicture(getNPCLogo(),
Workbook.PICTURE_TYPE_PNG);
Drawing pat = currentSheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor();
anchor.setAnchorType(2);
anchor.setCol1((short) 0);
anchor.setCol2((short) 1);
anchor.setRow1(0);
anchor.setRow2(1);
Picture pic = pat.createPicture(anchor, picIndex);
The problem I'm having, though, is that this creates an image that
fills the height and width of the first cell, which is not what I want
(it looks stretched out). What I want is for the image to appear at
it's normal width and height at a specific position I give it, but I
can't seem to understand how to make that happen. When I replace
setCol1 et. al. with setDx1 and setDy1, etc... I get an image that is
a fraction of the height and width of the original image. I don't
understand what units the setDx2 and setDy2 is expecting (and it's not
documented anywhere I've found) so I can't get the image to the right
size.
Can someone please give me some clear guidance (beyond what little
code is out there in the wild which I've tried variations on ad
nauseum) on how to accomplish this using POI 3.5 beta5? I'm sure I'm
just missing a little something that I don't understand but I can't
see what that is.
Thanks!
Jon Brisbin
Portal Webmaster
NPC International, Inc.
Re: Problems adding logo image to spreadsheet
Posted by Dave Fisher <da...@jmlafferty.com>.
Hi -
So, the documentation of an opensource project could be better? That's
not surprising.
If you want help then you might try to have a better attitude.
How big is your image? Where on the spreadsheet are you placing it?
Are doing anything more than a single page or worksheet?
Perhaps you can share your code. Images are non-intuitive because
that's how Excel is inside. This might help with documentation in the
future.
Regards,
Dave
On Jun 3, 2009, at 11:59 AM, Jon Brisbin wrote:
>
> On Jun 3, 2009, at 12:07 PM, Dave Fisher wrote:
>
>> Take a look at the thread starting March 18, 2009 by AKewalramani
>> and the subject of "Anchor type methods in XSSF" includes responses
>> from Yegor:
>>
>>>> This was easy to achieve in HSSF because the HSSFClientAnchor
>>>> class had a setAnchorType
>>>> method which I could set a value to "2" and the image would move
>>>> with the cell and it would
>>>> not resize itself as the cell size is changed.
>>
>> Regards,
>> Dave
>
> If I set the anchor type to "2", I still have to multiply the pixel
> height and width of my image by seemingly random values. In other
> words, if I multiply the width of my image by 5.4 and the height of
> my image by 3, I get a reasonable facsimile of the original inside
> my spreadsheet.
>
> Besides all of this being extraordinarily non-intuitive, I did
> search extensively for example code, etc... and couldn't find
> anything that says "to insert an image into your spreadsheet, keep
> A, B, and C, in mind...", citing the above strangeness. It would
> seem that the POI developers would have documented the fact that you
> have know the secret codes to enter to get the results you're
> looking for, especially if you simply have a requirement to generate
> a spreadsheet to email to a non-technical user and you've never done
> that before and you have heard about POI only second-hand and you
> don't have time to spend digging into the source code of the thing
> just so you can use it in a small, non-critical task.
>
> It's been a very frustrating experience so far, to say the least...
>
> Jon Brisbin
> Portal Webmaster
> NPC International, Inc.
>
>
> ---------------------------------------------------------------------
> 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
Re: Problems adding logo image to spreadsheet
Posted by MSB <ma...@tiscali.co.uk>.
Jon,
Try to go with my second suggestion if you can. Use Excel to create a
template that you open and process with POI. That way you can be sure the
logo is sized and positioned exactly as you want.
I have checked this morning and POI will not corrupt the image at all once
it has been positioned on the worksheet. If you can use this technique, it
will help you to neatly side step the problem you are facing.
David, thanks for digging out that older post.
Yours
Mark B
Jon Brisbin-2 wrote:
>
>
> On Jun 3, 2009, at 12:07 PM, Dave Fisher wrote:
>
>> Take a look at the thread starting March 18, 2009 by AKewalramani
>> and the subject of "Anchor type methods in XSSF" includes responses
>> from Yegor:
>>
>>>> This was easy to achieve in HSSF because the HSSFClientAnchor
>>>> class had a setAnchorType
>>>> method which I could set a value to "2" and the image would move
>>>> with the cell and it would
>>>> not resize itself as the cell size is changed.
>>
>> Regards,
>> Dave
>
> If I set the anchor type to "2", I still have to multiply the pixel
> height and width of my image by seemingly random values. In other
> words, if I multiply the width of my image by 5.4 and the height of my
> image by 3, I get a reasonable facsimile of the original inside my
> spreadsheet.
>
> Besides all of this being extraordinarily non-intuitive, I did search
> extensively for example code, etc... and couldn't find anything that
> says "to insert an image into your spreadsheet, keep A, B, and C, in
> mind...", citing the above strangeness. It would seem that the POI
> developers would have documented the fact that you have know the
> secret codes to enter to get the results you're looking for,
> especially if you simply have a requirement to generate a spreadsheet
> to email to a non-technical user and you've never done that before and
> you have heard about POI only second-hand and you don't have time to
> spend digging into the source code of the thing just so you can use it
> in a small, non-critical task.
>
> It's been a very frustrating experience so far, to say the least...
>
> Jon Brisbin
> Portal Webmaster
> NPC International, Inc.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Problems-adding-logo-image-to-spreadsheet-tp23851486p23864586.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problems adding logo image to spreadsheet
Posted by Jon Brisbin <jo...@npcinternational.com>.
On Jun 3, 2009, at 12:07 PM, Dave Fisher wrote:
> Take a look at the thread starting March 18, 2009 by AKewalramani
> and the subject of "Anchor type methods in XSSF" includes responses
> from Yegor:
>
>>> This was easy to achieve in HSSF because the HSSFClientAnchor
>>> class had a setAnchorType
>>> method which I could set a value to "2" and the image would move
>>> with the cell and it would
>>> not resize itself as the cell size is changed.
>
> Regards,
> Dave
If I set the anchor type to "2", I still have to multiply the pixel
height and width of my image by seemingly random values. In other
words, if I multiply the width of my image by 5.4 and the height of my
image by 3, I get a reasonable facsimile of the original inside my
spreadsheet.
Besides all of this being extraordinarily non-intuitive, I did search
extensively for example code, etc... and couldn't find anything that
says "to insert an image into your spreadsheet, keep A, B, and C, in
mind...", citing the above strangeness. It would seem that the POI
developers would have documented the fact that you have know the
secret codes to enter to get the results you're looking for,
especially if you simply have a requirement to generate a spreadsheet
to email to a non-technical user and you've never done that before and
you have heard about POI only second-hand and you don't have time to
spend digging into the source code of the thing just so you can use it
in a small, non-critical task.
It's been a very frustrating experience so far, to say the least...
Jon Brisbin
Portal Webmaster
NPC International, Inc.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problems adding logo image to spreadsheet
Posted by Dave Fisher <da...@jmlafferty.com>.
Take a look at the thread starting March 18, 2009 by AKewalramani and
the subject of "Anchor type methods in XSSF" includes responses from
Yegor:
>> This was easy to achieve in HSSF because the HSSFClientAnchor
>> class had a setAnchorType
>> method which I could set a value to "2" and the image would move
>> with the cell and it would
>> not resize itself as the cell size is changed.
Regards,
Dave
On Jun 3, 2009, at 10:00 AM, MSB wrote:
>
> Sorry, I am being really slow this evening.
>
> Firstly, I have had a very quick dig around and I think that there
> could be
> some bad news. The HSSFClientAnchor ties an image to a specific
> number of
> rows and columns; it uses them to calculate the size of the image.
> So, you
> will have to add the image after you have resized the rows and
> columns to
> accomodate it correctly.
>
> However, have you thought of using a template file to base your
> spreadsheets
> upon? You could create a template file with the logo positioned and
> sized
> just as you want it at the top of the sheet. Save that file away and
> call it
> something like template.xls or template.xlt to make it apparant that
> it is a
> template file. Now, when you want to create a file, open the template,
> populate the sheet and save it all away under a new/different
> filename. All
> you will need to do is remember where the image ends so that you do
> not end
> up creating cells in a row that would overwrite portions of it.
>
> I know that this example relates to Office 2007 but it may also
> point you in
> an interetsing direction;
>
> http://www.online-tech-tips.com/ms-office-tips/header-on-excel/
>
> as it is entitled how to insert a graphic header...Again, you will be
> creating a template through Excel and using that as the basis for
> your POI
> generated files but I reckon that approach should work.
>
> Yours
>
> Mark B
>
>
> Jon Brisbin-2 wrote:
>>
>> I've not found a lot of documentation on how to add images to
>> spreadsheets using POI and the examples I have found don't seem to
>> work like I expect. I could really use some help here.
>>
>> I'm trying to add a company logo graphic (which I pull from our
>> intranet via Java URL object) to a HSSFWorkbook I create. My code
>> looks like this:
>>
>> int picIndex = workbook.addPicture(getNPCLogo(),
>> Workbook.PICTURE_TYPE_PNG);
>> Drawing pat = currentSheet.createDrawingPatriarch();
>> HSSFClientAnchor anchor = new HSSFClientAnchor();
>> anchor.setAnchorType(2);
>> anchor.setCol1((short) 0);
>> anchor.setCol2((short) 1);
>> anchor.setRow1(0);
>> anchor.setRow2(1);
>> Picture pic = pat.createPicture(anchor, picIndex);
>>
>> The problem I'm having, though, is that this creates an image that
>> fills the height and width of the first cell, which is not what I
>> want
>> (it looks stretched out). What I want is for the image to appear at
>> it's normal width and height at a specific position I give it, but I
>> can't seem to understand how to make that happen. When I replace
>> setCol1 et. al. with setDx1 and setDy1, etc... I get an image that is
>> a fraction of the height and width of the original image. I don't
>> understand what units the setDx2 and setDy2 is expecting (and it's
>> not
>> documented anywhere I've found) so I can't get the image to the right
>> size.
>>
>> Can someone please give me some clear guidance (beyond what little
>> code is out there in the wild which I've tried variations on ad
>> nauseum) on how to accomplish this using POI 3.5 beta5? I'm sure I'm
>> just missing a little something that I don't understand but I can't
>> see what that is.
>>
>> Thanks!
>>
>> Jon Brisbin
>> Portal Webmaster
>> NPC International, Inc.
>>
>
> --
> View this message in context: http://www.nabble.com/Problems-adding-logo-image-to-spreadsheet-tp23851486p23855274.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> 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
Re: Problems adding logo image to spreadsheet
Posted by MSB <ma...@tiscali.co.uk>.
Sorry, I am being really slow this evening.
Firstly, I have had a very quick dig around and I think that there could be
some bad news. The HSSFClientAnchor ties an image to a specific number of
rows and columns; it uses them to calculate the size of the image. So, you
will have to add the image after you have resized the rows and columns to
accomodate it correctly.
However, have you thought of using a template file to base your spreadsheets
upon? You could create a template file with the logo positioned and sized
just as you want it at the top of the sheet. Save that file away and call it
something like template.xls or template.xlt to make it apparant that it is a
template file. Now, when you want to create a file, open the template,
populate the sheet and save it all away under a new/different filename. All
you will need to do is remember where the image ends so that you do not end
up creating cells in a row that would overwrite portions of it.
I know that this example relates to Office 2007 but it may also point you in
an interetsing direction;
http://www.online-tech-tips.com/ms-office-tips/header-on-excel/
as it is entitled how to insert a graphic header...Again, you will be
creating a template through Excel and using that as the basis for your POI
generated files but I reckon that approach should work.
Yours
Mark B
Jon Brisbin-2 wrote:
>
> I've not found a lot of documentation on how to add images to
> spreadsheets using POI and the examples I have found don't seem to
> work like I expect. I could really use some help here.
>
> I'm trying to add a company logo graphic (which I pull from our
> intranet via Java URL object) to a HSSFWorkbook I create. My code
> looks like this:
>
> int picIndex = workbook.addPicture(getNPCLogo(),
> Workbook.PICTURE_TYPE_PNG);
> Drawing pat = currentSheet.createDrawingPatriarch();
> HSSFClientAnchor anchor = new HSSFClientAnchor();
> anchor.setAnchorType(2);
> anchor.setCol1((short) 0);
> anchor.setCol2((short) 1);
> anchor.setRow1(0);
> anchor.setRow2(1);
> Picture pic = pat.createPicture(anchor, picIndex);
>
> The problem I'm having, though, is that this creates an image that
> fills the height and width of the first cell, which is not what I want
> (it looks stretched out). What I want is for the image to appear at
> it's normal width and height at a specific position I give it, but I
> can't seem to understand how to make that happen. When I replace
> setCol1 et. al. with setDx1 and setDy1, etc... I get an image that is
> a fraction of the height and width of the original image. I don't
> understand what units the setDx2 and setDy2 is expecting (and it's not
> documented anywhere I've found) so I can't get the image to the right
> size.
>
> Can someone please give me some clear guidance (beyond what little
> code is out there in the wild which I've tried variations on ad
> nauseum) on how to accomplish this using POI 3.5 beta5? I'm sure I'm
> just missing a little something that I don't understand but I can't
> see what that is.
>
> Thanks!
>
> Jon Brisbin
> Portal Webmaster
> NPC International, Inc.
>
--
View this message in context: http://www.nabble.com/Problems-adding-logo-image-to-spreadsheet-tp23851486p23855274.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problems adding logo image to spreadsheet
Posted by MSB <ma...@tiscali.co.uk>.
Hello Jon,
Sorry to say that I cannot be of any help directly but have you performed a
search of the list yet? I seem to remember that someone posted a very useful
bit of code that illustrated how to add an image and control the size -
again my old brain is telling me it has something to do with the default
font but I could well be wrong. I am sorry to say that I cannot search the
list myself as I access it through Nabble.
If I have the opportunity this evening, I will have a good search around and
see if I can find anything out for you or make any sense of adding images to
sheets and will post a message if I come up with any information. Tomorrow
is an 'office' day for me so I should have one or two opportunities to play
with the api and again will post if I manage to solve the problem of setting
an image to a fixed size.
Yours
Mark B
Jon Brisbin-2 wrote:
>
> I've not found a lot of documentation on how to add images to
> spreadsheets using POI and the examples I have found don't seem to
> work like I expect. I could really use some help here.
>
> I'm trying to add a company logo graphic (which I pull from our
> intranet via Java URL object) to a HSSFWorkbook I create. My code
> looks like this:
>
> int picIndex = workbook.addPicture(getNPCLogo(),
> Workbook.PICTURE_TYPE_PNG);
> Drawing pat = currentSheet.createDrawingPatriarch();
> HSSFClientAnchor anchor = new HSSFClientAnchor();
> anchor.setAnchorType(2);
> anchor.setCol1((short) 0);
> anchor.setCol2((short) 1);
> anchor.setRow1(0);
> anchor.setRow2(1);
> Picture pic = pat.createPicture(anchor, picIndex);
>
> The problem I'm having, though, is that this creates an image that
> fills the height and width of the first cell, which is not what I want
> (it looks stretched out). What I want is for the image to appear at
> it's normal width and height at a specific position I give it, but I
> can't seem to understand how to make that happen. When I replace
> setCol1 et. al. with setDx1 and setDy1, etc... I get an image that is
> a fraction of the height and width of the original image. I don't
> understand what units the setDx2 and setDy2 is expecting (and it's not
> documented anywhere I've found) so I can't get the image to the right
> size.
>
> Can someone please give me some clear guidance (beyond what little
> code is out there in the wild which I've tried variations on ad
> nauseum) on how to accomplish this using POI 3.5 beta5? I'm sure I'm
> just missing a little something that I don't understand but I can't
> see what that is.
>
> Thanks!
>
> Jon Brisbin
> Portal Webmaster
> NPC International, Inc.
>
--
View this message in context: http://www.nabble.com/Problems-adding-logo-image-to-spreadsheet-tp23851486p23854589.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org