In Microsoft Office Excel for Windows there is an option called "Link to file" which can be selected during inserting image into excel sheet. When you insert image which is located at some remote host (ex: http://www.domain.com/image.jpg) and click to this option, image will be linked to the remote file. That means every time when excel file is opened, image will be loaded from remote host.
Is it possible to make this in Apache POI? If yes, can someone give me small example?
Been giving this a bit of thought and would suggest that the best way to proceed is to use Excel to create a workbook with an image embedded into it and the properties set just as you require. Next, unzip the .xlsx and take a close look at the xml markup. If you find the part of the markup that triggers this behaviour then it ought to be possible to trace that object within the openxml4j layer. When you have a handle on which openxml4j object to target then it ought to be possible to access that directly initially and, later, to add this functionality into the api because I do not think it currently has it.
PS it would probably be better to create two .xlsx files and compare them. One should just have the image inserted, the other the image inserted so that it updates. That way, it should be possible to identify the important part of the markup by comparing the two files.
As you can see, no big difference between these two XMLs. The reasony why I want to avoid manual creating of XML with some XML library are Ids (in case of mulitple images) and properties in drawing.xml file.
Can you give me URL with documentation and examples for OpenXML4j? Is it possible to access OpenXML layer from upper layers or only with changing source code of library?
Thanks for that Milos. I think that the key is most likely in the Target attribute for the relationship. That contains the URL that should allow you to link directly to the source of the image and download any update.
As for javadocs for the openxml4j layer, well I am afraid that I do not believe that there are any. The good news is that most of the objects you do need to get your hands on in the openxml4j layer should be available from client code, without the need to make any changes to the underlying API. If you are using an IDE like NetBeans for example, it often opens up prompts that display just what is accessible from which class and that can help you to drill down into the structure of the openxml4j layer to get at the objects you require. Over the next day or so, I should have the chance to play with some code to see what I can come up with and I will download both of your example files to my PC to play around with. It may be that we have to unpack the document 'manually' so to speak using the various relationships but I am hoping that this is not the case. As a first step, I am going to do something like this;
1. Open the workbook.
2. Get a sheet.
3. On that sheet object, call the createDrawingPatriarch() method.
This will return an instance of the XSSFDrawing class from which I can get at the openxml4j object called org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTDrawing which is the root element of the drawing hierarchy for pictures in that sheet. I am hoping that this, or objects it contains, will allow me to dig down to discover the type of information we are looking for.
Now, just out of interest, do you need to update the drawings in the workbook file using java code or are you simply trying to discover what they are? If the latter, then our task ought to be fairly easy, if the latter then we are going to need to get at the stream that connects us to the drawing data held in the workbook file and update it from the data source, somehow.
Anyway, will posts of I make any progress this evening and all the best with your searches. Keep me updated if you would with your progress.
Thank you very much for these great hints! :) You helped me a lot, now I know where to start from my research.
I am not sure that I understood your question, but I will need to create new image objects in drawings and to add remote source for them. I will not update existing images found in excel template, per example. Or I didn't understand your question?
Will update ticket if find something interesting. :)
We are on good way. :) I successfully created drawing1.xml with the same parameters as drawing1.xml from my example file (excel-example-link.xlsx). Java code is attached.
The following things are left to do:
1) Figure out how drawing1.xml.rels file is generated with OpenXML4J API.
2) Figure out how values for <xdr:colOff> and <xdr:rowOff> can be calculated from OpenXML4J API (drawing1.xml element)
3) Figure out how to generate unique id (rId1) for the image in case if multiple images exist in a file.
It should be possible just to update the drawing as it is stored within the archive. I have not had a play with this for a long time but, if I remember correctly, it ought to be possible to pick up an OutputStream from one of the openxml4j objects that connects to the image stored within the archive. If we can do that, it should be possible to stream the new 'version' of the image directly into the archive to replace the previous version. This will obviate the need to update any other parts of the xml.
I got tied up with answering another question, one I thought would be very straightforward(!) - but should have the time to look into the streaming issue today. Will post if I make progress.
If you have the time once you have completed the project you are working on, would you consider working this up into a patch that could be applied to the API? Still, I have not looked at the problem closely myself - I will today, promise - and am going to look at adding this functionality into the API. If I get the work done first, I will post again to this thread.
Yes, offcourse, I would like to help implementing this functionality into the API. I will implement it this week, when I finish the project I am working on. I will need your help about explaining me basic organisation of project. Also, I would like to help about the other bugs / features.
XSSFPictureData data = pic.getPictureData();
byte pictureBytes = data.getData();
The question is what should XSSFPictureData return if the
relationship is external:
(a) throw IllegalStateException("not supported for remote images")
(b) open InputStream and read data from remote.
(c) provide a way to check the type of the relationship (
pictureData.isRemote() ) and an alternative way to get the data.
the ability to get remote data is importantfor the
XSSFPicture.resize() method with adjusts picture anchor to 100%.
> Also, I would like to help about the other bugs / features.
Sorry for the late reply Milos. I wanted to speak to Yegor - the chair of the project management commitee - about your contribution and the possibility of your becoming more involved with POI. Yegor replied to me saying that he is happy to help and mentor you - especially as your patch will touch code that he write - and I can see from the list that he has already made contact.