Apache POI - Expand All / Collapse All

Question

I am now working on Apache POI to create an excel from Java, where I need to have Expand/Collapse All in the exported excel. Please find the below images I want before and after expand, and the below code so far I tried. (It may need required jar, if you are trying at your end). Can anyone please help in this.

Code:

package com.skumar.excel;

import java.io.FileOutputStream;
import java.util.Set;
import java.util.TreeSet;

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();*/
        excelFilename = "C:\\aaa\\excel.xls";
        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++;



            //Next row and reset column 
            idx = idx + 1;
            idy = 0; // reset column 



            //Header of First Group
            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++;

            //1st row of 1st Group
            idx = idx + 1; 
            idy = 0; // reset column 
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("ABC1");
            idy++;
            c = row.createCell(idy);
            c.setCellValue("102");
            idy++;
            c = row.createCell(idy);
            c.setCellValue(22.23);
            idy++;

            //2nd row of 1st Group
            idx = idx + 1;
            idy = 0; // reset column 
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("ABC2");
            idy++;
            c = row.createCell(idy);
            c.setCellValue("105");
            idy++;
            c = row.createCell(idy);
            c.setCellValue(25.23);
            idy++;

            //3rd row of 1st Group 
            idx = idx + 1;
            idy = 0; // reset column 
            int lastRow = idx + 1;
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("ABC3");
            idy++;
            c = row.createCell(idy);
            c.setCellValue("103");
            idy++;
            c = row.createCell(idy);
            c.setCellValue(100.33);
            idy++;

            // Second Group: Populate detail row data

            //Header of 2nd Group
            idx = idx + 1;
            idy = 0; // reset column 
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("XYZ");
            idy++;
            c = row.createCell(idy);
            c.setCellValue("103");
            idy++;
            c = row.createCell(idy);
            c.setCellValue(100.33);
            idy++;

            //1st row of 2nd Group
            idx = idx + 1;
            idy = 0;
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("XYZ1");
            idy++;
            c = row.createCell(idy);
            c.setCellValue("103");
            idy++;
            c = row.createCell(idy);
            c.setCellValue(100.33);
            idy++;

            //2nd row of 2nd Group 
            idx = idx + 1;
            idy = 0; // reset column 
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("XYZ2");
            idy++;
            c = row.createCell(idy);
            c.setCellValue("103");
            idy++;
            c = row.createCell(idy);
            c.setCellValue(100.33);
            idy++;

            //3rd row of 2nd Group
            idx = idx + 1;
            idy = 0; // reset column 
            int lastOfSecondGroup = idx+1;
            row = sheet1.createRow(idx);
            c = row.createCell(idy);
            c.setCellValue("XYZ3");
            idy++;
            c = row.createCell(idy);
            c.setCellValue("103");
            idy++;
            c = row.createCell(idy);
            c.setCellValue(100.33);
            idy++;
            System.out.println(firstRow + "   " + lastRow);
            //Group the Rows together
            sheet1.groupRow(1, lastOfSecondGroup-1);
            sheet1.setRowGroupCollapsed(firstRow, true);
            sheet1.setRowSumsBelow(false);

            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;
    }
}

score 5 · Answer 1 · answered Dec 15 '16 at 14:55

5

As per the first image, you can not achieve this result by creating a single group. You need two groups - one from row 2 to 5 and second from row 6 to 9

Code should be -

        sheet1.groupRow(1, 4);
        sheet1.setRowGroupCollapsed(1, true);
        sheet1.groupRow(5, 8);
        sheet1.setRowGroupCollapsed(5, true);

I think instead of these hard-coded rows no, there would be some logic of grouping. Based on the logic, you can specify startrow and end row for grouping

answered Dec 15 '16 at 14:55

Vikas Sachdeva

5,633
2
17
26

Yes , I already have that. However I am looking for only one expand/collapse icon. I think it can doable if we pivottable. Am not getting a good example. But thanks a lot for you reply – SK. Dec 15 '16 at 15:11

score 0 · Answer 2 · answered Jul 16 '21 at 16:05

I found the issue with inner groups being expanded automatically when outer group get collapsed. Here is similar discussion.

The snippet which worked reliably for me (should be treated as workaround I think)

((XSSFRow) row).getCTRow().setCollapsed(true);
sheet.setRowGroupCollapsed(row.getRowNum() + 1, true);

Apache POI - Expand All / Collapse All

2 Answers2