NPOI execl 数据过多报错和空数据处理 随记
public class PCommon { #region 其他处理信息方法 /// <summary> /// 隐藏手机号中间4位 /// </summary> /// <param name="mobile"></param> /// <returns></returns> public static string hidContract(string mobile) { string strB = string.Empty; string strMobile = string.Empty; string strE = string.Empty; if (mobile != null && mobile.Length > 7) { strB = mobile.Substring(0, 3); strE = mobile.Substring(7, mobile.Length - 7); strMobile = strB + "****" + strE; return strMobile; } else return mobile; } /// <summary> /// 根据手机获取crm 连接 /// </summary> /// <param name="Mobile"></param> /// <returns></returns> public static string GetCrmUrl(string Mobile) { if (!string.IsNullOrEmpty(Mobile)) { Encoding encode = Encoding.GetEncoding("UTF-8"); byte[] bytedata = encode.GetBytes(Mobile + "MetenWeb"); string sign = Convert.ToBase64String(bytedata, 0, bytedata.Length); //DateTime.Now.ToString("yyyy-MM-dd%20HH:mm:ss") string Md5sign = CipherHelper.Hash(string.Format("Mobile={0}&Partner={1}&RequestTime={2}{3}", sign, "Meten", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "Meten!!22qq"), CipherHelper.HashFormat.MD532); return "http://crm.meten.com/Public/LeadDefaultForMeten?Partner=Meten&Sign=" + Md5sign + "&RequestTime=" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "&mobile=" + sign; } else { return "#"; } } #endregion #region 加载数据处理 /// <summary> /// 获去所有城市 /// </summary> /// <returns></returns> public static List<City> getAllCity() { List<City> list = Caches.Citys.Items.Where(p => (((!p.Name.Contains("其它") && p.Name != "顺德")) || p.Name == "其它") && p.IsStat == 0).OrderBy(p => p.OrderNo) .Select(p => new City() { ID = p.ID, EIP_ID = p.EIP_ID, Name = p.Name, CrmId = p.CrmId }).ToList(); return list; } public static List<City> GetUserCity() { int userid = new Verification().UserId; var singlelist = new UserManager().Single(x => x.Id == userid); string[] cids = singlelist.CityIds.Split(','); if (singlelist != null) { if (singlelist.CityIds == "1") { List<City> list = getAllCity(); return list; } else { if (!string.IsNullOrEmpty(singlelist.CityIds)) { List<City> list = Caches.Citys.Items.Where(p => (((!p.Name.Contains("其它") && p.Name != "顺德")) || p.Name == "其它") && p.IsStat == 0).Where(p => cids.Contains(p.ID.ToString())).OrderBy(p => p.OrderNo) .Select(p => new City() { ID = p.ID, EIP_ID = p.EIP_ID, Name = p.Name, CrmId = p.CrmId }).ToList(); return list; } } } return null; } /// <summary> /// 根据城市的crmid查询中心名称 /// </summary> /// <param name="crmid"></param> /// <returns></returns> public static List<AreaSchool> GetSchoolName(string cityName) { string empid = Caches.Citys.GetEipId(cityName); return GetBrand(empid); } /// <summary> /// 根据城市id获取城市的名称 /// </summary> /// <param name="CItyids"></param> /// <returns></returns> [Obsolete] public static string GetCityNames(string cityIds) { if (string.IsNullOrEmpty(cityIds)) return ""; int[] ids = cityIds.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(p => int.Parse(p)).ToArray(); StringBuilder sb = new StringBuilder(); foreach (int id in ids) { string cityName = Caches.Citys.GetCityName(id); if (!string.IsNullOrEmpty(cityName)) { if (sb.Length > 0) sb.Append(","); sb.Append(cityName); } } return sb.ToString(); } public static List<AreaSchool> GetBrand(string EipId) { List<AreaSchool> list = Caches.Schools.Items.Where(p => p.CityId == EipId && !string.IsNullOrEmpty(p.BranchId) && p.IsActive == true) .Select(p => new AreaSchool() { Id = p.Id, BranchId = p.BranchId, Name = p.Name }).ToList(); return list; } /// <summary> /// 字典表 /// </summary> /// <param name="DicType"></param> /// <returns></returns> public static List<DictionaryDesc> getDictionaryDesc(int DicType) { List<DictionaryDesc> list = Caches.DictionaryDescs.Items.Where(p => (p.DicType == DicType)) .Select(p => new DictionaryDesc() { Id = p.Id, DicKey = p.DicKey, DicValue = p.DicValue }).ToList(); return list; } /// <summary> /// 获取所有用户名称 /// </summary> /// <returns></returns> public static List<AdminUserGroups> getUserGroup() { return new UserGroupsCache().Items.Select(p => new AdminUserGroups() { Code = p.Code, Name = p.Name }).ToList(); } /// <summary> /// 根据合作商查二级TID /// </summary> /// <param name="userGroup"></param> /// <returns></returns> public static List<AdminUser> GetUserCps(int userGroup) { return new UserCache().Items.Where(p => p.UserGroup == userGroup).Select(p => new AdminUser() { ChineseName = p.ChineseName, SupportSecondLevel = p.SupportSecondLevel, UserName = p.UserName }).ToList(); } /// <summary> /// 根据id获取用户组名称 /// </summary> /// <param name="Id"></param> /// <returns></returns> public static string getGroupName(int Id) { return new UserGroupsCache().GetUserGroupName(Id); } /// <summary> /// 根据id获取用户组名称 /// </summary> /// <param name="Id"></param> /// <returns></returns> public static List<SupportsFirstLevel> GetAllSupport() { return new SupportsOperatorManager().GetAllSupport(); } public static List<OfferType> GetAllOfferType() { return new OfferTypeManager().GetList(); } public static List<MemberNType> GetNeedType() { return new NeedTypeCache().Items; } #endregion #region 上传图片方法处理 private object obj = new object(); /// <summary> /// oss 上传图片的方法 /// </summary> /// <param name="bucketName"></param> /// <param name="fileName"></param> /// <param name="fileToUpload"></param> /// <returns></returns> public static bool UploadImg(string fileName, HttpPostedFileBase file) { if (string.IsNullOrEmpty(fileName)) return false; string dir = "meten-com"; var endpoint = SiteConfig.aliossEndpoint; var accessKeyId = SiteConfig.aliossAccessKeyId; var accessKeySecret = SiteConfig.aliossAccessKeySecret; OssClient ossClient = new OssClient(endpoint, accessKeyId, accessKeySecret); try { if (IsSafeFile(file.InputStream)) { file.InputStream.Seek(0, SeekOrigin.Begin); string md5; md5 = OssUtils.ComputeContentMd5(file.InputStream, file.ContentLength); var objectMeta = new ObjectMetadata() { ContentMd5 = md5, ContentType = file.ContentType, ContentLength = file.InputStream.Length, CacheControl = "no-cache" }; ossClient.PutObject(dir, "images/" + fileName, file.InputStream, objectMeta); } } catch (Exception ex) { new LogHelper().WriteTraceLog(ex); return false; } return true; } /// <summary> /// 删除oss 上图片的方法 /// </summary> /// <param name="bucketName"></param> /// <param name="fileName"></param> /// <param name="file"></param> /// <returns></returns> public static bool DeleteImg(string FilePath) { if (string.IsNullOrEmpty(FilePath)) return false; FilePath = "images/" + FilePath; string bucketName = "meten-com"; var endpoint = SiteConfig.aliossEndpoint; var accessKeyId = SiteConfig.aliossAccessKeyId; var accessKeySecret = SiteConfig.aliossAccessKeySecret; OssClient ossClient = new OssClient(endpoint, accessKeyId, accessKeySecret); try { ossClient.DeleteObject(bucketName, FilePath); } catch (Exception ex) { new LogHelper().WriteTraceLog(ex); return false; } return true; } /// <summary> /// 判断是否是图片 /// </summary> /// <param name="fliestream"></param> /// <returns></returns> public static Boolean IsSafeFile(Stream fileStream) { try { bool returnval = true; StreamReader sr = new StreamReader(fileStream); string strFile = sr.ReadToEnd(); if (chkcontent(strFile)) { returnval = false; } return returnval; } catch { return false; } } public static bool chkcontent(string content) { bool returnval = false; var stringstr = "request|script=|script =|.getfolder|.createfolder|.deletefolder|.createdirectory|.deletedirectory|.saveas|wscript.shell|script.encode|server.|.createobject|execute|activexobject|language="; string[] sArray = stringstr.Split('|'); content = content.ToLower(); foreach (string i in sArray) { if (content.IndexOf(i) > -1) { returnval = true; break; } } return returnval; } #endregion /// <summary> /// 导出Excel /// </summary> /// <param name="title"></param> /// <param name="content"></param> public static void TransferExcel(string title, string content) { // 设置编码和附件格式 HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xls", title)); HttpContext.Current.Response.ContentType = "application/excel"; HttpContext.Current.Response.Charset = "utf-8"; // 返回客户端 HttpContext.Current.Response.Write("<html><body><head><meta http-equiv=Content-Type content=\"text/html; charset=utf-8\"></head>"); HttpContext.Current.Response.Write(content); HttpContext.Current.Response.Write("</body></html>"); //HttpContext.Current.Response.End(); HttpContext.Current.ApplicationInstance.CompleteRequest(); } public static string WriteToExcel(DataTable dt) { //创建保存的路径(每天一个文件夹) string fn = string.Empty; string filename = "/Upload/TempFiles/" + DateTime.Now.ToString("yyyyMMdd") + "/"; string path = HttpContext.Current.Server.MapPath(filename); try { if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } //删除近10天的文件夹 try { for (int i = 20; i > 0; i--) { string path2 = HttpContext.Current.Server.MapPath("/Upload/TempFiles/" + DateTime.Now.AddDays(-i).ToString("yyyyMMdd") + "/"); if (Directory.Exists(path2)) { Directory.Delete(path2, true); } } } catch { } fn = "Export_" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss") + ".xlsx"; path += fn; //创建工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); //创建sheet表 ISheet sheet = workbook.CreateSheet("Sheet1"); //设置单元格宽度 sheet.SetColumnWidth(0, 20 * 256); sheet.SetColumnWidth(1, 28 * 256); sheet.SetColumnWidth(2, 28 * 256); sheet.SetColumnWidth(3, 25 * 256); //创建列的属性,高为:20*20 IRow headerRow = sheet.CreateRow(0); headerRow.Height = 20 * 20; //创建列 foreach (DataColumn column in dt.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); //构建表格里的内容 int rowIndex = 1; foreach (DataRow row in dt.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dt.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } //使用文件流,写入磁盘 using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write)) { fs.Flush(); workbook.Write(fs); } StreamWriter sw = new StreamWriter(path + "success.log", false, Encoding.UTF8); sw.Write("下载成功!地址为:" + path + fn); sw.Flush(); sw.Close(); } catch (Exception e) { StreamWriter sw = new StreamWriter(path + "error.log", false, Encoding.UTF8); sw.Write("错误信息:" + e.Message + "\r\n 错误跟踪:" + e.StackTrace); sw.Flush(); sw.Close(); } //返回文件的磁盘路径 return filename + fn; } public static List<SupportsFirstLevel> SupportsFirstLevelList() { List<SupportsFirstLevel> list = new LeadsManager().GetFirstLevelList(); return list; } /// <summary> /// 用在后台推广商管理 /// </summary> public static object objs = new object(); static FileStream stream; static DataTable table; public static DataTable SupportImportExcelToDataTable(HttpPostedFileBase postedfile) { lock (objs) { if (postedfile != null) { try { string filename = DateTime.Now.ToString("yyyyMMddhhssmmffffff") + Path.GetExtension(postedfile.FileName); string fullpath = HttpContext.Current.Server.MapPath("~/Upload/TempFiles/"); if (!Directory.Exists(fullpath)) { Directory.CreateDirectory(fullpath); } fullpath += filename; postedfile.SaveAs(fullpath); table = new DataTable(); stream = File.Open(fullpath, FileMode.Open, FileAccess.Read); dynamic workbook = null; if (Path.GetExtension(postedfile.FileName) == ".xls") { workbook = new HSSFWorkbook(stream); } else { workbook = new XSSFWorkbook(stream); } ISheet sheet = workbook.GetSheetAt(0); //获取sheet的首行 IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { if (headerRow.GetCell(i) != null) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } } int rowCount = sheet.LastRowNum; for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row != null && row.FirstCellNum > -1) { DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < table.Columns.Count; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } table.Rows.Add(dataRow); } else { break; } } stream.Close(); File.Delete(fullpath); return table; } catch (Exception ex) { new LogHelper().WriteTraceLog(ex); table.Dispose(); return null; } finally { stream.Close(); table.Dispose(); } } else { return null; } } } } public class ExecData<T> where T : class, new() { static FileStream stream; public static object obj = new object(); private static LogHelper log = new LogHelper(); public List<T> ImportExcelToDataTable(HttpPostedFileBase postedfile) { if (postedfile != null) { try { lock (obj) { List<T> list = new List<T>(); List<string> Columns = new List<string>(); string ExtensionName = Path.GetExtension(postedfile.FileName); string filename = DateTime.Now.ToString("yyyyMMddhhssmmffffff") + ExtensionName; string fullpath = HttpContext.Current.Server.MapPath("~/Upload/TempFiles/"); if (!Directory.Exists(fullpath)) { Directory.CreateDirectory(fullpath); } fullpath += filename; postedfile.SaveAs(fullpath); stream = File.Open(fullpath, FileMode.Open, FileAccess.Read); dynamic workbook; if (ExtensionName == ".xlsx") { workbook = new XSSFWorkbook(stream); } else { workbook = new HSSFWorkbook(stream); } log.WriteInfoLog("初始化excal导入程序 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); ISheet sheet = workbook.GetSheetAt(0); //获取sheet的首行 IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; //获取列名 headerRow.Cells.ForEach(x => { if (x != null) { Columns.Add(x.StringCellValue); } }); int rowCount = sheet.LastRowNum; log.WriteInfoLog("excal导入程序 遍历数据" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row != null && row.FirstCellNum > -1) { T model = new T(); for (int j = row.FirstCellNum; j < cellCount; j++) { model.GetType().GetProperties().ToList().ForEach(x => { if (row.GetCell(j) != null && Columns[j] == x.Name) { x.SetValue(model, GetCellValue(x, row.GetCell(j))); return; } }); } list.Add(model); } } stream.Close(); File.Delete(fullpath); return list; } } catch (Exception ex) { log.WriteTraceLog(ex); return null; } finally { stream.Close(); } } else { return null; } } //获取cell的数据,并设置为对应的数据类型 public object GetCellValue(PropertyInfo prop, ICell cell) { object value = null; if (prop != null) { switch (prop.PropertyType.ToString()) { case "System.Int32": case "System.Nullable`1[System.Int32]": value = (int)cell.NumericCellValue; break; case "System.Decimal": value = (decimal)cell.NumericCellValue; break; case "System.Boolean": case "System.Nullable`1[System.Boolean]": value = cell.BooleanCellValue; break; case "System.DateTime": case "System.Nullable`1[System.DateTime]": value = cell.DateCellValue; break; default: value = cell.ToString(); break; } } return value; } } public class NPOIMemoryStream : MemoryStream { /// <summary> /// 获取流是否关闭 /// </summary> public bool AllowClose { get; set; } public NPOIMemoryStream(bool colse = false) { AllowClose = colse; } public override void Close() { if (AllowClose) { base.Close(); } } }