Search code examples
apache-poi

Apache POI: autoSizeColumn() with text rotation and wrap text

I'm using a prepared .xlsx file and edit it programatically. This is the original:

enter image description here

In my code, I autosize the column:

    XSSFSheet sheet = workbook.getSheetAt(0);

    sheet.getColumnStyle(COLUMN_H).setWrapText(true);
    System.out.println("COL " + sheet.getColumnStyle(COLUMN_H).getWrapText());  // it's 'true'
    
    sheet.autoSizeColumn(COLUMN_H);

The end result looks like this:

enter image description here

I hoped the column would shrink just enough to show all the lines of the text.

I'm using version 5.2.2 of the POI library.

Is this not what autosize does? Is there a way to calculate the prefered width of the column and set it explicitely?

When I double click on the border right of column H, the width is set as expected.

Solution

  • Yes, this is how Sheet.autoSizeColumn works up to now. It cannot do the same as Excel's GUI can do, simply because it does not have the same resources and possibilities. Excel's GUI runs as desktop application and so has access to the full memory and graphical system. Apache POI only has access to the memory which the Java JRE got and the data which is stored in the Excel file. That's not enough to do the same things, the Excel GUI can do.

    To be able to auto-size the width of Excel cells the program must know the font metrics and the text content in rich text format for each cell in the column to calculate the needed width for the text. This is what Apache POI tries up to now. And this is expensive enough when you see how much time Sheet.autoSizeColumn consumes only for a little amount of rows. In addition, this needs access to fonts and GUI-classes such as Java AWT for example. This is currently already causing problems in some server-systems.

    To be able additional considering the wrap-text case without having explicit line breaks in the text or when text is rotated too, the row heights for each row in the column also needs to be known. Apache POI does not take this into account until now. That's why Sheet.autoSizeColumn can only work correctly when there are explicit line breaks in the text and when the text is not rotated.

    The following complete example shows this.

    import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.SheetUtil;

public class ExcelAutoSizeColumn {

 public static void main(String args[]) throws Exception {
  //String testString = "Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.";

  String testString = 
    "Lorem ipsum dolor sit amet,\n"
   +"consetetur sadipscing elitr,\n"
   +"sed diam nonumy eirmod tempor \n"
   +"invidunt ut labore et dolore \n"
   +"magna aliquyam erat,\n"
   +"sed diam voluptua. At vero eos \n"
   +"et accusam et justo duo dolores \n"
   +"et ea rebum. Stet clita kasd gubergren,\n"
   +"no sea takimata sanctus est Lorem\n"
   +"ipsum dolor sit amet.";

  String filePath = "./Excel.xlsx";
  String fontName = "Calibri";
  short fontSize = 11;
  //short rotation = 0;
  //short rowHeight = -1;  //-1 for auto height
  short rotation = 90;
  short rowHeight = 180*20;
  boolean italic = false;
  boolean bold = false;

  try ( Workbook workbook = new XSSFWorkbook();
        java.io.FileOutputStream out = new java.io.FileOutputStream(filePath)) {
            
   Font font = workbook.createFont();
   font.setFontHeightInPoints(fontSize);
   font.setFontName(fontName);
   font.setItalic(italic);
   font.setBold(bold);
   CellStyle style = workbook.createCellStyle();
   style.setFont(font);
   style.setWrapText(true);
   style.setRotation(rotation);

   Sheet sheet = workbook.createSheet();
   Row row = sheet.createRow(0);
   row.setHeight(rowHeight);
   Cell cell = row.createCell(0);
   cell.setCellValue(testString);
   cell.setCellStyle(style);
  
   double width = SheetUtil.getColumnWidth(sheet, 0, true);
   System.out.println(width);

   sheet.autoSizeColumn(0, true);

   workbook.write(out);
  }
 }
}

    You could file a bug report about this in https://bz.apache.org/bugzilla/buglist.cgi?list_id=201224&product=POI. But I doubt it will be fixed soon, because it will increase the time consuming of that method very much.