Introduction

When people first inspect an .xlsx file, sharedStrings.xml looks like the obvious place to sort text because it stores many of the workbook's strings. That is usually the wrong mental model. Excel sorts rows and cell values, not the shared-string table in isolation, and the shared-string indexes inside worksheet XML must remain consistent with the string table.

What Shared Strings Actually Do

An .xlsx file is a ZIP container of XML parts. One of those parts can be xl/sharedStrings.xml, which stores deduplicated text values. Cells that contain shared strings do not store the text directly. They store an integer index that points into the shared-string table.

That means two important things:

• the string table is a lookup structure, not the logical row order of the sheet
• reordering shared strings without updating cell indexes corrupts the workbook meaning

So if cell A1 points to shared-string index 5, and you sort the string table but leave the cell index unchanged, A1 now displays the wrong text.

Sort Rows, Not the Shared String Table

If your goal is to sort spreadsheet data, sort the rows in worksheet order and let your library rebuild or preserve string references correctly.

A practical example with openpyxl looks like this:

1from openpyxl import Workbook
2
3wb = Workbook()
4ws = wb.active
5ws.append(["Name", "Score"])
6ws.append(["Charlie", 78])
7ws.append(["Alice", 91])
8ws.append(["Bob", 85])
9
10header = list(ws.iter_rows(min_row=1, max_row=1, values_only=True))[0]
11data_rows = list(ws.iter_rows(min_row=2, values_only=True))
12
13sorted_rows = sorted(data_rows, key=lambda row: row[0])
14
15ws.delete_rows(2, ws.max_row - 1)
16for row in sorted_rows:
17    ws.append(row)
18
19wb.save("sorted.xlsx")

The workbook library handles the file structure. You sort the business data, not the sharedStrings.xml part directly.

If You Manipulate Open XML Directly

Sometimes you are not using a high-level library. You may be transforming raw Open XML parts. In that case, the safe rule is:

1. parse the worksheet rows
2. resolve shared-string indexes to actual text when needed for comparison
3. sort row records
4. write rows back
5. either preserve the existing shared-string table or rebuild it together with updated indexes

A simplified example of resolving a shared-string value by index:

1from xml.etree import ElementTree as ET
2
3NS = {
4    "main": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
5}
6
7
8def load_shared_strings(path):
9    root = ET.parse(path).getroot()
10    values = []
11    for si in root.findall("main:si", NS):
12        texts = [t.text or "" for t in si.findall(".//main:t", NS)]
13        values.append("".join(texts))
14    return values
15
16
17shared = load_shared_strings("xl/sharedStrings.xml")
18print(shared[0])

That is fine for reading. The dangerous part is writing a modified string table without keeping every referencing cell aligned.

Rebuilding the Shared String Table

If you truly need to regenerate sharedStrings.xml, treat it like a remapping problem. Build a new unique-string list, assign each unique text a new index, and rewrite every shared-string cell in every worksheet to the new index.

That process is more like a normalization pass than a sort pass. The order of strings in the table itself is usually unimportant as long as the references match.

A simple remapping pattern is:

1def rebuild_index(strings):
2    lookup = {}
3    ordered = []
4
5    for value in strings:
6        if value not in lookup:
7            lookup[value] = len(ordered)
8            ordered.append(value)
9
10    return lookup, ordered
11
12lookup, ordered = rebuild_index(["Charlie", "Alice", "Alice", "Bob"])
13print(lookup)
14print(ordered)

This gives you a deterministic index map, but you still must update all cell references that use those strings.

Choosing the Right Sorting Algorithm

The specific sorting algorithm matters much less than the data model. For ordinary sheet sorting, Python's built-in sorted, Java's Collections.sort, or a library sort is usually enough. The key is sorting row records by resolved values.

Use a stable sort if secondary order matters. For example, when sorting by department but preserving original order among equal departments, stable behavior is helpful.

1rows = [
2    ("Sales", "Charlie"),
3    ("Engineering", "Alice"),
4    ("Sales", "Bob"),
5]
6
7print(sorted(rows, key=lambda r: r[0]))

The real correctness challenge is not whether the algorithm is quicksort or mergesort. It is whether you are sorting the right unit: rows instead of raw shared-string entries.

Common Pitfalls

The biggest mistake is sorting sharedStrings.xml directly and assuming the sheet will still display the same values. It will not unless every string index reference is updated too.

Another mistake is comparing cell XML indexes instead of resolved text values. Shared-string indexes are lookup ids, not alphabetical order.

A third mistake is ignoring rich text and multi-run strings in the string table. Some shared-string entries contain multiple text nodes, so a naive parser can lose formatting or content.

Finally, do not over-engineer the sort itself before fixing the data model. In spreadsheet work, row integrity and reference consistency matter more than the specific comparison algorithm.

Summary

• 'sharedStrings.xml is a deduplicated lookup table, not the actual sheet sort order'
• To sort Excel data, sort worksheet rows and keep shared-string references consistent
• Directly reordering the shared-string table breaks cell meanings unless indexes are remapped everywhere
• High-level libraries such as openpyxl are safer than raw XML edits for normal sorting tasks
• If you rebuild the shared-string table, treat it as a full remapping problem, not a simple lexical sort