Apache POI, calling autosize after auto filter

Question

I have been searching for a while, and haven't been able to find a solution as yet.

This question has been asked before on here but the OP didn't get a response and I didn't want to resurrect an old thread so decided on asking a new question. The OP thread is here.

The problem I'm having is that I've got a spreadsheet which is created with data obtained from a database, however sometimes the data within cells can be quite lengthy, so wanted POI to autosize to save the user having to do it, but because I'm setting autofilter before calling autosize, it's not quite working.

I'm using Apache POI 3.9.

I either get it to a point where it's autosized but hasn't taken into account the autofilter dropdown arrow, or I get a null pointer exception.

I've tried moving a for loop all over the place, including at the end of where the data is written to the spreadsheet, and also to just before the file output stream, but to no avail.

I have also tried using a couple of different fonts but that hasn't worked either.

Hoping someone can help me out please.

Thanks

Gayl

Answer 1

I'm the one that wrote the original thread you referred. At the end I achieved a solution, not the one I was looking for but at least it is working for me. I just forgot to update my question with the solution I found.

I created a new method that iterates over the columns of the sheet I want to autosize and do two things. First I autosize the column so we will have the width we don't want because it doesn't take into account the arrow's width. Then I set the width of the column manually including the width of the arrow. I had to play a little bit to find the width of the arrow (which for me is 1300). I guess this width can work for you but you're free to set it as you want.

As you can imagine, you should first get and autofilter the data. After that you call a method to autosize the columns, like the one I used:

private static final int WIDTH_ARROW_BUTTON = 1300;

private void autosizeColumnsFromSheet(final Sheet excelSheet, final int fromColumn, final int toColumn) {
        for (int i = fromColumn; i <= toColumn; i++) {
            excelSheet.autoSizeColumn(i);
            try {
                excelSheet.setColumnWidth(i, excelSheet.getColumnWidth(i) + WIDTH_ARROW_BUTTON);
            } catch (final Exception e) {
                // don't do anything - just let autosize handle it
            }
        }
    }

Baty

I think you can update the line excelSheet.autoSizeColumn(new Short(String.valueOf(i))); into excelSheet.autoSizeColumn(i); which would be easier to read :) – Ietta

Thanks @JosephBudin, totally agree with you! It's been a long time since the post and I don't know why I was using Short instead of just using the int. – Baty

Answer 2

As per my understanding, your selected text of the filter dropdown is hiding because of arrow. If I am right, why not you just give some padding after autosize! I mean first autosize the col by:

testSheet.autoSizeColumn(ColIndex);

then calculate the column width and add some desired padding in the width

testSheet.setColumnWidth(ColIndex ,testSheet.getColumnWidth(ColIndex)+PaddingWidth);

this will give sufficient padding for arrow icon right to the dropdown and the text will comlpletely appear.

Bona

Answer 3

            int WIDTH_ARROW_BUTTON = 2 * 255;
        for (int i = 0; i < row.getLastCellNum(); i++) {
            sheet.autoSizeColumn(i);
            // For filter additional arrow width
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) + WIDTH_ARROW_BUTTON);
        }

Greenbrier