IT轮子系列（六）——Excel上传与解析，一套代码解决所有Excel业务上传，你Get到了吗
前言
在日常开发当中，excel的上传与解析是很常见的。根据业务不同，解析的数据模型也都不一样。不同的数据模型也就需要不同的校验逻辑，这往往需要写多套的代码进行字段的检验，如必填项，数据格式。为了避免重复编写逻辑检验代码，于是有了这篇文章。
第一步、读取Excel表格数据
1 public ActionResult UploadExcel() 2 { 3 ResultInfo<List<User>> result = new ResultInfo<List<User>>(); 4 5 var files = Request.Files; 6 var form = Request.Form; 7 if (files.Count > 0) 8 { 9 var file = files[0]; 10 //获取文件扩展名 11 var ext = System.IO.Path.GetExtension(file.FileName); 12 var newPath = ""; 13 try 14 { 15 //获取文件路径 16 var path = HttpContext.Server.MapPath("/Upload/Excels/"); 17 if (!System.IO.Directory.Exists(path)) 18 { 19 System.IO.Directory.CreateDirectory(path); 20 } 21 //保存文件 22 var newFileName = System.Guid.NewGuid().ToString("N") + ext;//新文件名 23 newPath = path + newFileName; 24 file.SaveAs(newPath); 25 DataTable dt = FileUtil.ExcelToDataTable(newPath, true); 26 result = ValidateExcelData<User>(dt); 27 } 28 catch (Exception ex) 29 { 30 result.IsError = true; 31 result.Msg = ex.Message; 32 } 33 finally 34 { 35 //清除文件 36 System.IO.File.Delete(newPath); 37 } 38 } 39 var obj = new { 40 success = result.IsError, 41 msg = result.Msg 42 }; 43 return Json(obj); 44 }
PS:这里的上传，使用了前面文章中关于文件上传的，如有不明白，自行翻看前面IT轮子系列的文章拉。
关于excel的读取，使用的是NPOI组件，网上关于NPOI的文章也很多，这里也不再重复。代码(这代码也是从网上copy来的,已经找不到连接了，在这里感谢一下这位博友): )如下：
1 /// <summary> 2 /// 将excel导入到datatable 3 /// </summary> 4 /// <param name="filePath">excel路径</param> 5 /// <param name="isColumnName">第一行是否是列名</param> 6 /// <returns>返回datatable</returns> 7 public static DataTable ExcelToDataTable(string filePath, bool isColumnName) 8 { 9 DataTable dataTable = null; 10 FileStream fs = null; 11 DataColumn column = null; 12 DataRow dataRow = null; 13 IWorkbook workbook = null; 14 ISheet sheet = null; 15 IRow row = null; 16 ICell cell = null; 17 int startRow = 0; 18 try 19 { 20 using (fs = File.OpenRead(filePath)) 21 { 22 // 2007版本 23 if (filePath.IndexOf(".xlsx") > 0) 24 workbook = new XSSFWorkbook(fs); 25 // 2003版本 26 else if (filePath.IndexOf(".xls") > 0) 27 workbook = new HSSFWorkbook(fs); 28 29 if (workbook != null) 30 { 31 sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet 32 dataTable = new DataTable(); 33 if (sheet != null) 34 { 35 int rowCount = sheet.LastRowNum;//总行数 36 if (rowCount > 0) 37 { 38 IRow firstRow = sheet.GetRow(0);//第一行 39 int cellCount = firstRow.LastCellNum;//列数 40 41 //构建datatable的列 42 if (isColumnName) 43 { 44 startRow = 1;//如果第一行是列名，则从第二行开始读取 45 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) 46 { 47 cell = firstRow.GetCell(i); 48 if (cell != null) 49 { 50 if (cell.StringCellValue != null) 51 { 52 column = new DataColumn(cell.StringCellValue); 53 dataTable.Columns.Add(column); 54 } 55 } 56 } 57 } 58 else 59 { 60 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) 61 { 62 column = new DataColumn("column" + (i + 1)); 63 dataTable.Columns.Add(column); 64 } 65 } 66 67 //填充行 68 for (int i = startRow; i <= rowCount; ++i) 69 { 70 row = sheet.GetRow(i); 71 if (row == null) continue; 72 73 dataRow = dataTable.NewRow(); 74 for (int j = row.FirstCellNum; j < cellCount; ++j) 75 { 76 cell = row.GetCell(j); 77 if (cell == null) 78 { 79 dataRow[j] = ""; 80 } 81 else 82 { 83 //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,) 84 switch (cell.CellType) 85 { 86 case CellType.Blank: 87 dataRow[j] = ""; 88 break; 89 case CellType.Numeric: 90 short format = cell.CellStyle.DataFormat; 91 //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理 92 if (format == 14 || format == 31 || format == 57 || format == 58) 93 dataRow[j] = cell.DateCellValue; 94 else 95 dataRow[j] = cell.NumericCellValue; 96 break; 97 case CellType.String: 98 dataRow[j] = cell.StringCellValue; 99 break; 100 } 101 } 102 } 103 dataTable.Rows.Add(dataRow); 104 } 105 } 106 } 107 } 108 } 109 return dataTable; 110 } 111 catch (Exception) 112 { 113 if (fs != null) 114 { 115 fs.Close(); 116 } 117 return null; 118 } 119 }
第二步、使用泛型检验数据
这里所说的通用，只不过是把检验的规则、配置放到了数据库中。然后根据类名从数据库读取配置规则。在这篇文章中，配置是没有使用到数据库的，直接使用了一个配置类，代码如下：
1 /// <summary> 2 /// excel导入配置表 3 /// </summary> 4 public class Sys_ExcelImportConfig 5 { 6 /// <summary> 7 /// 表名 8 /// </summary> 9 public string TableName { get; set; } 10 /// <summary> 11 /// 表列名 12 /// </summary> 13 public string TableColumnName { get; set; } 14 /// <summary> 15 /// excel列名 16 /// </summary> 17 public string ExcelColumnName { get; set; } 18 /// <summary> 19 /// 数据类型 20 /// </summary> 21 public string DataType { get; set; } 22 /// <summary> 23 /// 是否必填项 24 /// </summary> 25 public bool IsRequired { get; set; } 26 /// <summary> 27 /// 是否值类型 28 /// </summary> 29 public bool IsValueType { get; set; } 30 /// <summary> 31 /// 是否检验数据格式，如手机号、email 32 /// </summary> 33 public bool IsDataChecked { get; set; } 34 /// <summary> 35 /// 正则表达式 若IsDataChecked为true则使用正则进行校验 36 /// </summary> 37 public string Reg { get; set; } 38 39 }
PS:在实际项目中，可以在后台添加一个配置界面。这个配置数据模型就对应数据库中一个表。因此，如果系统需要导入多个业务模型的Excel 只需做好配置就OK拉。
数据解析的泛型方法
代码如下：
1 /// <summary> 2 /// 验证excel数据的格式 3 /// </summary> 4 /// <typeparam name="T">泛型</typeparam> 5 /// <param name="dt">NPOI读取的数据表</param> 6 /// <returns>泛型结果集</returns> 7 private ResultInfo<List<T>> ValidateExcelData<T>(DataTable dt) where T : new() 8 { 9 ResultInfo<List<T>> result = new ResultInfo<List<T>>(); 10 //从数据库读取本次导入的excel配置表 11 /* 12 * 这里的demo就直接写到程序里，不做数据库配置 13 * 在实际项目中，list可以从一个表获取，类的属性就对应 14 * 表中配置的字段 15 */ 16 //1、定义配置数据源，这里配置三列 17 List<Sys_ExcelImportConfig> configList = new List<Sys_ExcelImportConfig> { 18 new Sys_ExcelImportConfig{ 19 TableName="User", 20 TableColumnName="Name", 21 DataType=typeof(System.String).FullName, 22 ExcelColumnName="姓名", 23 IsRequired = true 24 }, 25 new Sys_ExcelImportConfig{ 26 TableName="User", 27 TableColumnName="Position", 28 DataType=typeof(System.String).FullName, 29 ExcelColumnName="职位", 30 IsRequired = true 31 }, 32 new Sys_ExcelImportConfig{ 33 TableName="User", 34 TableColumnName="Age", 35 DataType=typeof(int).FullName, 36 ExcelColumnName="年龄", 37 IsValueType = true 38 } 39 }; 40 //2、遍历数据源 41 var count = dt.Rows.Count; 42 var isError = false; 43 var msg = ""; 44 if (count > 0) 45 { 46 //获取所有的公共属性 47 var proInfos = typeof(T).GetProperties(); 48 //遍历所有的行 49 for (int i = 0; i < count; i++) 50 { 51 T obj = new T(); 52 /* 53 * 遍历所有的配置列 54 * 不在配置列中都不导入 55 */ 56 foreach (Sys_ExcelImportConfig config in configList) 57 { 58 var isContain = dt.Columns.Contains(config.ExcelColumnName); 59 if (isContain)//如果包含 60 { 61 //读取该行该列的值 62 var value = dt.Rows[i][config.ExcelColumnName].ToString(); 63 if (config.IsRequired)//是否为表填项 64 { 65 if ("".Equals(value))//为空，退出循环体 66 { 67 isError = true; 68 //返回错误信息 69 msg = string.Format("Excel表中第{0}中{1}的值不允许为空", i, config.ExcelColumnName); 70 break; 71 } 72 } 73 if (config.IsValueType)//是否值类型:是，则验证数据格式 74 { 75 Type methodType = Type.GetType(config.DataType); 76 77 //这里关键的是& 引用类型的参数 78 Type[] parameters = new Type[2] { typeof(string), Type.GetType(config.DataType + "&") }; 79 var method = methodType.GetMethod("TryParse", parameters); 80 Object[] paraObjs = new Object[2]; 81 paraObjs[0] = value; 82 var objResult = method.Invoke(null, paraObjs); 83 //值类型是否转换成功 84 if (!(bool)objResult) 85 { 86 isError = true; 87 //返回错误信息 88 msg = string.Format("Excel表中第{0}行中[{1}]的值数据格式不正确", i + 1, config.ExcelColumnName); 89 break; 90 } 91 } 92 93 //给公共属性并赋值 94 var property = proInfos.FirstOrDefault(t => t.Name == config.TableColumnName); 95 if (property != null) 96 { 97 property.SetValue(obj, value); 98 } 99 } 100 } 101 if (isError) 102 { 103 //退出所有的循环 104 break; 105 } 106 //验证通过 107 //添加到数据列表 108 result.Data.Add(obj); 109 } 110 } 111 /* 112 * 这里可以返回isError和msg 113 */ 114 result.IsError = isError; 115 result.Msg = msg; 116 return result; 117 }
代码中注释写的很详细，如有看不明白，欢迎砸砖头和留言......
对于其他业务的EXCEL上传，只需在后台做配置就可以了，将数据模型传到方法中，如demo中的User. 在拿到返回的List后 可以做进一步的处理，如写入到数据库。在实际项目中，为了做到共用，可以将这个泛型方法放到一个common项目中，这样别的项目可以直接引用这个common项目。
1 public class ResultInfo<T> 2 { 3 public ResultInfo() 4 { 5 IsError = false; 6 Msg = "操作成功"; 7 Data = default(T); 8 } 9 /// <summary> 10 /// 状态true/false 11 /// </summary> 12 public bool IsError { get; set; } 13 /// <summary> 14 /// 结果信息 15 /// </summary> 16 public string Msg { get; set; } 17 /// <summary> 18 /// 数据 19 /// </summary> 20 public T Data { get; set; } 21 }
后记
从10月初，确切的说9月29号起，也写了7、8篇技术类文章。有的文章也有几百的阅读量，可评论留言的人却少之又少，点赞、推荐就更没有。如果这些文章确实帮到了你，对你的工作有那么一点点的用，希望路过的兄弟姐妹们可以有赞的点个赞，有推荐的来个推荐，有转载的来个转载,为我这个博客园增添点人气。
谢谢拉。。。。。。。 GOOD NIGHT.
ps:最后来张阅读量的截图：
