Expand Apache POI Excel column

Question

I created this class with the code necessary to simulate what I need. The need is to generate an Excel file with some data and, that the cell ( the size of the ballot) is enough to accommodate the text. In most posts I found the indication to use:

sheet.autoSizeColumn(0)

Also

Cellstyle style = Cell.getCellStyle()

style.setWrapText(true)

Cell.setCellStyle(style)

but the setWrapText does the text break automatically, and it’s not what I wanted.

I did not get the desired result. I would like indications of what I can do to resolve.

Update: Using this snippet of code:

   // style.setWrapText(true);
    Cell cellSer1 = row.createCell(0);
    // cellSer1 = row.createCell(0);
    cellSer1.setCellStyle(style);
    String myCellSer1 = "Nota Fiscal";
    String text = myCellSer1;
    int width = text.length();
    sheet.setColumnWidth(0, width * 250);
    String stringCellValueSer1 = myCellSer1;
    cellSer1.setCellValue(stringCellValueSer1);

I got something, I can change the size of the column, but it’s laborious,.

Update2:

Exception in thread "main" java.lang.Illegalstateexception: This Worksheet does not contain merged Regions at org.apache.poi.xssf.usermodel.XSSFSheet.getMergedRegions(Xssfsheet.java:1116) at org.apache.poi.ss.util.SheetUtil.getCellWidth(Sheetutil.java:110) at org.apache.poi.ss.util.SheetUtil.getColumnWidthForRow(Sheetutil.java:282) at org.apache.poi.ss.util.SheetUtil.getColumnWidth(Sheetutil.java:235) at org.apache.poi.ss.util.SheetUtil.getColumnWidth(Sheetutil.java:214) at org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(Xssfsheet.java:400) at org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(Xssfsheet.java:382) at view.XLSTest.main(Xlstest.java:98)

Update3:

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class XLSTest {

    public static void main(String[] args) throws FileNotFoundException {

         HSSFWorkbook wb = new HSSFWorkbook();
 HSSFSheet sheet = wb.createSheet("sheetName");
 File file;
 file = new File("C:\\Temp\\Notas.xls");
 FileOutputStream fos = new FileOutputStream(file);

 HSSFRow row;
 int count = 0;
 String posicaoExcel = null;

 row = sheet.createRow((short)count);

 Cell cellSer1 = row.createCell(0);
 String myCellSer1 = "Nota Fiscal";
 String stringCellValueSer1 = myCellSer1;
 cellSer1.setCellValue(stringCellValueSer1);

 Cell cellnf1 = row.createCell(1);
 String myCell1 = "Série";
 String stringCellValue1 = myCell1;
 cellnf1.setCellValue(stringCellValue1);

 for (int i = 0; i <= 5; i++) {
     count++;
     posicaoExcel = "longgggggggggggggggggggggggggggg";

     row = sheet.createRow((short)count);
     Cell cellSer = row.createCell(0);
     String myCellSer = posicaoExcel;
     String stringCellValueSer = myCellSer;
     cellSer.setCellValue(stringCellValueSer);


     Cell cellnf = row.createCell(1);       
     String myCell = posicaoExcel;
     String stringCellValue = myCell;
     cellnf.setCellValue(stringCellValue);
 }

 int quantidadeColunas = sheet.getRow(0).getPhysicalNumberOfCells();
 for(int i = 0; i < quantidadeColunas; i++ ) {
      sheet.autoSizeColumn(i);
 }

 try {
     wb.write(fos);
 } catch (IOException ex) {
     Logger.getLogger(XLSTest.class.getName()).log(Level.SEVERE, null, ex);
 }

 try {
     fos.flush();
 } catch (IOException ex) {
     Logger.getLogger(XLSTest.class.getName()).log(Level.SEVERE, null, ex);
 }
    }
}

---

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XLSTest {

    public static void main(String[] args) throws FileNotFoundException {

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheetName");
        File file;
        file = new File("C:\\Users\\Lucio\\Documents\\Notas.xls");
        FileOutputStream fos = new FileOutputStream(file);

        XSSFRow row;
        int count = 0;
        String posicaoExcel = null;

        XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        row = sheet.createRow(count);

        Cell cellSer1 = row.createCell(0);
        // cellSer1 = row.createCell(0);
        cellSer1.setCellStyle(style);
        String myCellSer1 = "Nota Fiscal";
        String stringCellValueSer1 = myCellSer1;
        cellSer1.setCellValue(stringCellValueSer1);
        // sheet.autoSizeColumn(1);

        Cell cellnf1 = row.createCell(1);
        // cellnf1 = row.createCell(1);
        String myCell1 = "Série";
        cellnf1.setCellStyle(style);
        String stringCellValue1 = myCell1;
        cellnf1.setCellValue(stringCellValue1);

        //sheet.autoSizeColumn(2);
        for (int i = 0; i <= 5; i++) {
            count++;
            posicaoExcel = "longgggggggggggggggggggggggggggg";

            row = sheet.createRow(count);
            Cell cellSer = row.createCell(0);
            // cellSer = row.createCell(0);
            String myCellSer = posicaoExcel;
            String stringCellValueSer = myCellSer;
            cellSer.setCellValue(stringCellValueSer);
            cellSer.setCellStyle(style);
            // sheet.autoSizeColumn(1);

            Cell cellnf = row.createCell(1);
            //cellnf = row.createCell(1);
            String myCell = posicaoExcel;
            String stringCellValue = myCell;
            cellnf.setCellValue(stringCellValue);
            cellnf.setCellStyle(style);
            // sheet.autoSizeColumn(2);

        }

        //loop para alterar o tamanho da coluna
        int t = count;
        for (int i = 0; i < t; i++) {
            sheet.autoSizeColumn(t);
        }

        try {
            wb.write(fos);
        } catch (IOException ex) {
            Logger.getLogger(XLSTest.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {
            fos.flush();
        } catch (IOException ex) {
            Logger.getLogger(XLSTest.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

Answer 1

The problem with your for to change the column size is that it uses the row value as the basis, not the column value.

To get the number of columns, you can use the method getPhysicalNumberOfCells()

 int quantidadeColunas = sheet.getRow(0).getPhysicalNumberOfCells();
 for(int i = 0; i < quantidadeColunas; i++ ) {
      sheet.autoSizeColumn(i);
 }

Upshot:

Complete code:

 XSSFWorkbook wb = new XSSFWorkbook();
 XSSFSheet sheet = wb.createSheet("sheetName");
 File file;
 file = new File("C:\\Temp\\Notas.xls");
 FileOutputStream fos = new FileOutputStream(file);

 XSSFRow row;
 int count = 0;
 String posicaoExcel = null;

 row = sheet.createRow(count);

 Cell cellSer1 = row.createCell(0);
 String myCellSer1 = "Nota Fiscal";
 String stringCellValueSer1 = myCellSer1;
 cellSer1.setCellValue(stringCellValueSer1);

 Cell cellnf1 = row.createCell(1);
 String myCell1 = "Série";
 String stringCellValue1 = myCell1;
 cellnf1.setCellValue(stringCellValue1);

 for (int i = 0; i <= 5; i++) {
     count++;
     posicaoExcel = "longgggggggggggggggggggggggggggg";

     row = sheet.createRow(count);
     Cell cellSer = row.createCell(0);
     String myCellSer = posicaoExcel;
     String stringCellValueSer = myCellSer;
     cellSer.setCellValue(stringCellValueSer);


     Cell cellnf = row.createCell(1);       
     String myCell = posicaoExcel;
     String stringCellValue = myCell;
     cellnf.setCellValue(stringCellValue);
 }

 int quantidadeColunas = sheet.getRow(0).getPhysicalNumberOfCells();
 for(int i = 0; i < quantidadeColunas; i++ ) {
      sheet.autoSizeColumn(i);
 }

 try {
     wb.write(fos);
 } catch (IOException ex) {
     Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
 }

 try {
     fos.flush();
 } catch (IOException ex) {
     Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
 }