Someone should submit a request for enhancement (RFE) to bugzilla.
Regards,
Dave
On Mar 21, 2009, at 1:37 AM, MSB wrote:
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]
--
View this message in context:
http://www.nabble.com/Writing-html-code-to-xls-cell-tp22441551p22633844.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]
