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

User · Answer

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  columnIndex  - gt  sheet autoSizeColumn columnIndex

User · Answer

You can add this  after your loop  for  int i   0  i lt 53 i          sheet autoSizeColumn i

User · Answer

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

User · Answer

You can wrap the text as well  PFB sample code   CellStyle wrapCellStyle   new workbook createCellStyle                        wrapCellStyle setWrapText true

User · Answer

For Excel POI    sheetName autoSizeColumn cellnum

User · Answer

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  lt  15  columnIndex                          sheet autoSizeColumn columnIndex                       Here  instead 15  you add the number of columns in your sheet   Hope someone helps this

User · Answer

Its very simple  use this one line code dataSheet autoSizeColumn 0   or give the number of column in bracket dataSheet autoSizeColumn cell number

User · Answer

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

User · Answer

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

User · Answer

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  lt  numberOfSheets  i              Sheet sheet   workbook getSheetAt i           if  sheet getPhysicalNumberOfRows    gt  0                Row row   sheet getRow sheet getFirstRowNum                 Iterator lt Cell gt  cellIterator   row cellIterator                while  cellIterator hasNext                      Cell cell   cellIterator next                    int columnIndex   cell getColumnIndex                    sheet autoSizeColumn columnIndex

User · Answer

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

User · Answer

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

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

Examples related to java

Examples related to excel

Examples related to expand

Examples related to apache-poi