Hi Mark,
It is working ,Thanks a lot for spending time for me. Regarding
htm content in cell can u expalin clearly how to do .
Thanks,
MSB wrote:
>
> 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-tp24604961p24734992.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]
