Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Apache POI autoSizeColumn Resizes Incorrectly

I'm using Apache POI in java to create an excel file. I fill in the data then try to autosize each column, however the sizes are always wrong (and I think consistent). The first two rows are always(?) completely collapsed. When I autosize the columns in excel, it works perfectly.

No blank cells are being written (I believe) and the resizing is the last thing I do.

Here's the relevant code: This is a boiled down version without error handling, etc.

public static synchronized String storeResults(ArrayList<String> resultList, String file) {     if (resultList == null || resultList.size() == 0) {         return file;     }     FileOutputStream stream = new FileOutputStream(file);      //Create workbook and result sheet     XSSFWorkbook book = new XSSFWorkbook();     Sheet results = book.createSheet("Results");      //Write results to workbook     for (int x = 0; x < resultList.size(); x++) {         String[] items = resultList.get(x).split(PRIM_DELIM);          Row row = results.createRow(x);         for (int i = 0; i < items.length; i++) {             row.createCell(i).setCellValue(items[i]);         }     }      //Auto size all the columns     for (x = 0; x < results.getRow(0).getPhysicalNumberOfCells(); x++) {         results.autoSizeColumn(x);     }      //Write the book and close the stream     book.write(stream);     stream.flush();     stream.close();      return file; } 

I know there are a few questions out there similar, but most of them are simply a case of sizing before filling in the data. And the few that aren't are more complicated/unanswered.

EDIT: I tried using a couple different fonts and it didn't work. Which isn't too surprising, as no matter what the font either all the columns should be completely collapsed or none should be.

Also, because the font issue came up, I'm running the program on Windows 7.

SOLVED: It was a font issue. The only font that I found that worked was Serif.

like image 882
Jaws212 Avatar asked Jun 05 '13 15:06

Jaws212


2 Answers

Just to make an answer out of my comment. The rows couldn't size properly because Java was unaware of the font you were trying to use this link should help if you want to install new fonts into Java so you could use something fancier. It also has the list of default fonts that Java knows.

Glad this helped and you got your issue solved!

like image 108
Shaded Avatar answered Sep 22 '22 02:09

Shaded


This is probably related to this POI Bug which is related to Java Bug JDK-8013716: Renderer for Calibri and Cambria Fonts fails since update 45.

In this case changing the Font or using JRE above 6u45 / 7u21 should fix the issue.

You can also mtigitate the issue and avoid the columns from being totally collapsed by using a code like this:

    sheet.autoSizeColumn(x);      if (sheet.getColumnWidth(x) == 0) {       // autosize failed use MIN_WIDTH       sheet.setColumnWidth(x, MIN_WIDTH);     } 
like image 44
Sebastien Avatar answered Sep 21 '22 02:09

Sebastien