String formatting in cells?

[Michael L <ic...@gmail.com>]

Is there a way to format the string put in a cell so that it will attempt to fit
in the visible dimensions of the cell? Currently, if I put a string whose length
is longer than that of the cell's width, the string is cut off and can only be
read if the reader manually extends the width of the cell. I guess what I really
want is for the string to go to a new line in the cell when it reaches the end
of the cell's width. Is this possible using POI?


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: String formatting in cells?

[Michael L <ic...@gmail.com>]

Thanks a lot, that helped tremendously. I had written a function to insert
newline characters into the string and while it worked, it didn't seem
particularly elegant. I've found that it won't wrap the text unless you set the
cell's value first before setting the cell's cellstyle(with its setWrapText set
to true) which was giving me some problems but it works when the cell's
cellstyle after setting its value.



---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: String formatting in cells?

[MSB <ma...@tiscali.co.uk>]

It is time for me to retire I think. Ignore last message and simply try this;

    public static void main(String[] args) {
        File file = null;
        FileOutputStream fos = null;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        HSSFCellStyle wrapStyle = null;
        try {
            file = new File("C:/temp/Wrap Test.xls");
            fos = new FileOutputStream(file);

            workbook = new HSSFWorkbook();

            wrapStyle = workbook.createCellStyle();
            wrapStyle.setWrapText(true);

            sheet = workbook.createSheet("Wrap Demo.");
            row = sheet.createRow(0);
            cell = row.createCell(0);

            cell.setCellValue("This is a slightly longer String.");
            cell.setCellStyle(wrapStyle);

            workbook.write(fos);
        }
        catch(IOException ioEx) {
            System.out.println("Caught: " + ioEx.getClass().getName());
            System.out.println("Message: " + ioEx.getMessage());
            System.out.println("Stacktrace follows:............");
            ioEx.printStackTrace(System.out);
        }
        finally {
            if(fos != null) {
                try {
                    fos.close();
                }
                catch(IOException ioEx) {
                    // I G N O R E
                }
            }
        }
    }

The key is the cell style object, it ensures that the long String will be
wrapped in the cell, that the 'breaks' occur at the word boundaries and
Excel itself ensures that the row is shown expanded to the correct height to
accomodate the cells contents.

Yours

Mark B


Michael L-3 wrote:
> 
> Is there a way to format the string put in a cell so that it will attempt
> to fit
> in the visible dimensions of the cell? Currently, if I put a string whose
> length
> is longer than that of the cell's width, the string is cut off and can
> only be
> read if the reader manually extends the width of the cell. I guess what I
> really
> want is for the string to go to a new line in the cell when it reaches the
> end
> of the cell's width. Is this possible using POI?
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
> 
> 
> 

-- 
View this message in context: http://old.nabble.com/String-formatting-in-cells--tp26649280p26654385.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: String formatting in cells?

[MSB <ma...@tiscali.co.uk>]

I am not certain just what your requirements/constraints are so this
suggestion may be useless; can you not simply increase the width of the cell
so that it is able to accomodate the String? Obvisouly, this means that the
width of the column will be set to accomodate the longest String but there
is already a method in the API to accomplish just this task. Take a look at
the autoSizeColumn() method that is defined on both of the HSSFSheet and
XSSFSheet classes. Typically, you use this by iterating through all of the
columns on a sheet immediately prior to saving the workbook away.

To answer your original question, yes it is possible to recover the width of
the column, find out how long the String is, insert a carriage
return/carriage returns at the appropriate place(s) in the String, write the
String way to the cell and set the cells format so that it's contents wrap
but this could present you with other problems; what if the row was not high
enough? Then the users would have to scroll the contents of the cell up and
down within the cell to view them fully. Of course you can increase the
rows' height but it may be that setting the columns width is the easiest,
neatest answer if this fits your requirement.

Yours

Mark B


Michael L-3 wrote:
> 
> Is there a way to format the string put in a cell so that it will attempt
> to fit
> in the visible dimensions of the cell? Currently, if I put a string whose
> length
> is longer than that of the cell's width, the string is cut off and can
> only be
> read if the reader manually extends the width of the cell. I guess what I
> really
> want is for the string to go to a new line in the cell when it reaches the
> end
> of the cell's width. Is this possible using POI?
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
> 
> 
> 

-- 
View this message in context: http://old.nabble.com/String-formatting-in-cells--tp26649280p26653493.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