NOPI导出Excel 

  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/

posted @ 2016-05-11 15:48  阿鹏哥  阅读(345)  评论(0)  编辑  收藏  举报