Apache POI causing autosave after autofilter

Question

I searched for a while and have not yet found a solution.

This question was asked before, but the OP did not receive an answer, and I did not want to revive the old thread, so I decided to ask a new question. Stream OP here .

The problem I am facing is that I have a spreadsheet that is created with data retrieved from the database, however sometimes the data in the cells can be quite long, so it requires the POI to be automatically sorted in order to save the user this but since I install the autofilter before calling autosize, it doesn’t quite work.

I am using Apache POI 3.9.

I either get to the point where it is authorized, but does not take into account the arrow of the autofilter drop-down list, or I get an exception with a null pointer.

I tried moving the for loop all over the place, including at the end, where the data is written to the spreadsheet, as well as immediately before the file output stream, but to no avail.

I also tried using a few different fonts, but that didn't work either.

Hope someone can help me.

thanks

Answer 1

I am the one who wrote the source stream you specified. In the end, I reached a solution, not what I was looking for, but at least it works for me. I just forgot to update my question with the solution I found.

I created a new method that repeats over the columns of the sheet that I want to authorize, and I do two things. First, I automatically sort the column, so we will have a width that we don’t want, because it does not take into account the width of the arrow. Then I set the column width manually, including the arrow width. I had to play a bit to find the width of the arrow (for me it is 1300). I suppose this width may work for you, but you can set it the way you want.

As you can imagine, you must first get the data autofilter. After that, you call the autosave method of the columns, for example 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(new Short(String.valueOf(i))); try { excelSheet.setColumnWidth(i, excelSheet.getColumnWidth(i) + WIDTH_ARROW_BUTTON); } catch (final Exception e) { // don't do anything - just let autosize handle it } } } 

Answer 2

As I understand it, the text of the filter pop-up window you selected is hidden due to the arrow. If I'm right, why don't you just indent after auto-parsing! I mean automate col first with:

 testSheet.autoSizeColumn(ColIndex); 

then calculate the width of the column and add the desired fill width

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

this will give a sufficient addition for the arrow icon directly to the drop-down menu, and the text will be fully displayed.

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); }