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

前言

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

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

1. string type = string.Empty;
2. if (!dict.TryGetValue(field, out type))
3. {
4.     return "TEXT";
5. }
7. switch (type.ToUpper())
8. {
9.     case "C":
10.     case "L": // Bit(Logic) treat as Text
11.         return "TEXT";
13.     case "I":
14.         return "INTEGER";
16.     case "B":
17.     case "F":
18.     case "N":
19.     case "Y":
20.         return "DOUBLE";
22.     case "D":
23.     case "T":
24.         return "DATETIME";
26.     default:
27.         return "TEXT";
28. }

复制代码

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

1. if (cellValue == DBNull.Value || cellValue == null)
2. {
3.   if ((sheetName.Equals("sheet1", StringComparison.OrdinalIgnoreCase) || sheetName.Equals("sheet2", StringComparison.OrdinalIgnoreCase)) &&
4.       (columnName.Equals("date_from", StringComparison.OrdinalIgnoreCase) || columnName.Equals("date_to", StringComparison.OrdinalIgnoreCase)))
5.   {
6.       values += "'" + new string(' ', 16) + "',";
7.   }
8.   else
9.   {
10.       values += "NULL,";
11.   }
13.   continue;

复制代码

}
内容多行时处理不同

但单元格内容有多行时，xls认为换行是：CR+LF，xlsx则只有LF, 此时需要一个正则，来识别和修正这个换行：
[code]case "TEXT":    string escaped = Regex.Replace(cellValue.ToString().Replace("'", "''"), @"(?