With regard to the html, you will have to parse it to remove the tags and
extract the text you want to place into the cell. Sadly, there is no
'automatic' way to accomplish what you are after, but it should be easy
enough using the idexOf() and substring() methods of the String class for
example.
Sorry about the image explanation, it was a little complicated at first
glance.
Think of a single cell on a spreadsheet. It is possible to identify any
location within that cell by a pair of co-ordinates, x and y; the
co-ordinate 0, 0 is the top left hand corner of the cell for example. The
compete set of co-ordinates is limited; x values can only be in the range o
to 1023 and y values in the range 0 to 255. This co-oridnate system seems to
remain fixed irrespective of the size of the cell; that is to say the cell
is always 1023 by 255 units.
As you know, the HSSFClientAnchor class takes eight parameters. The latter
four identify the rows and columns that the image will span whilst the first
four determine the locations of the top left and bottom right hand corners
of the image within a cell using that co-ordinate system I have just
described.
Typically, the HSSFClientAnchor class is used like this;
HSSFCientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 1, 8);
Which describes the situation where an image would span from column 0, row 0
to column 1, row 8. It is this type of setup that causes the problem you
have seen; expand column 1 and the image expands with it whatever we try to
do with the anchor's type.
Well, I have found that it is possible to do something like the folowing;
HSSFCientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255, 0, 0, 0, 0);
This will place the image in cell A1 and the size of the image will be 255
'units' square; the units relate to the co-ordinate system and not to any
measure of size such as a centimeter, a point, a pixel, etc. I cannot
guarantee the paramaters are in the correct order as I am writing this
without consulting the javadoc but I hope it explains what I mean.
What I am going to try to accomplish is to find a way to map from a real
size - I am aiming at millimeters currently - to the coordinate system so
that it is possible to say, in effect;
"insert this image into cell A1. Place it 10mm in from the left hand edge of
the cell and 10mm down from the top of the cell. Make the image 25mm wide
and 30mm high."
Then by setting the various parameters of the HSSFClientAnchor class, the
image will be inserted into the correct location and sized appropriately.
To make use of the technique, I think that you will need to do the
following;
1. Populate the sheet.
2. Expand the column in question.
3. Place the image on the sheet.
This order is necessary because we are having to adjust the size of the
image in response to the re-sizing of the column as that is the event that
deforms it. It is not possible to apply this retrospectively, so the image
has to be sized and placed once the column is re-sized.
Hope that helps but do not worry too much at this stage if it sound
confusing, the code will help to clear up any confusion I hope - and
assuming I can get it to work!!
Mark B
deep4u wrote:
>
> Hi Mark,
> I didn't get that one. can u expalian clearly and i have one
> more doubt. suppose in a cell i wrore html content example
> <html><p><h6>Market value</h6></p></html>, but i need only dispaly purpose
> Market value. eleminating the html tags. for this is there any way, please
> let me know.
>
> Thanks,
>
>
> MSB wrote:
>>
>> I think that I might be on to a technique to solve this specific sort of
>> problem.
>>
>> Firstly, I have found out that it is possible to 'place' an image into a
>> specific cell by setting all of the co-ordinates for that cell - the last
>> four parameters of the HSSFClientAnchor class - to the same value. So, to
>> place an image in cell A1, the last four parameters would be 0, 0, 0, 0.
>>
>> Secondly, those first four parameters of the HSSFClientAnchor class -
>> dx1, dx2, dy1 and dy2 - determine the locations of the top left and
>> bottom right ahnd corners of the image within the cell; and this is to my
>> mind the crucial point. By limiting the location to just one cell using
>> the latter four parameters and then playing with the values of the first
>> four, it is possible to place an image within a cell and to determine the
>> size of that image.
>>
>> Thirdly, it seems that the co-ordinate system within the cell is fixed
>> with regard to the range of values. Apparantly, moving from the top of
>> the cell downwards, the range of co-ordinate values runs from 0 to 255.
>> Moving from the right hand edge of the cell leftwards, the co-ordinate
>> values move from 0 to 1023. The challenge I now have to crack is
>> determining how these co-ordinate values can be manipulated to ensure
>> that an image is inserted at the correct location within the cell and is
>> sized appropriately.
>>
>> Again, I think that I am onto apossible solution but it involves
>> converting between Excel's system of expressing the columns width to
>> pixels, determining the 'actual' width of the column, caculating now many
>> pixels - how far and how large - to set the image and then converting
>> this back into the co-ordinate systems values. Could be a bit tricky and
>> I will not have the time to work on it during the day today as we are
>> catching up on the work we missed yesterday owing to the rain. As always
>> though, I will post if I make any progress.
>>
>> Yours
>>
>> Mark B
>>
>>
>> deep4u wrote:
>>>
>>> Hi mark,
>>> when u free send me the code.
>>>
>>> Thanks,
>>>
>>> MSB wrote:
>>>>
>>>> The image ought to be fine. Using Excel, it is possible to set the same
>>>> sort of properties that you can with POI, i.e. that the image should
>>>> not move or resize itself with the cells. To do this using 'my' version
>>>> of Excel, you select the image on the worksheet and click on the right
>>>> hand mouse button; this pops open a menu from which you can select the
>>>> 'Size and Properties' option. The options screen that appears in
>>>> response to this has a number of tabs on it. One of them is labelled
>>>> 'Properties' and selecting this allows you to say how the image should
>>>> respond to resizing of the cells. I would select the 'Don't move or
>>>> size with cells' option. Then the image ought to remain tha same size
>>>> irrespective of what you do with the columns or rows.
>>>>
>>>> If you are in no hurry, I can put together some code later today to
>>>> test this hypothesis and then post the results to you. Sadly, I have to
>>>> leave in about an hour to help a group of volunteers construct a flight
>>>> of steps linking two footpaths together - which should be fun given the
>>>> weather forecast today - but should be able to get some code together
>>>> this evening.
>>>>
>>>> Yours
>>>>
>>>> Mark B
>>>>
>>>>
>>>> deep4u wrote:
>>>>>
>>>>> Hi mark,
>>>>> I need to create a excel dynamically means based On
>>>>> weekly, Monthly, suppose this is current week so
>>>>> 07/24/09-07/31/09,...next based on months july month the name
>>>>> excelsheet will be "july.xls" , and next nonth "August.xls"... If i
>>>>> taking a template ok but in my program i will set autosize on that no
>>>>> streched the image?
>>>>>
>>>>> Thanks,
>>>>>
>>>>>
>>>>> MSB wrote:
>>>>>>
>>>>>> You need to do the following;
>>>>>>
>>>>>> Start Excel
>>>>>> If necessary, open a new workbook - Excel usually opens up with a
>>>>>> new, empty workbook so this may not be necessary.
>>>>>> Select Insert->Picture and navigate your way to where the logo image
>>>>>> is stored. Select (highlight) the image and then click on the Insert
>>>>>> button.
>>>>>> Now, you should see that the logo has been inserted into the
>>>>>> worksheet and you can use the mouse to drag it into the correct
>>>>>> location and re-size it as necessary.
>>>>>> Save the file away and remember where you stored it and the name you
>>>>>> used.
>>>>>>
>>>>>> That has created the template that you will pick up using POI and
>>>>>> populate with data. All you need to do is something like this;
>>>>>>
>>>>>> File file = new File("..Path to and name of the file you created
>>>>>> above..");
>>>>>> FileInputStream fis = new FileInputStream(file);
>>>>>> HSSFWorkbook workbook = new HSSFWorkbook(fis);
>>>>>> HSSFSheet sheet = workbook.getSheetAt(0);
>>>>>>
>>>>>> and when you have your sheet, populate it in the usual manner and
>>>>>> then save the completed workbook away again, most likely using a
>>>>>> different name so that the template can be used again and again as
>>>>>> the basis for further documents. It is common to see users do this
>>>>>> sort of thing as POI's image manipulation facilities are not quite as
>>>>>> feature rich as are Excel's.
>>>>>>
>>>>>> Hope that helps. If it is not clear, I will create a template and
>>>>>> some code that picks it up and populates it and PM both to you; just
>>>>>> let me know.
>>>>>>
>>>>>> Yours
>>>>>>
>>>>>> Mark B
>>>>>>
>>>>>>
>>>>>> deep4u wrote:
>>>>>>>
>>>>>>> Hi mark,
>>>>>>> I need to create Excel sheet dynamically with logo. But
>>>>>>> using Template how it is possible.
>>>>>>>
>>>>>>> Thanks,
>>>>>>>
>>>>>>>
>>>>>>> MSB wrote:
>>>>>>>>
>>>>>>>> So, setting the anchor type does not solve the problem?
>>>>>>>>
>>>>>>>> In that case, I think that your only recourse is to create a
>>>>>>>> document template using Excel and to place the logo onto the
>>>>>>>> document with it. Then you should be able to safely use HSSF/XSSF
>>>>>>>> to populate the worksheet.
>>>>>>>>
>>>>>>>> Yours
>>>>>>>>
>>>>>>>> Mark B
>>>>>>>>
>>>>>>>>
>>>>>>>> deep4u wrote:
>>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>> i use the poi 3.5 also image will be expanding. I write like
>>>>>>>>> this
>>>>>>>>> anchor.setAnchorType(HSSFClientAnchor.DONT_MOVE_AND_RESIZE);
>>>>>>>>> or anchor.setAnchorType(3); how to reslove this.
>>>>>>>>>
>>>>>>>>> Thanks,
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> deep4u wrote:
>>>>>>>>>>
>>>>>>>>>> Hello,
>>>>>>>>>> I am new to this Library. Its working great, i used logo
>>>>>>>>>> (image) in the excel sheet. with the auto sizing the image also
>>>>>>>>>> expanding and contracting according to that column data.
>>>>>>>>>>
>>>>>>>>>> Can i keep the image irrespective of the column size?
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
--
View this message in context:
http://www.nabble.com/autosize-in-Excel-irrespetive-of-image-tp24604961p24733685.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
