You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Satish HS <wi...@gmail.com> on 2009/03/10 20:16:37 UTC
Writing html code to xls cell
Hello All,
My need is to set the cell value with an html code, and get the cell
content in xls file with Rich Text and images (if any ) inlined.
Can any of you help me in this regard. Is this supported?
Thanks,
Satish
Re: Writing html code to xls cell
Posted by Dave Fisher <da...@jmlafferty.com>.
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:markbrdsly@tiscali.co.uk]
> Sent: Tuesday, March 24, 2009 1:24 AM
> To: user@poi.apache.org
> 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:yegor@dinom.ru]
>> 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:markbrdsly@tiscali.co.uk]
>>>> Sent: Friday, March 20, 2009 12:42 AM
>>>> To: user@poi.apache.org
>>>> 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:AKewalramani@accelrys.com]
>>>>> 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:markbrdsly@tiscali.co.uk]
>>>>> Sent: Thursday, March 12, 2009 10:44 AM
>>>>> To: user@poi.apache.org
>>>>> 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 <ma...@tiscali.co.uk>
>>>>>> 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 <ma...@tiscali.co.uk>
>>>>>>> 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 <markbrdsly@tiscali.co.uk
>>>>>>>>>> >
>>>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>> --
>>>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>
>>>>>>>
>>>>>>
>>>>> --
>>>>> 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: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>> --
>>>> 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: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Writing html code to xls cell
Posted by MSB <ma...@tiscali.co.uk>.
You are well ahead of me on this one!!
Must admit that i do agree about the risks inherent in this approach and you
need to consider what your deadlines are IMO. If you have the time, work
with POI's developers to patch XSSF so that it includes the additional
functionality. If not, use a hack like this to meet your deadline, build a
patch for XSSF and then modify your code to use the patched API.
itextaccel 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:markbrdsly@tiscali.co.uk]
> Sent: Tuesday, March 24, 2009 1:24 AM
> To: user@poi.apache.org
> 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:yegor@dinom.ru]
>> 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:markbrdsly@tiscali.co.uk]
>>>> Sent: Friday, March 20, 2009 12:42 AM
>>>> To: user@poi.apache.org
>>>> 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:AKewalramani@accelrys.com]
>>>>> 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:markbrdsly@tiscali.co.uk]
>>>>> Sent: Thursday, March 12, 2009 10:44 AM
>>>>> To: user@poi.apache.org
>>>>> 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 <ma...@tiscali.co.uk>
>>>>>> 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 <ma...@tiscali.co.uk>
>>>>>>> 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 <ma...@tiscali.co.uk>
>>>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>> --
>>>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>
>>>>>>>
>>>>>>
>>>>> --
>>>>> 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: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>> --
>>>> 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: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Writing-html-code-to-xls-cell-tp22441551p22696866.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Writing html code to xls cell
Posted by Avinash Kewalramani <AK...@accelrys.com>.
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:markbrdsly@tiscali.co.uk]
Sent: Tuesday, March 24, 2009 1:24 AM
To: user@poi.apache.org
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:yegor@dinom.ru]
> 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:markbrdsly@tiscali.co.uk]
>>> Sent: Friday, March 20, 2009 12:42 AM
>>> To: user@poi.apache.org
>>> 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:AKewalramani@accelrys.com]
>>>> 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:markbrdsly@tiscali.co.uk]
>>>> Sent: Thursday, March 12, 2009 10:44 AM
>>>> To: user@poi.apache.org
>>>> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>>>>>> 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 <ma...@tiscali.co.uk>
>>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>> --
>>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>
>>>> --
>>>> 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: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>> --
>>> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Writing html code to xls cell
Posted by MSB <ma...@tiscali.co.uk>.
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:yegor@dinom.ru]
> 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:markbrdsly@tiscali.co.uk]
>>> Sent: Friday, March 20, 2009 12:42 AM
>>> To: user@poi.apache.org
>>> 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:AKewalramani@accelrys.com]
>>>> 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:markbrdsly@tiscali.co.uk]
>>>> Sent: Thursday, March 12, 2009 10:44 AM
>>>> To: user@poi.apache.org
>>>> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>>>>>> 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 <ma...@tiscali.co.uk>
>>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>> --
>>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>
>>>> --
>>>> 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: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>> --
>>> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Writing html code to xls cell
Posted by Avinash Kewalramani <AK...@accelrys.com>.
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:yegor@dinom.ru]
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:markbrdsly@tiscali.co.uk]
>> Sent: Friday, March 20, 2009 12:42 AM
>> To: user@poi.apache.org
>> 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:AKewalramani@accelrys.com]
>>> 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:markbrdsly@tiscali.co.uk]
>>> Sent: Thursday, March 12, 2009 10:44 AM
>>> To: user@poi.apache.org
>>> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>>>>> 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 <ma...@tiscali.co.uk>
>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>
>>>>>>>
>>>>>>
>>>>> --
>>>>> 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: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>
>>> --
>>> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Writing html code to xls cell
Posted by Yegor Kozlov <ye...@dinom.ru>.
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:markbrdsly@tiscali.co.uk]
>> Sent: Friday, March 20, 2009 12:42 AM
>> To: user@poi.apache.org
>> 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:AKewalramani@accelrys.com]
>>> 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:markbrdsly@tiscali.co.uk]
>>> Sent: Thursday, March 12, 2009 10:44 AM
>>> To: user@poi.apache.org
>>> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>>>>> 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 <ma...@tiscali.co.uk>
>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>
>>>>>>>
>>>>>>
>>>>> --
>>>>> 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: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>
>>> --
>>> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Writing html code to xls cell
Posted by David Fisher <df...@jmlafferty.com>.
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:markbrdsly@tiscali.co.uk]
>> Sent: Friday, March 20, 2009 12:42 AM
>> To: user@poi.apache.org
>> 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:AKewalramani@accelrys.com]
>>> 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:markbrdsly@tiscali.co.uk]
>>> Sent: Thursday, March 12, 2009 10:44 AM
>>> To: user@poi.apache.org
>>> 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 <ma...@tiscali.co.uk>
>>>> 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 <ma...@tiscali.co.uk>
>>>>> 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 <ma...@tiscali.co.uk>
>>>>> 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: user-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> --
>>>>> 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: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>
>>>>
>>>
>>> --
>>> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Writing html code to xls cell
Posted by MSB <ma...@tiscali.co.uk>.
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:markbrdsly@tiscali.co.uk]
> Sent: Friday, March 20, 2009 12:42 AM
> To: user@poi.apache.org
> 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:AKewalramani@accelrys.com]
>> 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:markbrdsly@tiscali.co.uk]
>> Sent: Thursday, March 12, 2009 10:44 AM
>> To: user@poi.apache.org
>> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>>>> 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 <ma...@tiscali.co.uk>
>>>> 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: user-unsubscribe@poi.apache.org
>>>> >> For additional commands, e-mail: user-help@poi.apache.org
>>>> >>
>>>> >>
>>>> >
>>>> >
>>>>
>>>> --
>>>> 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: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>
>>>
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Writing html code to xls cell
Posted by Avinash Kewalramani <AK...@accelrys.com>.
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:markbrdsly@tiscali.co.uk]
Sent: Friday, March 20, 2009 12:42 AM
To: user@poi.apache.org
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:AKewalramani@accelrys.com]
> 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:markbrdsly@tiscali.co.uk]
> Sent: Thursday, March 12, 2009 10:44 AM
> To: user@poi.apache.org
> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>>> 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: user-unsubscribe@poi.apache.org
>>> >> For additional commands, e-mail: user-help@poi.apache.org
>>> >>
>>> >>
>>> >
>>> >
>>>
>>> --
>>> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>
>>
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Writing html code to xls cell
Posted by MSB <ma...@tiscali.co.uk>.
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:AKewalramani@accelrys.com]
> 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:markbrdsly@tiscali.co.uk]
> Sent: Thursday, March 12, 2009 10:44 AM
> To: user@poi.apache.org
> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>>> 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: user-unsubscribe@poi.apache.org
>>> >> For additional commands, e-mail: user-help@poi.apache.org
>>> >>
>>> >>
>>> >
>>> >
>>>
>>> --
>>> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>
>>
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Writing html code to xls cell
Posted by Avinash Kewalramani <AK...@accelrys.com>.
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:AKewalramani@accelrys.com]
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:markbrdsly@tiscali.co.uk]
Sent: Thursday, March 12, 2009 10:44 AM
To: user@poi.apache.org
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>> 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: user-unsubscribe@poi.apache.org
>> >> For additional commands, e-mail: user-help@poi.apache.org
>> >>
>> >>
>> >
>> >
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Writing html code to xls cell
Posted by Avinash Kewalramani <AK...@accelrys.com>.
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:markbrdsly@tiscali.co.uk]
Sent: Thursday, March 12, 2009 10:44 AM
To: user@poi.apache.org
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>> 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: user-unsubscribe@poi.apache.org
>> >> For additional commands, e-mail: user-help@poi.apache.org
>> >>
>> >>
>> >
>> >
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Merging word 2007 docx with POI in Cocoon
Posted by "Lee, Betty" <BL...@rwd.com>.
Hello,
We are currently using Cocoon and need to combine multiple word 2007
documents. I've been familiarizing with POI but have not found anything
concrete that can merge the documents.
Any help is greatly appreciated.
-Betty
==============================================================================
Please follow the hyperlink for an important electronic communications disclaimer:
Veuillez suivre le lien hypertexte pour un d�ni important de communications �lectroniques:
http://www.rwd.com/emaildisclaimer.htm
==============================================================================
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Writing html code to xls cell
Posted by Satish HS <wi...@gmail.com>.
Dear MSB,
May be I should look into your idea of providing link in a cell. For
now, it seems like the only solution possible for me.
I had thought of converting html to image and placing that image in a cell,
but that removes the option of editing in cell, which is also a requirement.
Anyways, I will look at the hyperlink option and see how it goes from there.
Thanks for all your help. I appreciate it.
Regards,
Satish HS
On Thu, Mar 12, 2009 at 1:44 PM, MSB <ma...@tiscali.co.uk> wrote:
>
> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
> 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 <ma...@tiscali.co.uk>
> >> 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: user-unsubscribe@poi.apache.org
> >> >> For additional commands, e-mail: user-help@poi.apache.org
> >> >>
> >> >>
> >> >
> >> >
> >>
> >> --
> >> 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: user-unsubscribe@poi.apache.org
> >> For additional commands, e-mail: user-help@poi.apache.org
> >>
> >>
> >
> >
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Writing html code to xls cell
Posted by MSB <ma...@tiscali.co.uk>.
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>> 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: user-unsubscribe@poi.apache.org
>> >> For additional commands, e-mail: user-help@poi.apache.org
>> >>
>> >>
>> >
>> >
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Writing html code to xls cell
Posted by Satish HS <wi...@gmail.com>.
Dear Dave,
Thanks for your response, Yes, the requirement was to put the whole html
file in a single cell, but now that understand it is almost impossible to
do, I have to decide on limiting the features/syntax of html used.
I will look at your suggested solutions, thanks much for the help.
Regards,
Satish HS
On Thu, Mar 12, 2009 at 1:22 PM, Dave Fisher <da...@jmlafferty.com> wrote:
> Satish,
>
> If your requirements are set by someone else to put a whole html markup in
> a cell, then you are being asked to do the impossible - a square peg into a
> round hole.
>
> If you only have a short richtext run that involves applying style to parts
> of a paragraph then your idea makes sense. I mean that your markup is
> <SPAN></SPAN> and other font changes, you have a chance using a technique
> like Mark's.
>
> If you want more complex markup - like a table then really a cell in a
> worksheet has an exact logical correspondence to <TD></TD> markup as in
> Mark's example.
>
> If your complex markup includes images and graphics then you will need to
> learn how to use the Escher drawing layer.
>
> The real question you need to ask your client is if there are limitations
> in what these "complete html pages" really are.
>
> I hope this helps you.
>
> Regards,
> Dave
>
>
> On Mar 12, 2009, at 9:50 AM, 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>>>>>>
>>>>> 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: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>> --
>>> 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: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Writing html code to xls cell
Posted by Dave Fisher <da...@jmlafferty.com>.
Satish,
If your requirements are set by someone else to put a whole html
markup in a cell, then you are being asked to do the impossible - a
square peg into a round hole.
If you only have a short richtext run that involves applying style to
parts of a paragraph then your idea makes sense. I mean that your
markup is <SPAN></SPAN> and other font changes, you have a chance
using a technique like Mark's.
If you want more complex markup - like a table then really a cell in a
worksheet has an exact logical correspondence to <TD></TD> markup as
in Mark's example.
If your complex markup includes images and graphics then you will need
to learn how to use the Escher drawing layer.
The real question you need to ask your client is if there are
limitations in what these "complete html pages" really are.
I hope this helps you.
Regards,
Dave
On Mar 12, 2009, at 9:50 AM, 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
>>> 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 <ma...@tiscali.co.uk>
>> 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: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>
>>>
>>
>> --
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Writing html code to xls cell
Posted by Satish HS <wi...@gmail.com>.
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk>
> 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: user-unsubscribe@poi.apache.org
> >> For additional commands, e-mail: user-help@poi.apache.org
> >>
> >>
> >
> >
>
> --
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Writing html code to xls cell
Posted by MSB <ma...@tiscali.co.uk>.
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
--
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Writing html code to xls cell
Posted by Satish HS <wi...@gmail.com>.
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 <ma...@tiscali.co.uk> 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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Writing html code to xls cell
Posted by MSB <ma...@tiscali.co.uk>.
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 <ma...@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Writing html code to xls cell
Posted by Satish HS <wi...@gmail.com>.
Alright, here is my need, My input is a html String as below:
String str = "<html><i>Some word</i></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 <ma...@tiscali.co.uk> wrote:
> cell
Re: Writing html code to xls cell
Posted by MSB <ma...@tiscali.co.uk>.
Forgive me please, but I not quite sure what exactly you wish to do and would
ask whether you can let us have a few more details please?
Are you saying you want to place the whole of an html document into a cell,
a single tag, an element, etc? What do you want to do with the images, and
how is Excel to display the worksheet once you have completed it -
remembering that Excel can render html markup?
Satish HS wrote:
>
> Hello All,
> My need is to set the cell value with an html code, and get the cell
> content in xls file with Rich Text and images (if any ) inlined.
> Can any of you help me in this regard. Is this supported?
>
> Thanks,
> Satish
>
>
--
View this message in context: http://www.nabble.com/Writing-html-code-to-xls-cell-tp22441551p22450234.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Writing html code to xls cell
Posted by Satish HS <wi...@gmail.com>.
I am using sferyx html editor to fill the html buffer. Any ideas to export
to excel?
Thanks,
Satish HS
On Wed, Mar 11, 2009 at 3:43 PM, Dennis Gesker <de...@gesker.com> wrote:
> Not sure what you are using to generate your html but if you are using JSF
> here is one method:
>
> http://wiki.apache.org/myfaces/Exporting_DataTable_To_MS-Excel
>
> Dennis
>
> On Tue, Mar 10, 2009 at 1:16 PM, Satish HS <wi...@gmail.com> wrote:
>
> > Hello All,
> > My need is to set the cell value with an html code, and get the cell
> > content in xls file with Rich Text and images (if any ) inlined.
> > Can any of you help me in this regard. Is this supported?
> >
> > Thanks,
> > Satish
> >
>
>
>
> --
> Dennis R. Gesker
> email: dennis@gesker.com
> google talk: gesker@gmail.com
> aim: dennisgesker
> pgp/gpg: 0x267D3FE8
> First things first, but not necessarily in that order. -- Unknown
>
Re: Writing html code to xls cell
Posted by Dennis Gesker <de...@gesker.com>.
Not sure what you are using to generate your html but if you are using JSF
here is one method:
http://wiki.apache.org/myfaces/Exporting_DataTable_To_MS-Excel
Dennis
On Tue, Mar 10, 2009 at 1:16 PM, Satish HS <wi...@gmail.com> wrote:
> Hello All,
> My need is to set the cell value with an html code, and get the cell
> content in xls file with Rich Text and images (if any ) inlined.
> Can any of you help me in this regard. Is this supported?
>
> Thanks,
> Satish
>
--
Dennis R. Gesker
email: dennis@gesker.com
google talk: gesker@gmail.com
aim: dennisgesker
pgp/gpg: 0x267D3FE8
First things first, but not necessarily in that order. -- Unknown