webapi 导入excel处理数据
参考资料
https://blog.csdn.net/pan_junbiao/article/details/82935992
https://www.cnblogs.com/dansediao/p/5482467.html
https://www.cnblogs.com/shiyh/p/7478241.html
excel转成datatable工具类（ExcelHelp）
using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Reflection; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.SS.Util; using NPOI.XSSF.UserModel; namespace ELearning.Common.Extensions { public static class ExcelHelp { /// <summary> /// excel文件流转化成datatable /// </summary> public static DataTable ExcelToTableForXLSX(Stream fileStream, bool haveNote = false) { var dt = new DataTable(); using (var fs = fileStream) { var xssfworkbook = new XSSFWorkbook(fs); var sheet = xssfworkbook.GetSheetAt(0); //表头 判断是否包含备注 var firstRowNum = sheet.FirstRowNum; if (haveNote) { firstRowNum += 1; } var header = sheet.GetRow(firstRowNum); var columns = new List<int>(); for (var i = 0; i < header.LastCellNum; i++) { var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell); if (obj == null || obj.ToString() == string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i.ToString())); //continue; } else dt.Columns.Add(new DataColumn(obj.ToString())); columns.Add(i); } //数据 for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++) { var dr = dt.NewRow(); var hasValue = false; if (sheet.GetRow(i) == null) { continue; } foreach (var j in columns) { var cell = sheet.GetRow(i).GetCell(j); if (cell != null && cell.CellType == CellType.Numeric) { //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型 if (DateUtil.IsCellDateFormatted(cell)) //日期类型 { dr[j] = cell.DateCellValue; } else //其他数字类型 { dr[j] = cell.NumericCellValue; } } else { dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell); } if (dr[j] != null && dr[j].ToString() != string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } } } return dt; } /// <summary> /// 获取单元格类型(xlsx) /// </summary> /// <param name="cell"></param> /// <returns></returns> private static object GetValueTypeForXLSX(XSSFCell cell) { if (cell == null) return null; switch (cell.CellType) { case CellType.Blank: //BLANK: return null; case CellType.Boolean: //BOOLEAN: return cell.BooleanCellValue; case CellType.Numeric: //NUMERIC: return cell.NumericCellValue; case CellType.String: //STRING: return cell.StringCellValue; case CellType.Error: //ERROR: return cell.ErrorCellValue; case CellType.Formula: //FORMULA: default: return "=" + cell.CellFormula; } } #region 转化实体为dataTable /// <summary> /// Convert a List{T} to a DataTable. /// </summary> public static DataTable ToDataTable<T>(this List<T> items) { var tb = new DataTable(typeof(T).Name); PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance); foreach (PropertyInfo prop in props) { Type t = GetCoreType(prop.PropertyType); tb.Columns.Add(prop.Name, t); } foreach (T item in items) { var values = new object[props.Length]; for (int i = 0; i < props.Length; i++) { values[i] = props[i].GetValue(item, null); } tb.Rows.Add(values); } return tb; } /// <summary> /// Return underlying type if type is Nullable otherwise return the type /// </summary> public static Type GetCoreType(Type t) { if (t != null && IsNullable(t)) { if (!t.IsValueType) { return t; } else { return Nullable.GetUnderlyingType(t); } } else { return t; } } /// <summary> /// Determine of specified type is nullable /// </summary> public static bool IsNullable(Type t) { return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>)); } #endregion #region datatable to list /// <summary> /// DataTable转成List /// </summary> public static List<T> ToDataList<T>(this DataTable dt) { var list = new List<T>(); var plist = new List<PropertyInfo>(typeof(T).GetProperties()); foreach (DataRow item in dt.Rows) { var s = Activator.CreateInstance<T>(); for (var i = 0; i < dt.Columns.Count; i++) { var info = plist.Find(p => p.Name == dt.Columns[i].ColumnName); if (info != null) { try { if (!Convert.IsDBNull(item[i])) { object v = null; if (info.PropertyType.ToString().Contains("System.Nullable")) { v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType)); } else { v = Convert.ChangeType(item[i], info.PropertyType); } info.SetValue(s, v, null); } } catch (Exception ex) { throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message); } } } list.Add(s); } return list; } #endregion } }
SqlBulkCopyHelper工具类
using System; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace ELearning.Common.Helpers { public class SqlBulkCopyHelper { public static void SaveTable(DataTable dtTable) { var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); var sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction) {BulkCopyTimeout = 5000}; try { sbc.DestinationTableName = dtTable.TableName; sbc.WriteToServer(dtTable); } catch (Exception ex) { //处理异常 } finally { //sqlcmd.Clone(); //srcConnection.Close(); //desConnection.Close(); } } } }
对应excel实体类：
/// <summary> /// 导入用户视图模型 /// </summary> public class InsertAdminUsersViewModel { public string 性别 { set; get; } public string 出生日期 { set; get; } public string 身份证号 { set; get; } public string 经销商名称 { set; get; } public string 经销商岗位 { set; get; } public string 更新时间 { set; get; } }
导入信息接口（InsertAdminUsers）：
/// <summary> /// 导入用户 /// </summary> [HttpPost, Route("api/user/InsertAdminUsers")] [AllowAnonymous] public object InsertAdminUsers() { var filelist = HttpContext.Current.Request.Files; var users = new List<InsertAdminUsersViewModel>(); if (filelist.Count > 0) { for (var i = 0; i < filelist.Count; i++) { var file = filelist[i]; var dataTable = ExcelHelp.ExcelToTableForXLSX(file.InputStream);//excel转成datatable users = dataTable.ToDataList<InsertAdminUsersViewModel>();//datatable转成list } } var succe = new List<ESysUser>(); var faile = new List<ESysUser>(); var names = userService.FindList(u => !u.IsDelete).Select(u => u.LoginName).ToList(); //数据list转成数据库实体对应的list foreach (var u in users) { if (string.IsNullOrEmpty(u.状态) || !u.状态.Equals("1")) continue; var s = new ESysUser { CreateTime = DateTime.Now, Birthday = DateTime.ParseExact(u.出生日期, "yyyyMMdd", CultureInfo.CurrentCulture), Email = string.Empty, IsDelete = false, ModifyTime = DateTime.ParseExact(u.更新时间, "yyyyMMddHHmmssfff", CultureInfo.CurrentCulture), UserID = GuidUtil.NewSequentialId(), UserName = u.职员名称, UserType = "JXS", Unumber = u.职员代码, AgentJobName = u.经销商岗位, AgentName = u.经销商名称. CardNo = u.身份证号 }; if (!string.IsNullOrEmpty(s.CardNo) && s.CardNo.Length > 14) { var str = s.CardNo.Substring(6, 8); try { s.Birthday = DateTime.ParseExact(str, "yyyyMMdd", CultureInfo.CurrentCulture); } catch (Exception e) { Console.WriteLine(e); } } var t = names.Where(f => f == s.LoginName); var p1 = succe.Where(o => o.LoginName == s.LoginName); if (t.Any() || p1.Any()) { s.Remark = "登录名重复"; faile.Add(s); } else { succe.Add(s); } } var dt = succe.ToDataTable();//转成 SqlBulkCopy所需要的类型：datatable if (string.IsNullOrEmpty(dt.TableName)) dt.TableName = "ESysUser"; var r = succe.Count; SqlBulkCopyHelper.SaveTable(dt);//批量插入 var list = new { succeed = succe.Take(100).ToList(), failed = faile.Take(100).ToList() }; //数据太多的话，浏览器会崩溃 return OK(list); }
基本上就是这些了。
