查看: 220|回复: 3

Python清洗Excel公式与合并单元格导出LLM友好数据

[复制链接]
发表于 2 小时前 | 显示全部楼层 |阅读模式
把一份销售台账直接交给大模型,回答经常不可靠,问题多数不在模型,而在表格本身。金额列在文件里存的是 =D2*E2 这样的公式,直接导出文本就是一行公式串;区域列为了让“华东”跨三行显示做了单元格合并,导出后只有每组第一行有值,其余行为空;单价带着 #,##0.00 格式,导出文本是 45.50,千位分隔符还会让 1,365.00 整个被引号包住。这些给人看的排版约定,进入模型上下文后全变成了噪声。

合理的做法是把准备工作交给库:读出每个单元格的存储值、把公式换成数值、展开合并区域,再按目标格式导出。本文使用 Spire.XLS for Python,安装命令如下:
  1. pip install spire.xls
复制代码

先明确对象职责。Workbook 是工作簿容器,负责 LoadFromFile、CalculateAllValue、Worksheets、SaveToFile、SaveToMarkdown;Worksheet 代表工作表,提供 Name、AllocatedRange、HasMergedCells、MergedCells、RemoveMergedCells、SaveToFile、ToImage;CellRange 代表单元格或区域,提供 DisplayedText、NumberValue、DateTimeValue、HasFormula、HasNumber、Formula、EnvalutedValue。示例输入是一份两个工作表的销售台账:订单表 9 行 6 列,区域列有三组合并单元格,金额列是公式,末行是合计;库存表 4 行 4 列,普通单行表头。

一、先探测结构,别急着改数据

准备数据的第一步是探测而不是修改:每个表多少行多少列、哪里有合并、哪里有公式。AllocatedRange 给出数据区的整体跨度,MergedCells 枚举每个合并区域的起始行列与跨度,配合 HasFormula 逐格扫描可以数出公式单元格的分布。
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. workbook = Workbook()
  4. workbook.LoadFromFile('销售台账.xlsx')
  5. for i in range(workbook.Worksheets.Count):
  6.     sheet = workbook.Worksheets[i]
  7.     region = sheet.AllocatedRange
  8.     print('sheet %d: %s | rows=%d cols=%d' % (
  9.         i, sheet.Name, region.RowCount, region.ColumnCount))
  10.     if bool(sheet.HasMergedCells):
  11.         merged = sheet.MergedCells
  12.         print(' merged regions: %d' % merged.Count)
  13.         for j in range(merged.Count):
  14.             m = merged[j]
  15.             print(' %s%d:%s%d' % (
  16.                 chr(64 + m.Column), m.Row,
  17.                 chr(64 + m.Column + m.ColumnCount - 1),
  18.                 m.Row + m.RowCount - 1))
  19.     formula_cells = 0
  20.     for r in range(1, region.RowCount + 1):
  21.         for c in range(1, region.ColumnCount + 1):
  22.             if bool(sheet.Range[r, c].HasFormula):
  23.                 formula_cells += 1
  24.     print(' formula cells: %d' % formula_cells)
  25. workbook.Dispose()
复制代码

输出显示订单表有 8 个公式单元格,正好是 7 行金额加 1 个合计;3 个合并区域对应三组订单,分别是 B2:B4、B5:B6、B7:B8。这里有两个细节值得留意:MergedCells 只在表里确实存在合并区域时才有内容,取之前先判 HasMergedCells;HasMergedCells 这类布尔属性返回的是包装对象,放进 if 前用 bool() 转一次更稳。

二、同一个格子有不止一种取值

导出格式解决结构问题,取值方式决定内容质量。同一个单元格在库里有多种读法:DisplayedText 返回按格式渲染的显示文本,NumberValue、DateTimeValue 返回存储值,公式单元格用 Formula 看式子、用 EnvalutedValue 拿求值结果。
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. workbook = Workbook()
  4. workbook.LoadFromFile('销售台账.xlsx')
  5. sheet = workbook.Worksheets[0]
  6. d = sheet.Range[2, 3]
  7. print('C2 DisplayedText :', d.DisplayedText)
  8. print('C2 DateTimeValue :', d.DateTimeValue.ToString('yyyy-MM-dd'))
  9. print('C2 NumberValue   :', d.NumberValue)
  10. p = sheet.Range[2, 5]
  11. print('E2 DisplayedText :', p.DisplayedText)
  12. print('E2 NumberValue   :', p.NumberValue)
  13. f = sheet.Range[2, 6]
  14. print('F2 HasFormula    :', bool(f.HasFormula))
  15. print('F2 Formula       :', f.Formula)
  16. print('F2 EnvalutedValue:', f.EnvalutedValue)
  17. workbook.Dispose()
复制代码

三组输出对应三类决策。日期格显示为 2026-11-03,存储值是序列数 46329.0,喂给模型用格式化字符串即可,只有做日期运算才需要 DateTimeValue;单价显示 45.50,存储值是 45.5,显示文本带格式,直接当数字会解析失败;Text 属性在数字单元格上取不到内容,所以文本表示一律用 DisplayedText;公式格的 EnvalutedValue 返回求值后的字符串 546,这是后面导出环节的关键取值。

三、CSV 导出:求值前后差一份账

CSV 是喂给模型最省 token 的格式。Worksheet.SaveToFile 的三个参数分别是文件名、分隔符和编码。差异出在调用时机:加载后直接导出,公式单元格输出的是公式文本;先对工作簿调用 CalculateAllValue,导出的才是数值。
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. workbook = Workbook()
  4. workbook.LoadFromFile('销售台账.xlsx')
  5. workbook.Worksheets[0].SaveToFile('订单_raw.csv', ',', Encoding.get_UTF8())
  6. workbook.Dispose()
  7. workbook = Workbook()
  8. workbook.LoadFromFile('销售台账.xlsx')
  9. workbook.CalculateAllValue()
  10. for i in range(workbook.Worksheets.Count):
  11.     sheet = workbook.Worksheets[i]
  12.     sheet.SaveToFile('%s.csv' % sheet.Name, ',', Encoding.get_UTF8())
  13.     print('saved:', sheet.Name)
  14. workbook.Dispose()
复制代码

求值前的文件里,金额列每一行都是带引号的公式文本,模型只能把它当字符串;求值后变成 546.00 这样的数值。两条路线还各留了一个痕迹:求值后合计行的 4,576.00 因为千位分隔符被引号包住,合并单元格造成的区域列空值也原样保留在文件里。CSV 导出忠实转储显示层,这两个问题要在导出之前修。订单与库存两个表粒度不同,各自导出成独立文件,模型按需取用,不必把整本工作簿塞进上下文。

四、展开合并单元格,把值还给每一行

合并区域只在左上角存值,其余成员格读出来是空。展开分三步:从 MergedCells 记下每个区域的起始位置、跨度和左上角值,逐区域调用 RemoveMergedCells 解除合并,再向下填充。
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. workbook = Workbook()
  4. workbook.LoadFromFile('销售台账.xlsx')
  5. for i in range(workbook.Worksheets.Count):
  6.     sheet = workbook.Worksheets[i]
  7.     if not bool(sheet.HasMergedCells):
  8.         continue
  9.     merged = sheet.MergedCells
  10.     captured = []
  11.     for j in range(merged.Count):
  12.         m = merged[j]
  13.         captured.append((m.Row, m.Column, m.RowCount,
  14.                          sheet.Range[m.Row, m.Column].Text))
  15.     for j in range(merged.Count):
  16.         sheet.RemoveMergedCells(merged[j])
  17.     for row, col, row_count, value in captured:
  18.         for r in range(row, row + row_count):
  19.             sheet.Range[r, col].Text = value
  20.     print('%s: %d merged regions flattened' % (sheet.Name, len(captured)))
  21. workbook.SaveToFile('销售台账_展开.xlsx', ExcelVersion.Version2016)
  22. workbook.Dispose()
复制代码

顺序很关键:捕获要发生在解除合并之前,颠倒过来就拿不到值;填充时用记录下来的 RowCount 逐行写入,跨多行的区域一次覆盖到位。与样例表对照,展开后区域列从“每组只有一行有值”变成每行都有归属,A-1002、A-1003 这类行不再带着空区域进入下游。

五、JSON:整簿快捷导出与按列定制

JSON 把表头变成键、每行变成对象,字段与值的绑定关系是显式的,适合接口和结构化管线。库提供了整簿一行导出:SaveToFile 指定 FileFormat.Json,每个工作表输出一个数组,每行一个对象。
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. workbook = Workbook()
  4. workbook.LoadFromFile('销售台账.xlsx')
  5. workbook.CalculateAllValue()
  6. workbook.SaveToFile('订单_quick.json', FileFormat.Json)
  7. print('quick json saved')
  8. workbook.Dispose()
复制代码

这份导出适合快速查看,但作为喂给模型的记录形态还差三步:数值全部成了字符串(例如数量的 12 被写成字符串),金额保留公式原文(CalculateAllValue 不影响这份导出),合并未展开的表里 A-1002 这类行干脆没有区域键。字段的类型与有无全凭显示层,模型拿到的是一份需要再猜的结构。

按列定制构建可以解决这三处:基于展开后的工作簿,日期列取 DateTimeValue 格式化成 ISO 写法,金额列取 EnvalutedValue 转成数值,数字列用 HasNumber 判断后读 NumberValue,其余列读 Text;合计行按首列文本跳过。
  1. import json
  2. from spire.xls import *
  3. from spire.xls.common import *
  4. workbook = Workbook()
  5. workbook.LoadFromFile('销售台账_展开.xlsx')
  6. workbook.CalculateAllValue()
  7. sheet = workbook.Worksheets[0]
  8. region = sheet.AllocatedRange
  9. headers = [sheet.Range[1, c].Text for c in range(1, region.ColumnCount + 1)]
  10. records = []
  11. for r in range(2, region.RowCount + 1):
  12.     if sheet.Range[r, 1].Text == '合计':
  13.         continue
  14.     record = {}
  15.     for c in range(1, region.ColumnCount + 1):
  16.         cell = sheet.Range[r, c]
  17.         header = headers[c - 1]
  18.         if header == '日期':
  19.             record[header] = cell.DateTimeValue.ToString('yyyy-MM-dd')
  20.         elif header == '金额':
  21.             record[header] = float(cell.EnvalutedValue)
  22.         elif bool(cell.HasNumber):
  23.             record[header] = cell.NumberValue
  24.         else:
  25.             record[header] = cell.Text
  26.     records.append(record)
  27. with open('订单.json', 'w', encoding='utf-8') as fp:
  28.     json.dump(records, fp, ensure_ascii=False, indent=2)
  29. print('records:', len(records))
  30. workbook.Dispose()
复制代码

产物共 7 条记录,金额是 546.0 这样的数值而不是带千位分隔符的字符串,日期是 ISO 写法,合并单元格已经在上一步展开,每个对象都完整自带区域归属。模型拿到的是一份不需要再猜结构的记录列表,合计行也不会混进明细被当成一条订单。

六、Markdown:整簿一次输出

对话与小样本场景里最省事的是 Markdown 管道表。展开与求值完成后,SaveToMarkdown 一次把整簿各表写成带分节标题的管道表,每张工作表对应一个 ### ==== 表名 ==== 小节。
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. workbook = Workbook()
  4. workbook.LoadFromFile('销售台账_展开.xlsx')
  5. workbook.CalculateAllValue()
  6. workbook.SaveToMarkdown('销售台账.md')
  7. print('markdown saved')
  8. workbook.Dispose()
复制代码

输出里分隔行带 :--- 对齐标记,单元格取的是显示层文本,1,365.00 的千位分隔原样保留。因为合并单元格已在前面展开,区域列每行都有值;公式已经过 CalculateAllValue,金额列是数值。需要注意的是管道行会随表格变长线性消耗上下文,几十行以内用 Markdown,上千行的表仍然走 CSV 或 JSON。

七、五步流程与适用边界

这套准备流程按顺序走五步:探测结构,确认行列跨度、合并区域和公式分布;用类型化取值拿到存储值而不是显示文本;CalculateAllValue 把公式换成数值再导出 CSV;展开合并单元格让每一行自带归属;最后按用途产出 JSON 或 Markdown。CSV 省上下文,JSON 字段绑定显式,Markdown 适合小样本进对话;JSON 和 Markdown 各有一条 FileFormat.Json、SaveToMarkdown 的整簿导出之路,字段形态需要受控时走按列定制的构建。

这套做法的边界也很清楚:它修的是数据形态,不改业务口径——合计行是跳过还是保留、金额取原始值还是显示值,由调用方的规则决定,代码里那个合计判断就是规则落点。表格结构越规整,喂给模型的上下文越省,回答里的臆测越少。
回复

使用道具 举报

发表于 2 小时前 | 显示全部楼层

Re: Python清洗Excel公式与合并单元格导出LLM友好数据

这个思路很实用,先把表格当成“给人看的排版产物”而不是“数据容器”来看,很多坑就解释得通了。几个点我比较有共鸣:一是先探测再修改,尤其是合并区域和公式分布,先摸清楚再动手,比直接遍历清洗安全得多;二是同一个单元格多种取值方式,DisplayedText 负责“人看到的”,NumberValue/DateTimeValue 负责“机器算的”,公式格再用求值结果,这个区分对下游喂模型很关键。 顺着这个思路补充两点实践中的体会:合并单元格展开时,最好以合并区域左上角的值向下或向右填充,同时保留一个“是否原始合并”的标记列,方便回溯;公式格在读取 EnvalutedValue 之前,先调一次 CalculateAllValue 更稳妥,避免文件里缓存值缺失或过期。另外千位分隔符那个问题,本质上是别把显示文本当数值用,金额、单价这类字段统一走 NumberValue 再按目标格式序列化,基本就能避开引号包裹和解析失败。 最后那句好像被截断了,公式格的 EnvalutedValue 后面应该是想讲求值结果的用法吧?如果后面还有导出 Markdown 或构造 LLM 上下文的部分,挺期待继续看,尤其是多工作表怎么组织、表头怎么保留、以及空值和合计行怎么处理这些细节。
回复 支持 反对

使用道具 举报

发表于 2 小时前 | 显示全部楼层

Re: Python清洗Excel公式与合并单元格导出LLM友好数据

这个思路很实用,尤其是“先探测结构,别急着改数据”这一点很关键。把公式、合并单元格和显示格式分开处理,确实能避免把给人看的排版噪声直接塞进模型上下文。你对 DisplayedText、NumberValue 和公式求值结果的区别讲得比较清楚,日期和金额在导出前统一成明确文本格式也会更稳。合并区域展开后记得向下填充首行值,否则导出文本里还是会出现空值。期待后续导出 LLM 友好数据那部分,尤其是 Markdown 或结构化文本的字段组织方式。
回复 支持 反对

使用道具 举报

发表于 2 小时前 | 显示全部楼层

Re: Python清洗Excel公式与合并单元格导出LLM友好数据

这篇思路很实用,核心就是把“给人看的排版”和“给模型吃的数据”分开处理。先探测结构再改数据这点很关键,尤其是 HasMergedCells 先判空、布尔包装对象用 bool() 转一次,都是实际写脚本时容易踩的坑。 取值方式那段也讲得清楚:日期用格式化字符串、单价用 NumberValue、文本展示用 DisplayedText,公式格则先看 Formula 再取求值结果,比直接导出显示文本可靠得多。合并单元格展开时按起始格向下填充,公式先 CalculateAllValue 再转数值,应该就能把大部分噪声清掉。 另外最后公式求值结果那段好像没贴完,期待补全,特别是合并区域展开和最终导出格式的完整示例。
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

指导单位

江苏省公安厅

江苏省通信管理局

浙江省台州刑侦支队

DEFCON GROUP 86025

Hacking Group 021A

旗下站点

态势感知中心

应急响应中心

红盟安全

联系我们

官方QQ群:112851260

官方邮箱:security#ihonker.org(#改成@)

官方核心成员

关注微信公众号

Archiver|手机版|小黑屋| ( 沪ICP备2021026908号 )

GMT+8, 2026-11-23 04:44 , Processed in 0.021527 second(s), 18 queries , Gzip On, Redis On.

Powered by ihonker.com

Copyright © 2015-现在.

  • 返回顶部