Java creates multi-level grouping, collapses or expands grouping in Excel

This paper introduces the method of creating grouping in Excel by Java program, which can group rows or columns and set whether to expand or collapse detailed data. When you set up data groups and expand or collapse them, you can do the following:
Method 1:
Method of adoption sheet.groupByRows(int firstRow, int lastRow, boolean isCollapsed) sheet.groupByColumns (int firstcolumn, int lastcolumn, Boolean iscollapsed) set column grouping.
Method 2:
Method of adoption sheet.getCellRange(String name).groupByRows(boolean isCollapsed) to set row grouping; through the method sheet.getCellRange(String name).groupByColumns(boolean isCollapsed) sets the column grouping.
The following will show the specific implementation method through the Java code example.

Using tool: Free Spire.XLS For Java (free)
Jar package acquisition and import: via the official website Download jar package , and unzip the Spire.Xls.jar Import Java programs; or Maven Warehouse download import.

Java code example

In example 1, the method is used to realize:

import com.spire.xls.*;

import java.awt.*;

public class MultiLevelGroup_XLS {
    public static void main(String[] args) {
        //Create Workbook
        Workbook wb = new Workbook();
        //Get first sheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Write data to cells and style cells
        sheet.get("A1").setValue("A project--project plan ");
        sheet.get("A1").getStyle().getFont().setColor(new Color(0,0,255));
        sheet.get("A1").getStyle().getFont().isBold(true);
        sheet.get("B1").setValue("B project--project plan ");
        sheet.get("B1").getStyle().getFont().setColor(new Color(255,127,80));
        sheet.get("B1").getStyle().getFont().isBold(true);
        sheet.get("C1").setValue("C project--project plan ");
        sheet.get("C1").getStyle().getFont().setColor(new Color(50,205,50));
        sheet.get("C1").getStyle().getFont().isBold(true);
        sheet.get("A1:C1").autoFitColumns();//Set adaptive column
        sheet.get("A3").setValue("get ready");
        sheet.get("A3").getStyle().getFont().setColor(new Color(0,0,255));
        sheet.get("A4").setValue("Task 1");
        sheet.get("A5").setValue("Task 2");
        sheet.getCellRange("A4:A5").borderAround(LineStyleType.Thin);
        sheet.getCellRange("A4:A5").borderInside(LineStyleType.Thin);
        sheet.get("A7").setValue("start-up");
        sheet.get("A7").getStyle().getFont().setColor(new Color(0,0,255));
        sheet.get("A8").setValue("Task 1");
        sheet.get("A9").setValue("Task 2");
        sheet.getCellRange("A8:A9").borderAround(LineStyleType.Thin);
        sheet.getCellRange("A8:A9").borderInside(LineStyleType.Thin);

        //In the isSummaryRowBelow method, pass in the parameter false, which means to display the detailed data below
        sheet.getPageSetup().isSummaryRowBelow(false);

        //Use the groupByRows method to group rows and set group expansion or collapse
        sheet.groupByRows(2,9,false);//Group by line and expand lines 2 through 9
        sheet.groupByRows(4,5,false);
        sheet.groupByRows(8,9,true);//Group by line and collapse lines 8 and 9
        sheet.groupByColumns(2,3,false);//Group by column and expand columns 2 and 3

        //Save document
        wb.saveToFile("NestedGroup.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

Create results:

Example 2 is implemented by method 2:

import com.spire.xls.*;

public class ExpandAndCollapseGroups {
    public static void main(String[] args) {
        //Load document
        Workbook wb =new Workbook();
        wb.loadFromFile("NestedGroup.xlsx");

        //Get worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Get cell settings group collapse, expand
        sheet.getCellRange("A8:A9").groupByRows(false);//Group expand cell row
        sheet.getCellRange("A4:A5").groupByRows(true);//Group collapse cell row
        sheet.getCellRange("B1:C1").groupByColumns(true);//Group collapse cell column

        //Save document
        wb.saveToFile("ExpandOrCollapseGroup.xlsx",FileFormat.Version2013);
        wb.dispose();
    }
}

Comparison before and after setting effect:

Keywords: Java Excel Maven

Added by sportryd on Wed, 27 May 2020 17:32:25 +0300