How to Sort Data in Excel Using Python

#python #programming #data #excel

Whenever a sales report, inventory list, or signup sheet lands on your desk, sorting is usually the first thing you do: dates from oldest to newest, revenue from highest to lowest, or grouped by department and then ranked within each group. With large datasets, clicking through Excel's sort dialog is easy to get wrong on range selection, and doing it across dozens of files is not realistic at all. With Python you can define one-column or multi-column sort rules for any worksheet, and even sort by cell background color, font color, or conditional formatting icons. This article demonstrates these common sorting scenarios using Spire.XLS for Python.

Why Sort Excel Data with Python

Sorting in a script has a few concrete advantages:

Batch processing: the same sort rules apply to many files or worksheets once written.
Rich sort dimensions: beyond values, rows can be ordered by background color, font color, and icons.
Composable rules: multi-level sorts apply in order, giving precise control over "sort by this first, then that".

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

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

Sorting by One or More Columns

Sorting goes through workbook.DataSorter: add sort columns to the SortColumns collection, then call Sort() on a range. The example below sorts a five-column range by column 3 ascending, then column 4 ascending:

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

workbook = Workbook()
workbook.LoadFromFile("Data.xlsx")
worksheet = workbook.Worksheets[0]

# Add sort columns: column 3 ascending, column 4 ascending (0-based index)
workbook.DataSorter.SortColumns.Add(2, OrderBy.Ascending)
workbook.DataSorter.SortColumns.Add(3, OrderBy.Ascending)

# Apply sorting to the specified range
workbook.DataSorter.Sort(worksheet["A1:E19"])

workbook.SaveToFile("Sorted.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The first argument of SortColumns.Add() is the column index (0-based, so 2 means column C), and the second is an OrderBy value — Ascending or Descending. Sort() accepts a cell range and sorts only within it; you can pass a string range like worksheet["A1:E19"] or worksheet.AllocatedRange for the entire used area. With multiple columns, the earlier ones are the primary keys and later ones break ties.

Multi-Level Sorting: Values, Colors, and Icons Together

Real-world spreadsheets often carry visual markers — background colors marking priority, font colors distinguishing status, conditional formatting icons showing trends. The SortComparsionType enum extends sorting beyond values. Here is a four-level sort: values, background color, font color, and icon:

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

workbook = Workbook()
workbook.LoadFromFile("Data.xlsx")
worksheet = workbook.Worksheets[0]

# Clear previous rules to avoid accumulation
workbook.DataSorter.SortColumns.Clear()

# Level 1: column A by values, ascending
workbook.DataSorter.SortColumns.Add(0, SortComparsionType.Values, OrderBy.Ascending)
# Level 2: column C by background color, dark colors to the bottom
workbook.DataSorter.SortColumns.Add(2, SortComparsionType.BackgroundColor, OrderBy.Bottom)
# Level 3: column D by font color, dark colors to the bottom
workbook.DataSorter.SortColumns.Add(3, SortComparsionType.FontColor, OrderBy.Bottom)
# Level 4: column F by icon, high-value icons to the top
workbook.DataSorter.SortColumns.Add(5, SortComparsionType.Icon, OrderBy.Top)

# Apply sorting to the entire used range
workbook.DataSorter.Sort(worksheet.AllocatedRange)

workbook.SaveToFile("MultiSorted.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

The overload that takes a SortComparsionType handles visual sorting: Values sorts by cell value, BackgroundColor by fill, FontColor by font, and Icon by conditional formatting icon. For these types, OrderBy uses Top/Bottom to decide direction — Top moves the attribute toward the top of the range, Bottom toward the bottom. Levels apply in order; later keys only matter when earlier keys tie.

Applying Sorting to Multiple Worksheets

Once the rules are defined, applying them across worksheets is just another loop. Clear SortColumns before defining a new rule set, or rules from the previous sheet will accumulate:

for i in range(0, workbook.Worksheets.Count):
    worksheet = workbook.Worksheets.get_Item(i)
    workbook.DataSorter.SortColumns.Clear()
    workbook.DataSorter.SortColumns.Add(0, SortComparsionType.Values, OrderBy.Ascending)
    workbook.DataSorter.Sort(worksheet.AllocatedRange)

Worksheets.Count gives the sheet total; get_Item(i) fetches each sheet, after which the rules are cleared, redefined, and applied — so every worksheet in one file gets processed under its own rules.

Practical Tips

0-based column index: the first argument of SortColumns.Add() is the worksheet column index — column A is 0, not 1.
Clear before redefining: DataSorter is workbook-level; call SortColumns.Clear() before setting up a new rule set, otherwise leftover rules stack up and produce confusing results.
Two sets of OrderBy values: value sorts use Ascending/Descending; color and icon sorts use Top/Bottom. Do not mix them.
Range selection: pass a string range like worksheet["A1:E19"] when you know the data bounds; use AllocatedRange to cover the whole used area when you do not.
Resource cleanup: call workbook.Dispose() after each file, and in batch scenarios process and release files one at a time.

Conclusion

This article covered the common ways to sort data in Excel using Python: defining single- and multi-column rules through DataSorter.SortColumns and applying them with Sort(), performing multi-level sorts by background color, font color, and conditional formatting icons via SortComparsionType, and applying sort rules across every worksheet in a workbook. Combined with file iteration, these operations fit directly into report cleanup and data preparation pipelines.

DEV Community