你的位置:首页 > ASP.net教程

[ASP.net教程]基于NPOI的Excel数据导入


从Excel导入数据最令人头疼的是数据格式的兼容性,特别是日期类型的兼容性。为了能够无脑导入日期,折腾了一天的NPOI。在经过测试确实可以导入任意格式的合法日期后,写下这篇小文,与大家共享。完整代码请移步:https://github.com/xuanbg/Utility

概述:

这个帮助类是一个泛型类,泛型参数对应的实体类还起到模板的作用。如果你的Excel文件使用与实体类不同的列标题的话,可以通过给属性加上Alias特性,将列标题和属性进行对应。例如:

Excel格式如图:

实体类:

 1 using System; 2 using Insight.Utils.Common; 3  4 namespace Insight.WS.Server.Common.Entity 5 { 6   public class Logistics 7   { 8     [Alias("订单号")] 9     public string OrderCode { get; set; }10 11     [Alias("物流公司")]12     public string Service { get; set; }13 14     [Alias("物流单号")]15     public string Number { get; set; }16 17     [Alias("发货时间")]18     public DateTime DeliveryTime { get; set; }19   }20 }

返回的Json:

 1 [ 2  { 3   "OrderCode": "201611S1200324", 4   "Service": "顺丰", 5   "Number": "33012231F54351", 6   "DeliveryTime": "2016-11-10T11:02:44" 7  }, 8  { 9   "OrderCode": "2016111200324",10   "Service": "顺丰",11   "Number": "33012231F54352",12   "DeliveryTime": "2016-11-12T09:02:44"13  },14  {15   "OrderCode": "2016111200324",16   "Service": "EMS",17   "Number": "33012231F54353",18   "DeliveryTime": "2016-11-12T09:02:44"19  }20 ]

 

1、类主体,负责根据传入的文件路径读取数据,并调用其他私有方法对数据进行处理。最后转换成List<T>并序列化成Json返回。

 1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.IO; 5 using Insight.Utils.Entity; 6 using NPOI.SS.UserModel; 7  8 namespace Insight.Utils.Common 9 {10   public class NpoiHelper<T> where T : new()11   {12     private readonly Result _Result = new Result();13 14     /// <summary>15     /// 导入Excel文件16     /// </summary>17     /// <param name="path">文件路径</param>18     /// <param name="index">Sheet索引</param>19     /// <returns>Result</returns>20     public Result Import(string path, int index = 0)21     {22       if (!File.Exists(path))23       {24         _Result.FileNotExists();25         return _Result;26       }27 28       IWorkbook book;29       using (var file = new FileStream(path, FileMode.Open, FileAccess.Read))30       {31         book = WorkbookFactory.Create(file);32       }33 34       if (index >= book.NumberOfSheets)35       {36         _Result.SheetNotExists();37         return _Result;38       }39 40       var sheet = book.GetSheetAt(index);41       var table = GetSheetData(sheet);42       var list = Util.ConvertToList<T>(table);43       _Result.Success(list);44       return _Result;45     }46   }47 }

 

2、GetSheetData方法,负责将Sheet中的数据读取到DataTable。这里通过实体类属性的特性值作为列名,属性类型作为列数据类型来初始化DataTable。当然,首行是例外,因为首行是列标题而非数据。

 1     /// <summary> 2     /// 读取Sheet中的数据到DataTable 3     /// </summary> 4     /// <param name="sheet">当前数据表</param> 5     /// <returns>DataTable</returns> 6     private DataTable GetSheetData(ISheet sheet) 7     { 8       var table = InitTable(sheet); 9       if (table == null) return null;10 11       var rows = sheet.GetEnumerator();12       while (rows.MoveNext())13       {14         var row = (IRow) rows.Current;15         if (row.RowNum == 0) continue;16 17         var dr = table.NewRow();18         for (var i = 0; i < table.Columns.Count; i++)19         {20           try21           {22             dr[i] = GetCellData(row.GetCell(i));23           }24           catch (Exception)25           {26             dr[i] = DBNull.Value;27           }28 29         }30         table.Rows.Add(dr);31       }32 33       return table;34     }

初始化DataTable的方法:

 1     /// <summary> 2     /// 初始化DataTable 3     /// </summary> 4     /// <param name="sheet">当前数据表</param> 5     /// <returns>DataTable</returns> 6     private DataTable InitTable(ISheet sheet) 7     { 8       var title = sheet.GetRow(0); 9       if (title == null)10       {11         _Result.NoRowsRead();12         return null;13       }14 15       try16       {17         var dict = GetDictionary();18         var table = new DataTable();19         foreach (var cell in title.Cells)20         {21           var col_name = cell.StringCellValue;22           var col_type = dict[col_name];23           table.Columns.Add(cell.StringCellValue, col_type);24         }25 26         return table;27       }28       catch29       {30         _Result.IncorrectExcelFormat();31         return null;32       }33     }

生成模板字典的方法:

 1     /// <summary> 2     /// 获取指定类型的属性名称/类型字典 3     /// </summary> 4     /// <returns>Dictionary</returns> 5     private Dictionary<string, Type> GetDictionary() 6     { 7       var dict = new Dictionary<string, Type>(); 8       var propertys = typeof(T).GetProperties(); 9       foreach (var p in propertys)10       {11         string name;12         var attributes = p.GetCustomAttributes(typeof(AliasAttribute), false);13         if (attributes.Length > 0)14         {15           var type = (AliasAttribute)attributes[0];16           name = type.Alias;17         }18         else19         {20           name = p.Name;21         }22 23         dict.Add(name, p.PropertyType);24       }25 26       return dict;27     }

 

3、重点来了!

因为日期/时间在Excel中可能被表示为文本格式或日期格式(其实是Numeric类型),所以在CellType为String/Numeric的时候,先取一下cell的DateCellValue,异常后再取cell的StringCellValue/NumericCellValue就好了。

这样,无论日期是文本或日期格式,都可以完美获取。

 1    /// <summary> 2     /// 读Excel单元格的数据 3     /// </summary> 4     /// <param name="cell">Excel单元格</param> 5     /// <returns>object 单元格数据</returns> 6     private object GetCellData(ICell cell) 7     { 8       switch (cell.CellType) 9       {10         case CellType.Numeric:11           try12           {13             return cell.DateCellValue;14           }15           catch (InvalidOperationException)16           {17             return cell.NumericCellValue;18           }19 20         case CellType.String:21           try22           {23             return cell.DateCellValue;24           }25           catch (InvalidOperationException)26           {27             return cell.StringCellValue;28           }29 30         case CellType.Boolean:31           return cell.BooleanCellValue;32 33         case CellType.Unknown:34         case CellType.Formula:35         case CellType.Blank:36         case CellType.Error:37           return null;38         default:39           return null;40       }41     }

 

 4、DataTable转成List<T>的方法:

 1     /// <summary> 2     /// 将DataTable转为List 3     /// </summary> 4     /// <param name="table">DataTable</param> 5     /// <returns>List</returns> 6     public static List<T> ConvertToList<T>(DataTable table) where T: new() 7     { 8       var list = new List<T>(); 9       var propertys = typeof(T).GetProperties();10       foreach (DataRow row in table.Rows)11       {12         var obj = new T();13         foreach (var p in propertys)14         {15           string name;16           var attributes = p.GetCustomAttributes(typeof(AliasAttribute), false);17           if (attributes.Length > 0)18           {19             var type = (AliasAttribute) attributes[0];20             name = type.Alias;21           }22           else23           {24             name = p.Name;25           }26 27           if (table.Columns.Contains(name))28           {29             if (!p.CanWrite) continue;30 31             var value = row[name];32             if (value == DBNull.Value) value = null;33 34             p.SetValue(obj, value, null);35           }36         }37         list.Add(obj);38       }39       return list;40     }

自定义特性:

 1 using System; 2  3 namespace Insight.Utils.Common 4 { 5   [AttributeUsage(AttributeTargets.Property)] 6   public class AliasAttribute : Attribute 7   { 8     /// <summary> 9     /// 属性别名10     /// </summary>11     public string Alias { get; }12 13     /// <summary>14     /// 构造方法15     /// </summary>16     /// <param name="alias">别名</param>17     public AliasAttribute(string alias)18     {19       Alias = alias;20     }21   }22 }

 

 

请大家对此多发表意见和建议,谢谢。