Reputation: 19071

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

I am using Apache POI API to generate excel spreadsheet to output some data.

The problem I am facing is when the spreadsheet is created and opened, columns are not expanded so that some long text like Date formatted text is not showing up on first glance.

I could just double click the column border in excel to expand or drag the border to adjust the column width but there could be 20+ columns and there is no way I want to do that manually every time I open the spreadsheet :(

I found out (though could be wrong method) groupRow() and setColumnGroupCollapsed() might be able to do the trick but no luck. Maybe I'm using it in wrong way.

Sample Code snippet

        Workbook wb = new HSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        //create sheet
        Sheet sheet = wb.createSheet("masatoSheet");

        //not really working yet.... :(
        //set group for expand/collapse
        //sheet.groupRow(0, 10); //just random fromRow toRow argument values...
        //sheet.setColumnGroupCollapsed(0, true);

        //create row
        Row row = sheet.createRow((short)0);
        //put a cell in the row and store long text data
        row.createCell(0).setCellValue("Loooooooong text not to show up first");

When this spreadsheet is created, the "Looooooong text not to show up first" string is in the cell but since the column is not expanded only "Loooooooo" is showing up.

How can I configure it so that when I open my spreadsheet, the column is already expanded???

Upvotes: 144

Answers (14)

Karkala Srikanth

Reputation: 85

After adding the data to the sheet, this working for me.

 if (errorSpreadsheet.getPhysicalNumberOfRows() > 0)
        {
            Row xssfRow = errorSpreadsheet.getRow(errorSpreadsheet.getFirstRowNum());
            Iterator<Cell> cellIterator = xssfRow.cellIterator();
            while (cellIterator.hasNext())
            {
                Cell cell = cellIterator.next();
                int columnIndex = cell.getColumnIndex();
                errorSpreadsheet.autoSizeColumn(columnIndex);
            }
        }

Upvotes: 0

juan

Reputation: 1

you can use resize column sheet.autoSizeColumn but dont use it each time use it when you done writing

Upvotes: 0

snap

Reputation: 1915

If you know the count of your columns (f.e. it's equal to a collection list). You can simply use this one liner to adjust all columns of one sheet (if you use at least java 8):

IntStream.range(0, columnCount).forEach(sheet::autoSizeColumn)

Upvotes: 9

Italo Oré

Reputation: 65

You can add this, after your loop.

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

Upvotes: 4

Rahul Mahadik

Reputation: 12261

I use below simple solution:

This is your workbook and sheet:

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("YOUR Workshhet");

then add data to your sheet with columns and rows. Once done with adding data to sheet write following code to autoSizeColumn width.

for (int columnIndex = 0; columnIndex < 15; columnIndex++) {
                    sheet.autoSizeColumn(columnIndex);
                }

Here, instead 15, you add the number of columns in your sheet.

Hope someone helps this.

Upvotes: 3

Akash

Reputation: 99

You can wrap the text as well. PFB sample code:

CellStyle wrapCellStyle = new_workbook.createCellStyle();
                    wrapCellStyle.setWrapText(true);

Upvotes: 1

Ondrej Kvasnovsky

Reputation: 4643

If you want to auto size all columns in a workbook, here is a method that might be useful:

public void autoSizeColumns(Workbook workbook) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < numberOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        if (sheet.getPhysicalNumberOfRows() > 0) {
            Row row = sheet.getRow(sheet.getFirstRowNum());
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                int columnIndex = cell.getColumnIndex();
                sheet.autoSizeColumn(columnIndex);
            }
        }
    }
}

Upvotes: 56

Muhammad Faisal Shahzad

Reputation: 1

Its very simple, use this one line code dataSheet.autoSizeColumn(0)

or give the number of column in bracket dataSheet.autoSizeColumn(cell number )

Upvotes: 0

Sean

Reputation: 7737

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

Upvotes: 224

UVM

Reputation: 9914

You can try something like this:

HSSFSheet summarySheet = wb.createSheet();
summarySheet.setColumnWidth(short column, short width);

Here params are:column number in sheet and its width But,the units of width are pretty small, you can try 4000 for example.

Upvotes: 12

Francis Gabriel Rodriguez

Reputation: 37

You can use setColumnWidth() if you want to expand your cell more.

Upvotes: -2

Mateen

Reputation: 1671

sample code below

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("your sheet name");
HSSFRow row = sheet.createRow(0);

cell = row.createCell(0);
cell.setCellValue("A BIG NAME WITH AUTO SIZE FEATURE ENABLED");

//this is crucial

sheet.autoSizeColumn(0);

//argument must be cell number

cell = row.createCell(1);
cell.setCellValue("a big name without auto size feature enabled");

Check the output and go nuts :)

Upvotes: 7

Adnan Ghaffar

Reputation: 1423

For Excel POI:

sheetName.autoSizeColumn(cellnum);

Upvotes: 9

Unni Kris

Reputation: 3095

Tip : To make Auto size work , the call to sheet.autoSizeColumn(columnNumber) should be made after populating the data into the excel.

Calling the method before populating the data, will have no effect.

Upvotes: 56

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

Answers (14)

Related Questions