NPOI大数据量多个sheet导出源码（原）
代码如下：
1 #region NPOI大数据量多个sheet导出 2 3 /// <summary> 4 /// 大数据量多个sheet导出 5 /// </summary> 6 /// <typeparam name="T">数据源实体类</typeparam> 7 /// <param name="objList">数据源</param> 8 /// <param name="fileName">文件名称</param> 9 /// <param name="btyBytes">导出数据流</param> 10 /// <param name="columnInfo">显示列对应数据字典</param> 11 /// <param name="listCount">每个sheet包含数据条数</param> 12 /// <returns></returns> 13 public static bool ExportExcelTest<T>(List<T> objList, string fileName, ref byte[] btyBytes, 14 Dictionary<string, string> columnInfo = null,int listCount=10000) 15 { 16 bool bResult = false; 17 //在内存中生成一个Excel文件： 18 HSSFWorkbook book = new HSSFWorkbook(); 19 if (objList != null && objList.Count > 0) 20 { 21 double sheetCount = Math.Ceiling((double)objList.Count / listCount); 22 for (int i = 0; i < sheetCount; i++) 23 { 24 ISheet sheet = null; 25 sheet = book.CreateSheet("sheet" + i); 26 sheet.DefaultRowHeight = 20 * 10; 27 List<T> list = new List<T>(); 28 list = objList.Skip<T>(listCount * i).Take<T>(listCount).ToList(); 29 30 int rowIndex = 0; 31 int StartColIndex = 0; 32 int colIndex = StartColIndex; 33 34 //创建表头样式 35 ICellStyle style = book.CreateCellStyle(); 36 style.Alignment = HorizontalAlignment.Center; 37 style.WrapText = true; 38 IFont font = book.CreateFont(); 39 font.FontHeightInPoints = 16; 40 font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; 41 font.FontName = "简体中文"; 42 style.SetFont(font);//HEAD 样式 43 44 Type myType = null; 45 myType = objList[0].GetType(); 46 //根据反射从传递进来的属性名信息得到要显示的属性 47 List<PropertyInfo> myPro = new List<PropertyInfo>(); 48 PropertyInfo[] properties = myType.GetProperties(); 49 50 #region 定义表头 51 int m = 0; 52 if (columnInfo != null) 53 { 54 var rowheader = sheet.CreateRow(0); 55 rowheader.Height = rowheader.Height = 20 * 20; 56 foreach (string cName in columnInfo.Keys) 57 { 58 PropertyInfo p = myType.GetProperty(cName); 59 if (p != null) 60 { 61 myPro.Add(p); 62 rowheader.CreateCell(m).SetCellValue(columnInfo[cName]); 63 m++; 64 } 65 } 66 } 67 #endregion 68 #region 定义表体并赋值 69 //如果没有找到可用的属性则结束 70 if (myPro.Count == 0) { return bResult; } 71 foreach (T obj in list) 72 { 73 int n = 0; 74 if (sheet != null) 75 { 76 rowIndex++; 77 var sheetrow = sheet.CreateRow(rowIndex); 78 sheetrow.Height = sheetrow.Height = 20 * 20; 79 foreach (PropertyInfo p in myPro) 80 { 81 dynamic val = p.GetValue(obj, null) ?? ""; 82 string valtype = val.GetType().ToString(); 83 if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -1) 84 { 85 val = Convert.ToDouble(val); 86 } 87 else if (valtype.ToLower().IndexOf("datetime", StringComparison.Ordinal) > -1) 88 { 89 val = val.ToString("yyyy-MM-dd HH:mm:ss"); 90 if (val.Equals("0001-01-01 00:00:00")) 91 { 92 val = ""; 93 } 94 } 95 sheetrow.CreateCell(n).SetCellValue(val); 96 n++; 97 } 98 } 99 100 } 101 #endregion 102 } 103 } 104 else 105 { 106 //在工作薄中建立工作表 107 HSSFSheet sheet = book.CreateSheet() as HSSFSheet; 108 sheet.SetColumnWidth(0, 30 * 256); 109 if (sheet != null) sheet.CreateRow(0).CreateCell(0).SetCellValue("暂无数据！"); 110 } 111 112 try 113 { 114 HttpResponse rs = System.Web.HttpContext.Current.Response; 115 rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 116 rs.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); 117 rs.ContentType = "application/ms-excel"; 118 using (MemoryStream ms = new MemoryStream()) 119 { 120 book.Write(ms); 121 rs.BinaryWrite(ms.ToArray()); 122 ms.Flush(); 123 } 124 } 125 catch (SystemException ex) 126 { 127 LogHelper.Write(ex); 128 } 129 catch (ApplicationException ex) 130 { 131 LogHelper.Write(ex); 132 } 133 return bResult; 134 } 135 136 137 #endregion
