You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by pr...@contractor.thomson.com on 2007/10/12 08:19:58 UTC
Export an image as PNG to a particular cell in Excel using POI.
Hi,
I would like to know if we can export an image as PNG to a particular cell in Excel using POI.
I have refered to the examples given on the website and tried exporting an image to excel.
It works fine for image to excel worksheet, but there are no APIs that would enable image export to a cell.
cell.SetCellValue() does not list any way to have image or even btye array/stream to be written to the cell.
Is this, what I am expecting to do, not possible using POI API?
Regards,
Prachi
Re: Re[2]: Export an image as PNG to a particular cell in Excel using POI.
Posted by Luiz Antonio Falaguasta Barbosa <lu...@gwe.com.br>.
Hi people,
I ran the example bellow and it is pretty fine!
Is it possible do the same with .DOC and .PPT files?
Thanks in advance!
Luiz
On 10/15/07, Yegor Kozlov <ye...@dinom.ru> wrote:
>
>
> http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/usermodel/examples/OfficeDrawing.java
>
> Yegor
>
> > Hi Prachi,
>
> > Could you share the fragment of your code where you put the image into
> the
> > .XLS file?
>
> > I have to do the same with template files. I wanna change keys by images
> in
> > specific cells of the file, but I didn't find how to do it yet.
>
> > For me, doesn't matter if I will loose an original image, because I
> won't
> > have any image into the cell. Actually, I have to create another one
> file
> > with the image (in the new file) instead of the key (that existed in the
> > original file).
>
> > Thanks in advance!
>
> > Luiz
>
> > On 10/12/07, prachi.narkar@contractor.thomson.com <
> > prachi.narkar@contractor.thomson.com> wrote:
> >>
> >> Thanks for your prompt reply!
> >> Yes, the positioning can be achieved, but then as the documentation
> says,
> >> that any existing image would be erased with the new image.
> >>
> >> What I need is that corrsponding to every record (row) in the Excel
> sheet,
> >> I would need an image to be exported.
> >> But with the limitation that it would erase an existing image, wouldnt
> the
> >> existing images exported be erased?
> >> Say I export an image for record 1 and for record 2 the previous image
> >> would be erased and new image added?
> >>
> >> -Prachi
> >>
> >> ________________________________
> >>
> >> From: Nick Burch [mailto:nick@torchbox.com]
> >> Sent: Fri 12/10/2007 11:29
> >> To: POI Users List
> >> Subject: Re: Export an image as PNG to a particular cell in Excel using
> >> POI.
> >>
> >>
> >>
> >> On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
> >> > I would like to know if we can export an image as PNG to a particular
> >> > cell in Excel using POI.
> >>
> >> I don't think you can actually put an image as the real contents of a
> cell
> >> (it's just not something the excel format supports). I think all you
> can
> >> do is add in an image, then position/size it to occupy the whole cell.
> >>
> >> Nick
> >>
> >> ---------------------------------------------------------------------
> >> 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
> >>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Re[2]: Export an image as PNG to a particular cell in Excel using POI.
Posted by Luiz Antonio Falaguasta Barbosa <lu...@gwe.com.br>.
Thanks Yegor!
On 10/15/07, Yegor Kozlov <ye...@dinom.ru> wrote:
>
>
> http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/usermodel/examples/OfficeDrawing.java
>
> Yegor
>
> > Hi Prachi,
>
> > Could you share the fragment of your code where you put the image into
> the
> > .XLS file?
>
> > I have to do the same with template files. I wanna change keys by images
> in
> > specific cells of the file, but I didn't find how to do it yet.
>
> > For me, doesn't matter if I will loose an original image, because I
> won't
> > have any image into the cell. Actually, I have to create another one
> file
> > with the image (in the new file) instead of the key (that existed in the
> > original file).
>
> > Thanks in advance!
>
> > Luiz
>
> > On 10/12/07, prachi.narkar@contractor.thomson.com <
> > prachi.narkar@contractor.thomson.com> wrote:
> >>
> >> Thanks for your prompt reply!
> >> Yes, the positioning can be achieved, but then as the documentation
> says,
> >> that any existing image would be erased with the new image.
> >>
> >> What I need is that corrsponding to every record (row) in the Excel
> sheet,
> >> I would need an image to be exported.
> >> But with the limitation that it would erase an existing image, wouldnt
> the
> >> existing images exported be erased?
> >> Say I export an image for record 1 and for record 2 the previous image
> >> would be erased and new image added?
> >>
> >> -Prachi
> >>
> >> ________________________________
> >>
> >> From: Nick Burch [mailto:nick@torchbox.com]
> >> Sent: Fri 12/10/2007 11:29
> >> To: POI Users List
> >> Subject: Re: Export an image as PNG to a particular cell in Excel using
> >> POI.
> >>
> >>
> >>
> >> On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
> >> > I would like to know if we can export an image as PNG to a particular
> >> > cell in Excel using POI.
> >>
> >> I don't think you can actually put an image as the real contents of a
> cell
> >> (it's just not something the excel format supports). I think all you
> can
> >> do is add in an image, then position/size it to occupy the whole cell.
> >>
> >> Nick
> >>
> >> ---------------------------------------------------------------------
> >> 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
> >>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re[2]: Export an image as PNG to a particular cell in Excel using POI.
Posted by Yegor Kozlov <ye...@dinom.ru>.
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/usermodel/examples/OfficeDrawing.java
Yegor
> Hi Prachi,
> Could you share the fragment of your code where you put the image into the
> .XLS file?
> I have to do the same with template files. I wanna change keys by images in
> specific cells of the file, but I didn't find how to do it yet.
> For me, doesn't matter if I will loose an original image, because I won't
> have any image into the cell. Actually, I have to create another one file
> with the image (in the new file) instead of the key (that existed in the
> original file).
> Thanks in advance!
> Luiz
> On 10/12/07, prachi.narkar@contractor.thomson.com <
> prachi.narkar@contractor.thomson.com> wrote:
>>
>> Thanks for your prompt reply!
>> Yes, the positioning can be achieved, but then as the documentation says,
>> that any existing image would be erased with the new image.
>>
>> What I need is that corrsponding to every record (row) in the Excel sheet,
>> I would need an image to be exported.
>> But with the limitation that it would erase an existing image, wouldnt the
>> existing images exported be erased?
>> Say I export an image for record 1 and for record 2 the previous image
>> would be erased and new image added?
>>
>> -Prachi
>>
>> ________________________________
>>
>> From: Nick Burch [mailto:nick@torchbox.com]
>> Sent: Fri 12/10/2007 11:29
>> To: POI Users List
>> Subject: Re: Export an image as PNG to a particular cell in Excel using
>> POI.
>>
>>
>>
>> On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
>> > I would like to know if we can export an image as PNG to a particular
>> > cell in Excel using POI.
>>
>> I don't think you can actually put an image as the real contents of a cell
>> (it's just not something the excel format supports). I think all you can
>> do is add in an image, then position/size it to occupy the whole cell.
>>
>> Nick
>>
>> ---------------------------------------------------------------------
>> 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
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Export an image as PNG to a particular cell in Excel using POI.
Posted by Luiz Antonio Falaguasta Barbosa <lu...@gwe.com.br>.
Understood.
So, to do the same with .DOC and .PPT files is not possible, right?
Is it needed a lot of time to do this in the HPWF and HLSF?
I need something like that, but I think I need some help to put it into POI.
Thanks in advance!
Luiz
On 10/16/07, prachi.narkar@contractor.thomson.com <
prachi.narkar@contractor.thomson.com> wrote:
>
> Hi,
>
> I am attaching a simple servlet that exports image to excel file.
> Please note that you will need POI 3.0.1 version of jar.
> Hope this is useful!
>
> Regards,
> Prachi
>
> ________________________________
>
> From: Luiz Antonio Falaguasta Barbosa [mailto:luiz.barbosa@gwe.com.br]
> Sent: Mon 15/10/2007 12:42
> To: POI Users List
> Subject: Re: Export an image as PNG to a particular cell in Excel using
> POI.
>
>
>
> Hi Prachi,
>
> Could you share the fragment of your code where you put the image into the
> .XLS file?
>
> I have to do the same with template files. I wanna change keys by images
> in
> specific cells of the file, but I didn't find how to do it yet.
>
> For me, doesn't matter if I will loose an original image, because I won't
> have any image into the cell. Actually, I have to create another one file
> with the image (in the new file) instead of the key (that existed in the
> original file).
>
> Thanks in advance!
>
> Luiz
>
> On 10/12/07, prachi.narkar@contractor.thomson.com <
> prachi.narkar@contractor.thomson.com> wrote:
> >
> > Thanks for your prompt reply!
> > Yes, the positioning can be achieved, but then as the documentation
> says,
> > that any existing image would be erased with the new image.
> >
> > What I need is that corrsponding to every record (row) in the Excel
> sheet,
> > I would need an image to be exported.
> > But with the limitation that it would erase an existing image, wouldnt
> the
> > existing images exported be erased?
> > Say I export an image for record 1 and for record 2 the previous image
> > would be erased and new image added?
> >
> > -Prachi
> >
> > ________________________________
> >
> > From: Nick Burch [mailto:nick@torchbox.com]
> > Sent: Fri 12/10/2007 11:29
> > To: POI Users List
> > Subject: Re: Export an image as PNG to a particular cell in Excel using
> > POI.
> >
> >
> >
> > On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
> > > I would like to know if we can export an image as PNG to a particular
> > > cell in Excel using POI.
> >
> > I don't think you can actually put an image as the real contents of a
> cell
> > (it's just not something the excel format supports). I think all you can
> > do is add in an image, then position/size it to occupy the whole cell.
> >
> > Nick
> >
> > ---------------------------------------------------------------------
> > 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
> >
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
RE: Export an image as PNG to a particular cell in Excel using POI.
Posted by pr...@contractor.thomson.com.
Hi,
I am attaching a simple servlet that exports image to excel file.
Please note that you will need POI 3.0.1 version of jar.
Hope this is useful!
Regards,
Prachi
________________________________
From: Luiz Antonio Falaguasta Barbosa [mailto:luiz.barbosa@gwe.com.br]
Sent: Mon 15/10/2007 12:42
To: POI Users List
Subject: Re: Export an image as PNG to a particular cell in Excel using POI.
Hi Prachi,
Could you share the fragment of your code where you put the image into the
.XLS file?
I have to do the same with template files. I wanna change keys by images in
specific cells of the file, but I didn't find how to do it yet.
For me, doesn't matter if I will loose an original image, because I won't
have any image into the cell. Actually, I have to create another one file
with the image (in the new file) instead of the key (that existed in the
original file).
Thanks in advance!
Luiz
On 10/12/07, prachi.narkar@contractor.thomson.com <
prachi.narkar@contractor.thomson.com> wrote:
>
> Thanks for your prompt reply!
> Yes, the positioning can be achieved, but then as the documentation says,
> that any existing image would be erased with the new image.
>
> What I need is that corrsponding to every record (row) in the Excel sheet,
> I would need an image to be exported.
> But with the limitation that it would erase an existing image, wouldnt the
> existing images exported be erased?
> Say I export an image for record 1 and for record 2 the previous image
> would be erased and new image added?
>
> -Prachi
>
> ________________________________
>
> From: Nick Burch [mailto:nick@torchbox.com]
> Sent: Fri 12/10/2007 11:29
> To: POI Users List
> Subject: Re: Export an image as PNG to a particular cell in Excel using
> POI.
>
>
>
> On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
> > I would like to know if we can export an image as PNG to a particular
> > cell in Excel using POI.
>
> I don't think you can actually put an image as the real contents of a cell
> (it's just not something the excel format supports). I think all you can
> do is add in an image, then position/size it to occupy the whole cell.
>
> Nick
>
> ---------------------------------------------------------------------
> 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: Export an image as PNG to a particular cell in Excel using POI.
Posted by Luiz Antonio Falaguasta Barbosa <lu...@gwe.com.br>.
Hi Prachi,
Could you share the fragment of your code where you put the image into the
.XLS file?
I have to do the same with template files. I wanna change keys by images in
specific cells of the file, but I didn't find how to do it yet.
For me, doesn't matter if I will loose an original image, because I won't
have any image into the cell. Actually, I have to create another one file
with the image (in the new file) instead of the key (that existed in the
original file).
Thanks in advance!
Luiz
On 10/12/07, prachi.narkar@contractor.thomson.com <
prachi.narkar@contractor.thomson.com> wrote:
>
> Thanks for your prompt reply!
> Yes, the positioning can be achieved, but then as the documentation says,
> that any existing image would be erased with the new image.
>
> What I need is that corrsponding to every record (row) in the Excel sheet,
> I would need an image to be exported.
> But with the limitation that it would erase an existing image, wouldnt the
> existing images exported be erased?
> Say I export an image for record 1 and for record 2 the previous image
> would be erased and new image added?
>
> -Prachi
>
> ________________________________
>
> From: Nick Burch [mailto:nick@torchbox.com]
> Sent: Fri 12/10/2007 11:29
> To: POI Users List
> Subject: Re: Export an image as PNG to a particular cell in Excel using
> POI.
>
>
>
> On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
> > I would like to know if we can export an image as PNG to a particular
> > cell in Excel using POI.
>
> I don't think you can actually put an image as the real contents of a cell
> (it's just not something the excel format supports). I think all you can
> do is add in an image, then position/size it to occupy the whole cell.
>
> Nick
>
> ---------------------------------------------------------------------
> 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: Re[4]: Export an image as PNG to a particular cell in Excel using POI.
Posted by Christophe Charles <ch...@gmail.com>.
Thank Yegor !
I've change my eclipse.ini file like :
-vmargs
-Xms512m
-Xmx768m
And I put this values in the Eclipse'menu :
Window/Preferences/Tomcat/Parameters JVM/
Now it seems work fine !
Regards
Chris
2007/10/18, Yegor Kozlov <ye...@dinom.ru>:
>
> OutOfMemory when creating large excel files is a limitation of POI.
> The only way to solve the OutOfMemory problem is to provide larger
> heap value.
> We have plans to improve it. Hope to get it fixed in future versions
> of POI.
>
> Yegor
>
> > Hi Yehor,
>
> > I have to build a workbook with one sheet and 16500rows x 30cells, but
> it's
> > don't !
>
> > This is a servlet' Tomcat and around 10000rows the process stop (out of
> > memory).
>
> > I'm use :
> > Eclipse 3.2
> > Tomcat 5.0.18
> > Win XP Pro 2002 SP2
> > 3 Ghz 1Go
>
> > My JVM configuration :
> > -vmargs
> > -Xms512m
> > -Xmx512m
> > -XX:PermSize=512m
> > -XX:MaxPermSize=512m
>
>
>
> > The code :
>
> > public void addAResulset(ArrayList aResulset, int startLine) {
> > for (int line = 0; line < aResulset.size(); line++) {
> > addRow((ArrayList)aResulset.get(line), startLine + line, null);
> > }
> > }
>
> > public void addRow(ArrayList rowData, int numLigne, HSSFCellStyle
> cellStyle)
> > {
> > SimpleDateFormat formatDateFR = new SimpleDateFormat("dd/MM/yyyy");
> > Timestamp typeDate = new Timestamp(0);
> > BigDecimal typeNumber = new BigDecimal(0);
>
> > HSSFRow row = null;
> > HSSFCell cell = null;
>
> > row = this._sheet.createRow(numLigne);
>
> > for (int i = 0; i < rowData.size (); i++) {
> > cell = row.createCell((short) i);
>
> > if (cellStyle != null) {
> > cell.setCellStyle(cellStyle);
> > }
>
> > Object object = (Object)rowData.get(i);
>
> > if (object != null) {
> > if (object.getClass().isInstance(typeDate)) {
> > Date date = (Date)object;
> > cell.setCellValue(new HSSFRichTextString(formatDateFR.format
> > (date)));
> > } else if (object.getClass().isInstance(typeNumber)) {
> > Number number = (Number)object;
> > cell.setCellValue(number.doubleValue());
> > } else {
> > cell.setCellValue(new
> > HSSFRichTextString(((String)object).trim()));
> > }
> > } else {
> > cell.setCellValue(new HSSFRichTextString(""));
> > }
> > }
> > }
>
>
> > Any idea or information ?
> > Thanks
>
> > Regards
>
> > Chris
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re[4]: Export an image as PNG to a particular cell in Excel using POI.
Posted by Yegor Kozlov <ye...@dinom.ru>.
OutOfMemory when creating large excel files is a limitation of POI.
The only way to solve the OutOfMemory problem is to provide larger
heap value.
We have plans to improve it. Hope to get it fixed in future versions
of POI.
Yegor
> Hi Yehor,
> I have to build a workbook with one sheet and 16500rows x 30cells, but it's
> don't !
> This is a servlet' Tomcat and around 10000rows the process stop (out of
> memory).
> I'm use :
> Eclipse 3.2
> Tomcat 5.0.18
> Win XP Pro 2002 SP2
> 3 Ghz 1Go
> My JVM configuration :
> -vmargs
> -Xms512m
> -Xmx512m
> -XX:PermSize=512m
> -XX:MaxPermSize=512m
> The code :
> public void addAResulset(ArrayList aResulset, int startLine) {
> for (int line = 0; line < aResulset.size(); line++) {
> addRow((ArrayList)aResulset.get(line), startLine + line, null);
> }
> }
> public void addRow(ArrayList rowData, int numLigne, HSSFCellStyle cellStyle)
> {
> SimpleDateFormat formatDateFR = new SimpleDateFormat("dd/MM/yyyy");
> Timestamp typeDate = new Timestamp(0);
> BigDecimal typeNumber = new BigDecimal(0);
> HSSFRow row = null;
> HSSFCell cell = null;
> row = this._sheet.createRow(numLigne);
> for (int i = 0; i < rowData.size(); i++) {
> cell = row.createCell((short) i);
> if (cellStyle != null) {
> cell.setCellStyle(cellStyle);
> }
> Object object = (Object)rowData.get(i);
> if (object != null) {
> if (object.getClass().isInstance(typeDate)) {
> Date date = (Date)object;
> cell.setCellValue(new HSSFRichTextString(formatDateFR.format
> (date)));
> } else if (object.getClass().isInstance(typeNumber)) {
> Number number = (Number)object;
> cell.setCellValue(number.doubleValue());
> } else {
> cell.setCellValue(new
> HSSFRichTextString(((String)object).trim()));
> }
> } else {
> cell.setCellValue(new HSSFRichTextString(""));
> }
> }
> }
> Any idea or information ?
> Thanks
> Regards
> Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Re[2]: Export an image as PNG to a particular cell in Excel using POI.
Posted by Christophe Charles <ch...@gmail.com>.
Hi Yehor,
I have to build a workbook with one sheet and 16500rows x 30cells, but it's
don't !
This is a servlet' Tomcat and around 10000rows the process stop (out of
memory).
I'm use :
Eclipse 3.2
Tomcat 5.0.18
Win XP Pro 2002 SP2
3 Ghz 1Go
My JVM configuration :
-vmargs
-Xms512m
-Xmx512m
-XX:PermSize=512m
-XX:MaxPermSize=512m
The code :
public void addAResulset(ArrayList aResulset, int startLine) {
for (int line = 0; line < aResulset.size(); line++) {
addRow((ArrayList)aResulset.get(line), startLine + line, null);
}
}
public void addRow(ArrayList rowData, int numLigne, HSSFCellStyle cellStyle)
{
SimpleDateFormat formatDateFR = new SimpleDateFormat("dd/MM/yyyy");
Timestamp typeDate = new Timestamp(0);
BigDecimal typeNumber = new BigDecimal(0);
HSSFRow row = null;
HSSFCell cell = null;
row = this._sheet.createRow(numLigne);
for (int i = 0; i < rowData.size(); i++) {
cell = row.createCell((short) i);
if (cellStyle != null) {
cell.setCellStyle(cellStyle);
}
Object object = (Object)rowData.get(i);
if (object != null) {
if (object.getClass().isInstance(typeDate)) {
Date date = (Date)object;
cell.setCellValue(new HSSFRichTextString(formatDateFR.format
(date)));
} else if (object.getClass().isInstance(typeNumber)) {
Number number = (Number)object;
cell.setCellValue(number.doubleValue());
} else {
cell.setCellValue(new
HSSFRichTextString(((String)object).trim()));
}
} else {
cell.setCellValue(new HSSFRichTextString(""));
}
}
}
Any idea or information ?
Thanks
Regards
Chris
Re[6]: Export an image as PNG to a particular cell in Excel using POI.
Posted by Yegor Kozlov <ye...@dinom.ru>.
Prachi, you have to options:
(a) download the latest build and try HSSFPicture.resize(). It will
resize the image to its original dimensions.
(b) Specify the image position when constructing HSSFClientAnchor. In
this case you set the upper-left and bottom-right coordinates.
From your previous post I understood you wanted to fit the image in 1
column and 1 row. If so, the anchor should be
new HSSFClientAnchor(0,0,0,0,(short)2,i,(short)5,i+1).
anchor.setAnchorType() does not affect the image position. To better
understand it, insert an image with anchor.setAnchorType(1) and open the
workbook in Excel. Go to the Format Picture dialog, Properties tab.
Here is your setting. Try to resize columns. Try to change this
property and see how Excel behaves when you resize columns.
Regards,
Yegor
> Hi,
>
> I will try getting the latest build and try this method.
> thanks!
>
> The sample code I am currently using is:
>
> try{
> HSSFWorkbook wb = new HSSFWorkbook();
> HSSFSheet sheet = wb.createSheet("new sheet");
>
> sheet.setColumnWidth((short)2, (short) 5250);
> HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
> HSSFClientAnchor anchor;
>
> for(int i=0;i<2;i++){
>
> HSSFRow row = sheet.createRow((short)i);
> HSSFCell cell = row.createCell((short)0);
>
> row.createCell((short)0).setCellValue(i+1);
> row.createCell((short)1).setCellValue("Compound " + (i+1));
> row.setHeight((short)2500);
> //sheet.addMergedRegion(new
> Region(i,(short)2,i,(short)5));
>
> anchor = new
> HSSFClientAnchor(0,0,0,0,(short)2,i,(short)5,i);
> anchor.setAnchorType( 3 ); --tried using 2 as well!
> patriarch.createPicture(anchor, loadPicture( <image path>, wb ));
> }
>
> }catch(IOException ex)
> {
> System.out.println("Error!!");
> }
>
> This resizes the image and extends as per the row height and column width.
>
> Let me know if theres something I am missing or doing incorrectly.
>
> Regards,
> PRachi
> ________________________________
> From: Yegor Kozlov [mailto:yegor@dinom.ru]
> Sent: Fri 19/10/2007 13:58
> To: POI Users List
> Subject: Re[4]: Export an image as PNG to a particular cell in Excel using POI.
>>
>> I could not find HSSFPicture.resize() .
>> May be I dont have the updated APIs.
> You need to build POI from svn or download a daily build from
> http://encore.torchbox.com/poi-svn-build/.
>> But would this retain the original size of the image?
> Yes, HSSFPicture.resize() adjusts anchor to the actual image
> dimensions.
>>
>> Even if I set the anchor type to 2 or 3, it does resize the image
>> and extends to the number of columns specified.
>> Am I missing something?
>>
> Would you provide sample code?
> Yegor
>> ________________________________
>> From: Yegor Kozlov [mailto:yegor@dinom.ru]
>> Sent: Tue 16/10/2007 15:04
>> To: POI Users List
>> Subject: Re[2]: Export an image as PNG to a particular cell in Excel using POI.
>> I've recently added HSSFPicture.resize() which resizes image anchor to
>> the actual width and height. It might be useful to you.
>> If you want to set the anchor manually you need to change the anchor
>> type as follows:
>> anchor.setAnchorType(2); //Move but don't size with cells
>> By default anchor type=0 which means "Move and size with Cells" and it
>> is what is happening with your image: it gets resized when you resize
>> the columns.
>>> Also, if I need to fit the image to just 1 column and 1row and not
>>> span multiple cols/rows, how should the following API be used:
>>> anchor = new HSSFClientAnchor(0,0,0,255,(short)2,2,(short)4,7);
>>> I am quite confused on the x/y coordinates.
>> The first four values define the offset in the top-left and
>> bottom-right cells. If you need to fit an image to exactly 1 column
>> and 1 row leave them 0. The next four values are 0-based coordinates of
>> the top-left and bottom-right cells.
>> So, to put an image in 1x1 cell the anchor would be
>> new HSSFClientAnchor(0,0,0,0,(short)col1, row1,(short)(col1+1), row+1);
>> where col1 and row1 are coordinates of the top-left cell.
>> Regards,
>> Yegor
>>> Any help on its usage would be appreciated.
>>>
>>> Thanks,
>>> Prachi
>>> ________________________________
>>> From: Nick Burch [mailto:nick@torchbox.com]
>>> Sent: Mon 15/10/2007 11:19
>>> To: POI Users List
>>> Subject: RE: Export an image as PNG to a particular cell in Excel using POI.
>>> On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
>>>> Yes, the positioning can be achieved, but then as the documentation
>>>> says, that any existing image would be erased with the new image.
>>> I didn't write the code so I can't be sure, but I have a feeling that the
>>> warning relates to images already in the file when you start, not ones you
>>> add in. So, you ought to be fine to open the file up, and add several
>>> images to it before closing.
>>> Nick
>>> ---------------------------------------------------------------------
>>> 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
> ---------------------------------------------------------------------
> 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: Re[4]: Export an image as PNG to a particular cell in Excel using POI.
Posted by pr...@contractor.thomson.com.
Hi,
I will try getting the latest build and try this method.
thanks!
The sample code I am currently using is:
try{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
sheet.setColumnWidth((short)2, (short) 5250);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor;
for(int i=0;i<2;i++){
HSSFRow row = sheet.createRow((short)i);
HSSFCell cell = row.createCell((short)0);
row.createCell((short)0).setCellValue(i+1);
row.createCell((short)1).setCellValue("Compound " + (i+1));
row.setHeight((short)2500);
//sheet.addMergedRegion(new Region(i,(short)2,i,(short)5));
anchor = new HSSFClientAnchor(0,0,0,0,(short)2,i,(short)5,i);
anchor.setAnchorType( 3 ); --tried using 2 as well!
patriarch.createPicture(anchor, loadPicture( <image path>, wb ));
}
}catch(IOException ex)
{
System.out.println("Error!!");
}
This resizes the image and extends as per the row height and column width.
Let me know if theres something I am missing or doing incorrectly.
Regards,
PRachi
________________________________
From: Yegor Kozlov [mailto:yegor@dinom.ru]
Sent: Fri 19/10/2007 13:58
To: POI Users List
Subject: Re[4]: Export an image as PNG to a particular cell in Excel using POI.
>
> I could not find HSSFPicture.resize() .
> May be I dont have the updated APIs.
You need to build POI from svn or download a daily build from http://encore.torchbox.com/poi-svn-build/.
> But would this retain the original size of the image?
Yes, HSSFPicture.resize() adjusts anchor to the actual image
dimensions.
>
> Even if I set the anchor type to 2 or 3, it does resize the image
> and extends to the number of columns specified.
> Am I missing something?
>
Would you provide sample code?
Yegor
> ________________________________
> From: Yegor Kozlov [mailto:yegor@dinom.ru]
> Sent: Tue 16/10/2007 15:04
> To: POI Users List
> Subject: Re[2]: Export an image as PNG to a particular cell in Excel using POI.
> I've recently added HSSFPicture.resize() which resizes image anchor to
> the actual width and height. It might be useful to you.
> If you want to set the anchor manually you need to change the anchor
> type as follows:
> anchor.setAnchorType(2); //Move but don't size with cells
> By default anchor type=0 which means "Move and size with Cells" and it
> is what is happening with your image: it gets resized when you resize
> the columns.
>> Also, if I need to fit the image to just 1 column and 1row and not
>> span multiple cols/rows, how should the following API be used:
>> anchor = new HSSFClientAnchor(0,0,0,255,(short)2,2,(short)4,7);
>> I am quite confused on the x/y coordinates.
> The first four values define the offset in the top-left and
> bottom-right cells. If you need to fit an image to exactly 1 column
> and 1 row leave them 0. The next four values are 0-based coordinates of
> the top-left and bottom-right cells.
> So, to put an image in 1x1 cell the anchor would be
> new HSSFClientAnchor(0,0,0,0,(short)col1, row1,(short)(col1+1), row+1);
> where col1 and row1 are coordinates of the top-left cell.
> Regards,
> Yegor
>> Any help on its usage would be appreciated.
>>
>> Thanks,
>> Prachi
>> ________________________________
>> From: Nick Burch [mailto:nick@torchbox.com]
>> Sent: Mon 15/10/2007 11:19
>> To: POI Users List
>> Subject: RE: Export an image as PNG to a particular cell in Excel using POI.
>> On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
>>> Yes, the positioning can be achieved, but then as the documentation
>>> says, that any existing image would be erased with the new image.
>> I didn't write the code so I can't be sure, but I have a feeling that the
>> warning relates to images already in the file when you start, not ones you
>> add in. So, you ought to be fine to open the file up, and add several
>> images to it before closing.
>> Nick
>> ---------------------------------------------------------------------
>> 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
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re[4]: Export an image as PNG to a particular cell in Excel using POI.
Posted by Yegor Kozlov <ye...@dinom.ru>.
>
> I could not find HSSFPicture.resize() .
> May be I dont have the updated APIs.
You need to build POI from svn or download a daily build from http://encore.torchbox.com/poi-svn-build/.
> But would this retain the original size of the image?
Yes, HSSFPicture.resize() adjusts anchor to the actual image
dimensions.
>
> Even if I set the anchor type to 2 or 3, it does resize the image
> and extends to the number of columns specified.
> Am I missing something?
>
Would you provide sample code?
Yegor
> ________________________________
> From: Yegor Kozlov [mailto:yegor@dinom.ru]
> Sent: Tue 16/10/2007 15:04
> To: POI Users List
> Subject: Re[2]: Export an image as PNG to a particular cell in Excel using POI.
> I've recently added HSSFPicture.resize() which resizes image anchor to
> the actual width and height. It might be useful to you.
> If you want to set the anchor manually you need to change the anchor
> type as follows:
> anchor.setAnchorType(2); //Move but don't size with cells
> By default anchor type=0 which means "Move and size with Cells" and it
> is what is happening with your image: it gets resized when you resize
> the columns.
>> Also, if I need to fit the image to just 1 column and 1row and not
>> span multiple cols/rows, how should the following API be used:
>> anchor = new HSSFClientAnchor(0,0,0,255,(short)2,2,(short)4,7);
>> I am quite confused on the x/y coordinates.
> The first four values define the offset in the top-left and
> bottom-right cells. If you need to fit an image to exactly 1 column
> and 1 row leave them 0. The next four values are 0-based coordinates of
> the top-left and bottom-right cells.
> So, to put an image in 1x1 cell the anchor would be
> new HSSFClientAnchor(0,0,0,0,(short)col1, row1,(short)(col1+1), row+1);
> where col1 and row1 are coordinates of the top-left cell.
> Regards,
> Yegor
>> Any help on its usage would be appreciated.
>>
>> Thanks,
>> Prachi
>> ________________________________
>> From: Nick Burch [mailto:nick@torchbox.com]
>> Sent: Mon 15/10/2007 11:19
>> To: POI Users List
>> Subject: RE: Export an image as PNG to a particular cell in Excel using POI.
>> On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
>>> Yes, the positioning can be achieved, but then as the documentation
>>> says, that any existing image would be erased with the new image.
>> I didn't write the code so I can't be sure, but I have a feeling that the
>> warning relates to images already in the file when you start, not ones you
>> add in. So, you ought to be fine to open the file up, and add several
>> images to it before closing.
>> Nick
>> ---------------------------------------------------------------------
>> 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
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Re[2]: Export an image as PNG to a particular cell in Excel using POI.
Posted by pr...@contractor.thomson.com.
Hi,
I could not find HSSFPicture.resize() .
May be I dont have the updated APIs.
But would this retain the original size of the image?
Even if I set the anchor type to 2 or 3, it does resize the image and extends to the number of columns specified.
Am I missing something?
-Prachi
________________________________
From: Yegor Kozlov [mailto:yegor@dinom.ru]
Sent: Tue 16/10/2007 15:04
To: POI Users List
Subject: Re[2]: Export an image as PNG to a particular cell in Excel using POI.
I've recently added HSSFPicture.resize() which resizes image anchor to
the actual width and height. It might be useful to you.
If you want to set the anchor manually you need to change the anchor
type as follows:
anchor.setAnchorType(2); //Move but don't size with cells
By default anchor type=0 which means "Move and size with Cells" and it
is what is happening with your image: it gets resized when you resize
the columns.
> Also, if I need to fit the image to just 1 column and 1row and not
> span multiple cols/rows, how should the following API be used:
> anchor = new HSSFClientAnchor(0,0,0,255,(short)2,2,(short)4,7);
> I am quite confused on the x/y coordinates.
The first four values define the offset in the top-left and
bottom-right cells. If you need to fit an image to exactly 1 column
and 1 row leave them 0. The next four values are 0-based coordinates of
the top-left and bottom-right cells.
So, to put an image in 1x1 cell the anchor would be
new HSSFClientAnchor(0,0,0,0,(short)col1, row1,(short)(col1+1), row+1);
where col1 and row1 are coordinates of the top-left cell.
Regards,
Yegor
> Any help on its usage would be appreciated.
>
> Thanks,
> Prachi
> ________________________________
> From: Nick Burch [mailto:nick@torchbox.com]
> Sent: Mon 15/10/2007 11:19
> To: POI Users List
> Subject: RE: Export an image as PNG to a particular cell in Excel using POI.
> On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
>> Yes, the positioning can be achieved, but then as the documentation
>> says, that any existing image would be erased with the new image.
> I didn't write the code so I can't be sure, but I have a feeling that the
> warning relates to images already in the file when you start, not ones you
> add in. So, you ought to be fine to open the file up, and add several
> images to it before closing.
> Nick
> ---------------------------------------------------------------------
> 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[2]: Export an image as PNG to a particular cell in Excel using POI.
Posted by Yegor Kozlov <ye...@dinom.ru>.
I've recently added HSSFPicture.resize() which resizes image anchor to
the actual width and height. It might be useful to you.
If you want to set the anchor manually you need to change the anchor
type as follows:
anchor.setAnchorType(2); //Move but don't size with cells
By default anchor type=0 which means "Move and size with Cells" and it
is what is happening with your image: it gets resized when you resize
the columns.
> Also, if I need to fit the image to just 1 column and 1row and not
> span multiple cols/rows, how should the following API be used:
> anchor = new HSSFClientAnchor(0,0,0,255,(short)2,2,(short)4,7);
> I am quite confused on the x/y coordinates.
The first four values define the offset in the top-left and
bottom-right cells. If you need to fit an image to exactly 1 column
and 1 row leave them 0. The next four values are 0-based coordinates of
the top-left and bottom-right cells.
So, to put an image in 1x1 cell the anchor would be
new HSSFClientAnchor(0,0,0,0,(short)col1, row1,(short)(col1+1), row+1);
where col1 and row1 are coordinates of the top-left cell.
Regards,
Yegor
> Any help on its usage would be appreciated.
>
> Thanks,
> Prachi
> ________________________________
> From: Nick Burch [mailto:nick@torchbox.com]
> Sent: Mon 15/10/2007 11:19
> To: POI Users List
> Subject: RE: Export an image as PNG to a particular cell in Excel using POI.
> On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
>> Yes, the positioning can be achieved, but then as the documentation
>> says, that any existing image would be erased with the new image.
> I didn't write the code so I can't be sure, but I have a feeling that the
> warning relates to images already in the file when you start, not ones you
> add in. So, you ought to be fine to open the file up, and add several
> images to it before closing.
> Nick
> ---------------------------------------------------------------------
> 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: Export an image as PNG to a particular cell in Excel using POI.
Posted by pr...@contractor.thomson.com.
Thanks!
I tried exporting multiple images, with different row/col position and it exports them fine.
But I tried to fit the image to single column and row - i.e. resize row and column to fit the image, but somehow the row does not resize.
Also, the image stretches as much as I increase the column width.
I have used the following code:
sheet.setColumnWidth((short)2, (short)7500);
sheet.setDefaultRowHeight((short) 3000);
Also, if I need to fit the image to just 1 column and 1row and not span multiple cols/rows, how should the following API be used:
anchor = new HSSFClientAnchor(0,0,0,255,(short)2,2,(short)4,7);
I am quite confused on the x/y coordinates.
Any help on its usage would be appreciated.
Thanks,
Prachi
________________________________
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Mon 15/10/2007 11:19
To: POI Users List
Subject: RE: Export an image as PNG to a particular cell in Excel using POI.
On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
> Yes, the positioning can be achieved, but then as the documentation
> says, that any existing image would be erased with the new image.
I didn't write the code so I can't be sure, but I have a feeling that the
warning relates to images already in the file when you start, not ones you
add in. So, you ought to be fine to open the file up, and add several
images to it before closing.
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Export an image as PNG to a particular cell in Excel using POI.
Posted by Nick Burch <ni...@torchbox.com>.
On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
> Yes, the positioning can be achieved, but then as the documentation
> says, that any existing image would be erased with the new image.
I didn't write the code so I can't be sure, but I have a feeling that the
warning relates to images already in the file when you start, not ones you
add in. So, you ought to be fine to open the file up, and add several
images to it before closing.
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Export an image as PNG to a particular cell in Excel using POI.
Posted by pr...@contractor.thomson.com.
Thanks for your prompt reply!
Yes, the positioning can be achieved, but then as the documentation says, that any existing image would be erased with the new image.
What I need is that corrsponding to every record (row) in the Excel sheet, I would need an image to be exported.
But with the limitation that it would erase an existing image, wouldnt the existing images exported be erased?
Say I export an image for record 1 and for record 2 the previous image would be erased and new image added?
-Prachi
________________________________
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Fri 12/10/2007 11:29
To: POI Users List
Subject: Re: Export an image as PNG to a particular cell in Excel using POI.
On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
> I would like to know if we can export an image as PNG to a particular
> cell in Excel using POI.
I don't think you can actually put an image as the real contents of a cell
(it's just not something the excel format supports). I think all you can
do is add in an image, then position/size it to occupy the whole cell.
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Export an image as PNG to a particular cell in Excel using POI.
Posted by Nick Burch <ni...@torchbox.com>.
On Fri, 12 Oct 2007, prachi.narkar@contractor.thomson.com wrote:
> I would like to know if we can export an image as PNG to a particular
> cell in Excel using POI.
I don't think you can actually put an image as the real contents of a cell
(it's just not something the excel format supports). I think all you can
do is add in an image, then position/size it to occupy the whole cell.
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org