[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>        [Descr
定義了方法“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"來糾正顯示，不過這