Autosize Column Width Using Poi

Apache POI Excel - how to configure columns to be expanded?

After you have added all your data to the sheet, you can call autoSizeColumn(int column) on your sheet to autofit the columns to the proper size

Here is a link to the API.

See this post for more reference
Problem in fitting the excel cell size to the size of the content when using apache poi

How to speed up autosizing columns in apache POI?

Solution which worked for me:

It was possible to avoid merged regions, so I could iterate through the other cells and finally autosize to the largest cell like this:

int width = ((int)(maxNumCharacters * 1.14388)) * 256;
sheet.setColumnWidth(i, width);

where 1.14388 is a max character width of the "Serif" font and 256 font units.

Performance of autosizing improved from 10 minutes to 6 seconds.

How to set fixed column width in Apache POI

setColumnWidth(int, int) should work ... is it because you reset the sizes to auto in your loop?

for (int i = 0; i < headers.length; i++) {
    sheet.autoSizeColumn(i);
}

Start your loop from 1 to headers.length instead.

apache poi excel big auto column width

First Select the first row or header because only Header can give you the max number of cells in a row.

HSSFRow row = wb.getSheetAt(0).getRow(0);

Then use autoSizeColumn on each column of that row

for(int colNum = 0; colNum<row.getLastCellNum();colNum++)   
    wb.getSheetAt(0).autoSizeColumn(colNum);

This will set the column width same as that of its Header width.

Using POI to both detect and set column width and to apply text wrap based on that width

First approach

Set wrap text cell style for whole description column (column B) in your template using Excel's GUI. Then do using following getPreferredCellStyle method to get that column cell style and set it as the preferred cell style for each cell in description column (column B).

 CellStyle getPreferredCellStyle(Cell cell) {
  // a method to get the preferred cell style for a cell
  // this is either the already applied cell style
  // or if that not present, then the row style (default cell style for this row)
  // or if that not present, then the column style (default cell style for this column)
  CellStyle cellStyle = cell.getCellStyle();
  // if no explicit cell style applied then cellStyle.getIndex() is 0 for XSSF
  // or 15 (0xF = the index to the default ExtendedFormatRecord (0xF)) for HSSF
  if ((cell instanceof XSSFCell && cellStyle.getIndex() == 0) || (cell instanceof HSSFCell && cellStyle.getIndex() == 15)) cellStyle = cell.getRow().getRowStyle();
  if (cellStyle == null) cellStyle = cell.getSheet().getColumnStyle(cell.getColumnIndex());
  if (cellStyle == null) cellStyle = cell.getCellStyle();
  return cellStyle;
 }

Then

...
Cell description = nextRow.createCell(1);
description.setCellValue(fizz.getDescription());
description.setCellStyle(getPreferredCellStyle(description));
...

Second approach

Do using CellUtil to set wrap text cell style for each cell in description column.

...
Cell description = nextRow.createCell(1);
description.setCellValue(fizz.getDescription());
CellUtil.setCellStyleProperty(description, CellUtil.WRAP_TEXT, true);
...

For both approaches

Do not set autoSizeColumn for the description column (column B). In your example, only autosize column 0 (A) but not column 1 (B):

...
sheet.autoSizeColumn(0);
...

So the column width of column B remains unchanged as width as it is in the template.

How to auto adjust the column in excel in apache POI

You can use HSSFSheet.autoSizeColumn(columnNumber) method to align the columns perfectly.

This method adjusts the column width to fit the contents, read the doc.

After setting all cell values for all columns you can use this method, in your current code call this method after for loop.

Sample code

sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);

Note - You have to do this separately for all columns which you want to be aligned and the call to sheet.autoSizeColumn(columnNumber) should be made after populating the data into the excel. Calling before populating data will not have any effect.

Apache POI and autofit XSSFRow heigth by content

As long as the row is not within a merged region, then it will auto height if the height is not set explicitly. So you need set height to undefined (-1). See Row.setHeight:

Set the row's height or set to ff (-1) for undefined/default-height.

Complete example:

Source Excel.xlsx:

Code:

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;

public class CreateExcelCellWrapText {

 public static void main(String[] args) throws Exception {
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./Excel.xlsx"));

  Sheet sheet = workbook.getSheetAt(0);
  
  Row row = sheet.getRow(2);
  Cell cell = row.getCell(2); // cell C3
  cell.setCellValue("Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua");
  CellUtil.setCellStyleProperty(cell, CellUtil.WRAP_TEXT, true); // make sure wrap text is set.
  row.setHeight((short)-1); // set row heigth undefined so it is auto heigth

  FileOutputStream out = new FileOutputStream("./ExcelNew.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

Result ExcelNew.xlsx:

If you don't know which rows must be auto height and need to decide that from the required height, then you need calculating the required height dependent of cell width, text and used font. That's a challenging task. See How to get the needed height of a multi line rich-text field (any font, any font size) having defined width using Java? for a possible solution.

---

Related Topics