Sorting Excel Data with Python: Three Scenarios and Implementations

Sorting is one of the most common operations in Excel data processing. Manual sorting is perfectly adequate for small datasets and occasional tasks.

However, when data changes frequently, multiple files need batch processing, or records must follow custom business rules, automating the process with code can produce more consistent and reproducible results.

This article uses Spire.XLS for Python. We will first explain a shared preprocessing step, then demonstrate three sorting scenarios: sorting a single column, sorting an entire table by a specific column, and sorting by a custom sequence.

Environment Setup and a Shared Prerequisite

Install Spire.XLS for Python using pip:

pip install Spire.XLS

Once installed, add the following import statement at the beginning of your code:

from spire.xls import *

Convert Formulas to Static Values Before Sorting

The first two examples share the same preprocessing step: iterate through the sorting range and replace formulas with their calculated values. This lets the sorting operation work with static cell values and avoids potential changes to formula references when cells move.

The following code performs this conversion. We explain it here once rather than repeating the explanation in each example:

for cell in cellRange:
    if cell.HasFormula:
        value = cell.FormulaValue
        cell.Clear(ExcelClearOptions.ClearContent)
        cell.Value = value

Also, the column index used by SortColumns.Add() is zero-based: column A corresponds to 0, column B corresponds to 1, and so on. Check the index against your worksheet structure when configuring the sort.

Three Sorting Scenarios

Scenario 1: Sort a Single Column

This approach rearranges the values within one column only. Other columns remain unchanged.

from spire.xls import *
workbook = Workbook()
workbook.LoadFromFile("Input.xlsx")
worksheet = workbook.Worksheets[0]
cellRange = worksheet["G1:G13"]
# Convert formulas to static values (as explained in Section 1)
for cell in cellRange:
    if (cell.HasFormula):
        value = cell.FormulaValue
        cell.Clear(ExcelClearOptions.ClearContent)
        cell.Value = value
# Sort by column G (zero-based index 6), by value in descending order
workbook.DataSorter.SortColumns.Add(6, SortComparsionType.Values, OrderBy.Descending)
workbook.DataSorter.Sort(cellRange)
workbook.SaveToFile("output/SortByColumn.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Key points:

• The sorting range is G1:G13, which includes only the target column.
• The value 6 in SortColumns.Add(6, ...) is the zero-based index for column G.
• Values are sorted in descending order.

Scenario 2: Sort an Entire Table by a Specific Column

Use one column as the sorting key to rearrange entire rows while preserving the relationships between values within each row.

from spire.xls import *
workbook = Workbook()
workbook.LoadFromFile("Input.xlsx")
worksheet = workbook.Worksheets[0]
cellRange = worksheet["A1:G13"]
# Convert formulas to static values (as explained in Section 1)
for cell in cellRange:
    if (cell.HasFormula):
        value = cell.FormulaValue
        cell.Clear(ExcelClearOptions.ClearContent)
        cell.Value = value
# Use column G (zero-based index 6) as the key, sorting by value in descending order
workbook.DataSorter.SortColumns.Add(6, SortComparsionType.Values, OrderBy.Descending)
workbook.DataSorter.Sort(cellRange)
workbook.SaveToFile("output/SortByColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Key points:

• Compared with Scenario 1, the sorting key remains the same. Only the sorting range changes - from G1:G13 to A1:G13.
• When Sort() runs, each row within the range moves according to its value in column G. The contents of columns A through F move with the row.
• This approach is more suitable for typical business data. For example, when sorting a sales table by the Sales Amount column, the product, region, date, and other information in each row must move together to keep the records aligned.

Scenario 3: Sort by a Custom Sequence

Ascending and descending order cover only some sorting requirements. Sometimes, records need to follow a business-defined sequence, such as High, Medium, and Low priority, or a predefined order of identifiers. A custom sort list handles these cases.

from spire.xls import *
workbook = Workbook()
workbook.LoadFromFile("Input.xlsx")
worksheet = workbook.Worksheets[0]
cellRange = worksheet["A1:G13"]
# Explicitly define the sorting order
customList = ["6", "5", "4", "3", "2", "1", "12", "11", "10", "9", "8", "7"]
# Sort by column A (zero-based index 0) using the custom sequence
workbook.DataSorter.SortColumns.Add(0, customList)
workbook.DataSorter.Sort(cellRange)
workbook.SaveToFile("output/CustomSortList.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Key points:

• Values in the sorting column follow their order in customList rather than their numerical magnitude.
• The Sort() operation still applies to the entire A1:G13 range, with the rows arranged according to the custom sequence of values in column A.

Keep two points in mind:

• The element types in the custom list should match the data type of the sorting column. In this example, the numbers are provided as strings. Otherwise, values may not match correctly.
• If the sorting column contains values that are absent from the custom list, verify their placement in the actual output. Check that the data and custom list correspond before running the final sort.

Comparing the Three Scenarios

The central distinction is the sorting range: it determines which cells move, while the sorting key determines their order. The code for single-column sorting and entire-table sorting is almost identical; only the range passed to Sort() changes. Custom-sequence sorting replaces value comparison with the order defined in a list.

Conclusion

When sorting with Spire.XLS for Python, keep three points in mind:

• Handle formulas first: If you need static values for sorting, convert formulas to their calculated values beforehand to avoid potential reference changes.
• Choose the correct sorting range: Pass a single-column range to rearrange that column alone, or a full-table range to move complete rows together.
• Remember that column indexes start at 0: Check the column index against your worksheet structure when configuring SortColumns.Add().

Apply these points to your data, then use the appropriate example above to handle common Excel sorting tasks.