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]
