You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by deep4u <de...@gmail.com> on 2009/08/03 08:46:16 UTC
Re: autosize in Excel irrespetive of image
Hi Mark,
what is the character value for arrow type of bullet. can u send
out what are available characters.
Thanks,
MSB wrote:
>
> I am just weak and could not resist having a play. Anyway, here you go,
> how to insert a bulleted list into a cell;
>
> File file = null;
> FileOutputStream fos = null;
> HSSFWorkbook workbook = null;
> HSSFSheet sheet = null;
> HSSFRow row = null;
> HSSFCell cell = null;
> HSSFCellStyle wrapStyle = null;
> String contents = null;
> // This is the code for the bullet character
> char bulletChar = 8226;
> // and this the code for the new line character. You may
> // equally be able to use '\n' or '\r' but I did not try these out
> char newLineChar = 10;
> try {
> file = new File("........... Your file name
> .................");
> fos = new FileOutputStream(file);
> workbook = new HSSFWorkbook();
> sheet = workbook.createSheet();
> // You need to set wrapping on for the cell, so create a style
> with
> // that attribute set.
> wrapStyle = workbook.createCellStyle();
> wrapStyle.setWrapText(true);
> row = sheet.createRow(0);
> cell = row.createCell(0);
> // Note the bulletChar and newLineChar characters used when
> // building the String for the cell.
> contents = bulletChar +
> "Item One" +
> newLineChar +
> bulletChar +
> "Item Two" +
> newLineChar +
> bulletChar +
> "Item Three" +
> newLineChar +
> bulletChar +
> "Item Four";
> cell.setCellValue(new HSSFRichTextString(contents));
> // Set the cell sstyle to ensure the contents wrap.
> cell.setCellStyle(wrapStyle);
>
> // This just sets the height of the row and the width to allow
> the
> // list to be seen clearly.
> row.setHeight((short)1000);
> sheet.setColumnWidth(0, 5000);
> workbook.write(fos);
>
>
>
> deep4u wrote:
>>
>> Hi Mark,
>> Thanks for spending u r valuable time. i have one more doubt
>> can we create bullets in excel using apche poi.If u know please let me
>> know how to do.
>>
>> Thanks,
>>
>> MSB wrote:
>>>
>>> Oh, well done, glad that I was finally able to help you find a solution
>>> even if it should have been obvious to me that the anchor would only be
>>> an influence once the file was opened using Excel, sorry about that.
>>> Even though you have a workable solution, just in case there are others
>>> following this thread, I am still going to post the demonstration code I
>>> have put together - the html stuff is below everything else, so just
>>> scroll down for that.
>>>
>>> Currently, it only adjusts the width of the image and only works in
>>> situations where the column is far wider than the image. Later on, I am
>>> going to see if I can extend it to deal with the situation where the
>>> column is narrower than the image and where the image is placed across a
>>> number of columns. Once I have done this, I will try to do similar for
>>> rows.
>>>
>>> import java.io.File;
>>> import java.io.FileInputStream;
>>> import java.io.FileOutputStream;
>>> import java.io.ByteArrayOutputStream;
>>> import java.io.FileNotFoundException;
>>> import java.io.IOException;
>>>
>>> import org.apache.poi.hssf.usermodel.HSSFWorkbook;
>>> import org.apache.poi.hssf.usermodel.HSSFSheet;
>>> import org.apache.poi.hssf.usermodel.HSSFRow;
>>> import org.apache.poi.hssf.usermodel.HSSFCell;
>>> import org.apache.poi.hssf.usermodel.HSSFCellStyle;
>>> import org.apache.poi.hssf.usermodel.HSSFFont;
>>> import org.apache.poi.hssf.usermodel.HSSFRichTextString;
>>> import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
>>> import org.apache.poi.hssf.usermodel.HSSFPatriarch;
>>>
>>> /**
>>> * An instance of this class demonstrates that it is possible to
>>> determine the
>>> * width and location of an image dynamically.
>>> *
>>> * Currently, it is limited to setting the width of an image wihin a
>>> column
>>> * whose own width is far greater than that of the image. It cannot yet
>>> handle
>>> * situations where the image may lie across several columns and nor can
>>> it
>>> * (yet) alter the height of the image.
>>> *
>>> * @author Mark B [msb@apache.org]
>>> * @version 1.00 30th July 2009.
>>> */
>>> public class WorkbookFromTemplate {
>>>
>>> public void buildWorkbookFromTemplate(String imageFilename,
>>> String outputFilename)
>>> throws IOException,
>>> FileNotFoundException {
>>> File outputFile = null;
>>> FileOutputStream fos = null;
>>> HSSFWorkbook workbook = null;
>>> HSSFSheet sheet = null;
>>> HSSFRow row = null;
>>> HSSFCell cell = null;
>>> HSSFCellStyle style = null;
>>> HSSFFont font = null;
>>> HSSFClientAnchor anchor = null;
>>> HSSFPatriarch patriarch = null;
>>> double columnWidthCharUnits = 0.0;
>>> double columnWidthPixels = 0.0;
>>> double columnWidthMillimetres = 0.0;
>>> double coordinatePositionsPerMillimetre = 0.0;
>>> int leftBorder = 0;
>>> int pictureWidth = 0;
>>>
>>> // These values set the width of the border and the size of the
>>> // image.
>>> int reqBorder = 5;
>>> int reqPictureWidth = 25;
>>>
>>> String contents = null;
>>> try {
>>> workbook = new HSSFWorkbook();
>>> sheet = workbook.createSheet();
>>> style = workbook.createCellStyle();
>>> font = workbook.getFontAt((short)1);
>>> // By changing the point size of the font from 10 to 12, 14,
>>> 16, 8,
>>> // etc, it is possible to check that the image is re-sized
>>> correctly
>>> // as the width of the column responds to changes in the
>>> size of the
>>> // font
>>> font.setFontHeightInPoints((short)10);
>>> style.setFont(font);
>>>
>>> // Populate the first cell in rows 9 to 20 with long Strings
>>> of
>>> // data so that the resize operation will be quite dramatic.
>>> for(int i = 9; i < 20; i++) {
>>> row = sheet.createRow(i);
>>> cell = row.createCell(0);
>>> contents = ("Setting the value of cell 1 in row " +
>>> i +
>>> " the current time in milliseconds time is "
>>> +
>>> System.currentTimeMillis());
>>> cell.setCellValue(new HSSFRichTextString(contents));
>>> cell.setCellStyle(style);
>>> }
>>>
>>> // Autosize the column to accomdate the contents
>>> sheet.autoSizeColumn((short)0);
>>>
>>> // Recover the size of the column in Excel's character
>>> units.
>>> columnWidthCharUnits = sheet.getColumnWidth(0);
>>> // Convert from Excels' character units into pixels to get
>>> the total
>>> // width of the column in pixels
>>> columnWidthPixels = ExcelUtil.widthUnits2Pixel(
>>> (short)columnWidthCharUnits);
>>> // Convert from pixels to millimetres to get to total width
>>> of the
>>> // column in millimetres
>>> columnWidthMillimetres = columnWidthPixels /
>>> ExcelUtil.PIXELS_PER_MILLIMETRES;
>>> // We 'know' that the column conatins a maximum of 1023
>>> co-ordinate
>>> // positions. Calculate how many of these co-ordinate
>>> positions
>>> // there are in a millimetre.
>>> coordinatePositionsPerMillimetre =
>>> ExcelUtil.TOTAL_COLUMN_COORDINATE_POSITIONS /
>>> columnWidthMillimetres;
>>> // Calculate the number of co-ordinate positions necessary
>>> to leave
>>> // a border to the left of the image the required thickness.
>>> leftBorder = (int)(reqBorder *
>>> coordinatePositionsPerMillimetre);
>>> // Calculate the number of co-ordinate positions necessary
>>> to
>>> // set the width of the image to the required number of
>>> millimetres.
>>> // Remember to add on the thickness of the left border.
>>> pictureWidth = (int)(leftBorder +
>>> (reqPictureWidth *
>>> coordinatePositionsPerMillimetre));
>>>
>>> // Create the anchor instance. Note that the parameters that
>>> // specify the column(s) the image should occupy - they are
>>> // parameters number 5 and 7 - both specify the same column;
>>> in
>>> // this case 0. Normally, this would prevent the image from
>>> // being seen; the settings of the first four parameters can
>>> // be used to 'fine tune' this behaviour.
>>> //
>>> // Parameter 1 moves picture in from left hand edge.
>>> // Parameter 2 moves top edge of picture downwards
>>> // Parameter 3 moves the right hand edge of the image
>>> // Parameter 4 moves the bottom edge of the image down
>>> //
>>> anchor = new HSSFClientAnchor(leftBorder, // Inset image
>>> from left
>>> 0, // Inset image
>>> from top
>>> pictureWidth, // Width of
>>> image
>>> 0, // Height of
>>> image
>>> (short)0, // 'From' column
>>> 0, // 'From' row
>>> (short)0, // 'To' column
>>> 8); // 'To' row
>>> anchor.setAnchorType(3);
>>>
>>> // Add the image to the workbook
>>> int index =
>>> workbook.addPicture(this.imageToBytes(imageFilename),
>>> HSSFWorkbook.PICTURE_TYPE_JPEG);
>>> // Get the drawing patriarch and create the picture within
>>> it
>>> patriarch = sheet.createDrawingPatriarch();
>>> patriarch.createPicture(anchor, index);
>>>
>>> // Save the file away
>>> outputFile = new File(outputFilename);
>>> fos = new FileOutputStream(outputFile);
>>> workbook.write(fos);
>>> }
>>> finally {
>>> if(fos != null) {
>>> try {
>>> fos.close();
>>> fos = null;
>>> }
>>> catch(IOException ioEx) {
>>> // Not much I can do here!!
>>> }
>>> }
>>> }
>>> }
>>>
>>> /**
>>> * Loads - reads in and converts into an array of byte(s) - an image
>>> from
>>> * a named file.
>>> *
>>> * @param imageFilename A String that encapsulates the path to and
>>> name
>>> * of the file that contains the image which is
>>> to be
>>> * 'loaded'.
>>> * @return An array of type byte that contains the raw data of the
>>> named
>>> * image.
>>> * @throws java.io.FileNotFoundException Thrown if it was not
>>> possible to
>>> * open the specified file.
>>> * @throws java.io.IOException Thrown if reading the file failed or
>>> was
>>> * interrupted.
>>> */
>>> private byte[] imageToBytes(String imageFilename)
>>> throws FileNotFoundException,
>>> IOException {
>>> File imageFile = null;
>>> FileInputStream fis = null;
>>> ByteArrayOutputStream bos = null;
>>> int read = 0;
>>> try {
>>> imageFile = new File(imageFilename);
>>> fis = new FileInputStream(imageFile);
>>> bos = new ByteArrayOutputStream();
>>> while((read = fis.read()) != -1) {
>>> bos.write(read);
>>> }
>>> return(bos.toByteArray());
>>> }
>>> finally {
>>> if(fis != null) {
>>> try {
>>> fis.close();
>>> fis = null;
>>> }
>>> catch(IOException ioEx) {
>>> // Nothing to do here
>>> }
>>> }
>>> }
>>> }
>>>
>>> private String getContentsAsString(HSSFCell cell) {
>>> String contents = null;
>>> switch(cell.getCellType()) {
>>> case HSSFCell.CELL_TYPE_BLANK:
>>> case HSSFCell.CELL_TYPE_BOOLEAN:
>>> case HSSFCell.CELL_TYPE_ERROR:
>>> case HSSFCell.CELL_TYPE_FORMULA:
>>> case HSSFCell.CELL_TYPE_NUMERIC:
>>> contents = String.valueOf(cell.getNumericCellValue());
>>> break;
>>> case HSSFCell.CELL_TYPE_STRING:
>>> contents = cell.getRichStringCellValue().getString();
>>> break;
>>> }
>>> return(contents);
>>> }
>>>
>>> /**
>>> * @param args the command line arguments
>>> */
>>> public static void main(String[] args) {
>>> try {
>>> new WorkbookFromTemplate().buildWorkbookFromTemplate(
>>> "...Path to and name of image file....",
>>> "...Path to and name of document you want to
>>> create....");
>>> }
>>> catch(IOException ioEx) {
>>> System.out.println("Caught a: " +
>>> ioEx.getClass().getName());
>>> System.out.println("Message: " + ioEx.getMessage());
>>> System.out.println("Stacktrace follows..................");
>>> ioEx.printStackTrace(System.out);
>>> }
>>> }
>>> }
>>>
>>> /**
>>> * Utility methods used to perform conversions between Excel's character
>>> * based column and row size measurements and pixels. The clas also
>>> contains
>>> * various constants that are required in other calculations.
>>> *
>>> * @author xio[darjino@hotmail.com]
>>> * @version 1.01 30th July 2009.
>>> * Additional constants added by Mark B [msb@apache.org].
>>> */
>>> public class ExcelUtil {
>>>
>>> public static final int TOTAL_COLUMN_COORDINATE_POSITIONS = 1023; //
>>> MB
>>> public static final int TOTAL_ROW_COORDINATE_POSITIONS = 255; //
>>> MB
>>> public static final int PIXELS_PER_INCH = 96; //
>>> MB
>>> public static final double PIXELS_PER_MILLIMETRES = 3.78; //
>>> MB
>>> public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;
>>> public static final int UNIT_OFFSET_LENGTH = 7;
>>> public static final int[] UNIT_OFFSET_MAP = new int[]
>>> { 0, 36, 73, 109, 146, 182, 219 };
>>>
>>> /**
>>> * pixel units to excel width units(units of 1/256th of a character
>>> width)
>>> * @param pxs
>>> * @return
>>> */
>>> public static short pixel2WidthUnits(int pxs) {
>>> short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR *
>>> (pxs / UNIT_OFFSET_LENGTH));
>>> widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];
>>> return widthUnits;
>>> }
>>>
>>> /**
>>> * excel width units(units of 1/256th of a character width) to pixel
>>> units
>>> * @param widthUnits
>>> * @return
>>> */
>>> public static int widthUnits2Pixel(short widthUnits) {
>>> int pixels = (widthUnits / EXCEL_COLUMN_WIDTH_FACTOR)
>>> * UNIT_OFFSET_LENGTH;
>>> int offsetWidthUnits = widthUnits % EXCEL_COLUMN_WIDTH_FACTOR;
>>> pixels += Math.round((float) offsetWidthUnits /
>>> ((float) EXCEL_COLUMN_WIDTH_FACTOR /
>>> UNIT_OFFSET_LENGTH));
>>> return pixels;
>>> }
>>> }
>>>
>>> Here it the html stuff you asked about. Note that it does depend on your
>>> knowing in advance the sort of markup you will be getting; in terms of
>>> the entities. If you do not know this then there are other options you
>>> could use. One would be to create a full blown parser for the html - for
>>> which there is support in the jdk - but even this relies upon your
>>> knowing which tags mark the beginning and ending of the information you
>>> are interested in.
>>>
>>> String htmlExpression = " <h3> " +
>>> " User Interface </h3>";
>>>
>>> // I want to extract the words 'Uesr Interface' from the
>>> html
>>> // markup encapsulated within the hetmlExpression String.
>>> Looking
>>> // at it, I can see that the tag that folows the text I am
>>> after
>>> // is so the first thing to do is search for that tag.
>>> //
>>> // To do this, I am using the indexOf() method which returns
>>> the
>>> // position of the FIRST occurrence of it's parameter
>>> starting
>>> // from the beginning of the String. There is also the
>>> lastIndexOf()
>>> // method which will start at the end of the String and find
>>> the
>>> // last occurrence of it's parameter.
>>> //
>>> int endPoint = htmlExpression.indexOf("");
>>> //
>>> // As I am dealing with html, I know that the character that
>>> marks
>>> // the start point of the text I am after is the closing
>>> brace of
>>> // the preceding tag. So, I can search backwards through the
>>> // htmlExpression for that closing brace starting from the
>>> point
>>> // I have already found above
>>> //
>>> int startPoint = htmlExpression.lastIndexOf(">", endPoint);
>>> //
>>> // Now I have both the starting and ending points for the
>>> substring
>>> // I can simply extract it using the substring() method.
>>> //
>>> System.out.println("[" +
>>> htmlExpression.substring(startPoint, endPoint) +
>>> "]");
>>> //
>>> // Note that here, you are also seeing the search character
>>> - the >
>>> // character - included in the substring. This is beacause
>>> of the
>>> // way the indexOf() family of methods works when combined
>>> with the
>>> // substring method; they assume you want to return the
>>> search
>>> // character. If you do not, then add on to the index the
>>> length of the
>>> // search String, in this case 1;
>>> //
>>> startPoint++;
>>> System.out.println("[" +
>>> htmlExpression.substring(startPoint,
>>> endPoint).trim() +
>>> "]");
>>> //
>>> // Note as well that I have also stripped out the whitespave
>>> using
>>> // the trim() method.
>>>
>>> As for spending the time to help out, it's an investment really as it
>>> has yielded some code that can be added to the examples section of the
>>> documentation and others will - hopefully - benefit.
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> deep4u wrote:
>>>>
>>>> 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-tp24604961p24786132.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: autosize in Excel irrespetive of image
Posted by Aram Mirzadeh <aw...@mbcli.com>.
private final static char bulletChar = 8226;
deep4u wrote:
> Hi Mark,
> what is the character value for arrow type of bullet. can u send
> out what are available characters.
>
> Thanks,
>
>
> MSB wrote:
>
>> I am just weak and could not resist having a play. Anyway, here you go,
>> how to insert a bulleted list into a cell;
>>
>> File file = null;
>> FileOutputStream fos = null;
>> HSSFWorkbook workbook = null;
>> HSSFSheet sheet = null;
>> HSSFRow row = null;
>> HSSFCell cell = null;
>> HSSFCellStyle wrapStyle = null;
>> String contents = null;
>> // This is the code for the bullet character
>> char bulletChar = 8226;
>> // and this the code for the new line character. You may
>> // equally be able to use '\n' or '\r' but I did not try these out
>> char newLineChar = 10;
>> try {
>> file = new File("........... Your file name
>> .................");
>> fos = new FileOutputStream(file);
>> workbook = new HSSFWorkbook();
>> sheet = workbook.createSheet();
>> // You need to set wrapping on for the cell, so create a style
>> with
>> // that attribute set.
>> wrapStyle = workbook.createCellStyle();
>> wrapStyle.setWrapText(true);
>> row = sheet.createRow(0);
>> cell = row.createCell(0);
>> // Note the bulletChar and newLineChar characters used when
>> // building the String for the cell.
>> contents = bulletChar +
>> "Item One" +
>> newLineChar +
>> bulletChar +
>> "Item Two" +
>> newLineChar +
>> bulletChar +
>> "Item Three" +
>> newLineChar +
>> bulletChar +
>> "Item Four";
>> cell.setCellValue(new HSSFRichTextString(contents));
>> // Set the cell sstyle to ensure the contents wrap.
>> cell.setCellStyle(wrapStyle);
>>
>> // This just sets the height of the row and the width to allow
>> the
>> // list to be seen clearly.
>> row.setHeight((short)1000);
>> sheet.setColumnWidth(0, 5000);
>> workbook.write(fos);
>>
>>
>>
>> deep4u wrote:
>>
>>> Hi Mark,
>>> Thanks for spending u r valuable time. i have one more doubt
>>> can we create bullets in excel using apche poi.If u know please let me
>>> know how to do.
>>>
>>> Thanks,
>>>
>>> MSB wrote:
>>>
>>>> Oh, well done, glad that I was finally able to help you find a solution
>>>> even if it should have been obvious to me that the anchor would only be
>>>> an influence once the file was opened using Excel, sorry about that.
>>>> Even though you have a workable solution, just in case there are others
>>>> following this thread, I am still going to post the demonstration code I
>>>> have put together - the html stuff is below everything else, so just
>>>> scroll down for that.
>>>>
>>>> Currently, it only adjusts the width of the image and only works in
>>>> situations where the column is far wider than the image. Later on, I am
>>>> going to see if I can extend it to deal with the situation where the
>>>> column is narrower than the image and where the image is placed across a
>>>> number of columns. Once I have done this, I will try to do similar for
>>>> rows.
>>>>
>>>> import java.io.File;
>>>> import java.io.FileInputStream;
>>>> import java.io.FileOutputStream;
>>>> import java.io.ByteArrayOutputStream;
>>>> import java.io.FileNotFoundException;
>>>> import java.io.IOException;
>>>>
>>>> import org.apache.poi.hssf.usermodel.HSSFWorkbook;
>>>> import org.apache.poi.hssf.usermodel.HSSFSheet;
>>>> import org.apache.poi.hssf.usermodel.HSSFRow;
>>>> import org.apache.poi.hssf.usermodel.HSSFCell;
>>>> import org.apache.poi.hssf.usermodel.HSSFCellStyle;
>>>> import org.apache.poi.hssf.usermodel.HSSFFont;
>>>> import org.apache.poi.hssf.usermodel.HSSFRichTextString;
>>>> import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
>>>> import org.apache.poi.hssf.usermodel.HSSFPatriarch;
>>>>
>>>> /**
>>>> * An instance of this class demonstrates that it is possible to
>>>> determine the
>>>> * width and location of an image dynamically.
>>>> *
>>>> * Currently, it is limited to setting the width of an image wihin a
>>>> column
>>>> * whose own width is far greater than that of the image. It cannot yet
>>>> handle
>>>> * situations where the image may lie across several columns and nor can
>>>> it
>>>> * (yet) alter the height of the image.
>>>> *
>>>> * @author Mark B [msb@apache.org]
>>>> * @version 1.00 30th July 2009.
>>>> */
>>>> public class WorkbookFromTemplate {
>>>>
>>>> public void buildWorkbookFromTemplate(String imageFilename,
>>>> String outputFilename)
>>>> throws IOException,
>>>> FileNotFoundException {
>>>> File outputFile = null;
>>>> FileOutputStream fos = null;
>>>> HSSFWorkbook workbook = null;
>>>> HSSFSheet sheet = null;
>>>> HSSFRow row = null;
>>>> HSSFCell cell = null;
>>>> HSSFCellStyle style = null;
>>>> HSSFFont font = null;
>>>> HSSFClientAnchor anchor = null;
>>>> HSSFPatriarch patriarch = null;
>>>> double columnWidthCharUnits = 0.0;
>>>> double columnWidthPixels = 0.0;
>>>> double columnWidthMillimetres = 0.0;
>>>> double coordinatePositionsPerMillimetre = 0.0;
>>>> int leftBorder = 0;
>>>> int pictureWidth = 0;
>>>>
>>>> // These values set the width of the border and the size of the
>>>> // image.
>>>> int reqBorder = 5;
>>>> int reqPictureWidth = 25;
>>>>
>>>> String contents = null;
>>>> try {
>>>> workbook = new HSSFWorkbook();
>>>> sheet = workbook.createSheet();
>>>> style = workbook.createCellStyle();
>>>> font = workbook.getFontAt((short)1);
>>>> // By changing the point size of the font from 10 to 12, 14,
>>>> 16, 8,
>>>> // etc, it is possible to check that the image is re-sized
>>>> correctly
>>>> // as the width of the column responds to changes in the
>>>> size of the
>>>> // font
>>>> font.setFontHeightInPoints((short)10);
>>>> style.setFont(font);
>>>>
>>>> // Populate the first cell in rows 9 to 20 with long Strings
>>>> of
>>>> // data so that the resize operation will be quite dramatic.
>>>> for(int i = 9; i < 20; i++) {
>>>> row = sheet.createRow(i);
>>>> cell = row.createCell(0);
>>>> contents = ("Setting the value of cell 1 in row " +
>>>> i +
>>>> " the current time in milliseconds time is "
>>>> +
>>>> System.currentTimeMillis());
>>>> cell.setCellValue(new HSSFRichTextString(contents));
>>>> cell.setCellStyle(style);
>>>> }
>>>>
>>>> // Autosize the column to accomdate the contents
>>>> sheet.autoSizeColumn((short)0);
>>>>
>>>> // Recover the size of the column in Excel's character
>>>> units.
>>>> columnWidthCharUnits = sheet.getColumnWidth(0);
>>>> // Convert from Excels' character units into pixels to get
>>>> the total
>>>> // width of the column in pixels
>>>> columnWidthPixels = ExcelUtil.widthUnits2Pixel(
>>>> (short)columnWidthCharUnits);
>>>> // Convert from pixels to millimetres to get to total width
>>>> of the
>>>> // column in millimetres
>>>> columnWidthMillimetres = columnWidthPixels /
>>>> ExcelUtil.PIXELS_PER_MILLIMETRES;
>>>> // We 'know' that the column conatins a maximum of 1023
>>>> co-ordinate
>>>> // positions. Calculate how many of these co-ordinate
>>>> positions
>>>> // there are in a millimetre.
>>>> coordinatePositionsPerMillimetre =
>>>> ExcelUtil.TOTAL_COLUMN_COORDINATE_POSITIONS /
>>>> columnWidthMillimetres;
>>>> // Calculate the number of co-ordinate positions necessary
>>>> to leave
>>>> // a border to the left of the image the required thickness.
>>>> leftBorder = (int)(reqBorder *
>>>> coordinatePositionsPerMillimetre);
>>>> // Calculate the number of co-ordinate positions necessary
>>>> to
>>>> // set the width of the image to the required number of
>>>> millimetres.
>>>> // Remember to add on the thickness of the left border.
>>>> pictureWidth = (int)(leftBorder +
>>>> (reqPictureWidth *
>>>> coordinatePositionsPerMillimetre));
>>>>
>>>> // Create the anchor instance. Note that the parameters that
>>>> // specify the column(s) the image should occupy - they are
>>>> // parameters number 5 and 7 - both specify the same column;
>>>> in
>>>> // this case 0. Normally, this would prevent the image from
>>>> // being seen; the settings of the first four parameters can
>>>> // be used to 'fine tune' this behaviour.
>>>> //
>>>> // Parameter 1 moves picture in from left hand edge.
>>>> // Parameter 2 moves top edge of picture downwards
>>>> // Parameter 3 moves the right hand edge of the image
>>>> // Parameter 4 moves the bottom edge of the image down
>>>> //
>>>> anchor = new HSSFClientAnchor(leftBorder, // Inset image
>>>> from left
>>>> 0, // Inset image
>>>> from top
>>>> pictureWidth, // Width of
>>>> image
>>>> 0, // Height of
>>>> image
>>>> (short)0, // 'From' column
>>>> 0, // 'From' row
>>>> (short)0, // 'To' column
>>>> 8); // 'To' row
>>>> anchor.setAnchorType(3);
>>>>
>>>> // Add the image to the workbook
>>>> int index =
>>>> workbook.addPicture(this.imageToBytes(imageFilename),
>>>> HSSFWorkbook.PICTURE_TYPE_JPEG);
>>>> // Get the drawing patriarch and create the picture within
>>>> it
>>>> patriarch = sheet.createDrawingPatriarch();
>>>> patriarch.createPicture(anchor, index);
>>>>
>>>> // Save the file away
>>>> outputFile = new File(outputFilename);
>>>> fos = new FileOutputStream(outputFile);
>>>> workbook.write(fos);
>>>> }
>>>> finally {
>>>> if(fos != null) {
>>>> try {
>>>> fos.close();
>>>> fos = null;
>>>> }
>>>> catch(IOException ioEx) {
>>>> // Not much I can do here!!
>>>> }
>>>> }
>>>> }
>>>> }
>>>>
>>>> /**
>>>> * Loads - reads in and converts into an array of byte(s) - an image
>>>> from
>>>> * a named file.
>>>> *
>>>> * @param imageFilename A String that encapsulates the path to and
>>>> name
>>>> * of the file that contains the image which is
>>>> to be
>>>> * 'loaded'.
>>>> * @return An array of type byte that contains the raw data of the
>>>> named
>>>> * image.
>>>> * @throws java.io.FileNotFoundException Thrown if it was not
>>>> possible to
>>>> * open the specified file.
>>>> * @throws java.io.IOException Thrown if reading the file failed or
>>>> was
>>>> * interrupted.
>>>> */
>>>> private byte[] imageToBytes(String imageFilename)
>>>> throws FileNotFoundException,
>>>> IOException {
>>>> File imageFile = null;
>>>> FileInputStream fis = null;
>>>> ByteArrayOutputStream bos = null;
>>>> int read = 0;
>>>> try {
>>>> imageFile = new File(imageFilename);
>>>> fis = new FileInputStream(imageFile);
>>>> bos = new ByteArrayOutputStream();
>>>> while((read = fis.read()) != -1) {
>>>> bos.write(read);
>>>> }
>>>> return(bos.toByteArray());
>>>> }
>>>> finally {
>>>> if(fis != null) {
>>>> try {
>>>> fis.close();
>>>> fis = null;
>>>> }
>>>> catch(IOException ioEx) {
>>>> // Nothing to do here
>>>> }
>>>> }
>>>> }
>>>> }
>>>>
>>>> private String getContentsAsString(HSSFCell cell) {
>>>> String contents = null;
>>>> switch(cell.getCellType()) {
>>>> case HSSFCell.CELL_TYPE_BLANK:
>>>> case HSSFCell.CELL_TYPE_BOOLEAN:
>>>> case HSSFCell.CELL_TYPE_ERROR:
>>>> case HSSFCell.CELL_TYPE_FORMULA:
>>>> case HSSFCell.CELL_TYPE_NUMERIC:
>>>> contents = String.valueOf(cell.getNumericCellValue());
>>>> break;
>>>> case HSSFCell.CELL_TYPE_STRING:
>>>> contents = cell.getRichStringCellValue().getString();
>>>> break;
>>>> }
>>>> return(contents);
>>>> }
>>>>
>>>> /**
>>>> * @param args the command line arguments
>>>> */
>>>> public static void main(String[] args) {
>>>> try {
>>>> new WorkbookFromTemplate().buildWorkbookFromTemplate(
>>>> "...Path to and name of image file....",
>>>> "...Path to and name of document you want to
>>>> create....");
>>>> }
>>>> catch(IOException ioEx) {
>>>> System.out.println("Caught a: " +
>>>> ioEx.getClass().getName());
>>>> System.out.println("Message: " + ioEx.getMessage());
>>>> System.out.println("Stacktrace follows..................");
>>>> ioEx.printStackTrace(System.out);
>>>> }
>>>> }
>>>> }
>>>>
>>>> /**
>>>> * Utility methods used to perform conversions between Excel's character
>>>> * based column and row size measurements and pixels. The clas also
>>>> contains
>>>> * various constants that are required in other calculations.
>>>> *
>>>> * @author xio[darjino@hotmail.com]
>>>> * @version 1.01 30th July 2009.
>>>> * Additional constants added by Mark B [msb@apache.org].
>>>> */
>>>> public class ExcelUtil {
>>>>
>>>> public static final int TOTAL_COLUMN_COORDINATE_POSITIONS = 1023; //
>>>> MB
>>>> public static final int TOTAL_ROW_COORDINATE_POSITIONS = 255; //
>>>> MB
>>>> public static final int PIXELS_PER_INCH = 96; //
>>>> MB
>>>> public static final double PIXELS_PER_MILLIMETRES = 3.78; //
>>>> MB
>>>> public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;
>>>> public static final int UNIT_OFFSET_LENGTH = 7;
>>>> public static final int[] UNIT_OFFSET_MAP = new int[]
>>>> { 0, 36, 73, 109, 146, 182, 219 };
>>>>
>>>> /**
>>>> * pixel units to excel width units(units of 1/256th of a character
>>>> width)
>>>> * @param pxs
>>>> * @return
>>>> */
>>>> public static short pixel2WidthUnits(int pxs) {
>>>> short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR *
>>>> (pxs / UNIT_OFFSET_LENGTH));
>>>> widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];
>>>> return widthUnits;
>>>> }
>>>>
>>>> /**
>>>> * excel width units(units of 1/256th of a character width) to pixel
>>>> units
>>>> * @param widthUnits
>>>> * @return
>>>> */
>>>> public static int widthUnits2Pixel(short widthUnits) {
>>>> int pixels = (widthUnits / EXCEL_COLUMN_WIDTH_FACTOR)
>>>> * UNIT_OFFSET_LENGTH;
>>>> int offsetWidthUnits = widthUnits % EXCEL_COLUMN_WIDTH_FACTOR;
>>>> pixels += Math.round((float) offsetWidthUnits /
>>>> ((float) EXCEL_COLUMN_WIDTH_FACTOR /
>>>> UNIT_OFFSET_LENGTH));
>>>> return pixels;
>>>> }
>>>> }
>>>>
>>>> Here it the html stuff you asked about. Note that it does depend on your
>>>> knowing in advance the sort of markup you will be getting; in terms of
>>>> the entities. If you do not know this then there are other options you
>>>> could use. One would be to create a full blown parser for the html - for
>>>> which there is support in the jdk - but even this relies upon your
>>>> knowing which tags mark the beginning and ending of the information you
>>>> are interested in.
>>>>
>>>> String htmlExpression = " <h3> " +
>>>> " User Interface </h3>";
>>>>
>>>> // I want to extract the words 'Uesr Interface' from the
>>>> html
>>>> // markup encapsulated within the hetmlExpression String.
>>>> Looking
>>>> // at it, I can see that the tag that folows the text I am
>>>> after
>>>> // is so the first thing to do is search for that tag.
>>>> //
>>>> // To do this, I am using the indexOf() method which returns
>>>> the
>>>> // position of the FIRST occurrence of it's parameter
>>>> starting
>>>> // from the beginning of the String. There is also the
>>>> lastIndexOf()
>>>> // method which will start at the end of the String and find
>>>> the
>>>> // last occurrence of it's parameter.
>>>> //
>>>> int endPoint = htmlExpression.indexOf("");
>>>> //
>>>> // As I am dealing with html, I know that the character that
>>>> marks
>>>> // the start point of the text I am after is the closing
>>>> brace of
>>>> // the preceding tag. So, I can search backwards through the
>>>> // htmlExpression for that closing brace starting from the
>>>> point
>>>> // I have already found above
>>>> //
>>>> int startPoint = htmlExpression.lastIndexOf(">", endPoint);
>>>> //
>>>> // Now I have both the starting and ending points for the
>>>> substring
>>>> // I can simply extract it using the substring() method.
>>>> //
>>>> System.out.println("[" +
>>>> htmlExpression.substring(startPoint, endPoint) +
>>>> "]");
>>>> //
>>>> // Note that here, you are also seeing the search character
>>>> - the >
>>>> // character - included in the substring. This is beacause
>>>> of the
>>>> // way the indexOf() family of methods works when combined
>>>> with the
>>>> // substring method; they assume you want to return the
>>>> search
>>>> // character. If you do not, then add on to the index the
>>>> length of the
>>>> // search String, in this case 1;
>>>> //
>>>> startPoint++;
>>>> System.out.println("[" +
>>>> htmlExpression.substring(startPoint,
>>>> endPoint).trim() +
>>>> "]");
>>>> //
>>>> // Note as well that I have also stripped out the whitespave
>>>> using
>>>> // the trim() method.
>>>>
>>>> As for spending the time to help out, it's an investment really as it
>>>> has yielded some code that can be added to the examples section of the
>>>> documentation and others will - hopefully - benefit.
>>>>
>>>> Yours
>>>>
>>>> Mark B
>>>>
>>>>
>>>> deep4u wrote:
>>>>
>>>>> 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?
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
>
Re: autosize in Excel irrespetive of image
Posted by MSB <ma...@tiscali.co.uk>.
Unfortunately, I am going to have to say 'no' to both of your questions.
However, ot should be possible for you to find out.
I would try running a piece of code like this;
for(int i = 0 ; i < 9999 ; i++) {
System.out.println("The integer value " + i + " maps to the character ["
+ (char)i + "]");
}
Then simply scroll through the output to see if the character you have in
mind is there. If itis, all you need do to use it in the list is something
like;
char arrowBulletChar = 10000;
assuming that is the value of course.
Run the loop in blocks of ten thousand - so 0 - 9999, 10000 - 19999, 20000 -
29999, etc as that will be plenty of characters to look through at any one
time.
There is one final point you will have to bear in mind; the font that is
used in the workbook may not be able to support the characters or characters
you indentify in this manner. The only answer is to test throughly.
Hope that helps.
Yours
Mark B
deep4u wrote:
>
> Hi Mark,
> what is the character value for arrow type of bullet. can u
> send out what are available characters.
>
> Thanks,
>
>
> MSB wrote:
>>
>> I am just weak and could not resist having a play. Anyway, here you go,
>> how to insert a bulleted list into a cell;
>>
>> File file = null;
>> FileOutputStream fos = null;
>> HSSFWorkbook workbook = null;
>> HSSFSheet sheet = null;
>> HSSFRow row = null;
>> HSSFCell cell = null;
>> HSSFCellStyle wrapStyle = null;
>> String contents = null;
>> // This is the code for the bullet character
>> char bulletChar = 8226;
>> // and this the code for the new line character. You may
>> // equally be able to use '\n' or '\r' but I did not try these
>> out
>> char newLineChar = 10;
>> try {
>> file = new File("........... Your file name
>> .................");
>> fos = new FileOutputStream(file);
>> workbook = new HSSFWorkbook();
>> sheet = workbook.createSheet();
>> // You need to set wrapping on for the cell, so create a
>> style with
>> // that attribute set.
>> wrapStyle = workbook.createCellStyle();
>> wrapStyle.setWrapText(true);
>> row = sheet.createRow(0);
>> cell = row.createCell(0);
>> // Note the bulletChar and newLineChar characters used when
>> // building the String for the cell.
>> contents = bulletChar +
>> "Item One" +
>> newLineChar +
>> bulletChar +
>> "Item Two" +
>> newLineChar +
>> bulletChar +
>> "Item Three" +
>> newLineChar +
>> bulletChar +
>> "Item Four";
>> cell.setCellValue(new HSSFRichTextString(contents));
>> // Set the cell sstyle to ensure the contents wrap.
>> cell.setCellStyle(wrapStyle);
>>
>> // This just sets the height of the row and the width to
>> allow the
>> // list to be seen clearly.
>> row.setHeight((short)1000);
>> sheet.setColumnWidth(0, 5000);
>> workbook.write(fos);
>>
>>
>>
>> deep4u wrote:
>>>
>>> Hi Mark,
>>> Thanks for spending u r valuable time. i have one more doubt
>>> can we create bullets in excel using apche poi.If u know please let me
>>> know how to do.
>>>
>>> Thanks,
>>>
>>> MSB wrote:
>>>>
>>>> Oh, well done, glad that I was finally able to help you find a solution
>>>> even if it should have been obvious to me that the anchor would only be
>>>> an influence once the file was opened using Excel, sorry about that.
>>>> Even though you have a workable solution, just in case there are others
>>>> following this thread, I am still going to post the demonstration code
>>>> I have put together - the html stuff is below everything else, so just
>>>> scroll down for that.
>>>>
>>>> Currently, it only adjusts the width of the image and only works in
>>>> situations where the column is far wider than the image. Later on, I am
>>>> going to see if I can extend it to deal with the situation where the
>>>> column is narrower than the image and where the image is placed across
>>>> a number of columns. Once I have done this, I will try to do similar
>>>> for rows.
>>>>
>>>> import java.io.File;
>>>> import java.io.FileInputStream;
>>>> import java.io.FileOutputStream;
>>>> import java.io.ByteArrayOutputStream;
>>>> import java.io.FileNotFoundException;
>>>> import java.io.IOException;
>>>>
>>>> import org.apache.poi.hssf.usermodel.HSSFWorkbook;
>>>> import org.apache.poi.hssf.usermodel.HSSFSheet;
>>>> import org.apache.poi.hssf.usermodel.HSSFRow;
>>>> import org.apache.poi.hssf.usermodel.HSSFCell;
>>>> import org.apache.poi.hssf.usermodel.HSSFCellStyle;
>>>> import org.apache.poi.hssf.usermodel.HSSFFont;
>>>> import org.apache.poi.hssf.usermodel.HSSFRichTextString;
>>>> import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
>>>> import org.apache.poi.hssf.usermodel.HSSFPatriarch;
>>>>
>>>> /**
>>>> * An instance of this class demonstrates that it is possible to
>>>> determine the
>>>> * width and location of an image dynamically.
>>>> *
>>>> * Currently, it is limited to setting the width of an image wihin a
>>>> column
>>>> * whose own width is far greater than that of the image. It cannot yet
>>>> handle
>>>> * situations where the image may lie across several columns and nor
>>>> can it
>>>> * (yet) alter the height of the image.
>>>> *
>>>> * @author Mark B [msb@apache.org]
>>>> * @version 1.00 30th July 2009.
>>>> */
>>>> public class WorkbookFromTemplate {
>>>>
>>>> public void buildWorkbookFromTemplate(String imageFilename,
>>>> String outputFilename)
>>>> throws IOException,
>>>> FileNotFoundException {
>>>> File outputFile = null;
>>>> FileOutputStream fos = null;
>>>> HSSFWorkbook workbook = null;
>>>> HSSFSheet sheet = null;
>>>> HSSFRow row = null;
>>>> HSSFCell cell = null;
>>>> HSSFCellStyle style = null;
>>>> HSSFFont font = null;
>>>> HSSFClientAnchor anchor = null;
>>>> HSSFPatriarch patriarch = null;
>>>> double columnWidthCharUnits = 0.0;
>>>> double columnWidthPixels = 0.0;
>>>> double columnWidthMillimetres = 0.0;
>>>> double coordinatePositionsPerMillimetre = 0.0;
>>>> int leftBorder = 0;
>>>> int pictureWidth = 0;
>>>>
>>>> // These values set the width of the border and the size of the
>>>> // image.
>>>> int reqBorder = 5;
>>>> int reqPictureWidth = 25;
>>>>
>>>> String contents = null;
>>>> try {
>>>> workbook = new HSSFWorkbook();
>>>> sheet = workbook.createSheet();
>>>> style = workbook.createCellStyle();
>>>> font = workbook.getFontAt((short)1);
>>>> // By changing the point size of the font from 10 to 12,
>>>> 14, 16, 8,
>>>> // etc, it is possible to check that the image is re-sized
>>>> correctly
>>>> // as the width of the column responds to changes in the
>>>> size of the
>>>> // font
>>>> font.setFontHeightInPoints((short)10);
>>>> style.setFont(font);
>>>>
>>>> // Populate the first cell in rows 9 to 20 with long
>>>> Strings of
>>>> // data so that the resize operation will be quite
>>>> dramatic.
>>>> for(int i = 9; i < 20; i++) {
>>>> row = sheet.createRow(i);
>>>> cell = row.createCell(0);
>>>> contents = ("Setting the value of cell 1 in row " +
>>>> i +
>>>> " the current time in milliseconds time is
>>>> " +
>>>> System.currentTimeMillis());
>>>> cell.setCellValue(new HSSFRichTextString(contents));
>>>> cell.setCellStyle(style);
>>>> }
>>>>
>>>> // Autosize the column to accomdate the contents
>>>> sheet.autoSizeColumn((short)0);
>>>>
>>>> // Recover the size of the column in Excel's character
>>>> units.
>>>> columnWidthCharUnits = sheet.getColumnWidth(0);
>>>> // Convert from Excels' character units into pixels to get
>>>> the total
>>>> // width of the column in pixels
>>>> columnWidthPixels = ExcelUtil.widthUnits2Pixel(
>>>> (short)columnWidthCharUnits);
>>>> // Convert from pixels to millimetres to get to total width
>>>> of the
>>>> // column in millimetres
>>>> columnWidthMillimetres = columnWidthPixels /
>>>> ExcelUtil.PIXELS_PER_MILLIMETRES;
>>>> // We 'know' that the column conatins a maximum of 1023
>>>> co-ordinate
>>>> // positions. Calculate how many of these co-ordinate
>>>> positions
>>>> // there are in a millimetre.
>>>> coordinatePositionsPerMillimetre =
>>>> ExcelUtil.TOTAL_COLUMN_COORDINATE_POSITIONS /
>>>> columnWidthMillimetres;
>>>> // Calculate the number of co-ordinate positions necessary
>>>> to leave
>>>> // a border to the left of the image the required
>>>> thickness.
>>>> leftBorder = (int)(reqBorder *
>>>> coordinatePositionsPerMillimetre);
>>>> // Calculate the number of co-ordinate positions necessary
>>>> to
>>>> // set the width of the image to the required number of
>>>> millimetres.
>>>> // Remember to add on the thickness of the left border.
>>>> pictureWidth = (int)(leftBorder +
>>>> (reqPictureWidth *
>>>> coordinatePositionsPerMillimetre));
>>>>
>>>> // Create the anchor instance. Note that the parameters
>>>> that
>>>> // specify the column(s) the image should occupy - they are
>>>> // parameters number 5 and 7 - both specify the same
>>>> column; in
>>>> // this case 0. Normally, this would prevent the image from
>>>> // being seen; the settings of the first four parameters
>>>> can
>>>> // be used to 'fine tune' this behaviour.
>>>> //
>>>> // Parameter 1 moves picture in from left hand edge.
>>>> // Parameter 2 moves top edge of picture downwards
>>>> // Parameter 3 moves the right hand edge of the image
>>>> // Parameter 4 moves the bottom edge of the image down
>>>> //
>>>> anchor = new HSSFClientAnchor(leftBorder, // Inset image
>>>> from left
>>>> 0, // Inset image
>>>> from top
>>>> pictureWidth, // Width of
>>>> image
>>>> 0, // Height of
>>>> image
>>>> (short)0, // 'From'
>>>> column
>>>> 0, // 'From' row
>>>> (short)0, // 'To' column
>>>> 8); // 'To' row
>>>> anchor.setAnchorType(3);
>>>>
>>>> // Add the image to the workbook
>>>> int index =
>>>> workbook.addPicture(this.imageToBytes(imageFilename),
>>>> HSSFWorkbook.PICTURE_TYPE_JPEG);
>>>> // Get the drawing patriarch and create the picture within
>>>> it
>>>> patriarch = sheet.createDrawingPatriarch();
>>>> patriarch.createPicture(anchor, index);
>>>>
>>>> // Save the file away
>>>> outputFile = new File(outputFilename);
>>>> fos = new FileOutputStream(outputFile);
>>>> workbook.write(fos);
>>>> }
>>>> finally {
>>>> if(fos != null) {
>>>> try {
>>>> fos.close();
>>>> fos = null;
>>>> }
>>>> catch(IOException ioEx) {
>>>> // Not much I can do here!!
>>>> }
>>>> }
>>>> }
>>>> }
>>>>
>>>> /**
>>>> * Loads - reads in and converts into an array of byte(s) - an
>>>> image from
>>>> * a named file.
>>>> *
>>>> * @param imageFilename A String that encapsulates the path to and
>>>> name
>>>> * of the file that contains the image which
>>>> is to be
>>>> * 'loaded'.
>>>> * @return An array of type byte that contains the raw data of the
>>>> named
>>>> * image.
>>>> * @throws java.io.FileNotFoundException Thrown if it was not
>>>> possible to
>>>> * open the specified file.
>>>> * @throws java.io.IOException Thrown if reading the file failed or
>>>> was
>>>> * interrupted.
>>>> */
>>>> private byte[] imageToBytes(String imageFilename)
>>>> throws FileNotFoundException,
>>>> IOException {
>>>> File imageFile = null;
>>>> FileInputStream fis = null;
>>>> ByteArrayOutputStream bos = null;
>>>> int read = 0;
>>>> try {
>>>> imageFile = new File(imageFilename);
>>>> fis = new FileInputStream(imageFile);
>>>> bos = new ByteArrayOutputStream();
>>>> while((read = fis.read()) != -1) {
>>>> bos.write(read);
>>>> }
>>>> return(bos.toByteArray());
>>>> }
>>>> finally {
>>>> if(fis != null) {
>>>> try {
>>>> fis.close();
>>>> fis = null;
>>>> }
>>>> catch(IOException ioEx) {
>>>> // Nothing to do here
>>>> }
>>>> }
>>>> }
>>>> }
>>>>
>>>> private String getContentsAsString(HSSFCell cell) {
>>>> String contents = null;
>>>> switch(cell.getCellType()) {
>>>> case HSSFCell.CELL_TYPE_BLANK:
>>>> case HSSFCell.CELL_TYPE_BOOLEAN:
>>>> case HSSFCell.CELL_TYPE_ERROR:
>>>> case HSSFCell.CELL_TYPE_FORMULA:
>>>> case HSSFCell.CELL_TYPE_NUMERIC:
>>>> contents = String.valueOf(cell.getNumericCellValue());
>>>> break;
>>>> case HSSFCell.CELL_TYPE_STRING:
>>>> contents = cell.getRichStringCellValue().getString();
>>>> break;
>>>> }
>>>> return(contents);
>>>> }
>>>>
>>>> /**
>>>> * @param args the command line arguments
>>>> */
>>>> public static void main(String[] args) {
>>>> try {
>>>> new WorkbookFromTemplate().buildWorkbookFromTemplate(
>>>> "...Path to and name of image file....",
>>>> "...Path to and name of document you want to
>>>> create....");
>>>> }
>>>> catch(IOException ioEx) {
>>>> System.out.println("Caught a: " +
>>>> ioEx.getClass().getName());
>>>> System.out.println("Message: " + ioEx.getMessage());
>>>> System.out.println("Stacktrace follows..................");
>>>> ioEx.printStackTrace(System.out);
>>>> }
>>>> }
>>>> }
>>>>
>>>> /**
>>>> * Utility methods used to perform conversions between Excel's
>>>> character
>>>> * based column and row size measurements and pixels. The clas also
>>>> contains
>>>> * various constants that are required in other calculations.
>>>> *
>>>> * @author xio[darjino@hotmail.com]
>>>> * @version 1.01 30th July 2009.
>>>> * Additional constants added by Mark B [msb@apache.org].
>>>> */
>>>> public class ExcelUtil {
>>>>
>>>> public static final int TOTAL_COLUMN_COORDINATE_POSITIONS = 1023;
>>>> // MB
>>>> public static final int TOTAL_ROW_COORDINATE_POSITIONS = 255;
>>>> // MB
>>>> public static final int PIXELS_PER_INCH = 96;
>>>> // MB
>>>> public static final double PIXELS_PER_MILLIMETRES = 3.78;
>>>> // MB
>>>> public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;
>>>> public static final int UNIT_OFFSET_LENGTH = 7;
>>>> public static final int[] UNIT_OFFSET_MAP = new int[]
>>>> { 0, 36, 73, 109, 146, 182, 219 };
>>>>
>>>> /**
>>>> * pixel units to excel width units(units of 1/256th of a character
>>>> width)
>>>> * @param pxs
>>>> * @return
>>>> */
>>>> public static short pixel2WidthUnits(int pxs) {
>>>> short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR *
>>>> (pxs / UNIT_OFFSET_LENGTH));
>>>> widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];
>>>> return widthUnits;
>>>> }
>>>>
>>>> /**
>>>> * excel width units(units of 1/256th of a character width) to
>>>> pixel units
>>>> * @param widthUnits
>>>> * @return
>>>> */
>>>> public static int widthUnits2Pixel(short widthUnits) {
>>>> int pixels = (widthUnits / EXCEL_COLUMN_WIDTH_FACTOR)
>>>> * UNIT_OFFSET_LENGTH;
>>>> int offsetWidthUnits = widthUnits % EXCEL_COLUMN_WIDTH_FACTOR;
>>>> pixels += Math.round((float) offsetWidthUnits /
>>>> ((float) EXCEL_COLUMN_WIDTH_FACTOR /
>>>> UNIT_OFFSET_LENGTH));
>>>> return pixels;
>>>> }
>>>> }
>>>>
>>>> Here it the html stuff you asked about. Note that it does depend on
>>>> your knowing in advance the sort of markup you will be getting; in
>>>> terms of the entities. If you do not know this then there are other
>>>> options you could use. One would be to create a full blown parser for
>>>> the html - for which there is support in the jdk - but even this relies
>>>> upon your knowing which tags mark the beginning and ending of the
>>>> information you are interested in.
>>>>
>>>> String htmlExpression = " <h3> " +
>>>> " User Interface </h3>";
>>>>
>>>> // I want to extract the words 'Uesr Interface' from the
>>>> html
>>>> // markup encapsulated within the hetmlExpression String.
>>>> Looking
>>>> // at it, I can see that the tag that folows the text I am
>>>> after
>>>> // is so the first thing to do is search for that tag.
>>>> //
>>>> // To do this, I am using the indexOf() method which
>>>> returns the
>>>> // position of the FIRST occurrence of it's parameter
>>>> starting
>>>> // from the beginning of the String. There is also the
>>>> lastIndexOf()
>>>> // method which will start at the end of the String and
>>>> find the
>>>> // last occurrence of it's parameter.
>>>> //
>>>> int endPoint = htmlExpression.indexOf("");
>>>> //
>>>> // As I am dealing with html, I know that the character
>>>> that marks
>>>> // the start point of the text I am after is the closing
>>>> brace of
>>>> // the preceding tag. So, I can search backwards through
>>>> the
>>>> // htmlExpression for that closing brace starting from the
>>>> point
>>>> // I have already found above
>>>> //
>>>> int startPoint = htmlExpression.lastIndexOf(">", endPoint);
>>>> //
>>>> // Now I have both the starting and ending points for the
>>>> substring
>>>> // I can simply extract it using the substring() method.
>>>> //
>>>> System.out.println("[" +
>>>> htmlExpression.substring(startPoint, endPoint) +
>>>> "]");
>>>> //
>>>> // Note that here, you are also seeing the search character
>>>> - the >
>>>> // character - included in the substring. This is beacause
>>>> of the
>>>> // way the indexOf() family of methods works when combined
>>>> with the
>>>> // substring method; they assume you want to return the
>>>> search
>>>> // character. If you do not, then add on to the index the
>>>> length of the
>>>> // search String, in this case 1;
>>>> //
>>>> startPoint++;
>>>> System.out.println("[" +
>>>> htmlExpression.substring(startPoint,
>>>> endPoint).trim() +
>>>> "]");
>>>> //
>>>> // Note as well that I have also stripped out the
>>>> whitespave using
>>>> // the trim() method.
>>>>
>>>> As for spending the time to help out, it's an investment really as it
>>>> has yielded some code that can be added to the examples section of the
>>>> documentation and others will - hopefully - benefit.
>>>>
>>>> Yours
>>>>
>>>> Mark B
>>>>
>>>>
>>>> deep4u wrote:
>>>>>
>>>>> 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-tp24604961p24792542.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