You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Rauer, Matthias (EXT)" <ma...@siemens.com> on 2009/08/13 12:27:07 UTC
Adding Image in Sheet while Diagram already exists in other does
not work
Hello,
I made an .xls-File with several sheets. One sheet contains a diagram. When I add a picture in the first sheet (without diagram),
it will not be there, when I open the file again with Excel 2003.
If I remove the diagrams, I can see the added picture in the first sheet.
I hope anyone can help me.
Same problem with poi3.2 final and poi3.5 beta6.
Thanks,
Matthias
Re: AW: Adding Image in Sheet while Diagram already exists in other
does not work
Posted by MSB <ma...@tiscali.co.uk>.
Thanks Matthias,
Have downloaded the .zip file and checked to make sure everything is OK.
Later this evening, I will play with the code to see if anything jumps out
at me. As always, will post if I see anything.
Yours
Mark B
Rauer, Matthias (EXT) wrote:
>
> Hi Mark,
>
> a made a small example. http://www.filesavr.com/poitest
>
> I think, there are special cells in the first sheet.
> But I don't know why the picture is there if I remove the diagram.
>
> When I added a new fresh clean sheet, it works, too!
>
> The example also includes the output files from the little test program.
> You can generate the Excel files again and you will get the same output
> again.
>
> Thanks,
> Matthias
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: MSB [mailto:markbrdsly@tiscali.co.uk]
> Gesendet: Donnerstag, 13. August 2009 13:24
> An: user@poi.apache.org
> Betreff: Re: Adding Image in Sheet while Diagram already exists in other
> does not work
>
>
> Hello Mathias,
>
> Could we see the code you are running to insert the picture into the
> workbook please. Further, if it is not too big and if you can post it to
> an
> external entity, could you attach the workbook you are using as well
> please
> so that we can see where the diagrams are, etc.
>
> Yours
>
> Mark B
>
>
> Rauer, Matthias (EXT) wrote:
>>
>> Hello,
>>
>> I made an .xls-File with several sheets. One sheet contains a diagram.
>> When I add a picture in the first sheet (without diagram),
>> it will not be there, when I open the file again with Excel 2003.
>>
>> If I remove the diagrams, I can see the added picture in the first sheet.
>>
>> I hope anyone can help me.
>>
>> Same problem with poi3.2 final and poi3.5 beta6.
>>
>> Thanks,
>> Matthias
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24953205.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
>
>
>
--
View this message in context: http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24955314.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
AW: AW: Adding Image in Sheet while Diagram already exists in other
does not work
Posted by "Rauer, Matthias (EXT)" <ma...@siemens.com>.
Hi Mark,
I tested it on a clean new sheet and it worked. That is like your test, with creating a new sheet and adding the picture.
But here we use the Excel-Sheet as a template, so other people (non programmer) can change the format and layout.
They refer to an other sheet, where I only put the values. So I do not need any information about the first sheet. But we need all information including the picture in the first sheet for printing.
Normally there is no direct interaction on the first sheet and the diagram sheet.
We have 3 sheets.
1. printing layout with picture (=> small cells, references, number formatting, font)
2. diagram and table
3. unformatted values
I reduced the original Sheet until, there is no internal information anymore and
deleted the values sheet for the example.
Some years ago I used POI, but got problems with layout information and special characters (alpha, beta and other formula characters) when I add values with POI on that sheet. When I loaded the sheet again with Excelall layout information was gone :-(
But I haven't tested it again. I think one sheet for values only and an other sheet for layouting is a good way.: model and view(s)
Ok, I think I have to search for the cell in the first sheet to find out the problem.
When I removed all formatting information on the first sheet, it also works.
Thanks for helping,
Matthias
-----Ursprüngliche Nachricht-----
Von: MSB [mailto:markbrdsly@tiscali.co.uk]
Gesendet: Donnerstag, 13. August 2009 21:06
An: user@poi.apache.org
Betreff: Re: AW: Adding Image in Sheet while Diagram already exists in other does not work
Hello Matthias,
Sorry to say that I do not have an answer for you after playing with some
code, merely more questions.
Your 'Sheet1' was, I noticed, quite complex - there are lots of merged cells
and the column widths have all been reduced - and so I began to wonder if
there could be any strange interactions taking place between the cells on
Sheet1 and those on the diagram sheet. As a quick test, I ran the folowing;
public class POITestImage {
public static void main(final String[] args) throws Exception {
String[] filenames = {"C:/temp/POITest/worksheet-sameButWithDiagram.xls"};
for (String filename : filenames) {
// create a new workbook
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
filename));
byte[] bytes =
POITestImage.imageToBytes("C:/temp/POITest/image.png");
int pictureIdx = workbook.addPicture(bytes,
HSSFWorkbook.PICTURE_TYPE_PNG);
HSSFSheet sheet_view = workbook.createSheet("Image Sheet");
workbook.setSheetOrder("Image Sheet", 0);
HSSFPatriarch drawing = sheet_view.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor();
anchor.setAnchorType(0);
anchor.setCol1((short) 0);
anchor.setCol2((short) 53);
anchor.setRow1((short) 20);
anchor.setRow2((short) 43);
drawing.createPicture(anchor, pictureIdx);
// save workbook
String file = filename + ".withPicture.xls";
FileOutputStream fileOut = new FileOutputStream(file);
workbook.write(fileOut);
fileOut.close();
}
}
/**
* Loads - reads in and converts into an array of byte(s) - an image
from
* a named file.
*
* @param imageFilename A String that encapsulates the path to and name
* of the file that contains the image which is to
be
* 'loaded'.
* @return An array of type byte that contains the raw data of the named
* image.
* @throws java.io.FileNotFoundException Thrown if it was not possible
to
* open the specified file.
* @throws java.io.IOException Thrown if reading the file failed or was
* interrupted.
*/
private static byte[] imageToBytes(String imageFilename)
throws FileNotFoundException,
IOException {
File imageFile = null;
FileInputStream fis = null;
ByteArrayOutputStream bos = null;
int read = 0;
try {
imageFile = new File(imageFilename);
fis = new FileInputStream(imageFile);
bos = new ByteArrayOutputStream();
while((read = fis.read()) != -1) {
bos.write(read);
}
return(bos.toByteArray());
}
finally {
if(fis != null) {
try {
fis.close();
fis = null;
}
catch(IOException ioEx) {
// Nothing to do here
}
}
}
}
}
Ignore the static imageToBytes() method, this stands in the place of your
library code.
As you can see, it creates a new sheet, inserts it into the workbook, moves
that sheet so that it is the first in the book and then adds an image to it.
If you run this, when you open the workbook, you should see that the image
is visible on that first sheet and the diagram is still in position on the
diagram sheet. This is why I wondered about some interaction between Sheet1
and the diagram sheet - even if that thought has no basis in fact.
Following on from this, would it be possible for you to construct Sheet1
using POI code and to then insert the image after that? Having looked at
Sheet1 as it currently stands, I imagine that this could be quite a
challenge but it might - only might - solve the problem. The workbook your
code then took as input would contain only the diagram sheet.
Sorry that does not solve the original problem - in fact it may have clouded
the issue further as it cannot explain why simply adding the chart to the
diagram page could have such dire consequences - but it may point the way
toward a solution. Possibly, you could begin by replacing Sheet1 as it
currently stands with an 'empty' sheet that no work has been performed upon
and seeing if your original code runs successfully. If it does, you could
then modify that Sheet1 until you find the point at which the image cannot
be correctly added.
I will try something like this myself and let you know if I find a solution.
Yours
Mark B
Rauer, Matthias (EXT) wrote:
>
> Hi Mark,
>
> a made a small example. http://www.filesavr.com/poitest
>
> I think, there are special cells in the first sheet.
> But I don't know why the picture is there if I remove the diagram.
>
> When I added a new fresh clean sheet, it works, too!
>
> The example also includes the output files from the little test program.
> You can generate the Excel files again and you will get the same output
> again.
>
> Thanks,
> Matthias
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: MSB [mailto:markbrdsly@tiscali.co.uk]
> Gesendet: Donnerstag, 13. August 2009 13:24
> An: user@poi.apache.org
> Betreff: Re: Adding Image in Sheet while Diagram already exists in other
> does not work
>
>
> Hello Mathias,
>
> Could we see the code you are running to insert the picture into the
> workbook please. Further, if it is not too big and if you can post it to
> an
> external entity, could you attach the workbook you are using as well
> please
> so that we can see where the diagrams are, etc.
>
> Yours
>
> Mark B
>
>
> Rauer, Matthias (EXT) wrote:
>>
>> Hello,
>>
>> I made an .xls-File with several sheets. One sheet contains a diagram.
>> When I add a picture in the first sheet (without diagram),
>> it will not be there, when I open the file again with Excel 2003.
>>
>> If I remove the diagrams, I can see the added picture in the first sheet.
>>
>> I hope anyone can help me.
>>
>> Same problem with poi3.2 final and poi3.5 beta6.
>>
>> Thanks,
>> Matthias
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24953205.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
>
>
>
--
View this message in context: http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24956948.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: AW: AW: Adding Image in Sheet while Diagram already exists in
other does not work
Posted by MSB <ma...@tiscali.co.uk>.
Hello Matthias,
Delighted you managed to find a workaround to this fascinating problem.
Yours
Mark B
Rauer, Matthias (EXT) wrote:
>
> Hi all,
>
> I solved the problem, by inserting a clean new sheet and copying all
> information from the original template sheet. So I got the formatted
> output and the picture together on one sheet.
>
> The orignal sheet was from an other project, their can be some hidden
> information, references.
>
> Thanks for helping,
> Matthias
>
>
> -----Ursprüngliche Nachricht-----
> Von: MSB [mailto:markbrdsly@tiscali.co.uk]
> Gesendet: Donnerstag, 13. August 2009 21:06
> An: user@poi.apache.org
> Betreff: Re: AW: Adding Image in Sheet while Diagram already exists in
> other does not work
>
>
> Hello Matthias,
>
> Sorry to say that I do not have an answer for you after playing with some
> code, merely more questions.
>
> Your 'Sheet1' was, I noticed, quite complex - there are lots of merged
> cells
> and the column widths have all been reduced - and so I began to wonder if
> there could be any strange interactions taking place between the cells on
> Sheet1 and those on the diagram sheet. As a quick test, I ran the
> folowing;
>
> public class POITestImage {
>
> public static void main(final String[] args) throws Exception {
>
> String[] filenames =
> {"C:/temp/POITest/worksheet-sameButWithDiagram.xls"};
>
> for (String filename : filenames) {
> // create a new workbook
> HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
> filename));
>
> byte[] bytes =
> POITestImage.imageToBytes("C:/temp/POITest/image.png");
> int pictureIdx = workbook.addPicture(bytes,
> HSSFWorkbook.PICTURE_TYPE_PNG);
> HSSFSheet sheet_view = workbook.createSheet("Image Sheet");
> workbook.setSheetOrder("Image Sheet", 0);
> HSSFPatriarch drawing = sheet_view.createDrawingPatriarch();
> HSSFClientAnchor anchor = new HSSFClientAnchor();
> anchor.setAnchorType(0);
> anchor.setCol1((short) 0);
> anchor.setCol2((short) 53);
> anchor.setRow1((short) 20);
> anchor.setRow2((short) 43);
> drawing.createPicture(anchor, pictureIdx);
>
> // save workbook
> String file = filename + ".withPicture.xls";
> FileOutputStream fileOut = new FileOutputStream(file);
> workbook.write(fileOut);
> fileOut.close();
> }
> }
>
> /**
> * Loads - reads in and converts into an array of byte(s) - an image
> from
> * a named file.
> *
> * @param imageFilename A String that encapsulates the path to and
> name
> * of the file that contains the image which is
> to
> be
> * 'loaded'.
> * @return An array of type byte that contains the raw data of the
> named
> * image.
> * @throws java.io.FileNotFoundException Thrown if it was not possible
> to
> * open the specified file.
> * @throws java.io.IOException Thrown if reading the file failed or
> was
> * interrupted.
> */
> private static byte[] imageToBytes(String imageFilename)
> throws FileNotFoundException,
> IOException {
> File imageFile = null;
> FileInputStream fis = null;
> ByteArrayOutputStream bos = null;
> int read = 0;
> try {
> imageFile = new File(imageFilename);
> fis = new FileInputStream(imageFile);
> bos = new ByteArrayOutputStream();
> while((read = fis.read()) != -1) {
> bos.write(read);
> }
> return(bos.toByteArray());
> }
> finally {
> if(fis != null) {
> try {
> fis.close();
> fis = null;
> }
> catch(IOException ioEx) {
> // Nothing to do here
> }
> }
> }
> }
> }
>
> Ignore the static imageToBytes() method, this stands in the place of your
> library code.
>
> As you can see, it creates a new sheet, inserts it into the workbook,
> moves
> that sheet so that it is the first in the book and then adds an image to
> it.
> If you run this, when you open the workbook, you should see that the image
> is visible on that first sheet and the diagram is still in position on the
> diagram sheet. This is why I wondered about some interaction between
> Sheet1
> and the diagram sheet - even if that thought has no basis in fact.
>
> Following on from this, would it be possible for you to construct Sheet1
> using POI code and to then insert the image after that? Having looked at
> Sheet1 as it currently stands, I imagine that this could be quite a
> challenge but it might - only might - solve the problem. The workbook your
> code then took as input would contain only the diagram sheet.
>
> Sorry that does not solve the original problem - in fact it may have
> clouded
> the issue further as it cannot explain why simply adding the chart to the
> diagram page could have such dire consequences - but it may point the way
> toward a solution. Possibly, you could begin by replacing Sheet1 as it
> currently stands with an 'empty' sheet that no work has been performed
> upon
> and seeing if your original code runs successfully. If it does, you could
> then modify that Sheet1 until you find the point at which the image cannot
> be correctly added.
>
> I will try something like this myself and let you know if I find a
> solution.
>
> Yours
>
> Mark B
>
>
> Rauer, Matthias (EXT) wrote:
>>
>> Hi Mark,
>>
>> a made a small example. http://www.filesavr.com/poitest
>>
>> I think, there are special cells in the first sheet.
>> But I don't know why the picture is there if I remove the diagram.
>>
>> When I added a new fresh clean sheet, it works, too!
>>
>> The example also includes the output files from the little test program.
>> You can generate the Excel files again and you will get the same output
>> again.
>>
>> Thanks,
>> Matthias
>>
>>
>>
>> -----Ursprüngliche Nachricht-----
>> Von: MSB [mailto:markbrdsly@tiscali.co.uk]
>> Gesendet: Donnerstag, 13. August 2009 13:24
>> An: user@poi.apache.org
>> Betreff: Re: Adding Image in Sheet while Diagram already exists in other
>> does not work
>>
>>
>> Hello Mathias,
>>
>> Could we see the code you are running to insert the picture into the
>> workbook please. Further, if it is not too big and if you can post it to
>> an
>> external entity, could you attach the workbook you are using as well
>> please
>> so that we can see where the diagrams are, etc.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Rauer, Matthias (EXT) wrote:
>>>
>>> Hello,
>>>
>>> I made an .xls-File with several sheets. One sheet contains a diagram.
>>> When I add a picture in the first sheet (without diagram),
>>> it will not be there, when I open the file again with Excel 2003.
>>>
>>> If I remove the diagrams, I can see the added picture in the first
>>> sheet.
>>>
>>> I hope anyone can help me.
>>>
>>> Same problem with poi3.2 final and poi3.5 beta6.
>>>
>>> Thanks,
>>> Matthias
>>>
>>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24953205.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
>>
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24956948.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
>
>
>
--
View this message in context: http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24970012.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
AW: AW: Adding Image in Sheet while Diagram already exists in other
does not work
Posted by "Rauer, Matthias (EXT)" <ma...@siemens.com>.
Hi all,
I solved the problem, by inserting a clean new sheet and copying all information from the original template sheet. So I got the formatted output and the picture together on one sheet.
The orignal sheet was from an other project, their can be some hidden information, references.
Thanks for helping,
Matthias
-----Ursprüngliche Nachricht-----
Von: MSB [mailto:markbrdsly@tiscali.co.uk]
Gesendet: Donnerstag, 13. August 2009 21:06
An: user@poi.apache.org
Betreff: Re: AW: Adding Image in Sheet while Diagram already exists in other does not work
Hello Matthias,
Sorry to say that I do not have an answer for you after playing with some
code, merely more questions.
Your 'Sheet1' was, I noticed, quite complex - there are lots of merged cells
and the column widths have all been reduced - and so I began to wonder if
there could be any strange interactions taking place between the cells on
Sheet1 and those on the diagram sheet. As a quick test, I ran the folowing;
public class POITestImage {
public static void main(final String[] args) throws Exception {
String[] filenames = {"C:/temp/POITest/worksheet-sameButWithDiagram.xls"};
for (String filename : filenames) {
// create a new workbook
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
filename));
byte[] bytes =
POITestImage.imageToBytes("C:/temp/POITest/image.png");
int pictureIdx = workbook.addPicture(bytes,
HSSFWorkbook.PICTURE_TYPE_PNG);
HSSFSheet sheet_view = workbook.createSheet("Image Sheet");
workbook.setSheetOrder("Image Sheet", 0);
HSSFPatriarch drawing = sheet_view.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor();
anchor.setAnchorType(0);
anchor.setCol1((short) 0);
anchor.setCol2((short) 53);
anchor.setRow1((short) 20);
anchor.setRow2((short) 43);
drawing.createPicture(anchor, pictureIdx);
// save workbook
String file = filename + ".withPicture.xls";
FileOutputStream fileOut = new FileOutputStream(file);
workbook.write(fileOut);
fileOut.close();
}
}
/**
* Loads - reads in and converts into an array of byte(s) - an image
from
* a named file.
*
* @param imageFilename A String that encapsulates the path to and name
* of the file that contains the image which is to
be
* 'loaded'.
* @return An array of type byte that contains the raw data of the named
* image.
* @throws java.io.FileNotFoundException Thrown if it was not possible
to
* open the specified file.
* @throws java.io.IOException Thrown if reading the file failed or was
* interrupted.
*/
private static byte[] imageToBytes(String imageFilename)
throws FileNotFoundException,
IOException {
File imageFile = null;
FileInputStream fis = null;
ByteArrayOutputStream bos = null;
int read = 0;
try {
imageFile = new File(imageFilename);
fis = new FileInputStream(imageFile);
bos = new ByteArrayOutputStream();
while((read = fis.read()) != -1) {
bos.write(read);
}
return(bos.toByteArray());
}
finally {
if(fis != null) {
try {
fis.close();
fis = null;
}
catch(IOException ioEx) {
// Nothing to do here
}
}
}
}
}
Ignore the static imageToBytes() method, this stands in the place of your
library code.
As you can see, it creates a new sheet, inserts it into the workbook, moves
that sheet so that it is the first in the book and then adds an image to it.
If you run this, when you open the workbook, you should see that the image
is visible on that first sheet and the diagram is still in position on the
diagram sheet. This is why I wondered about some interaction between Sheet1
and the diagram sheet - even if that thought has no basis in fact.
Following on from this, would it be possible for you to construct Sheet1
using POI code and to then insert the image after that? Having looked at
Sheet1 as it currently stands, I imagine that this could be quite a
challenge but it might - only might - solve the problem. The workbook your
code then took as input would contain only the diagram sheet.
Sorry that does not solve the original problem - in fact it may have clouded
the issue further as it cannot explain why simply adding the chart to the
diagram page could have such dire consequences - but it may point the way
toward a solution. Possibly, you could begin by replacing Sheet1 as it
currently stands with an 'empty' sheet that no work has been performed upon
and seeing if your original code runs successfully. If it does, you could
then modify that Sheet1 until you find the point at which the image cannot
be correctly added.
I will try something like this myself and let you know if I find a solution.
Yours
Mark B
Rauer, Matthias (EXT) wrote:
>
> Hi Mark,
>
> a made a small example. http://www.filesavr.com/poitest
>
> I think, there are special cells in the first sheet.
> But I don't know why the picture is there if I remove the diagram.
>
> When I added a new fresh clean sheet, it works, too!
>
> The example also includes the output files from the little test program.
> You can generate the Excel files again and you will get the same output
> again.
>
> Thanks,
> Matthias
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: MSB [mailto:markbrdsly@tiscali.co.uk]
> Gesendet: Donnerstag, 13. August 2009 13:24
> An: user@poi.apache.org
> Betreff: Re: Adding Image in Sheet while Diagram already exists in other
> does not work
>
>
> Hello Mathias,
>
> Could we see the code you are running to insert the picture into the
> workbook please. Further, if it is not too big and if you can post it to
> an
> external entity, could you attach the workbook you are using as well
> please
> so that we can see where the diagrams are, etc.
>
> Yours
>
> Mark B
>
>
> Rauer, Matthias (EXT) wrote:
>>
>> Hello,
>>
>> I made an .xls-File with several sheets. One sheet contains a diagram.
>> When I add a picture in the first sheet (without diagram),
>> it will not be there, when I open the file again with Excel 2003.
>>
>> If I remove the diagrams, I can see the added picture in the first sheet.
>>
>> I hope anyone can help me.
>>
>> Same problem with poi3.2 final and poi3.5 beta6.
>>
>> Thanks,
>> Matthias
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24953205.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
>
>
>
--
View this message in context: http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24956948.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: AW: Adding Image in Sheet while Diagram already exists in other
does not work
Posted by MSB <ma...@tiscali.co.uk>.
Hello Matthias,
Sorry to say that I do not have an answer for you after playing with some
code, merely more questions.
Your 'Sheet1' was, I noticed, quite complex - there are lots of merged cells
and the column widths have all been reduced - and so I began to wonder if
there could be any strange interactions taking place between the cells on
Sheet1 and those on the diagram sheet. As a quick test, I ran the folowing;
public class POITestImage {
public static void main(final String[] args) throws Exception {
String[] filenames = {"C:/temp/POITest/worksheet-sameButWithDiagram.xls"};
for (String filename : filenames) {
// create a new workbook
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
filename));
byte[] bytes =
POITestImage.imageToBytes("C:/temp/POITest/image.png");
int pictureIdx = workbook.addPicture(bytes,
HSSFWorkbook.PICTURE_TYPE_PNG);
HSSFSheet sheet_view = workbook.createSheet("Image Sheet");
workbook.setSheetOrder("Image Sheet", 0);
HSSFPatriarch drawing = sheet_view.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor();
anchor.setAnchorType(0);
anchor.setCol1((short) 0);
anchor.setCol2((short) 53);
anchor.setRow1((short) 20);
anchor.setRow2((short) 43);
drawing.createPicture(anchor, pictureIdx);
// save workbook
String file = filename + ".withPicture.xls";
FileOutputStream fileOut = new FileOutputStream(file);
workbook.write(fileOut);
fileOut.close();
}
}
/**
* Loads - reads in and converts into an array of byte(s) - an image
from
* a named file.
*
* @param imageFilename A String that encapsulates the path to and name
* of the file that contains the image which is to
be
* 'loaded'.
* @return An array of type byte that contains the raw data of the named
* image.
* @throws java.io.FileNotFoundException Thrown if it was not possible
to
* open the specified file.
* @throws java.io.IOException Thrown if reading the file failed or was
* interrupted.
*/
private static byte[] imageToBytes(String imageFilename)
throws FileNotFoundException,
IOException {
File imageFile = null;
FileInputStream fis = null;
ByteArrayOutputStream bos = null;
int read = 0;
try {
imageFile = new File(imageFilename);
fis = new FileInputStream(imageFile);
bos = new ByteArrayOutputStream();
while((read = fis.read()) != -1) {
bos.write(read);
}
return(bos.toByteArray());
}
finally {
if(fis != null) {
try {
fis.close();
fis = null;
}
catch(IOException ioEx) {
// Nothing to do here
}
}
}
}
}
Ignore the static imageToBytes() method, this stands in the place of your
library code.
As you can see, it creates a new sheet, inserts it into the workbook, moves
that sheet so that it is the first in the book and then adds an image to it.
If you run this, when you open the workbook, you should see that the image
is visible on that first sheet and the diagram is still in position on the
diagram sheet. This is why I wondered about some interaction between Sheet1
and the diagram sheet - even if that thought has no basis in fact.
Following on from this, would it be possible for you to construct Sheet1
using POI code and to then insert the image after that? Having looked at
Sheet1 as it currently stands, I imagine that this could be quite a
challenge but it might - only might - solve the problem. The workbook your
code then took as input would contain only the diagram sheet.
Sorry that does not solve the original problem - in fact it may have clouded
the issue further as it cannot explain why simply adding the chart to the
diagram page could have such dire consequences - but it may point the way
toward a solution. Possibly, you could begin by replacing Sheet1 as it
currently stands with an 'empty' sheet that no work has been performed upon
and seeing if your original code runs successfully. If it does, you could
then modify that Sheet1 until you find the point at which the image cannot
be correctly added.
I will try something like this myself and let you know if I find a solution.
Yours
Mark B
Rauer, Matthias (EXT) wrote:
>
> Hi Mark,
>
> a made a small example. http://www.filesavr.com/poitest
>
> I think, there are special cells in the first sheet.
> But I don't know why the picture is there if I remove the diagram.
>
> When I added a new fresh clean sheet, it works, too!
>
> The example also includes the output files from the little test program.
> You can generate the Excel files again and you will get the same output
> again.
>
> Thanks,
> Matthias
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: MSB [mailto:markbrdsly@tiscali.co.uk]
> Gesendet: Donnerstag, 13. August 2009 13:24
> An: user@poi.apache.org
> Betreff: Re: Adding Image in Sheet while Diagram already exists in other
> does not work
>
>
> Hello Mathias,
>
> Could we see the code you are running to insert the picture into the
> workbook please. Further, if it is not too big and if you can post it to
> an
> external entity, could you attach the workbook you are using as well
> please
> so that we can see where the diagrams are, etc.
>
> Yours
>
> Mark B
>
>
> Rauer, Matthias (EXT) wrote:
>>
>> Hello,
>>
>> I made an .xls-File with several sheets. One sheet contains a diagram.
>> When I add a picture in the first sheet (without diagram),
>> it will not be there, when I open the file again with Excel 2003.
>>
>> If I remove the diagrams, I can see the added picture in the first sheet.
>>
>> I hope anyone can help me.
>>
>> Same problem with poi3.2 final and poi3.5 beta6.
>>
>> Thanks,
>> Matthias
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24953205.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
>
>
>
--
View this message in context: http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24956948.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
AW: Adding Image in Sheet while Diagram already exists in other
does not work
Posted by "Rauer, Matthias (EXT)" <ma...@siemens.com>.
Hi Mark,
a made a small example. http://www.filesavr.com/poitest
I think, there are special cells in the first sheet.
But I don't know why the picture is there if I remove the diagram.
When I added a new fresh clean sheet, it works, too!
The example also includes the output files from the little test program.
You can generate the Excel files again and you will get the same output again.
Thanks,
Matthias
-----Ursprüngliche Nachricht-----
Von: MSB [mailto:markbrdsly@tiscali.co.uk]
Gesendet: Donnerstag, 13. August 2009 13:24
An: user@poi.apache.org
Betreff: Re: Adding Image in Sheet while Diagram already exists in other does not work
Hello Mathias,
Could we see the code you are running to insert the picture into the
workbook please. Further, if it is not too big and if you can post it to an
external entity, could you attach the workbook you are using as well please
so that we can see where the diagrams are, etc.
Yours
Mark B
Rauer, Matthias (EXT) wrote:
>
> Hello,
>
> I made an .xls-File with several sheets. One sheet contains a diagram.
> When I add a picture in the first sheet (without diagram),
> it will not be there, when I open the file again with Excel 2003.
>
> If I remove the diagrams, I can see the added picture in the first sheet.
>
> I hope anyone can help me.
>
> Same problem with poi3.2 final and poi3.5 beta6.
>
> Thanks,
> Matthias
>
>
--
View this message in context: http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24953205.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: Adding Image in Sheet while Diagram already exists in other
does not work
Posted by MSB <ma...@tiscali.co.uk>.
Hello Mathias,
Could we see the code you are running to insert the picture into the
workbook please. Further, if it is not too big and if you can post it to an
external entity, could you attach the workbook you are using as well please
so that we can see where the diagrams are, etc.
Yours
Mark B
Rauer, Matthias (EXT) wrote:
>
> Hello,
>
> I made an .xls-File with several sheets. One sheet contains a diagram.
> When I add a picture in the first sheet (without diagram),
> it will not be there, when I open the file again with Excel 2003.
>
> If I remove the diagrams, I can see the added picture in the first sheet.
>
> I hope anyone can help me.
>
> Same problem with poi3.2 final and poi3.5 beta6.
>
> Thanks,
> Matthias
>
>
--
View this message in context: http://www.nabble.com/Adding-Image-in-Sheet-while-Diagram-already-exists-in-other-does-not-work-tp24952518p24953205.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