Re: Writing html code to xls cell

Tue, 24 Mar 2009 11:07:05 -0700 

Hi Avinash,
We would appreciate it if you would come over to the dev list and contribute some of your implementations. Josh, Yegor and Nick can help you find a proper way to extend POI directly. 


We plan to make a push to support OOXML as completely as possible, the  
more hands and heads the better :-)


Regards,
Dave

On Mar 24, 2009, at 9:57 AM, Avinash Kewalramani wrote:


Hi Mark


I sent you the file via Rapidshare. Actually I already have piece of  
code which does
what you are proposing.This is how I have support for embedded  
objects which are not yet supported
by POI.I hate to use this pecial piece of code to inject xml at the  
end into a POi created file.

It just seems risky and might fail.

Here is what I do

1.Reverse engineer the xml to understand the minimalistic xml I need  
to inject into the POi file to support a feature not yet supported  
by POI(e.g embedded objects, Hyperlinks on images)

2. write out the POI file I want.
3. Write out the minimal XML I need to support the new feature

4. use DOM4J with XSLT rules to merge the two files and voila you  
have a new file with the feature required.



Thanks
Avinash


-----Original Message-----
From: MSB [mailto:[email protected]]
Sent: Tuesday, March 24, 2009 1:24 AM
To: [email protected]
Subject: RE: Writing html code to xls cell



Sorry to say this but I cannot see the attachment - and it is purely  
my fault
as I am not registered with the POI list but view it through  
nabble.com.



If you have the time, could you pease upload the file to a file  
sharing site
- personally, I like to use www.rapidshare.com - and then post the  
link for
me? I am confident that it should be possible to do some post  
processing on
the file to 'insert' the hyperlinks after we have identified exactly  
what
changes need to be made. To my mind, the first two steps are to  
identify the
differences between the two files, to manually edit the 'non  
hyperlinked'

version of the file to add the hyperlink and then - there's that third

step!! - create a class that will make the changes for us. Depending  
on your
requirement, I can see a class with a static method that takes a  
reference
to the .xlsx file and lists of the images and the hyperlinks to  
attach. It

should all be quite good fun IMO and the only stumbling block could be

timing - we need to make sure POI has finished writing the file  
before we
begin working with it but I do know a trick if the methods do not  
block.



Oh, thanks for the compliement in another of your posts, but there  
is no way
that I am a good enough programmer now to be one of the developers  
of POI.



itextaccel wrote:


Hi Yegor and Mark

First up thanks for the constant support on this issue.

I did a little bit of digging into the xlsx files myself as Mark  
pointed

out it looks

like the hyperlink is stored as part of the drawing.xml file and a  
new

relationship is created
in drawing1.xml.rels


I created a file using POI with one image in it.Then added a link  
to the

image in it manually and

saved it as another file, unzipped both and compared the  
directories and

found two files which have this information

xl/drawing/_rels/drawing1.xml.rels
xl/drawing/drawing1.xml


I have attached a zip dir with both the above files for both  
scenarios

with hyperlinks and w/o


I think Plain Java injection of xml after creation might be the way  
for me

but if it is easy to build in POI
then I will hold off on that for now.



Regards
A



-----Original Message-----
From: Yegor Kozlov [mailto:[email protected]]
Sent: Saturday, March 21, 2009 9:46 AM
To: POI Users List
Subject: Re: Writing html code to xls cell


there are several types of hyperlinks in Excel that can be grouped  
in two

categories:


(1) BIFF8 hyperlinks. These are stored in the BIFF format and  
supported by

HSSF.
(2) Drawing hyperlinks. These are assigned to drawing objects such as
pictures and shapes. As far as I know, they are a
part of the Escher stuff and stored somewhere in the shape's Escher
container.


In theory, drawing hyperlinks should be stored similarly in  
PowerPoint and

Excel as they share the same drawing layer.

If it is so, then adding this support is quite a realistic task. I  
only

need to check if it is really so :)

Yegor


I have had the opportunity to dig around a little now and I do not  
think

that
it is possible to use HSSF/XSSF to add a hyperlinked image to a
worksheet.


To me, the critical point is that when you create a hyperlinked  
image

using
Excel, firstly the image is placed on the sheet, next the image is
selected
and then hyperlink button clicked to open a dialog and specify the
attributes - i.e. what you are linking to. So, it seems that the
hyperlink

becomes a 'property' of or is associated with the image so to  
speak. It

is
certainly the case that the hyperlink is not entered into a cell - I
moved

the image to see - and POI told me that the sheet contained no  
cells, no

formulae, just a single picture. As far as I am aware, there is no

opportunity to set attributes of this type - hyperlinks - for any  
images

that can be added to a worksheet using POI and that is why I have
concluded
it is not - in all likelihood - possible using the API.

Sadly, I was not able to find out how the hyperlink

information/specification was bound using the BiffViewer/ 
EFBiffViewer

utilities. Both correctly reported that a picture existed on the
worksheet
but it was not obvious where the hyperlink information was. The only
thing

that I did not try - as I assumed all I would get was a copy of  
the image

-

was to stream the HSSFPictureData object I would have recovered  
from the
sheet out to a file. If I have the time this weekend - lots of  
rugby to
watch as the Six Nations competition comes to an end and I am  
working on

Sunday as well - I will do this just to see what I get.


As you are using the xlsx file format - well I assume you are from  
you
original email - it may be worthwhile creating a worksheet that  
holds

just a

single hyperlinked image and then looking at the file's contents  
directly

-
not using POI. That may give you a clue about where the hyperlink

information is stored and - if it is easy enough to accomplish -  
you may

be

able to amend the xlsx file 'manually' using core java; by this, I  
mean
using POI to create the file and insert the image then core Java  
to add

the

hyperlinking information if such is possible as all xlsx files are  
is

zipped
xml. Sorry to admit that I cannot check this myself - I only have an
earlier

version of Office installed on my PC and we are Office free now at  
work.

If

you are not comfortable digging around inside the xlsx file, just  
create

one

and send it to me, I will try having a look myself but cannot  
promise

that
this approach can be made to work.


itextaccel wrote:

Thanks a lot MSB for your answer and also for taking the time(if
possible)
to look into this.

I will try to dig on my own and see if I find something too.I  
will post

it
here if I do.

Regards
A

-----Original Message-----
From: MSB [mailto:[email protected]]
Sent: Friday, March 20, 2009 12:42 AM
To: [email protected]
Subject: RE: Writing html code to xls cell



When Satish and I talked about using hyperlinks, I said that I  
knew it

was

possible to add a hyperlink to a cell but that I did not know if  
it was
possible to use an image as the anchor for the hyperlink. So I am  
sorry

to

say that I do not know the answer to your question at this time.  
I will

take
a look over the weekend if I have the time however.


itextaccel wrote:

sorry forgot to add the code block



   Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
       //Workbook wb = new HSSFWorkbook();
       CreationHelper helper = wb.getCreationHelper();


       //add a picture in this workbook.
       InputStream is = new FileInputStream(args[0]);
       byte[] bytes = IOUtils.toByteArray(is);
       is.close();
       int pictureIdx = wb.addPicture(bytes,
Workbook.PICTURE_TYPE_JPEG);

       //create sheet
       Sheet sheet = wb.createSheet();

       CellStyle hlink_style = wb.createCellStyle();
       Font hlink_font = wb.createFont();
       hlink_font.setUnderline(Font.U_SINGLE);
       hlink_font.setColor(IndexedColors.BLUE.getIndex());
       hlink_style.setFont(hlink_font);

       Cell cell;

       //URL
       cell = sheet.createRow(1).createCell((short)1);


       Hyperlink link =  
helper.createHyperlink(Hyperlink.LINK_URL);

       link.setAddress("http://poi.apache.org/";);
       cell.setHyperlink(link);
       cell.setCellStyle(hlink_style);


       sheet.setColumnWidth(1, sheet.getColumnWidth(0) *6);

       //create drawing
       Drawing drawing = sheet.createDrawingPatriarch();

       //Reset the image to the original size.
       //picture.resize();
       //picture.setLineStyle( picture.LINESTYLE_DASHDOTGEL );


       //add a picture shape
       ClientAnchor anchor = helper.createClientAnchor();
       //sheet.setColumnWidth(1, sheet.getColumnWidth(0) *2);

       anchor.setCol1(1);
       anchor.setRow1(1);
       anchor.setCol2(2);
       anchor.setRow2(2);

       Picture pict = drawing.createPicture(anchor, pictureIdx);

     String file = "picture.xls";
       if(wb instanceof XSSFWorkbook) file += "x";
       FileOutputStream fileOut = new FileOutputStream(file);
       wb.write(fileOut);
       fileOut.close();



Regards
A

-----Original Message-----
From: Avinash Kewalramani [mailto:[email protected]]
Sent: Thursday, March 19, 2009 5:04 PM
To: POI Users List
Subject: RE: Writing html code to xls cell

Hi
This is sort of related to this thread.I just want to create a
hyperlink
on a image

in a cell. Excel allows it but there is no example or api method  
I can

find that does that


Here is the code I have  and it does not create hyperlinks on  
images.
Mark talked about icons with links. how do you do that Mark with  
XSSF?

I
could use the same idea for images
as hyperlinks.

Thanks for any pointers.

Regards
A

-----Original Message-----
From: MSB [mailto:[email protected]]
Sent: Thursday, March 12, 2009 10:44 AM
To: [email protected]
Subject: Re: Writing html code to xls cell



The problem - at least to my mind - that you face, is that you  
wish to

place
a rendered copy of the html markup into a cell; and it is the
'rendering'

part of the problem that is the real stumbling block. The only  
way that

I

can think of to accomplish this is to use something like IE or  
Firefox

to

open the html and generate a rendered page, then grab an image  
and save

that

away as a bmp or jpg - I cannot remember which one can be placed  
onto a
spreadsheet offhand sorry - then you could position the image  
onto an

Excel

spreadsheet. Even then, I do not think that you can guarantee  
that each
image will occupy a single cell. Furthermore, I think that you  
will

face
just the same sorts of issues with .doc or Rich Text Format files,
because
both include the content - the text, tables, pictures, etc - and
formatting
information.


Could you not look at modifying what you want to do? I know that  
it is
possible to place hyperlinks into cells, what I do not know is  
whether

you

could create an icon that sort of resembles the document you  
want to

open,

place that in the cell, mark it as a hyperlink and link to the  
original

file

such that if the user clicks on the icon the file is opened  
using the

default application for that file type; Word for .doc and .rtf
(propably),
IE or Firefox for html files and so on.

Aside from that, I am out of ideas, sorry.


Satish HS wrote:

Dear MSB,

Thanks again.  Yes, My requirement is to write a complete html  
page to

one

single cell. However, if that is not possible, I would like to  
know if

there

is any way to write a complete .doc file or any RTF file to a  
single

cell?

Or any other swing component that I can use for rich text editing
whose

content can be exported to a single cell retaining all the  
format?


Thanks for your help. I hope you don't mind me asking further.

Thanks,
Satish HS



On Thu, Mar 12, 2009 at 3:23 AM, MSB <[email protected]>  
wrote:



Have you changed the requirement or are you saying that you  
want to

open
an
html file, parse the markup, render a complete page so that it
resembles
the

sort of display you would see in a browser,and then write that  
page

into
a

single cell of an Excel spreadhseet? If so, then the answer is  
no,

sorry.
I

do not have the ability, skills or time to create what amounts  
to a

browser.


Satish HS wrote:

Thanks for the response. I truly appreciate it. Regarding your

question

about the font and size, it will be specified in the html  
code.My

actual

need is to populate each cell with separate html-files.   So,  
each

cell

corresponds to a html-file.
I already have the java code which stores the html code in text

file(s),

how
can I populate each cell with separate file.

Can I do this with your solution of using Desktop class? Can  
you

send

me
a

small code snippet of it, if its not too much to ask.

Thanks,
Satish HS


On Wed, Mar 11, 2009 at 2:19 PM, MSB <[email protected]>

wrote:

Right, now I understand. You want HSSF to parse html markup  
for

you.


The short answer to your question then is no, it cannot do  
this.

You

will

have to create a tool to parse the html and extract the  
information

from

the

markup to create the cells. As an example of what I mean,  
what font

and

what
size should HSSF use to render the example you gave?


Just a simple question, why do you want to use HSSF to  
populate an

Excel

spreadsheet in this manner? Did you know that you can use  
Excel to

open

an

html file and that it - Excel that is - can parse the markup  
and

render

the

page for you? A short way around your problem would be to  
use Java

to

create

a text file containing the html code and then ask it to  
start Excel

for

you
using the Desktop class.


Satish HS wrote:

Alright, here is my need, My input is a html String as below:
String str = "<html>Some word</html>";

How do I pass this on using the API to Excel Cell, I want the

excel

cell

to
display "Some word"  in italic.

The actual need is not just to display the italic but to  
support

all

types

of html (rtf, background color, font, including images etc..)
I hope it is clear now, please let me know if you any doubts.

Regards,
Satish HS



On Wed, Mar 11, 2009 at 3:56 AM, MSB <[email protected] 
>

wrote:

cell



--
View this message in context:


http://www.nabble.com/Writing-html-code-to-xls-cell-tp22441551p22461389.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]





--
View this message in context:
http://www.nabble.com/Writing-html-code-to-xls-cell-tp22441551p22471025.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]





--
View this message in context:
http://www.nabble.com/Writing-html-code-to-xls-cell-tp22441551p22481814.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]


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




--
View this message in context:
http://www.nabble.com/Writing-html-code-to-xls-cell-tp22441551p22615547.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]








---------------------------------------------------------------------
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/Writing-html-code-to-xls-cell-tp22441551p22675874.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]




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
























					Reply via email to