[Cyan之旅]使用NPOI实现Excel的导入导出,踩坑若干.Cyan是博主【Soar360】自2014年以来开始编写整理的工具组件，用于解决现实工作中常用且与业务逻辑无关的问题。
什么是NPOI？NPOI是 POI 项目的 .NET 版本。POI是一个开源的java读写Excel、Word等微软OLE2组件文档的项目。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。
来自：百度百科
关于Office格式在老板本的Office软件中（97-2003），Excel文件的默认后缀是.xls，而新版本的Office软件(2007+)所用的默认后缀为.xlsx。这两种格式最要命的区别就是.xls后缀的文件，每个工作区中最大支持65536条数据，而.xlsx后缀的文件，每个工作区最大支持1048576行数据。
虽说65536已经足够大，但是在实际工作中确实有超过这个数值的情况，所以我们需要对两种格式都进行支持。如果数据量还是很大要怎么办？很简单，拆成多个工作区即可。
为此，我们定义了枚举“OfficeType”用来标明Excel格式：
/// <summary> /// Office文件格式 /// </summary> public enum OfficeType { /// <summary> /// 97-2003格式 /// </summary> [Description("Office2003")] Office2003, /// <summary> /// 2007+格式 /// </summary> [Description("Office2007")] Office2007 }
定义了方法“FormatFileName”来修正生成文件的文件名：
/// <summary> /// 格式化Excel文件名，根据Excel类型，为Excel增加后缀。 /// </summary> /// <param name="fileName">未格式化的文件名</param> /// <param name="officeType">Excel类型</param> /// <returns>格式化后的Excel文件名。</returns> public static String FormatFileName(String fileName, OfficeType officeType) { if (String.IsNullOrEmpty(fileName)) throw new ArgumentNullException("fileName"); var ext = officeType == OfficeType.Office2007 ? ".xlsx" : ".xls"; var name = fileName; if (!fileName.EndsWith(ext, StringComparison.CurrentCultureIgnoreCase)) { name += ext; } return name; }
怎样的导出，才是有节操的导出？数据导出是一个经常性的工作，这项工作在2014年5月份，占用了我2/3的工作时长。这期间遇到的问题如下：
我们要做导出的数据源格式多种多样，可能是DataSet、DataTable、List<T>甚至是Dictionary<TKey, TValue>，如何才能兼容这些格式的数据源呢？系统中有部分基础数据是缓存的，比如文章类型表。数据源提供的只有类型ID一列，并不包含类型名称，而导出是必须要求有类型名称的。如果为了实现导出，单独搞一个数据源或者手工再对Dto进行加工，也太过得不偿失了。与上一条类似，如果我们的数据源返回的数据是True和Flase，而我们必须要将导出的数据显示为“是、否”或者“启用中、已停用”。导出信息需要将数据源中的两个字段进行拼接后输出的。比如，数据源中有姓名和身份证号，但是导出数据要求输出到一个单元格中。……其实说白了，就两个问题：
数据源兼容数据格式化为了解决这两个问题，博主设计出一个接口“IExportColumn”：
/// <summary> /// 导出列接口 /// </summary> /// <typeparam name="T">数据行类型</typeparam> public interface IExportColumn<in T> { /// <summary> /// 列标题 /// </summary> String Title { get; } /// <summary> /// 获取该列的值 /// </summary> /// <param name="row"></param> /// <param name="index"></param> /// <returns></returns> Object GetValue(T row, Int32 index); }
只读Title属性表示导出列的标题。GetValue方法，传入数据项和该项在集合中的索引。同时增加了通用列“ExportColumn<T>”：
/// <summary> /// 导出列 /// </summary> /// <typeparam name="T"></typeparam> public class ExportColumn<T> : IExportColumn<T> { public ExportColumn(String title, Func<T, Int32, Object> funcGetValue) { if (String.IsNullOrEmpty(title)) throw new ArgumentNullException("title"); if (funcGetValue == null) throw new ArgumentNullException("funcGetValue"); this.Title = title; this._funcGetValue = funcGetValue; } PRivate readonly Func<T, Int32, Object> _funcGetValue; public string Title { get; private set; } public object GetValue(T row, int index) { return this._funcGetValue(row, index); } }
还有方便导出DataTable的“DataRowExportColumn”：
public class DataRowExportColumn : IExportColumn<DataRow> { public DataRowExportColumn(String name) : this(name, String.Empty) { } public DataRowExportColumn(String name, String title) : this(name, title, null) { } public DataRowExportColumn(String name, String title, Func<Object, Int32, Object> funcFormatValue) { if (String.IsNullOrEmpty(name)) throw new ArgumentNullException("name"); this.Name = name; this._title = title; this._funcFormatValue = funcFormatValue; } public String Name { get; private set; } private readonly String _title; private readonly Func<Object, Int32, Object> _funcFormatValue; public string Title { get { return String.IsNullOrEmpty(this._title) ? this.Name : this._title; } } public object GetValue(DataRow row, int index) { var val = row[this.Name]; return this._funcFormatValue != null ? _funcFormatValue(val, index) : val; } }
当然，我们需要一个导出方法：
/// <summary> /// 导出Excel,如果Excel类型为Office2003，那么数据行数不能超过65535，如果超过，则会被拆分到多个工作区中。 /// </summary> /// <typeparam name="T">数据类型</typeparam> /// <param name="dataSource">数据源</param> /// <param name="excelType">EXCEL格式</param> /// <param name="sheetName">工作区名称</param> /// <param name="saveStream">保存到的文件流</param> /// <param name="columns">导出列</param> public static void ExportExcel<T>(IList<T> dataSource, OfficeType excelType, String sheetName, Stream saveStream, IList<IExportColumn<T>> columns)
那么，导出数据的代码看上去就像是这个样子：
using (var fs = new FileStream(tmpFileName, FileMode.Create)) { ExcelHelper.ExportExcel(list, OfficeType.Office2003, "保险卡", fs, new IExportColumn<Entity.InsuranceCard>[] { new ExportColumn<Entity.InsuranceCard>("编号", (o, i) => o.Id), new ExportColumn<Entity.InsuranceCard>("卡号", (o, i) => o.Number), new ExportColumn<Entity.InsuranceCard>("类型", (o, i) => o.InsuranceCardTypeName), new ExportColumn<Entity.InsuranceCard>("制卡时间", (o, i) => o.CreatedTime), new ExportColumn<Entity.InsuranceCard>("是否开通", (o, i) => o.Enabled ? "已开通" : "锁定"), new ExportColumn<Entity.InsuranceCard>("是否激活", (o, i) => o.Activated ? "已激活" : "未激活"), new ExportColumn<Entity.InsuranceCard>("密码", (o, i) => o.Password) }); }
什么，你说怎么兼容DataTable和Dictionary<TKey, TValue>？骚年，“dt.Rows.Cast<DataRow>().ToList()”懂不懂，“dic.Select(i => new { i.Key, i.Value }).ToList()”懂不懂？什么，你还在用.NET 2.0？LINQBridge你值得拥有。
数据导入数据导出的数据源是来自计算机的，而数据导入的数据源是来自人的。一旦有“人”这个元素参与进来，就必须增加一系列的约束，系统才能正常理解人想要表达的操作。毕竟，计算机并不是那么智能。
如果要用Excel导入数据，我们要求，Excel的第一行必须为列标题，不能有多行标题和跨行跨列的情况。如果有任何不符合条件的，导入就会失败。没办法，机器就是机器。我们选择使用DataSet作为数据导入的返回类型，方便处理而且通用性比较强。最主要的是，可以在Visual Studio中直接查看DataSet的内容，方便排查错误。
/// <summary> /// 导入Excel /// </summary> /// <param name="fileStream"></param> /// <returns></returns> /// <exception cref="ArgumentNullException"></exception> public static DataSet ImportExcel(Stream fileStream)
数据导入会自动识别Excel的格式，是97-2003还是2007+，所以，我们只需要将Excel文件的数据流传入即可。
说说那些坑不要在asp.net中尝试将导出数据流直接设置为Response.OutputStream，这会导致错误。虽然Excel文件能够打开，但是有提示框。推荐的做法是输出到临时文件后让用户去下载。如果导出手机号码、身份证等纯数字信息时，Excel会将该信息显示为科学计数法，影响使用和查看。Cyan组件中已经修复这个问题。但是如果导出格式是Csv，那么需要再数据前填充制表符"t"来纠正显示，不过这
