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?
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
--
View this message in context:
http://www.nabble.com/autosize-in-Excel-irrespetive-of-image-tp24604961p24759023.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]
