做数据处理的人大概率碰到过这个场景：CSV清洗完了，编码修好了，脏数据清掉了，转成xlsx打开一看——金额列左对齐，筛选下拉没有数字区间只有文本列表；日期列按字符串排序，没有按年/月/日展开的层级结构。所有列都能看，但所有列的交互能力都丢了。
这不是清洗没做好，是写入时只设了显示格式，没改底层值类型。Excel筛选器的树状结构——按年→月→日展开日期、按区间划分数字——依赖的是单元格存储的实际数据类型，不是显示格式。
Excel不帮你做隐式转换
字符串"799.00"配上#,##0.00格式，筛选下拉出来的是：等于、不等于、包含、开头是——文本的筛选规则。float(799.0)配上同样的格式，筛选下拉变成：大于、小于、介于、前10项。内容一模一样，但Excel对这个单元格的认知完全不同。日期也一样。字符串"2025-10-19"配日期格式，筛选器按字母排序；datetime(2025,10,19)配同一格式，筛选器自动聚合成年→月→日的三层结构。
这个行为是Excel写死的：number_format只决定单元格里显示成什么样子，不改变cell.value的实际类型。字符串不会因为套了一个数字格式就变成数值。
项目初期我们犯的错误就是这个——用openpyxl写好数据，逐列设了#,##0.00和YYYY-MM-DD格式，自信满满打开文件，发现全白费。排查很久才意识到问题出在对Excel API的理解偏差上。
自检测机制：先摸底再动手
知道问题原因之后，解法看上去很直接：写入前把字符串转成对应类型就行。但这里有一个更实际的问题——不能对所有列无差别做类型转换。订单号也是纯数字，把它转成float写入，Excel的筛选器倒是能用了，但17位的订单号在写入之前就被float截断了精度，得不偿失。
所以正确的做法是两阶段：先拿样本做自检测，给每列打好格式标签，再按标签指导全量批处理。目标是每列只做一次判断（在样本阶段做完），批处理阶段只做值转换和格式设置，干净、可预测。
阶段一：样本检测。 读入所有行之后，先做表头定位。电商导出的CSV经常在表头之前塞几行"数据导出日期：2025-10-19"或"平台：淘宝"之类的冗余信息。pandas的header=0会把这些行当表头，必须用header=None读所有行，再自己写函数定位。这个函数的逻辑：取前5行，逐行检查每个单元格，同时满足三个条件才算通过——单元格不是空字符串、第一个字符不是特殊字符（空格/逗号/分号/币种符号/制表符）、最后一个字符也不是这些特殊字符。当一个行中超过一半的单元格都通过检查时，认为这一行是表头。5行扫描完都没找到合适的，回退到第0行——至少能跑，比崩掉强。
找到表头后，把这行的值提取出来设成DataFrame的columns，然后把这行及之前的所有行截掉。截掉之后记得重置行索引，否则后续"取前15行做样本"的操作会从错误的索引位置取值。
搞定表头后，取前15~20行做列类型检测样本。逐列做三件事：检查是否含首尾特殊字符（需要清洗的信号）、检查是否匹配文本/日期/数值三类格式特征、打好格式标签。有一个处理小文件的边界情况：如果整个文件只有5行（表头+4行数据），样本检测阶段有多少行就用多少行，15行的检测阈值动态降低为当前有效行数。
阶段二：全量批处理。 样本检测阶段给每列打好了格式标签，同时记录了每种标签的辅助信息（文本列的12+位掩码、日期列对应的strptime格式字符串、数值列的小数位数）。批处理阶段不再做类型判断，逐列按标签和辅助信息做值转换+格式设置。核心价值：每列只做一次判断，批处理只做转换，不会因为单行异常数据中断全程。
样本检测和批处理之间有一个容易忽略的环节——格式标签冲突解决。一列订单号同时满足"12位数字"（文本条件）和"可被float()解析"（数值条件）。如果不设优先级，同一列会被同时标记为文本和数值，写入逻辑混乱。冲突规则：文本 > 日期 > 数值 > 普通，互斥不叠加。订单号最终标记为文本列，不做数值转换。
列类型的三层判断逻辑
文本格式列（最高优先级）。12位及以上连续纯数字。商品ID、订单号、流水号——这些数字没有计算意义，但Excel默认截断15位后有效数字并触发科学计数法。扫描到这类列直接标记为文本，排除出后续的日期和数值判断。12这个阈值不是随便拍的——实测12位商品ID（如955956980424）在Excel中已可能触发科学计数法，10位ID多数业务场景也需要原样保留。12是经验值，覆盖绝大多数非计算数字字段。
日期格式列（中间优先级）。电商CSV中常见5种日期写法：YYYY-MM-DD HH:MM:SS、YYYY/MM/DD HH:MM:SS、YYYY-MM-DD、YYYY/MM/DD、YYYYMMDD，偶尔有带中文的YYYY年MM月DD日。逐列取前15行，每行按格式列表依次用datetime.strptime尝试解析，解析成功率超过阈值（比如60%）标记为日期列，并记录这一列匹配到的确切格式字符串。注意同列中可能混有少量无法解析的空值或占位符——允许一定比例的解析失败，不要因为一行不符就放弃整列。
数值格式列（最低优先级）。仅含小数点且能被float()安全解析。金额、单价、佣金率都属于这一类。检测前先排除已被标记为文本和日期的列。有一个常见的干扰项——纯短横。列中大量数值但少量行是-或–或—，float("-")直接抛出ValueError，这列就会被判定为非数值列。所以短横需要单独做一个预处理步骤。
短横占位符的预处理
电商CSV的金额、运费、佣金列中纯短横很常见，表示"此条记录无对应金额"。不是NULL，不是数据缺失，是业务上的值=0。确认一个单元格是否纯短横需要三步：去掉首尾空格→判断剩余内容是否只有单个短横字符→确认该短横属于Unicode短横字符集（连字符hyphen -、短破折号en dash –、长破折号em dash —）。三个条件都满足才替换为字符串"0"，再进入值类型转换。只替换数值和日期上下文中的短横——备注列也常有短横，但不替换。
写入时的值类型转换
前面全部准备就绪后，到了执行写入的环节。三个流程：
- 日期列：
datetime.datetime.strptime(cell_str, fmt)→ datetime对象 → 写入cell.value，设number_format为YYYY-MM-DD HH:MM:SS - 数值列：
float(cell_str)→ float → 写入cell.value，设number_format为#,##0.00 - 文本列：直接写入字符串，设number_format为
@
几个实际踩过的坑。float("799.00")返回799.0不是799，但#,##0.00格式下显示为"799.00"，不影响视觉。datetime.strptime要求格式字符串和输入精确匹配，不匹配直接抛ValueError——所以样本检测阶段必须记录每列匹配到的确切格式字符串，不能只记"这一列是日期"。文本列的@格式不是必须的（字符串写入后默认就是文本），但显式设置可以防止用户双击单元格时触发Excel的类型自动推断。
CSV输出中长数字的保护
如果输出的是CSV格式，保护长数字的方式和xlsx不同。CSV没有单元格类型，所有值都是文本。方案是利用Excel对前导单引号(')的特殊处理——在CSV单元格中写'123456789012，Excel导入时把单引号识别为文本前缀，显示时不出现，内容保持为纯字符串。
之前尝试过="123..."的公式写法，Excel直接把公式文本显示在单元格里，被否了。前导单引号是目前唯一可行的方案。
数据流验证
清洗脚本的每个写入操作前后必须有独立验证。清空sheet后验证表空了，写入后验证行数和原始数据一致，类型转换后抽样双击目标列点击筛选按钮确认数字区间和日期树状结构已正确展示。不要复用同一段数据读取代码做验证——读取和验证必须用全新的连接器，否则解析bug不会在验证阶段自我暴露。这个原则适用于所有数据处理脚本，不限于格式转换。
总结成一句话：识别列类型、转换值类型、设显示格式，三步顺序不能错。顺序错了的数据文件，每一列看起来都有内容，但没有一列能在Excel里好好用。这个经验是在实际的代码迭代中一步步踩出来的，一开始也以为设了number_format就够了——直到打开文件才发现Excel不帮你做隐式转换。