Excel: xls与xlsx格式转换排坑指南

前言

总结一下在把Excel 5.0/95 的XLS转换为Excel 2007的XLSX新格式遇到的问题。

数据类型匹配

XLS文件里的单元格是General类型，但在XLSX里，是有针对具体的列设置数据类型的，这使得在操作database时，有可能造成数据类型不一致的错误：

string type = string.Empty; if (!dict.TryGetValue(field, out type)) { return "TEXT"; } switch (type.ToUpper()) { case "C": case "L": // Bit(Logic) treat as Text return "TEXT"; case "I": return "INTEGER"; case "B": case "F": case "N": case "Y": return "DOUBLE"; case "D": case "T": return "DATETIME"; default: return "TEXT"; } 

再者就是对于空串和Null，似乎xls认为没有什么差异，但xlsx读取出来，倘若单元格没有内容，""和DB.Null是有差异的：

if (cellValue == DBNull.Value || cellValue == null) { if ((sheetName.Equals("sheet1", StringComparison.OrdinalIgnoreCase) || sheetName.Equals("sheet2", StringComparison.OrdinalIgnoreCase)) && (columnName.Equals("date_from", StringComparison.OrdinalIgnoreCase) || columnName.Equals("date_to", StringComparison.OrdinalIgnoreCase))) { values += "'" + new string(' ', 16) + "',"; } else { values += "NULL,"; } continue; 

}

内容多行时处理不同

但单元格内容有多行时，xls认为换行是：CR+LF，xlsx则只有LF, 此时需要一个正则，来识别和修正这个换行：

case "TEXT": string escaped = Regex.Replace(cellValue.ToString().Replace("'", "''"), @"(?<!\r)\n", "\r\n"); values += "'" + escaped + "',"; break; 

default值

在xls中，如果每个column都有设置过数据类型，不是default的General，此时修改该column的某一行，如果不匹配，xls将有个角标显示警告，数据类型不符合预期，xls认为这是人为的一个失误。读取时，可能会被强制成default的值。

但如果事先设置了值，再设置数据类型，虽然不匹配，xls将无视这个警告，xls会当做是有意为之。

int: 0; doouble: 0.0; bool: string; DateTime: string; 

最大行数

xls支持的最大行数：大约是65K，65535行。 但是，当编辑其中内容再次保存时，可能只剩18K，在某些版本（Excel 5.0）会丢失一部分数据。

xlsx支持的最大行数：大约是10,000K, 1048576行。这时候就无法另存为xls格式，数据将丢失绝大部分。