When working with large amounts of information, the ability to quickly sort data can be very beneficial at times. By arranging data in ascending, descending, or customized order, users can easily spot trends, analyze relationships, and extract valuable insights. In this article, you will learn how to sort columns or rows in Excel in Python using Spire.XLS for Python.
- Sort By Columns in Excel in Python
- Sort By Custom List in Excel in Python
- Sort By Rows 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
Sort By Columns in Excel in Python
The Workbook.DataSorter.SortColumns.Add(key: int, sortComparsionType: SortComparsionType, orderBy: OrderBy) method provided by Spire.XLS for Python allows users to sort data based on different criteria. For example, you can sort cell values, cell colors or font colors in ascending, descending, or other order.
The following are the steps to sort the values in a specified column:
- Create a Workbook instance.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[] property.
- Specify the sorting mode using Workbook.DataSorter.SortColumns.Add() method.
- Sort data in a specified cell range using Workbook.DataSorter.Sort() method.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls.common import *
from spire.xls import *
# Create a Workbook instance
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Budget.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Sort values in the specified column in ascending order
workbook.DataSorter.SortColumns.Add(0, SortComparsionType.Values, OrderBy.Ascending)
# Sort in the specified cell range
workbook.DataSorter.Sort(worksheet["A1:E7"])
# Save the result file
workbook.SaveToFile("SortByColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Sort By Custom List in Excel in Python
You can also create a custom list and then sort data based on it using the Workbook.DataSorter.SortColumns.Add(key: int, customSortOrder: List[str]) method.
The following are the steps to sort data using a custom list:
- Create a Workbook instance.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[] property.
- Create a custom sort list, and then sort a specified column using it though Workbook.DataSorter.SortColumns.Add() method.
- Sort data in a specified cell range using Workbook.DataSorter.Sort() method.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls.common import *
from spire.xls import *
# Create a Workbook instance
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Budget.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Create a custom sort list
customList = ["DE","MA", "CO", "FL", "VA", "WI"]
# Sort a specified column using the custom list
workbook.DataSorter.SortColumns.Add(4, customList )
# Sort in the specified cell range
workbook.DataSorter.Sort(worksheet["A1:E7"])
# Save the result file
workbook.SaveToFile("CustomSortList.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Sort By Rows in Excel in Python
To sort a specified row in Excel, you need to set the sort orientation to LeftToRight, specify the sort mode and sort row data accordingly.
The following are the steps to sort the values in a specified row:
- Create a Workbook instance.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[] property.
- Set whether to include titles when sorting using Workbook.DataSorter.IsIncludeTitle property.
- Set the sort orientation using Workbook.DataSorter.Orientation property.
- Specify the sorting mode, and then sort data in the first row using Workbook.DataSorter.Sort(Worksheet.Rows[0]) method.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls.common import *
from spire.xls import *
# Create a Workbook instance
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Year.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Set whether to include titles when sorting
workbook.DataSorter.IsIncludeTitle = True
# Set the sort orientation
workbook.DataSorter.Orientation = SortOrientationType.LeftToRight
# Specify the sorting mode
workbook.DataSorter.SortColumns.Add(0,SortComparsionType.Values,OrderBy.Descending)
# Sort data in the first row
workbook.DataSorter.Sort(sheet.Rows[0])
# Save the result file
workbook.SaveToFile("SortByRows.xlsx", 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.
