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 [[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?
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
>
