Load remote image inside excel sheet

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

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: 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?

Thanks!
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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...
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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.
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

Miloš Malović
excel-example-included.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="http://milos.malovic.net/slika.jpg" 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?
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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. :)
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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.

ExcelTest.java
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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.

ExcelTest.java
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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.
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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.
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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.
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

Yegor Kozlov-4
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%.

> Also, I would like to help about the other bugs / features.

You are very much welcome.

Regards,
Yegor

>
> --
> 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.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

Mark Beardsley
In reply to this post by Miloš Malović
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
Reply | Threaded
Open this post in threaded view
|

Re: Load remote image inside excel sheet

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.