[Cyan之旅]使用NPOI实现Excel的导入导出,踩坑若干._.NET_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > .NET > [Cyan之旅]使用NPOI实现Excel的导入导出,踩坑若干.

[Cyan之旅]使用NPOI实现Excel的导入导出,踩坑若干.

 2015/5/13 21:04:00  Soar、毅  程序员俱乐部  我要评论(0)
  • 摘要:Cyan是博主【Soar360】自2014年以来开始编写整理的工具组件,用于解决现实工作中常用且与业务逻辑无关的问题。什么是NPOI?NPOI是POI项目的.NET版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。使用NPOI你就可以在没有安装Office或者相应环境的机器上对WORD/EXCEL文档进行读写。NPOI是构建在POI3.x版本之上的,它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。来自
  • 标签: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的工作时长。这期间遇到的问题如下:

  1. 我们要做导出的数据源格式多种多样,可能是DataSet、DataTable、List<T>甚至是 Dictionary<TKey, TValue>,如何才能兼容这些格式的数据源呢?
  2. 系统中有部分基础数据是缓存的,比如文章类型表。数据源提供的只有类型ID一列,并不包含类型名称,而导出是必须要求有类型名称的。如果为了实现导出,单独搞一个数据源或者手工再对Dto进行加工,也太过得不偿失了。
  3. 与上一条类似,如果我们的数据源返回的数据是True和Flase,而我们必须要将导出的数据显示为“是、否”或者“启用中、已停用”。
  4. 导出信息需要将数据源中的两个字段进行拼接后输出的。比如,数据源中有姓名和身份证号,但是导出数据要求输出到一个单元格中。
  5. ……

其实说白了,就两个问题:

  1. 数据源兼容
  2. 数据格式化

为了解决这两个问题,博主设计出一个接口“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文件的数据流传入即可。

说说那些坑

  1. 不要在ASP.NET中尝试将导出数据流直接设置为Response.OutputStream,这会导致错误。虽然Excel文件能够打开,但是有提示框。推荐的做法是输出到临时文件后让用户去下载。
  2. 如果导出手机号码、身份证等纯数字信息时,Excel会将该信息显示为科学计数法,影响使用和查看。Cyan组件中已经修复这个问题。但是如果导出格式是Csv,那么需要再数据前填充制表符"t"来纠正显示,不过这样的话在复制时,制表符也是会被读取和复制的,所以在导入时,记得要对数据进行Trim。
  3. 因为办公软件的多样性,在正式运行过程中出现了Excel软件只显示一个工作区,而NPOI读取到了多个工作区的情况。Excel不显示的工作区行数为0,所以在导入的时候,要将行数为0的工作区过滤掉。
  4. 如果Excel中包含日期,而Excel中的日期是以数字格式处理的,所以要得到正确的日期格式,需要进行额外的处理。Cyan目前已经支持。
  5. 如果Excel表格中只有10条数据,但是用户在第20行设置样式或者无意中设置了空行,那么会造成NPOI实际读取了20行的数据,但是其中的10行都是空的。不过用户可能察觉不到。所以在编写代码时要注意过滤空行。

最后,源码奉上:Cyan.Toolkit.Office

发表评论
用户名: 匿名