Sort Data (by Rows, Columns) in Excel in Python

Sorting data in Excel can help you organize information in a way that makes it easier to analyze and interpret. In Excel, you can sort the data in ascending or descending order, and you can also specify if you want to sort by values, cell color, font color, etc. This article will show you how to sort rows or columns in Excel in Python with the Spire.XLS for Python library.

Four examples are covered:

1. Sort Rows in Excel in Python

2. Sort Columns in Excel in Python

• Sort Data by Cell Values
• Sort Data by Cell Color
• Sort Data by Font Color

Install Python Excel Library

Before diving into the code, we need to install the Spire.XLS for Python library using the following pip command:

pip install Spire.XLS

SortColumns.Add method:

In this article, the core method used to sort Excel data is the SortColumns.Add(key, sortComparsionType, orderBy) method. The three parameters represent:

• key: The index of the column you want to sort.
• sortComparsionType: Specifies the sorting method. Supports sorting by cell values (Values), by cell color (BackgroundColor), and by font color (FontColor).
• orderBy: Indicates the sorting order, such as ascending (Ascending), descending (Descending), placing the specified value on the top (Top), placing the specified value at the bottom (Bottom).

Sort Rows in Excel in Python

The below code sample sorts the first row of the worksheet in ascending order.

from spire.xls.common import *
from spire.xls import *
 
# Load an Excel document
workbook = Workbook()
workbook.LoadFromFile("input.xlsx")
 
# Get the first worksheet
sheet = workbook.Worksheets[0]
 
# Sort the first row
workbook.DataSorter.IsIncludeTitle = True
workbook.DataSorter.Orientation = SortOrientationType.LeftToRight
workbook.DataSorter.SortColumns.Add(0,SortComparsionType.Values,OrderBy.Ascending)
workbook.DataSorter.Sort(sheet.Rows[0])
 
# Save to file
workbook.SaveToFile("SortRows.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Sort Data by Cell Values in Python

The below code sample sorts the data in the first column of the specified range in descending order.

from spire.xls.common import *
from spire.xls import *
 
# Load an Excel document
workbook = Workbook()
workbook.LoadFromFile("Orders.xlsx")
 
# Get the first worksheet
worksheet = workbook.Worksheets[0]
 
# Sort a specified column in descending order
workbook.DataSorter.SortColumns.Add(0, SortComparsionType.Values, OrderBy.Descending)
workbook.DataSorter.Sort(worksheet["A1:D7"])
 
# Save the Excel document
workbook.SaveToFile("SortByCellValue.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Sort Data by Cell Color in Python

The below code sample places the cell with red background at the top.

from spire.xls.common import *
from spire.xls import *
 
# Load an Excel document
workbook = Workbook()
workbook.LoadFromFile("Orders.xlsx")
 
# Get the first worksheet
worksheet = workbook.Worksheets[0]
 
# Place the red cell in the specified column at the top
column = workbook.DataSorter.SortColumns.Add(3, SortComparsionType.BackgroundColor, OrderBy.Top)
column.Color = Color.get_Red()
workbook.DataSorter.Sort(worksheet["A1:D7"])
 
# Save the Excel document
workbook.SaveToFile("SortByCellColor.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Sort Data by Font Color in Python

The below code sample places the cell in red font at the bottom.

from spire.xls.common import *
from spire.xls import *
 
# Load an Excel document
workbook = Workbook()
workbook.LoadFromFile("Orders.xlsx")
 
# Get the first worksheet
worksheet = workbook.Worksheets[0]
 
# Place the red font in the specified column at the bottom
column = workbook.DataSorter.SortColumns.Add(1, SortComparsionType.FontColor, OrderBy.Bottom)
column.Color = Color.get_Red()
workbook.DataSorter.Sort(worksheet["A1:D7"])
 
# Save the Excel document
workbook.SaveToFile("SortByFontColor.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

For more examples of processing Excel files with Python, check out:

Spire.XLS for Python Program Guide Content