Search code examples
excelapache-poisxssf

Increasing height merged cells Apache POI

I am using Apache POI SXSSF to generate xlsx document. The document uses Times New Roman sizes 9 and 11, and the default cell width and height have been changed. The question is how to calculate the height of the merged cells so that all the text fits (the height of the cell must be dynamically set according to the given text)? The server running the application does not have a display, and this code is running in the IBM Integration Bus.

The solution from How to get the needed height of a multi line rich-text field (any font, any font size) having defined width using Java? is not suitable. The server running the application is missing a display and the string int ppi = java.awt.Toolkit.getDefaultToolkit().getScreenResolution(); returns an exception, and manually picking the ppi value is also not possible. If there is a display, everything works correctly.

And is there any way to use the "align center selection" function somehow?

Solution

  • I found that centering a selection gives a similar result as merging multiple cells horizontally, but I couldn't find an answer anywhere on how to use this in Apache POI. As a result, experimentally, I found out that in order to achieve this effect, you need to do the following things:

    1. Create CellStyle; specify setWrapText(true) and setAlignment(HorizontalAlignment.CENTER_SELECTION) for it
    2. Apply the style created in step 1 to all cells that need to be merged
    3. Specify the value in the first cell

    Code example:

    Font font = wb.createFont(); // where wb - is SXSSFWorkbook object
font.setFontName("Times New Roman");
font.setFontHeightInPoints((short) 11);

CellStyle style = wb.createCellStyle();
style.setFont(font);
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER_SELECTION);

for (int i = 0; i <= endCellNum - firstCellNum; i++){ // where endCellNum - number of last cell of selection and firstCellNum is number of first cell of selection
    Cell cell = curRow.createCell(firstCellNum + i);
    cell.setCellStyle(cs);
    if (i == 0){
        firstCell = cell;
    }
}

firstCell.setCellValue(value);