Hand a perfectly normal sales ledger to a large language model and the answers often cannot be trusted. The model is rarely at fault — the workbook is. The Amount column stores =D2*E2 formulas, so a direct text export turns every amount into a formula string. The Region column uses merged cells so that "East" spans three rows visually, and after export only the first row of each group carries the value while the rest stay empty. Unit Price carries a #,##0.00 format, so exported text reads 45.50, and the thousands separator wraps 1,365.00 in quotes that make it look like a string rather than a number. Formatting conventions that humans read at a glance all become noise inside a model's context window.
The fix is mechanical, and a library can do it: read the stored value of every cell, resolve formulas into numbers, flatten merged regions, and export into the format the pipeline needs. This article uses Spire.XLS for Python. Install it with:
pip install spire.xls The Object Model
| Object | Role | Members used in this article |
|---|---|---|
Workbook | Workbook container | LoadFromFile, CalculateAllValue, Worksheets, SaveToFile, SaveToMarkdown |
Worksheet | One worksheet | Name, AllocatedRange, HasMergedCells, MergedCells, RemoveMergedCells, SaveToFile, ToImage |
CellRange | A cell or a range | DisplayedText, NumberValue, DateTimeValue, HasFormula, HasNumber, Formula, EnvalutedValue |
The sample input is a two-sheet sales ledger: an Orders sheet with 9 rows and 6 columns, three merged regions in the Region column, a formula-driven Amount column, and a total row; and a Stock sheet with 4 rows and 4 columns under a plain single-row header. Every snippet below runs in order against this file.
Read the Structure Before You Fix It
The first pass should observe, not modify: how big each sheet is, where the merges are, where the formulas live. AllocatedRange reports the overall extent of the data area, MergedCells enumerates every merged region with its anchor and span, and a cell-by-cell scan with HasFormula counts the formula cells.
from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("sales_ledger.xlsx") for i in range(workbook.Worksheets.Count): sheet = workbook.Worksheets[i] region = sheet.AllocatedRange print("sheet %d: %s | rows=%d cols=%d" % ( i, sheet.Name, region.RowCount, region.ColumnCount)) if bool(sheet.HasMergedCells): merged = sheet.MergedCells print(" merged regions: %d" % merged.Count) for j in range(merged.Count): m = merged[j] print(" %s%d:%s%d" % ( chr(64 + m.Column), m.Row, chr(64 + m.Column + m.ColumnCount - 1), m.Row + m.RowCount - 1)) formula_cells = 0 for r in range(1, region.RowCount + 1): for c in range(1, region.ColumnCount + 1): if bool(sheet.Range[r, c].HasFormula): formula_cells += 1 print(" formula cells: %d" % formula_cells) workbook.Dispose() sheet 0: Orders | rows=9 cols=6 merged regions: 3 B2:B4 B5:B6 B7:B8 formula cells: 8 sheet 1: Stock | rows=4 cols=4 formula cells: 0 The 8 formula cells in Orders are the 7 amounts plus the grand total, and the 3 merged regions match the three order groups. MergedCells only carries content when merged regions actually exist, so check HasMergedCells first. Properties like HasMergedCells return wrapper objects; converting with bool() before using them in an if keeps the behavior predictable.
One Cell, Several Values
Choosing the export format settles the structure question; choosing the read method settles the content question. One cell exposes several values at once: DisplayedText returns the text as formatted for display, NumberValue and DateTimeValue return stored values, and a formula cell exposes the expression through Formula and the computed result through EnvalutedValue.
from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("sales_ledger.xlsx") sheet = workbook.Worksheets[0] d = sheet.Range[2, 3] print("C2 DisplayedText :", d.DisplayedText) print("C2 DateTimeValue :", d.DateTimeValue.ToString("yyyy-MM-dd")) print("C2 NumberValue :", d.NumberValue) p = sheet.Range[2, 5] print("E2 DisplayedText :", p.DisplayedText) print("E2 NumberValue :", p.NumberValue) f = sheet.Range[2, 6] print("F2 HasFormula :", bool(f.HasFormula)) print("F2 Formula :", f.Formula) print("F2 EnvalutedValue:", f.EnvalutedValue) workbook.Dispose() C2 DisplayedText : 2026-11-03 C2 DateTimeValue : 2026-11-03 C2 NumberValue : 46329.0 E2 DisplayedText : 45.50 E2 NumberValue : 45.5 F2 HasFormula : True F2 Formula : =D2*E2 F2 EnvalutedValue: 546 Each group of lines drives a different decision. The date displays as 2026-11-03 while its stored value is the serial number 46329.0 — feed the model the formatted string, and reserve DateTimeValue for date arithmetic. The unit price displays as 45.50 while the stored value is 45.5; display text carries formatting, and parsing it as a number is a trap. The Text property returns nothing usable on numeric cells, so DisplayedText is the reliable choice for a textual representation. On the formula cell, EnvalutedValue returns the computed 546 — the value the export steps below rely on.
CSV: The Same Table Before and After Calculation
CSV is the most token-efficient format to hand a model. Worksheet.SaveToFile takes the file name, the separator, and the encoding. Timing changes the output: export immediately after loading and formula cells land in the file as formula text; call CalculateAllValue on the workbook first and the same cells export as numbers.
from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("sales_ledger.xlsx") workbook.Worksheets[0].SaveToFile("orders_raw.csv", ",", Encoding.get_UTF8()) workbook.Dispose() workbook = Workbook() workbook.LoadFromFile("sales_ledger.xlsx") workbook.CalculateAllValue() for i in range(workbook.Worksheets.Count): sheet = workbook.Worksheets[i] sheet.SaveToFile("%s.csv" % sheet.Name, ",", Encoding.get_UTF8()) print("saved:", sheet.Name) workbook.Dispose() saved: Orders saved: Stock Before calculation, every Amount cell is quoted formula text like "=D2*E2", which the model can only treat as a string. After calculation the same cells read 546.00. Each variant leaves its own trace: the total row's 4,576.00 ends up quoted because of the thousands separator, and the empty Region cells caused by the merged ranges survive untouched — a CSV export dumps the display layer faithfully, so both problems have to be repaired before exporting, which is what the next two sections do. The Orders and Stock sheets describe different entities, so each becomes its own file and the pipeline can feed the model only the table it needs instead of the whole workbook.
Merged Cells: Give Every Row Its Own Value
A merged region stores its value only in the top-left cell; every other member reads back empty. Flattening takes three steps: record each region's anchor, span, and anchor value from MergedCells, unmerge region by region with RemoveMergedCells, then fill the value downward.
from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("sales_ledger.xlsx") for i in range(workbook.Worksheets.Count): sheet = workbook.Worksheets[i] if not bool(sheet.HasMergedCells): continue merged = sheet.MergedCells captured = [] for j in range(merged.Count): m = merged[j] captured.append((m.Row, m.Column, m.RowCount, sheet.Range[m.Row, m.Column].Text)) for j in range(merged.Count): sheet.RemoveMergedCells(merged[j]) for row, col, row_count, value in captured: for r in range(row, row + row_count): sheet.Range[r, col].Text = value print("%s: %d merged regions flattened" % (sheet.Name, len(captured))) workbook.SaveToFile("sales_ledger_flat.xlsx", ExcelVersion.Version2016) workbook.Dispose() Orders: 3 merged regions flattened Compared with the original sheet, the Region column now resolves on every row: A-1002 and A-1003 no longer enter downstream processing with an empty region. The capture must happen before the unmerge — reversing the order loses the values — and the fill loop uses the recorded RowCount so a multi-row region is covered in one pass.
JSON: One-Shot Export, Then Column-by-Column Control
JSON turns headers into keys and rows into objects, making the field-to-value binding explicit — the right shape for APIs and structured pipelines. The library exports an entire workbook in one call: SaveToFile with FileFormat.Json writes every worksheet as an array of row objects.
from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("sales_ledger.xlsx") workbook.CalculateAllValue() workbook.SaveToFile("orders_quick.json", FileFormat.Json) print("quick json saved") workbook.Dispose() quick json saved That export is fine for a quick look, but as model-bound records it falls three steps short: every value becomes a string ("Qty": "12"), the Amount keeps its formula text (=D2*E2 — CalculateAllValue does not change this export), and on a workbook whose merges are still in place, rows like A-1002 have no Region key at all. Field types and presence follow the display layer, leaving the model a structure it has to guess.
Building the records column by column settles all three: start from the flattened workbook, format the Date column from DateTimeValue as an ISO string, convert the Amount column from EnvalutedValue to a number, read NumberValue on numeric columns after an HasNumber check, read Text everywhere else, and skip the total row by its first-column label.
import json from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("sales_ledger_flat.xlsx") workbook.CalculateAllValue() sheet = workbook.Worksheets[0] region = sheet.AllocatedRange headers = [sheet.Range[1, c].Text for c in range(1, region.ColumnCount + 1)] records = [] for r in range(2, region.RowCount + 1): if sheet.Range[r, 1].Text == "Total": continue record = {} for c in range(1, region.ColumnCount + 1): cell = sheet.Range[r, c] header = headers[c - 1] if header == "Date": record[header] = cell.DateTimeValue.ToString("yyyy-MM-dd") elif header == "Amount": record[header] = float(cell.EnvalutedValue) elif bool(cell.HasNumber): record[header] = cell.NumberValue else: record[header] = cell.Text records.append(record) with open("orders.json", "w", encoding="utf-8") as fp: json.dump(records, fp, ensure_ascii=False, indent=2) print("records:", len(records)) workbook.Dispose() records: 7 In the output, Amount is a number such as 546.0 rather than a quoted string like "1,365.00", dates are ISO strings, and the flattened regions mean every object carries its own region. The model receives a record list with no structure left to guess, and the total row never sneaks into the details as if it were an order.
Markdown: The Whole Workbook in One Call
For chat context and small samples, the most convenient carrier is a Markdown pipe table. Once the merges are flattened and the formulas evaluated, SaveToMarkdown writes every worksheet in one call as a sectioned pipe table, one ### ==== SheetName ==== heading per sheet.
from spire.xls import * from spire.xls.common import * workbook = Workbook() workbook.LoadFromFile("sales_ledger_flat.xlsx") workbook.CalculateAllValue() workbook.SaveToMarkdown("sales_ledger.md") print("markdown saved") workbook.Dispose() markdown saved The separator rows carry :--- alignment marks, and cell values come from the display layer, so the 1,365.00 thousands separator survives as-is. The merged regions were flattened earlier, so the Region column resolves on every row; the formulas went through CalculateAllValue, so Amount is numeric. Pipe rows consume context linearly as a table grows — within a few dozen rows Markdown works well; beyond that, CSV or JSON is the better carrier.
API Quick Reference
| Class | Role |
|---|---|
Workbook | Load, calculate, and save the workbook |
Worksheet | Data area, merged regions, and per-sheet export |
CellRange | Cell and range reads, checks, and writes |
| Method / Property | Owner | Purpose |
|---|---|---|
LoadFromFile(path) | Workbook | Load an xlsx workbook |
CalculateAllValue() | Workbook | Evaluate all formulas so exports carry numbers |
SaveToFile(path, version) | Workbook | Save the workbook at a given version |
SaveToFile(path, FileFormat.Json) | Workbook | Export the whole workbook as JSON, one array per sheet |
SaveToMarkdown(path) | Workbook | Export the whole workbook as Markdown, sectioned per sheet |
SaveToFile(path, separator, encoding) | Worksheet | Export CSV with a custom separator and encoding |
AllocatedRange | Worksheet | Overall data extent with RowCount and ColumnCount |
MergedCells / RemoveMergedCells(range) | Worksheet | Enumerate and unmerge region by region |
DisplayedText | CellRange | Text as rendered by the cell format |
NumberValue / DateTimeValue | CellRange | Stored numeric and date values |
HasFormula / Formula / EnvalutedValue | CellRange | Formula check, expression text, computed result |
HasNumber | CellRange | Numeric-cell check |
The Five-Step Flow and Its Boundary
The preparation flow runs in five steps: probe the structure to map extents, merges, and formulas; read typed values instead of display text; call CalculateAllValue so CSV exports carry numbers instead of expressions; flatten merged regions so every row stands on its own; and produce JSON or Markdown depending on the consumer. CSV saves context, JSON binds fields explicitly, Markdown suits small samples in a chat; JSON and Markdown each have a one-call whole-workbook path through FileFormat.Json and SaveToMarkdown, and the column-by-column build takes over when field shapes need to be controlled.
The boundary of the approach is just as clear: it repairs data shape, not business semantics. Whether the total row is skipped or kept, and whether an amount is exported raw or as displayed, are caller decisions — the Total check in the code is exactly where that rule lives. The cleaner the table shape, the fewer tokens the context wastes, and the fewer guesses the model has to make.







