Load remote image inside excel sheet

[Miloš Malović]

Hi to all,

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:
Loading Image...) 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?

Thanks!

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821.html
Sent from the POI - User mailing list archive at Nabble.com.

[Miloš Malović]

UPDATE: I am talking about XLSX file.

I know how to manually add image to XML file which is loaded from remote
host, but I would like to avoid any manual editing of XML files...

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709824.html
Sent from the POI - User mailing list archive at Nabble.com.

[Mark Beardsley]

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.

Mark B

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.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709830.html
Sent from the POI - User mailing list archive at Nabble.com.

[Miloš Malović]

http://apache-poi.1045710.n5.nabble.com/file/n5709834/excel-example-included.xlsx
excel-example-included.xlsx
http://apache-poi.1045710.n5.nabble.com/file/n5709834/excel-example-link.xlsx
excel-example-link.xlsx

Hey Mark, thank You very much for the reply!

In attachment you will see two emample excel sheets.

*excel-example-link.xlsx* is the file where image is loaded from remote web
site
*excel-example-included.xlsx* is the file where image is included in XLSX
archive

Interesing part is drawing1.xml.rels file. In first case, XML looks like
this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships
xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
Target="Loading Image..." TargetMode="External"/>
</Relationships>

In second case it looks like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships
xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
Target="../media/image1.jpeg"/>
</Relationships>

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?

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709834.html
Sent from the POI - User mailing list archive at Nabble.com.

[Mark Beardsley]

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.

Yours

Mark B

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709859.html
Sent from the POI - User mailing list archive at Nabble.com.

[Miloš Malović]

Hey Mark!

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. :)

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709862.html
Sent from the POI - User mailing list archive at Nabble.com.

[Miloš Malović]

Hey Mark!

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.

Everything else is clear.

http://apache-poi.1045710.n5.nabble.com/file/n5709880/ExcelTest.java
ExcelTest.java

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709880.html
Sent from the POI - User mailing list archive at Nabble.com.

[Mark Beardsley]

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.

Yours

Mark B

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709885.html
Sent from the POI - User mailing list archive at Nabble.com.

[Miloš Malović]

Hey Mark!

I successfully generated file from Java (OpenXML4J) with identical XML
structure as file generated from Excel and it works. Code is attached.

I suppose that this can be done on a better way and that some elements are
unecessary.

http://apache-poi.1045710.n5.nabble.com/file/n5709887/ExcelTest.java
ExcelTest.java

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709887.html
Sent from the POI - User mailing list archive at Nabble.com.

[Miloš Malović]

This line:

drawingPatriarch.getPackagePart().addExternalRelationship(
"http://milos.malovic.net/slika.jpg", XSLFRelation.IMAGES.getRelation() );

solves problem of generation *drawing1.xml.rels* file and generation of
unique id for every image relation which is added. drawingPatriarch is
object of XSSFDrawing class.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709888.html
Sent from the POI - User mailing list archive at Nabble.com.

[Mark Beardsley]

..and I had only gone to make a cup of tea!!!

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.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709889.html
Sent from the POI - User mailing list archive at Nabble.com.

[Miloš Malović]

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.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709890.html
Sent from the POI - User mailing list archive at Nabble.com.

[Yegor Kozlov]

Miloš,

Sorry for my belated feedback.
Ability to insert remote images looks like a very promising
contribution. On the usermodel level I think the best would be to add
a new method to XSSFDrawing:

public XSSFPicture createPicture(XSSFClientAnchor anchor, URI externalRef) {

}

It will be almost identical to createPicture(XSSFClientAnchor anchor,
int pictureIndex), the only difference is that it will insert external
reference instead of internal.

A use case would look like this:

XSSFPicture pic = drawingPatriarch.createPicture(
new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 9, 23),
URI.create("http://milos.malovic.net/slika.jpg"));


Notice that XSSFPicture can retrieve its data :

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%.

Post by Miloš Malović
Also, I would like to help about the other bugs / features.

You are very much welcome.

Regards,
Yegor

Post by Miloš Malović
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709890.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------

[Mark Beardsley]

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.

All the best.

Yours

Mark B

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709921.html
Sent from the POI - User mailing list archive at Nabble.com.

[Miloš Malović]

Hi guys,

I was a little bit busy in last two days, that's the reason why I didn't
reply earlier.

Yegor,

For remote images we need picture data during creation of document only for
calculating image size for resize() method, so, I think that most logical
solution for this is b).

Lets continiue conversation about my contribute to project via mail.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Load-remote-image-inside-excel-sheet-tp5709821p5709939.html
Sent from the POI - User mailing list archive at Nabble.com.