Blog Archive

Apache POI Excel Row Group - Collapse, Expand and setCellFormula

How to Group Excel Rows using Apache POI so that they can be collapsed and expanded, also how to set Formula in a Cell Value
  • public void groupRow(int fromRow, int toRow)
  • public void setRowGroupCollapsed(int rowIndex, boolean collapse)

Apache POI Excel GroupRow Collapse Expand and setCellFormula

Apache POI Excel GroupRow Collapse Expand and setCellFormula

Source code for ExcelGroupData.java

package com.as400samplecode;

import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelGroupData {

   
    public static void main(String[] args)
    {

        String excelFilename = null;
       
        ExcelGroupData myExcel = new ExcelGroupData();
        if (args.length < 1)
        {
            System.err.println("Usage: java "+ myExcel.getClass().getName()+
            " Excel_Filename");
            System.exit(1);
        }

        excelFilename = args[0].trim();
        myExcel.generateExcel(excelFilename);

    }

    public void  generateExcel(String excelFilename){

        try {

            //New Workbook
            Workbook wb = new XSSFWorkbook();

            Cell c = null;

            //Cell style for header row
            CellStyle cs = wb.createCellStyle();
            cs.setFillForegroundColor(IndexedColors.LIME.getIndex());
            cs.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            Font f = wb.createFont();
            f.setBoldweight(Font.BOLDWEIGHT_BOLD);
            f.setFontHeightInPoints((short) 12);
            cs.setFont(f);
           
            //Cell style for summary row
            CellStyle css = wb.createCellStyle();
            f = wb.createFont();
            f.setBoldweight(Font.BOLDWEIGHT_BOLD);
            f.setFontHeightInPoints((short) 10);
            css.setFont(f);

            //New Sheet
            XSSFSheet sheet1 = null;
            sheet1 = (XSSFSheet) wb.createSheet("myData");

            // Row and column indexes
            int idx = 0;
            int idy = 0;

            //Generate column headings
            Row row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("Customer");
            c.setCellStyle(cs);
            sheet1.setColumnWidth(idy, 10 * 500);
            idy++;
            c = row.createCell(idy);
            c.setCellValue("Order Number");
            c.setCellStyle(cs);
            sheet1.setColumnWidth(idy, 10 * 500);
            idy++;
            c = row.createCell(idy);
            c.setCellValue("Order Total");
            c.setCellStyle(cs);
            sheet1.setColumnWidth(idy, 10 * 500);
            idy++;
           
            //Skip 2 rows and reset column 
            idx = idx + 3;
            idy = 0;
       
            //Populate detail row data
            int firstRow = idx + 1;
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("ABC");
            idy++;
            c = row.createCell(idy);
            c.setCellValue("101");
            idy++;
            c = row.createCell(idy);
            c.setCellValue(10.99);
            idy++;
           
            //Next row and reset column 
            idx = idx + 1;
            idy = 0;
           
            //Populate detail row data
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("ABC");
            idy++;
            c = row.createCell(idy);
            c.setCellValue("102");
            idy++;
            c = row.createCell(idy);
            c.setCellValue(22.23);
            idy++;
           
            //Next row and reset column 
            idx = idx + 1;
            idy = 0;
           
            //Populate detail row data
            int lastRow = idx + 1;
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("ABC");
            idy++;
            c = row.createCell(idy);
            c.setCellValue("103");
            idy++;
            c = row.createCell(idy);
            c.setCellValue(100.33);
            idy++;
           
            //Next row and reset column 
            idx = idx + 1;
            idy = 0;
           
            //Populate summary row data
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("ABC");
            c.setCellStyle(css);
            idy++;
            c = row.createCell(idy);
            c.setCellValue("");
            c.setCellStyle(css);
            idy++;
            c = row.createCell(idy);
            String myFormula = "SUM(" + getColumnName(idy) + firstRow + ":" + 
                                        getColumnName(idy) + lastRow + ")";
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellFormula(myFormula);
            c.setCellStyle(css);
            idy++;
           
            //Group the Rows together
            sheet1.groupRow(firstRow-1,lastRow-1);
            sheet1.setRowGroupCollapsed(firstRow-1, true);
           
            FileOutputStream fileOut = new FileOutputStream(excelFilename.trim());

            wb.write(fileOut);
            fileOut.close();

        }
        catch (Exception e) {
            System.out.println(e);
        }

    }
   
    private String getColumnName(int columnNumber) {
       
        String columnName = "";
        int dividend = columnNumber + 1;
        int modulus;

        while (dividend > 0){
            modulus = (dividend - 1) % 26;
            columnName = (char)(65 + modulus) + columnName;
            dividend = (int)((dividend - modulus) / 26);
        } 

        return columnName;
    }


}

To ungroup the rows and expand the rows use the following methods
  • public void ungroupRow(int fromRow, int toRow)
  • public void setRowGroupCollapsed(int rowIndex, false)

16 comments :

  1. Thank you!

    I googled to search how to do this operations and I found your post.

    Very useful +1

    ReplyDelete
  2. You are awesome as I was lost for weeks on how to get this done. Your sample is just what I was looking for and great and works flawless. Thanks a mil

    ReplyDelete
  3. Very useful! :) You solved my problem in a minute :)

    ReplyDelete
  4. Thanks for your post.
    Here is a question, if a may. What if I have more than one group (lets say three). How could I collapse them, one after another (from smallest level to top)? You see, if user document has more than one group, and he tries to open only one top level group, by cliking on plus, it will automaticly open all others groups.
    Thanks.

    ReplyDelete
    Replies
    1. Here is the same problem http://www.junlu.com/list/57/382093.html

      Delete
    2. Here is the best examples here - see generated outlines.xml 8/9/10. I guess this is what you meant :)

      Delete
  5. I want to group rows which are not in sequence. For example I want to create a group of row num (1,7,8,9,14,15) and another group of row nums (2,4,5,6,10,11). How can I do that using POI. From MS Excel UI it is possible.

    ReplyDelete
  6. Amigo muchas gracias por el aporte, esto me ayuda bastante en mi trabajo.

    ReplyDelete
  7. My requirement is exactly similar to below this sample code above except for the expansion is reverse here i.e on expanding (+) 7th row it shows from 4th row.

    My requirement is just opposite expansion sign(+) to be 4th row and on expand show rows till 7th.

    Is there any way we can achieve this. I tried all available methods ..it appears to be I got exhausted.

    Thanks In Advance and Wish You All A Very Very Happy and Great New Year.. 2014

    ReplyDelete
  8. Can you help me with a code where I have to split the excel sheet into two excel sheets on the basis of the missing values in the cell. That means one excel sheet wiil be have all rows with their cells filled with values and the other sheet to have rows with missing value cells. Anybody please help??

    ReplyDelete
  9. Cool Code!!! I do have a question ... how do I pragmatically un-check the "Summary row details below" option from the Data Tab-> Outline -> Settings ... to list grouped row in the other direction

    Any help is greatly appreciated!

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. figured!!! sheet.setRowSumsBelow(false);

    ReplyDelete
  12. Wow amazing, Nice content I found so many interesting stuff in your blog especially its discussion.excel training

    ReplyDelete