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 [[email protected]] * @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[[email protected]] * @version 1.01 30th July 2009. * Additional constants added by Mark B [[email protected]]. */ 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-tp24604961p24741313.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]
