Grouping rows and columns in Excel provides a more organized and structured view of data, making it easier to analyze and understand complex datasets. After grouping related rows or columns, you can collapse or expand them as needed to focus on specific subsets of information while hiding details. In this article, you will learn how to group or ungroup rows and columns , as well as how to collapse or expand groups in Excel in Python using Spire.XLS for Python.

• Group Rows and Columns in Excel in Python
• Ungroup Rows and Columns in Excel in Python
• Expand or Collapse Groups in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Group Rows and Columns in Excel in Python

Spire.XLS for Python provides the Worksheet.GroupByRows() and Worksheet.GroupByColumns() methods to group specific rows and columns in an Excel worksheet. The following are the detailed steps:

• Create a Workbook object.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get the specified worksheet using Workbook.Worksheets[] property.
• Group rows using Worksheet.GroupByRows() method.
• Group columns using Worksheet.GroupByColumns() method.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

inputFile = "Data.xlsx"
outputFile = "GroupRowsAndColumns.xlsx"

# Create a Workbook object
workbook = Workbook()

# Load a sample Excel file
workbook.LoadFromFile(inputFile)

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Group rows
sheet.GroupByRows(2, 6, False)
sheet.GroupByRows(8, 13, False)

# Group columns
sheet.GroupByColumns(4, 6, False)

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

Ungroup Rows and Columns in Excel in Python

Ungrouping rows and columns in Excel refer to the process of reversing the grouping operation and restoring the individual rows or columns to their original state.

To ungroup rows and columns in an Excel worksheet, you can use the Worksheet.UngroupByRows() and Worksheet.UngroupByColumns() methods. The following are the detailed steps:

• Create a Workbook object.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get the specified worksheet using Workbook.Worksheets[] property.
• Ungroup rows using Worksheet.UngroupByRows() method.
• Ungroup columns using Worksheet.UngroupByColumns() method.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

inputFile = "GroupRowsAndColumns.xlsx"
outputFile = "UnGroupRowsAndColumns.xlsx"

# Create a Workbook object
workbook = Workbook()

# Load a sample Excel file
workbook.LoadFromFile(inputFile)

# Get the first worksheet
sheet = workbook.Worksheets[0]

# UnGroup rows
sheet.UngroupByRows(2, 6)
sheet.UngroupByRows(8, 13)

# UnGroup columns
sheet.UngroupByColumns(4, 6)

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

Expand or Collapse Groups in Excel in Python

Expanding or collapsing groups in Excel refers to the action of showing or hiding the detailed information within a grouped section. With Spire.XLS for Python, you can expand or collapse groups through the Worksheet.Range[].ExpandGroup() or Worksheet.Range[].CollapseGroup() methods. The following are the detailed steps:

• Create a Workbook object.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get the specified worksheet using Workbook.Worksheets[] property.
• Expand a specific group using the Worksheet.Range[].ExpandGroup() method.
• Collapse a specific group using the Worksheet.Range[].CollapseGroup() method.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

inputFile = "Grouped.xlsx"
outputFile = "ExpandOrCollapseGroups.xlsx"

# Create a Workbook object
workbook = Workbook()

# Load a sample Excel file
workbook.LoadFromFile(inputFile)

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Expand a group
sheet.Range["A2:G6"].ExpandGroup(GroupByType.ByRows)

# Collapse a group
sheet.Range["D1:F15"].CollapseGroup(GroupByType.ByColumns)

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.