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:[email protected]] > Gesendet: Donnerstag, 13. August 2009 21:06 > An: [email protected] > 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:[email protected]] >> Gesendet: Donnerstag, 13. August 2009 13:24 >> An: [email protected] >> 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: [email protected] >> For additional commands, e-mail: [email protected] >> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [email protected] >> For additional commands, e-mail: [email protected] >> >> >> > > -- > 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: [email protected] > For additional commands, e-mail: [email protected] > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > > -- 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: [email protected] For additional commands, e-mail: [email protected]
