行和列的分组和取消分组
介绍
在 Microsoft Excel 文件中,您可以创建一个数据大纲,以便通过单击鼠标来显示和隐藏不同级别的细节。
单击大纲符号1、2、3、+和- 快速显示工作表中仅提供摘要或标题部分的行或列,或者您可使用符号查看摘要或标题下的详细信息,如下图所示:
行和列的分组
行和列的分组管理
Aspose.Cells提供了一个类,Workbook,它表示Microsoft Excel文件。Workbook类包含一个Worksheets集合,允许访问Excel文件中的每个工作表。工作表由Worksheet类表示。Worksheet类提供了一个Cells集合,表示工作表中的所有单元格。
Cells集合提供了几种用于管理工作表中的行或列的方法,以下是其中的一些方法。
分组行和列
通过调用Cells集合的groupRows和groupColumns方法,可以对行或列进行分组。两种方法接受以下参数:
- 第一个行/列索引,即组中的第一行或列。
- 最后一个行/列索引,即组中的最后一行或列。
- 是否隐藏,一个布尔参数,指定是否在分组后隐藏行/列。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(GroupingRowsandColumns.class) + "RowsAndColumns/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "Book1.xlsx"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
Cells cells = worksheet.getCells(); | |
// Grouping first six rows (from 0 to 5) and making them hidden by | |
// passing true | |
cells.groupRows(0, 5, true); | |
// Grouping first three columns (from 0 to 2) and making them hidden by | |
// passing true | |
cells.groupColumns(0, 2, true); | |
// Setting SummaryRowBelow property to false | |
worksheet.getOutline().setSummaryRowBelow(true); | |
// Setting SummaryColumnRight property to false | |
worksheet.getOutline().setSummaryColumnRight(true); | |
// Saving the modified Excel file in default (that is Excel 2003) format | |
workbook.save(dataDir + "GroupingRowsandColumns_out.xlsx"); |
分组设置
Microsoft Excel还允许配置用于显示的分组设置:
- 详细信息下面的摘要行。
- 明细右侧的汇总列。
分组设置
可以使用Worksheet类的Outline属性来配置这些分组设置。
明细下方的汇总行
开发人员可以通过使用Outline类的SummaryRowBelow方法来控制在明细下方显示汇总行。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(SummaryRowBelow.class) + "RowsAndColumns/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
Cells cells = worksheet.getCells(); | |
// Grouping first six rows (from 0 to 5) and making them hidden by passing true | |
cells.groupRows(0, 5, true); | |
// Grouping first three columns (from 0 to 2) and making them hidden by passing true | |
cells.groupColumns(0, 2, true); | |
// Setting SummaryRowBelow property to false | |
worksheet.getOutline().setSummaryRowBelow(false); | |
// Saving the modified Excel file in default (that is Excel 2003) format | |
workbook.save(dataDir + "SummaryRowBelow_out.xls"); |
将摘要列显示在详细信息右侧
可以使用Outline类的SummaryColumnRight方法来控制是否在明细右侧显示汇总列。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(SummaryRowRight.class) + "RowsAndColumns/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "BookStyles.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
Cells cells = worksheet.getCells(); | |
// Grouping first six rows (from 0 to 5) and making them hidden by passing true | |
cells.ungroupRows(0, 5); | |
// Grouping first three columns (from 0 to 2) and making them hidden by passing true | |
cells.ungroupColumns(0, 2); | |
// Saving the modified Excel file in default (that is Excel 2003) format | |
workbook.save(dataDir + "SummaryRowRight_out.xls"); |
取消行和列的分组
通过调用Cells集合的UngroupRows和UngroupColumns方法,可以取消对分组行或列的分组。两种方法接受相同的参数:
- 第一个行或列索引,即要取消分组的第一行/列。
- 最后一个行或列索引,即要取消分组的最后一行/列。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(UngroupingRowsandColumns.class) + "rows_cloumns/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "BookStyles.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
Cells cells = worksheet.getCells(); | |
// Grouping first six rows (from 0 to 5) and making them hidden by | |
// passing true | |
cells.ungroupRows(0, 5); | |
// Grouping first three columns (from 0 to 2) and making them hidden by | |
// passing true | |
cells.ungroupColumns(0, 2); | |
// Saving the modified Excel file in default (that is Excel 2003) format | |
workbook.save(dataDir + "UngroupingRowsandColumns_out.xls"); | |
// Print message | |
System.out.println("Rows and Columns ungrouped successfully."); |