1 NOPI导出Excel 2 3 /// <summary> 4 /// 导出的方法 Excel样式 5 /// </summary> 6 /// <param name="ds"></param> 7 /// <returns></returns> 8 public static byte[] ExportToExcelMultipleSheet(DataSet ds) 9 { 10 HSSFWorkbook hssfworkbook; 11 hssfworkbook = new HSSFWorkbook(); 12 ISheet sheet1 = hssfworkbook.CreateSheet(ds.Tables[0].TableName); 13 List<NPOI.SS.UserModel.ISheet> sheetList = new List<NPOI.SS.UserModel.ISheet>(); 14 sheetList.Add(sheet1); 15 16 17 for (int i = 1; i < ds.Tables.Count; i++) 18 { 19 ISheet sheet = hssfworkbook.CreateSheet(ds.Tables[i].TableName); 20 sheetList.Add(sheet); 21 } 22 LargeDataExportMultipleSheet(hssfworkbook, sheetList, ds); 23 MemoryStream file = new MemoryStream(); 24 hssfworkbook.Write(file); 25 file.Close(); 26 return file.ToArray(); 27 } 28 29 30 31 32 33 34 /// <summary> 35 /// 数据大于65536时使用 36 /// </summary> 37 /// <param name="dt"></param> 38 /// <returns></returns> 39 /// <summary> 40 /// 数据大于65536时使用 41 /// </summary> 42 /// <param name="dt"></param> 43 /// <returns></returns> 44 public static byte[] ExportToExcel(DataTable dt) 45 { 46 DataColumnCollection str = dt.Columns; 47 if (str.Count == 0) return null; 48 HSSFWorkbook hssfworkbook; 49 hssfworkbook = new HSSFWorkbook(); 50 ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); 51 List<NPOI.SS.UserModel.ISheet> sheetList = new List<NPOI.SS.UserModel.ISheet>(); 52 sheetList.Add(sheet1); 53 54 int rows = dt.Rows.Count + 1; 55 int p = rows % 65535 == 0 ? rows / 65535 : (rows / 65535) + 1; 56 for (int i = 1; i < p; i++) 57 { 58 ISheet sheet = hssfworkbook.CreateSheet("sheet" + (i + 1).ToString()); 59 sheetList.Add(sheet); 60 } 61 LargeDataExport(hssfworkbook, sheetList, dt); 62 MemoryStream file = new MemoryStream(); 63 hssfworkbook.Write(file); 64 file.Close(); 65 return file.ToArray(); 66 } 67 68 private static void LargeDataExport(NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook, List<NPOI.SS.UserModel.ISheet> sheetCollection, DataTable dt) 69 { 70 DataColumnCollection str = dt.Columns; 71 72 for (int i = 0; i < sheetCollection.Count; i++) 73 { 74 ISheet sheet1 = sheetCollection[i]; 75 76 if (i == 0) 77 { 78 IRow headerRow = sheet1.CreateRow(0); 79 for (int m = 0, len = str.Count; m < len; m++) 80 { 81 ICell curCell = headerRow.CreateCell(m); 82 headerRow.Height = 150 * 3; 83 ICellStyle style = hssfworkbook.CreateCellStyle(); 84 style.FillPattern = FillPattern.SolidForeground; 85 style.FillForegroundColor = HSSFColor.Grey25Percent.LightOrange.Index; 86 IFont font = hssfworkbook.CreateFont(); 87 font.FontHeightInPoints = 10; 88 font.Color = HSSFColor.White.Index; //HSSFColor.WHITE.index; 89 style.SetFont(font); 90 curCell.CellStyle = style; 91 curCell.SetCellValue(str[m].ToString()); 92 sheet1.SetColumnWidth(m, 400 * 10); 93 } 94 } 95 96 for (int j = i * 65535; j < (i + 1) * 65535; j++) 97 { 98 if (j > dt.Rows.Count - 1) 99 break; 100 IRow row = sheet1.CreateRow(j - 65535 * i + 1); 101 row.Height = 120 * 3; 102 103 for (int k = 0; k < dt.Columns.Count; k++) 104 { 105 ICell rowCell = row.CreateCell(k); 106 rowCell.SetCellValue(dt.Rows[j][k].ToString()); 107 } 108 } 109 } 110 } 111 112 ===================== 113 调用方法： 114115 116 /// <summary> 117 /// 将数据导出到Excel中 118 /// </summary> 119 /// <returns></returns> 120 public FileResult TXDebtExportExcel() 121 { 122 123 DataTable dt=new DataTable(); 124 125 dt =数据源； 126 127 //创建Excel文件的对象 128 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); 129 System.IO.MemoryStream ms = new System.IO.MemoryStream(); 130 131 NPOIExcelHelper n = new NPOIExcelHelper(); 132 133 byte[] fileArr = null; 134 try 135 { 136 fileArr = NPOIExcelHelper.ExportToExcel(dt); //括号内的参数为数据源 137 } 138 catch (Exception ex) 139 { 140 tempLog.Info(string.Format("用户:{0} 导出 数据 出现异常：{1}", userRealName, ex.Message)); 141 } 142 string fileName = "数据" + System.DateTime.Now.ToString("yyyyMMddhhssmm"); 143 tempLog.Info(string.Format("用户:{0}导出数据成功!", userRealName)); //记录日志 144 return File(fileArr, "application/vnd.ms-excel", fileName + ".xls"); 145 }
NOPI 下载地址 http://npoi.codeplex.com/releases/