Excel 导入并导出结果集
1、controler代码：
1 /// <summary> 2 /// 导入预归类意见书 3 /// </summary> 4 /// <param name="uploadfile"></param> 5 /// <returns></returns> 6 [HttpPost] 7 [ValidateAntiForgeryToken] 8 public FileResult INPUTExcel(IEnumerable<HttpPostedFileBase> uploadfile) 9 { 10 string strvalue = string.Empty; 11 byte[] fileContents = Encoding.Default.GetBytes("暂无数据!"); 12 SsoIdentity identity = this.User.Identity as SsoIdentity; 13 try 14 { 15 string SavePath = string.Empty; 16 bool bResult = false; 17 #region<<将Excel文件保存到服务器>> 18 foreach (var file in uploadfile) 19 { 20 string FileName = Path.GetFileName(file.FileName); 21 string PathR = Server.MapPath("~/UpLoadFile/PreClassified/"); 22 bResult = FileHelper.IsExistDirectory(PathR); 23 if (!bResult) 24 { 25 FileHelper.CreateDirectory(PathR); 26 } 27 SavePath = Path.Combine(PathR, FileName); 28 file.SaveAs(SavePath); 29 } 30 #endregion 31 if (!string.IsNullOrWhiteSpace(SavePath)) 32 { 33 LoginInfo loginInfo = new LoginInfo(); 34 UserHelper.GetUserInfo(false, ref loginInfo, identity); 35 //如果存在已存在数据选择是否覆盖的预归类意见书列表 36 bResult = _ibll.InputClassificationLabDecision(SavePath, ref fileContents,loginInfo.CustomCode);37 //FileHelper.UnLockFile(SavePath); 38 System.IO.File.Delete(SavePath);//添加导入完成之后在服务器端删除上传到服务器的EXCEL文件 39 40 strvalue = string.Format("三统一帐号{0} 执行预归类意见书管理子系统中预归类意见书 ", loginInfo.LoginName); 41 if (!bResult) 42 { 43 UserOperateLogHelper.WriteOperateLog(strvalue, EnumActionOperatonType.InPut, (int)EnumActionExecutedStatus.Error, identity,
(int)EnumSysFlags.PreClassification); 44 } 45 UserOperateLogHelper.WriteOperateLog(strvalue, EnumActionOperatonType.InPut, (int)EnumActionExecutedStatus.Success, identity,
(int)EnumSysFlags.PreClassification); 46 } 47 } 48 catch(Exception ex) 49 { 50 LogHelper.Write(ex); 51 } 52 return File(fileContents, "application/ms-excel", "ImportPerBooksResult.xls"); 53 }
2.BLL层代码
（1）处理Excel文件：
1 /// <summary> 2 /// 导入预归类意见书 3 /// </summary> 4 /// <param name="fileName"></param> 5 /// <param name="btyBytes"></param> 6 /// <returns></returns> 7 public bool InputClassificationLabDecision(string fileName, ref byte[] btyBytes, string CGAC_CO) 8 { 9 IWorkbook workbook = null; 10 List<PreClassificationOpBook> lst = new List<PreClassificationOpBook>(); 11 FileStream fs = null; 12 List<string> lstresult = null; 13 //string sheetName = null; 14 bool bResult = true; 15 ISheet sheet = null; 16 string filename = string.Format("MyClassificationLabDecision{0}.xls", DateTime.Now.ToString("HHmmssfff")); 17 int startRow = 0; 18 try 19 { 20 using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) 21 { 22 if (fileName.IndexOf(".xlsx") > 0) // 2007版本 23 { 24 workbook = new XSSFWorkbook(fs); 25 } 26 else if (fileName.IndexOf(".xls") > 0) // 2003版本 27 { 28 workbook = new HSSFWorkbook(fs); 29 } 30 if (workbook != null) 31 { 32 sheet = workbook.GetSheetAt(0); 33 if (sheet != null) 34 { 35 IRow firstRow = sheet.GetRow(0); 36 int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 37 startRow = sheet.FirstRowNum + 1; 38 //最后一列的标号 39 int rowCount = sheet.LastRowNum; 40 List<bool> lstdate = new List<bool>(); 41 for (int i = startRow; i <= rowCount; ++i) 42 { 43 bool IsNullRow = true; 44 PreClassificationOpBook mdl = new PreClassificationOpBook(); 45 IRow row = sheet.GetRow(i); 46 if (row == null) continue; //没有数据的行默认是null 47 for (int j = row.FirstCellNum; j < cellCount; ++j) 48 { 49 bool ibresult = false; 50 var cellValue=row.GetCell(j); 51 var rowValue = cellValue == null?"":cellValue.ToString(); 52 if (!string.IsNullOrWhiteSpace(rowValue) || !IsNullRow) 53 { 54 mdl = AddModelField(j, mdl, rowValue, ref ibresult); 55 if (j == 12)//如果列为生效日期列 56 { 57 lstdate.Add(ibresult); 58 } 59 IsNullRow = false; 60 } 61 } 62 if (!IsNullRow) 63 { 64 lst.Add(mdl); 65 } 66 } 67 //不采取全部一起提交的方式 每一条执行一次SaveChanage 68 AddModelList(lst, lstdate, ref lstresult,CGAC_CO); 69 } 70 } 71 } 72 } 73 catch (IOException ex) 74 { 75 LogHelper.Write(ex); 76 bResult = false; 77 } 78 catch (SqlException ex) 79 { 80 LogHelper.Write(ex); 81 bResult = false; 82 } 83 finally 84 { 85 if (fs != null) 86 { 87 fs.Dispose(); 88 }
//导出结果集 89 Dictionary<string, string> Dic = GETMATCHUP(); 90 Dic["CGAC_CO"] = "关区代码"; 91 Dic.Remove("IsEffect"); 92 FileHelper.ExExcelDouble<PreClassificationOpBook>(lst, filename, ref btyBytes, Dic, lstresult); 93 } 94 return bResult; 95 }
（2）Excel列存入model实体类
1 /// <summary> 2 /// 向Model中添加字段值 3 /// </summary> 4 /// <param name="i"></param> 5 /// <param name="mdl"></param> 6 /// <param name="row"></param> 7 /// <param name="ibresult"></param> 8 /// <returns></returns> 9 private PreClassificationOpBook AddModelField(int i, PreClassificationOpBook mdl, string rowValue, ref bool ibresult) 10 { 11 12 switch (i) 13 { 14 //case 0://第一行为流水号 15 // { 16 // mdl.SEQ_NO = rowValue; 17 // break; 18 // } 19 case 0://预归类意见书编号 20 { 21 mdl.PCS_NO_S = rowValue; 22 break; 23 } 24 case 1://经营单位（委托方）名称 25 { 26 mdl.TRADE_NAME = rowValue; 27 break; 28 } 29 case 2://经营单位(委托方)企业代码 30 { 31 mdl.TRADE_CO = rowValue; 32 break; 33 } 34 case 3://商品名称(中文) 35 { 36 mdl.G_NAME = rowValue; 37 break; 38 } 39 case 4://规格型号 40 { 41 mdl.G_MODEL = rowValue; 42 break; 43 } 44 case 5://商品名称(英文名称) 45 { 46 mdl.G_NAME_ENG = rowValue; 47 break; 48 } 49 case 6://商品名称(其他名称) 50 { 51 mdl.G_NAME_OTR = rowValue; 52 break; 53 } 54 case 7://商品描述(货物型号、规格、成份及用途) 55 { 56 mdl.G_DESCRIPTION = rowValue; 57 break; 58 } 59 case 8://归类结论 60 { 61 mdl.CODE_TS = rowValue; 62 break; 63 } 64 case 9://归类依据或理由 65 { 66 mdl.NOTE = rowValue; 67 break; 68 } 69 case 10://预归类单位名称 70 { 71 mdl.AGENT_NAME = rowValue; 72 break; 73 } 74 //case 11://预归类数据导入单位名称 75 // { 76 // mdl.AGENT_INPUT_NAME = rowValue; 77 // break; 78 // } 79 case 11://联系电话 80 { 81 mdl.AGENT_TEL = rowValue; 82 break; 83 } 84 //case 13://关区代码 85 // { 86 // mdl.CGAC_CO = rowValue; 87 // break; 88 // } 89 case 12://生效日期 90 { 91 DateTime dt = DateTime.Now; 92 bool bResult = ToolsHelper.CheckTime(rowValue.Replace("月", ""), ref dt); 93 if (bResult) 94 { 95 mdl.EFFECTIVE_DATE = dt; 96 } 97 ibresult = bResult; 98 break; 99 } 100 } 101 return mdl; 102 }
3.导出导入结果集
（1）固定列最后加一列导入结果列
1 /// <summary> 2 /// 将一组对象导出成EXCEL 3 /// </summary> 4 /// <typeparam name="T">要导出对象的类型</typeparam> 5 /// <param name="objList">一组对象</param> 6 /// <param name="fileName">导出后的文件名</param> 7 /// <param name="columnInfo">列名信息</param> 8 /// <param name="btyBytes"></param> 9 public static bool ExExcelDouble<T>(List<T> objList, string fileName, ref byte[] btyBytes, Dictionary<string, string> columnInfo = null,
List<string> lststring = null) where T : class 10 { 11 bool bResult = false; 12 try 13 { 14 //if (objList.Count > 50000) 15 //{ 16 // btyBytes = new byte[] { }; 17 // bResult = ExExcel<T>(objList, fileName, ref btyBytes, columnInfo, lststring); 18 // return bResult; 19 //} 20 HSSFWorkbook workbook = new HSSFWorkbook(); 21 //在工作薄中建立工作表 22 HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet; 23 sheet.SetColumnWidth(0, 30 * 256); 24 if (columnInfo != null && columnInfo.Count == 0) { return false; } 25 //生成EXCEL的HTML 26 string excelStr = ""; 27 Type myType = null; 28 if (objList.Any()) 29 { 30 myType = objList[0].GetType(); 31 //根据反射从传递进来的属性名信息得到要显示的属性 32 List<PropertyInfo> myPro = new List<PropertyInfo>(); 33 PropertyInfo[] properties = myType.GetProperties(); 34 int m = 0; 35 if (columnInfo != null) 36 { 37 var rowheader = sheet.CreateRow(0); 38 rowheader.Height = 20 * 20; 39 foreach (string cName in columnInfo.Keys) 40 { 41 PropertyInfo p = myType.GetProperty(cName); 42 if (p != null) 43 { 44 myPro.Add(p); 45 rowheader.CreateCell(m).SetCellValue(columnInfo[cName]); 46 m++; 47 } 48 } 49 if (lststring != null) 50 { 51 rowheader.CreateCell(myPro.Count).SetCellValue("导入提示"); 52 } 53 } 54 //如果没有找到可用的属性则结束 55 if (myPro.Count == 0) { return bResult; } 56 int i = 1;//lststring 计数 57 foreach (T obj in objList) 58 { 59 int n = 0; 60 if (sheet != null) 61 { 62 var sheetrow = sheet.CreateRow(i); 63 sheetrow.Height = sheetrow.Height = 20 * 20; 64 foreach (PropertyInfo p in myPro) 65 { 66 dynamic val = p.GetValue(obj, null) ?? ""; 67 string valtype = val.GetType().ToString(); 68 if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -1) 69 { 70 val = Convert.ToDouble(val); 71 } 72 else if (valtype.ToLower().IndexOf("datetime", StringComparison.Ordinal) > -1) 73 { 74 val = val.ToString("yyyy-MM-dd HH:mm:ss"); 75 if (val.Equals("0001-01-01 00:00:00")) 76 { 77 val = ""; 78 } 79 } 80 sheetrow.CreateCell(n).SetCellValue(val); 81 n++; 82 } 83 84 if (lststring != null) 85 { 86 sheetrow.CreateCell(n).SetCellValue(lststring[i - 1]); 87 } 88 } 89 90 Thread.Sleep(10); 91 i++; 92 } 93 } 94 else 95 { 96 if (sheet != null) sheet.CreateRow(0).CreateCell(0).SetCellValue("暂无数据！"); 97 } 98 99 using (MemoryStream ms = new MemoryStream()) 100 { 101 workbook.Write(ms); 102 if (btyBytes != null) 103 { 104 btyBytes = ms.ToArray(); 105 } 106 else 107 { 108 btyBytes = ms.ToArray(); 109 110 HttpResponse rs = System.Web.HttpContext.Current.Response; 111 rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 112 rs.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName,System.Text.Encoding.UTF8)); 113 rs.ContentType = "application/ms-excel"; 114 rs.BinaryWrite(btyBytes); 115 rs.End(); 116 } 117 ms.Flush(); 118 ms.Position = 0; 119 workbook = null; 120 } 121 //输出EXCEL 122 bResult = true; 123 } 124 catch (Exception ex) 125 { 126 LogHelper.Write(ex); 127 } 128 return bResult; 129 }
（2）动态列（不固定列）导入导出结果集
1 /// <summary> 2 /// 同义词导入 3 /// </summary> 4 /// <typeparam name="T">要导出对象的类型</typeparam> 5 /// <param name="objList">一组对象</param> 6 /// <param name="fileName">导出后的文件名</param> 7 /// <param name="columnInfo">列名信息</param>
/// <param name="MaxColNum">最大列数</param> 8 /// <param name="btyBytes"></param> 9 public static bool ExRandomColExcel<T>(List<T> objList, string fileName, ref byte[] btyBytes, Dictionary<string, string> columnInfo = null,
List<string> lststring = null, int MaxColNum = 0) where T : class 10 { 11 bool bResult = false; 12 try 13 { 14 HSSFWorkbook workbook = new HSSFWorkbook(); 15 //在工作薄中建立工作表 16 HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet; 17 sheet.SetColumnWidth(0, 30 * 256); 18 19 if (columnInfo != null && columnInfo.Count == 0) { return false; } 20 //生成EXCEL的HTML 21 Type myType = null; 22 if (objList.Any()) 23 { 24 CellType cellType = new CellType(); 25 26 myType = objList[0].GetType(); 27 //根据反射从传递进来的属性名信息得到要显示的属性 28 List<PropertyInfo> myPro = new List<PropertyInfo>(); 29 PropertyInfo[] properties = myType.GetProperties(); 30 int m = 0; 31 if (columnInfo != null) 32 { 33 var rowheader = sheet.CreateRow(0); 34 rowheader.Height = 20 * 20; 35 if (lststring != null) 36 { 37 rowheader.CreateCell(myPro.Count).SetCellValue("导入提示"); 38 } 39 for (int i = 0; i < MaxColNum; i++) 40 { 41 m++; 42 PropertyInfo p = myType.GetProperty(columnInfo.ElementAtOrDefault(i > columnInfo.Count - 1 ? columnInfo.Count - 1 : i).Key); 43 if (p != null) 44 { 45 myPro.Add(p); 46 rowheader.CreateCell(m).SetCellValue(
columnInfo.ElementAtOrDefault(i > columnInfo.Count - 1 ? columnInfo.Count - 1 : i).Value); 47 } 48 } 49 } 50 //如果没有找到可用的属性则结束 51 if (myPro.Count == 0) { return bResult; } 52 int rowCount = 1;//lststring 计数 53 foreach (T obj in objList) 54 { 55 PropertyInfo[] pro = obj.GetType().GetProperties(); 56 int n = 0; 57 if (sheet != null) 58 { 59 var sheetrow = sheet.CreateRow(rowCount); 60 sheetrow.Height = sheetrow.Height = 20 * 20; 61 //导入结果 62 if (lststring != null) 63 { 64 sheetrow.CreateCell(n).SetCellValue(lststring[rowCount - 1]); 65 } 66 for (int j = 0; j < MaxColNum; j++) 67 { 68 n++; 69 dynamic value = ""; 70 dynamic val = pro[j > pro.Length - 1 ? pro.Length - 1 : j].GetValue(obj, null); 71 string listtype = val.GetType().ToString(); 72 if (listtype.ToLower().IndexOf("list", StringComparison.Ordinal) > -1) 73 { 74 foreach (var item in val) 75 { 76 j++; 77 value = item; 78 string valtype = value.GetType().ToString(); 79 if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -1) 80 { 81 value = Convert.ToDouble(value); 82 } 83 else if (valtype.ToLower().IndexOf("datetime", StringComparison.Ordinal) > -1) 84 { 85 value = value.ToString("yyyy-MM-dd HH:mm:ss"); 86 } 87 sheetrow.CreateCell(n).SetCellValue(value); 88 n++; 89 } 90 if (j > pro.Length - 1) 91 { 92 break; 93 } 94 } 95 else 96 { 97 value = pro[j].GetValue(obj, null); 98 string valtype = value.GetType().ToString(); 99 if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -1) 100 { 101 value = Convert.ToDouble(value); 102 } 103 else if (valtype.ToLower().IndexOf("datetime", StringComparison.Ordinal) > -1) 104 { 105 value = value.ToString("yyyy-MM-dd HH:mm:ss"); 106 } 107 sheetrow.CreateCell(n).SetCellValue(value); 108 } 109 } 110 } 111 Thread.Sleep(10); 112 rowCount++; 113 } 114 } 115 else 116 { 117 if (sheet != null) sheet.CreateRow(0).CreateCell(0).SetCellValue("暂无数据！"); 118 } 119 120 using (MemoryStream ms = new MemoryStream()) 121 { 122 workbook.Write(ms); 123 if (btyBytes != null) 124 { 125 btyBytes = ms.ToArray(); 126 } 127 else 128 { 129 btyBytes = ms.ToArray(); 130 HttpResponse rs = System.Web.HttpContext.Current.Response; 131 rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 132 rs.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); 133 rs.ContentType = "application/ms-excel"; 134 rs.BinaryWrite(btyBytes); 135 rs.End(); 136 } 137 ms.Flush(); 138 ms.Position = 0; 139 workbook = null; 140 } 141 //输出EXCEL 142 bResult = true; 143 } 144 catch (Exception ex) 145 { 146 LogHelper.Write(ex); 147 } 148 return bResult; 149 }
