poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject RE: Writing html code to xls cell
Date Tue, 24 Mar 2009 08:24:04 GMT

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 <markbrdsly@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 <markbrdsly@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


Mime
View raw message