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

Question

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???

Answer 1

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