Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JXL Cell Formatting

Tags:

jxl

How to autofit content in cell using jxl api?

like image 934
SAK Avatar asked Nov 03 '09 05:11

SAK


4 Answers

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.

like image 88
clang1234 Avatar answered Nov 20 '22 21:11

clang1234


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);
    }
}
like image 33
neuro_sys Avatar answered Nov 20 '22 23:11

neuro_sys


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.

like image 6
John Avatar answered Nov 20 '22 21:11

John


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.

like image 2
Brian Beckett Avatar answered Nov 20 '22 23:11

Brian Beckett