SoFunction
Updated on 2025-04-06

Create and ungroup data in Excel using Python

introduction

Grouping in Excel is a function of organizing adjacent rows or columns together by adding hierarchies. When the grouping is complete, the user can simplify the data view by collapse or expanding the data group. This capability is especially suitable for scenarios where data contains multi-hierarchical structures, or where some of the data is required to be hidden in order to focus on specific content. This blog will introduce how to create or ungroup data in Excel using Python, covering mainly the following:

  • Python creates row and column grouping in Excel
  • Python create nested grouping in Excel
  • Python gets outline levels of rows and columns in Excel
  • Python expand or collapse grouping in Excel
  • Python creates a classification summary in Excel
  • Python ungroup rows and columns in Excel

Usage Tools

In Python, you can use for PythonLibrary to create and cancel data packets in Excel.

for Python is mainly used to create, read, edit and convert Excel files in Python applications. It can handle a variety of spreadsheet formats including XLS, XLSX, XLSB, XLSM, ODS, etc. In addition, you can convert Excel files to other file formats such as PDF, HTML, CSV, text, images, XML, SVG, ODS, PostScript, and XPS.

You can install for Python by running the following command in the terminal:

pip install

Python creates row and column grouping in Excel

For Python provides()and()Methods are used to group data of rows and columns in Excel worksheets respectively.

The parameters of these methods are as follows:

  • Index of the starting row or starting column of the group.
  • The end row or end column index of the group.
  • Whether to collapse grouping, True means folding, False means expanding.

Here is the implementation code for grouping rows and columns in an Excel worksheet using Python:

from  import *
 
# Open an Excel fileworkbook = Workbook()
("Data.xlsx")
 
# Get the first worksheetsheet = [0]
 
# Grouping Lines 2-6(2, 6, False)
 
# Group columns 2-3(2, 3, False)
 
# Save the result file("Create grouping.xlsx", ExcelVersion.Version2016)
()

Python create nested grouping in Excel

Nested grouping refers to nesting one group within another group to form a multi-level data structure. For example, in sales data, you can group by region and then group by sales personnel within each region. In this way, the organizational structure of the data is clearer and easy to analyze.

To create nested rows or column groups in an Excel worksheet, it can be done by using the() or() method multiple times.

Here is the implementation code for creating nested row groupings in Excel worksheets using Python:

from  import *
 
# Open an Excel fileworkbook = Workbook()
("Data.xlsx")
 
# Get the first worksheetsheet = [0]
 
# Grouping Lines 2-11(2, 11, False)
 
# Create nested groups in the above groups(2, 6, False)
 
# Save the result file("Create nested grouping.xlsx", ExcelVersion.Version2016)
()

Python gets outline levels of rows and columns in Excel

The default outline level for rows or columns in a worksheet is 0, indicating that the rows and columns are not currently grouped. Each time you group, the outline level will be increased by 1 accordingly. By using the [].RowGroupLevel and [].ColumnGroupLevel properties, you can get the outline level of a specific row or column.

Here is the implementation code to get the outline level of specific rows and columns in an Excel worksheet:

from  import *
 
# Open an Excel fileworkbook = Workbook()
("Create grouping.xlsx")
 
# Get the first worksheetsheet = [0]
 
# Get the outline level of a specific row and columnrow_outline_level = ["A2"].RowGroupLevel
col_outline_level = ["C1"].ColumnGroupLevel
 
# Print outline levelprint(f"CellA2Line outline level：{row_outline_level}")
print(f"CellC1Column Outline Level：{col_outline_level}")
 
()

Python expand or collapse grouping in Excel

To expand or collapse grouped data in an Excel worksheet, you can use the [].ExpandGroup or [].CollapseGroup methods, the former is used to expand groups and the latter is used to collapse groups.

Here is the implementation code for expanding or collapse grouped data in Excel using Python:

from  import *
 
# Open an Excel fileworkbook = Workbook()
("Create grouping.xlsx")
 
# Get the first worksheetsheet = [0]
 
# collapse grouped rows within a specified cell area["A2:C6"].CollapseGroup()
# Or expand grouped rows within the specified cell area# ["A2:C6"].ExpandGroup()
 
# collapse grouped columns within a specified cell area["B1:C11"].CollapseGroup()
# Or expand the grouped columns in the specified cell area# ["B2:C11"].ExpandGroup()
 
# Save the result file("Folding Grouping.xlsx", ExcelVersion.Version2016)
()

Python creates a classification summary in Excel

The SUBTOTAL function in Excel allows users to group data and automatically perform specific classification summary operations such as summing, counting, average, product, and maximum.

In()Methods add classification summary to the data. The parameters of this method are as follows:

  • Data area: Specify the area of ​​cells to be classified and summarized.
  • Grouping basis: Group the data by which column of index.
  • Summary column: A list of column indexes that need to be summed.
  • Summary Type: Select the function type used to calculate classification summary, such as sum (Sum), count (Count), etc.
  • Replace existing summary: Whether to replace the current classification summary, True means replacement, False means reservation.
  • Insert page break: Whether each set of data is paging, True means paging, False means not paging.
  • Display location: Whether to display summary below the data, True means it is below, and False means it is above.

Here is the implementation code for adding classification summary to the specified data area in Excel worksheet using Python:

from  import *
 
# Open an Excel fileworkbook = Workbook()
("Data.xlsx")
 
# Get the first worksheetsheet = [0]
 
# Create a classification summary for the specified data areacell_range = ["A2:C11"]
(cell_range, 0, [2], , True, False, True)
 
# Save the result file("Add a category summary.xlsx", ExcelVersion.Version2016)
()

Python ungroup rows and columns in Excel

While the grouping feature is beneficial for viewing specific data, sometimes it is necessary to ungroup in order to view the entire dataset.

To ungroup in Excel worksheets, you can use()or()Method, the former is used to cancel row grouping, and the latter is used to cancel column grouping.

Here is the implementation code to use Python to cancel row and column grouping in Excel worksheets:

from  import *
 
# Open an Excel fileworkbook = Workbook()
("Create grouping.xlsx")
 
# Get the first worksheetsheet = [0]
 
# Ungrouping Lines 2-6(2, 6)
 
# Ungroup columns 2-3(2, 3)
 
# Save the result file("Ungrouping.xlsx", ExcelVersion.Version2016)
()

The above is all about creating and canceling data packets in Excel using Python.

The above is the detailed content of using Python to create and cancel data grouping in Excel. For more information about creating and canceling data grouping in Python Excel, please follow my other related articles!