IT*系列（六）——Excel上传与解析，一套代码解决所有Excel业务上传，你Get到了吗

前言

在日常开发当中，excel的上传与解析是很常见的。根据业务不同，解析的数据模型也都不一样。不同的数据模型也就需要不同的校验逻辑，这往往需要写多套的代码进行字段的检验，如必填项，数据格式。为了避免重复编写逻辑检验代码，于是有了这篇文章。

第一步、读取Excel表格数据

         public ActionResult UploadExcel()
         {
             ResultInfo<List<User>> result = new ResultInfo<List<User>>();            

             var files = Request.Files;
             var form = Request.Form;
             if (files.Count > )
             {
                 var file = files[];
                 //获取文件扩展名
                 var ext = System.IO.Path.GetExtension(file.FileName);
                 var newPath = "";
                 try
                 {
                     //获取文件路径
                     var path = HttpContext.Server.MapPath("/Upload/Excels/");
                     if (!System.IO.Directory.Exists(path))
                     {
                         System.IO.Directory.CreateDirectory(path);
                     }
                     //保存文件
                     var newFileName = System.Guid.NewGuid().ToString("N") + ext;//新文件名
                     newPath = path + newFileName;
                     file.SaveAs(newPath);
                     DataTable dt = FileUtil.ExcelToDataTable(newPath, true);
                     result = ValidateExcelData<User>(dt);
                 }
                 catch (Exception ex)
                 {
                     result.IsError = true;
                     result.Msg = ex.Message;
                 }
                 finally
                 {
                     //清除文件
                     System.IO.File.Delete(newPath);
                 }
             }
             var obj = new {
                 success = result.IsError,
                 msg = result.Msg
             };
             return Json(obj);
         }

前端界面-上传Excel

PS:这里的上传，使用了前面文章中关于文件上传的，如有不明白，自行翻看前面IT*系列的文章拉。

关于excel的读取，使用的是NPOI组件，网上关于NPOI的文章也很多，这里也不再重复。代码(这代码也是从网上copy来的,已经找不到连接了，在这里感谢一下这位博友): )如下：

         /// <summary>
         /// 将excel导入到datatable
         /// </summary>
         /// <param name="filePath">excel路径</param>
         /// <param name="isColumnName">第一行是否是列名</param>
         /// <returns>返回datatable</returns>
         public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
         {
             DataTable dataTable = null;
             FileStream fs = null;
             DataColumn column = null;
             DataRow dataRow = null;
             IWorkbook workbook = null;
             ISheet sheet = null;
             IRow row = null;
             ICell cell = null;
             int startRow = ;
             try
             {
                 using (fs = File.OpenRead(filePath))
                 {
                     // 2007版本
                     if (filePath.IndexOf(".xlsx") > )
                         workbook = new XSSFWorkbook(fs);
                     // 2003版本
                     else if (filePath.IndexOf(".xls") > )
                         workbook = new HSSFWorkbook(fs);

                     if (workbook != null)
                     {
                         sheet = workbook.GetSheetAt();//读取第一个sheet，当然也可以循环读取每个sheet
                         dataTable = new DataTable();
                         if (sheet != null)
                         {
                             int rowCount = sheet.LastRowNum;//总行数
                             if (rowCount > )
                             {
                                 IRow firstRow = sheet.GetRow();//第一行
                                 int cellCount = firstRow.LastCellNum;//列数

                                 //构建datatable的列
                                 if (isColumnName)
                                 {
                                     startRow = ;//如果第一行是列名，则从第二行开始读取
                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                     {
                                         cell = firstRow.GetCell(i);
                                         if (cell != null)
                                         {
                                             if (cell.StringCellValue != null)
                                             {
                                                 column = new DataColumn(cell.StringCellValue);
                                                 dataTable.Columns.Add(column);
                                             }
                                         }
                                     }
                                 }
                                 else
                                 {
                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                     {
                                         column = new DataColumn("column" + (i + ));
                                         dataTable.Columns.Add(column);
                                     }
                                 }

                                 //填充行
                                 for (int i = startRow; i <= rowCount; ++i)
                                 {
                                     row = sheet.GetRow(i);
                                     if (row == null) continue;

                                     dataRow = dataTable.NewRow();
                                     for (int j = row.FirstCellNum; j < cellCount; ++j)
                                     {
                                         cell = row.GetCell(j);
                                         if (cell == null)
                                         {
                                             dataRow[j] = "";
                                         }
                                         else
                                         {
                                             //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                             switch (cell.CellType)
                                             {
                                                 case CellType.Blank:
                                                     dataRow[j] = "";
                                                     break;
                                                 case CellType.Numeric:
                                                     short format = cell.CellStyle.DataFormat;
                                                     //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                                     if (format ==  || format ==  || format ==  || format == )
                                                         dataRow[j] = cell.DateCellValue;
                                                     else
                                                         dataRow[j] = cell.NumericCellValue;
                                                     break;
                                                 case CellType.String:
                                                     dataRow[j] = cell.StringCellValue;
                                                     break;
                                             }
                                         }
                                     }
                                     dataTable.Rows.Add(dataRow);
                                 }
                             }
                         }
                     }
                 }
                 return dataTable;
             }
             catch (Exception)
             {
                 if (fs != null)
                 {
                     fs.Close();
                 }
                 return null;
             }
         }

NPOI读取Excel

第二步、使用泛型检验数据

这里所说的通用，只不过是把检验的规则、配置放到了数据库中。然后根据类名从数据库读取配置规则。在这篇文章中，配置是没有使用到数据库的，直接使用了一个配置类，代码如下：

   /// <summary>
     /// excel导入配置表
     /// </summary>
     public class Sys_ExcelImportConfig
     {
         /// <summary>
         /// 表名
         /// </summary>
         public string TableName { get; set; }
         /// <summary>
         /// 表列名
         /// </summary>
         public string TableColumnName { get; set; }
         /// <summary>
         /// excel列名
         /// </summary>
         public string ExcelColumnName { get; set; }
         /// <summary>
         /// 数据类型
         /// </summary>
         public string DataType { get; set; }
         /// <summary>
         /// 是否必填项
         /// </summary>
         public bool IsRequired { get; set; }
         /// <summary>
         /// 是否值类型
         /// </summary>
         public bool IsValueType { get; set; }
         /// <summary>
         /// 是否检验数据格式，如手机号、email
         /// </summary>
         public bool IsDataChecked { get; set; }
         /// <summary>
         /// 正则表达式 若IsDataChecked为true则使用正则进行校验
         /// </summary>
         public string Reg { get; set; }

     }

配置数据模型

PS:在实际项目中，可以在后台添加一个配置界面。这个配置数据模型就对应数据库中一个表。因此，如果系统需要导入多个业务模型的Excel 只需做好配置就OK拉。

---

数据解析的泛型方法

代码如下：

         /// <summary>
         /// 验证excel数据的格式
         /// </summary>
         /// <typeparam name="T">泛型</typeparam>
         /// <param name="dt">NPOI读取的数据表</param>
         /// <returns>泛型结果集</returns>
         private ResultInfo<List<T>> ValidateExcelData<T>(DataTable dt) where T : new()
         {
             ResultInfo<List<T>> result = new ResultInfo<List<T>>();
             //从数据库读取本次导入的excel配置表
             /*
              * 这里的demo就直接写到程序里，不做数据库配置
              * 在实际项目中，list可以从一个表获取，类的属性就对应
              * 表中配置的字段
              */
             //1、定义配置数据源，这里配置三列
             List<Sys_ExcelImportConfig> configList = new List<Sys_ExcelImportConfig> {
                 new Sys_ExcelImportConfig{
                     TableName="User",
                     TableColumnName="Name",
                     DataType=typeof(System.String).FullName,
                     ExcelColumnName="姓名",
                     IsRequired = true
                 },
                 new Sys_ExcelImportConfig{
                     TableName="User",
                     TableColumnName="Position",
                     DataType=typeof(System.String).FullName,
                     ExcelColumnName="职位",
                     IsRequired = true
                 },
                 new Sys_ExcelImportConfig{
                     TableName="User",
                     TableColumnName="Age",
                     DataType=typeof(int).FullName,
                     ExcelColumnName="年龄",
                     IsValueType = true
                 }
             };
             //2、遍历数据源
             var count = dt.Rows.Count;
             var isError = false;
             var msg = "";
             if (count > )
             {
                 //获取所有的公共属性
                 var proInfos = typeof(T).GetProperties();
                 //遍历所有的行
                 for (int i = ; i < count; i++)
                 {
                     T obj = new T();
                     /*
                      * 遍历所有的配置列
                      * 不在配置列中都不导入
                      */
                     foreach (Sys_ExcelImportConfig config in configList)
                     {
                         var isContain = dt.Columns.Contains(config.ExcelColumnName);
                         if (isContain)//如果包含
                         {
                             //读取该行该列的值
                             var value = dt.Rows[i][config.ExcelColumnName].ToString();
                             if (config.IsRequired)//是否为表填项
                             {
                                 if ("".Equals(value))//为空，退出循环体
                                 {
                                     isError = true;
                                     //返回错误信息
                                     msg = string.Format("Excel表中第{0}中{1}的值不允许为空", i, config.ExcelColumnName);
                                     break;
                                 }
                             }
                             if (config.IsValueType)//是否值类型:是，则验证数据格式
                             {
                                 Type methodType = Type.GetType(config.DataType);

                                 //这里关键的是& 引用类型的参数
                                 Type[] parameters = new Type[] { typeof(string), Type.GetType(config.DataType + "&") };
                                 var method = methodType.GetMethod("TryParse", parameters);
                                 Object[] paraObjs = new Object[];
                                 paraObjs[] = value;
                                 var objResult = method.Invoke(null, paraObjs);
                                 //值类型是否转换成功
                                 if (!(bool)objResult)
                                 {
                                     isError = true;
                                     //返回错误信息
                                     msg = string.Format("Excel表中第{0}行中[{1}]的值数据格式不正确", i + , config.ExcelColumnName);
                                     break;
                                 }
                             }

                             //给公共属性并赋值
                             var property = proInfos.FirstOrDefault(t => t.Name == config.TableColumnName);
                             if (property != null)
                             {
                                 property.SetValue(obj, value);
                             }
                         }
                     }
                     if (isError)
                     {
                         //退出所有的循环
                         break;
                     }
                     //验证通过
                     //添加到数据列表
                     result.Data.Add(obj);
                 }
             }
             /*
              * 这里可以返回isError和msg
              */
             result.IsError = isError;
             result.Msg = msg;
             return result;
         }

泛型解析方法

代码中注释写的很详细，如有看不明白，欢迎砸砖头和留言......

对于其他业务的EXCEL上传，只需在后台做配置就可以了，将数据模型传到方法中，如demo中的User. 在拿到返回的List后 可以做进一步的处理，如写入到数据库。在实际项目中，为了做到共用，可以将这个泛型方法放到一个common项目中，这样别的项目可以直接引用这个common项目。

  public class ResultInfo<T>
     {
         public ResultInfo()
         {
             IsError = false;
             Msg = "操作成功";
             Data = default(T);
         }
         /// <summary>
         /// 状态true/false
         /// </summary>
         public bool IsError { get; set; }
         /// <summary>
         /// 结果信息
         /// </summary>
         public string Msg { get; set; }
         /// <summary>
         /// 数据
         /// </summary>
         public T Data { get; set; }
     }

ResultInfo数据模型

后记

从10月初，确切的说9月29号起，也写了7、8篇技术类文章。有的文章也有几百的阅读量，可评论留言的人却少之又少，点赞、推荐就更没有。如果这些文章确实帮到了你，对你的工作有那么一点点的用，希望路过的兄弟姐妹们可以有赞的点个赞，有推荐的来个推荐，有转载的来个转载,为我这个博客园增添点人气。

谢谢拉。。。。。。。 GOOD NIGHT.

ps:最后来张阅读量的截图：