JXL Cell Formatting

Question

How to autofit content in cell using jxl api?

Bisector

Answer 1

I know this is an old question at this point, but I was looking for the solution to this and thought I would post it in case someone else needs it.

CellView Auto-Size

I'm not sure why the FAQ doesn't mention this, because it very clearly exists in the docs.

My code looked like the following:

for(int x=0;x<c;x++)
{
    cell=sheet.getColumnView(x);
    cell.setAutosize(true);
    sheet.setColumnView(x, cell);
}

c stores the number of columns created
cell is just a temporary place holder for the returned CellView object
sheet is my WriteableSheet object

The Api warns that this is a processor intensive function, so it's probably not ideal for large files. But for a small file like mine (<100 rows) it took no noticeable time.

Hope this helps someone.

Genocide

Thank you very much! Could you add the type of the cell? That is CellView ? – Bobwhite

Answer 2

The method is self explanatory and commented:

private void sheetAutoFitColumns(WritableSheet sheet) {
    for (int i = 0; i < sheet.getColumns(); i++) {
        Cell[] cells = sheet.getColumn(i);
        int longestStrLen = -1;

        if (cells.length == 0)
            continue;

        /* Find the widest cell in the column. */
        for (int j = 0; j < cells.length; j++) {
            if ( cells[j].getContents().length() > longestStrLen ) {
                String str = cells[j].getContents();
                if (str == null || str.isEmpty())
                    continue;
                longestStrLen = str.trim().length();
            }
        }

        /* If not found, skip the column. */
        if (longestStrLen == -1) 
            continue;

        /* If wider than the max width, crop width */
        if (longestStrLen > 255)
            longestStrLen = 255;

        CellView cv = sheet.getColumnView(i);
        cv.setSize(longestStrLen * 256 + 100); /* Every character is 256 units wide, so scale it. */
        sheet.setColumnView(i, cv);
    }
}

Alpers

Answer 3

for(int x=0;x<c;x++)
{
    cell=sheet.getColumnView(x);
    cell.setAutosize(true);
    sheet.setColumnView(x, cell);
}

It is fine, instead of scanning all the columns. Pass the column as a parameter.

void display(column) 
{ 
    Cell = sheet.getColumnView(column);
    cell.setAutosize(true);
    sheet.setColumnView(column, cell);
}

So when you wiill be displaying your text you can set the particular length. Can be helpfull for huge excel files.

Eshelman

Answer 4

From the JExcelApi FAQ

How do I do the equivilent of Excel's "Format/Column/Auto Fit Selection"?

There is no API function to do this for you. You'll need to write code that scans the cells in each column, calculates the maximum length, and then calls setColumnView() accordingly. This will get you close to what Excel does but not exactly. Since most fonts have variable width characters, to get the exact same value, you would need to use FontMetrics to calculate the maximum width of each string in the column. No one has posted code on how to do this yet. Feel free to post code to the Yahoo! group or send it directly to the FAQ author's listed at the bottom of this page.

FontMetrics presumably refers to java.awt.FontMetrics. You should be able to work something out with the getLineMetrics(String, Graphics) method I would have though.

Drice

Answer 5

CellView's autosize method doesn't work for me all the time. My way of doing this is by programatically set the size(width) of the column based on the highest length of data in the column. Then perform some mathematical operations.

CellView cv = excelSheet.getColumnView(0);
cv.setSize((highest + ((highest/2) + (highest/4))) * 256);

where highest is an int that holds the longest length of data in the column.

Kironde

Answer 6

setAutosize() method WILL NOT WORK if your cell has over 255 characters. This is related to the Excel 2003 max column width specification: http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

You will need to write your own autosize method to handle this case.

Onomatology

Answer 7

Try this exemple:

 expandColumns(sheet, 3);

    workbook.write();
    workbook.close();

private void expandColumn(WritableSheet sheet, int amountOfColumns){
    int c = amountOfColumns;
    for(int x=0;x<c;x++)
    {
        CellView cell = sheet.getColumnView(x);
        cell.setAutosize(true);
        sheet.setColumnView(x, cell);
    }
}

Leventis

Answer 8

Kotlin's implementation

private fun sheetAutoFitColumns(sheet: WritableSheet, columnsIndexesForFit: Array<Int>? = null, startFromRowWithIndex: Int = 0, excludeLastRows : Int = 0) {
    for (columnIndex in columnsIndexesForFit?.iterator() ?: IntProgression.fromClosedRange(0, sheet.columns, 1).iterator()) {
        val cells = sheet.getColumn(columnIndex)
        var longestStrLen = -1
        if (cells.isEmpty()) continue
        for (j in startFromRowWithIndex until cells.size - excludeLastRows) {
            if (cells[j].contents.length > longestStrLen) {
                val str = cells[j].contents
                if (str == null || str.isEmpty()) continue
                longestStrLen = str.trim().length
            }
        }
        if (longestStrLen == -1) continue
        val newWidth = if (longestStrLen > 255) 255 else longestStrLen
        sheet.setColumnView(columnIndex, newWidth)
    }
}

example for use

sheetAutoFitColumns(sheet) // fit all columns by all rows
sheetAutoFitColumns(sheet, arrayOf(0, 3))// fit A and D columns by all rows 
sheetAutoFitColumns(sheet, arrayOf(0, 3), 5)// fit A and D columns by rows after 5 
sheetAutoFitColumns(sheet, arrayOf(0, 3), 5, 2)// fit A and D columns by rows after 5 and ignore two last rows

Engaged