.net利用NPOI生成excel文件

整理代码，这个是生成excel文件，用的是HSSF的方式，只能生成65535行，256列的数据，如果要看office07之后的生成，之前的随笔里提过。这个是一个完整的过程。

首先是已经查找好的数据，这里就不写怎么查找了，生成的结果是DataSet ds。

首先是生成一个带需求结果的表格，DataTable dtExcel = GetNewTbl("dtExcelModel");

GetNewTbl方法的代码如下：

 1 public static DataTable GetNewTbl(string tableName)
 2 {
 3     var dt = new DataTable();
 4     switch (tableName)
 5     {
 6          case "ConfigModel":
 7                 dt.Columns.Add("CorpCode", typeof(String));
 8                 dt.Columns.Add("GroupName", typeof(String));
 9                 dt.Columns.Add("Guid", typeof(String));                  
10                 break;
11          case "dtExcelModel":
12                 dt.Columns.Add("id", typeof(String));
13                 dt.Columns.Add("状态", typeof(String));                    
14                 break;
15     }
16     return dt;
17 }

接着生成要打印的文件

 1 DataTable dtExcel = Dt.GetNewTbl("dtExcelModel");
 2 dtExcel.TableName = "Sheet1";
 3 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
 4 {
 5     DataRow dr = dtExcel.NewRow();
 6     dr["id"] = ds.Tables[0].Rows[i]["id"].ToString();
 7     dr["状态"] = ds.Tables[0].Rows[i]["Status"].ToString();
 8 }
 9 string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
10 RenderToExcel(dtExcel, Context, fileName);

RenderToExcel的方法

1 public static void RenderToExcel(DataTable table, HttpContext context, string fileName)
2 {
3     using (MemoryStream ms = RenderToExcel(table))
4     {
5         RenderToBrowser(ms, context, fileName);
6     }
7 }

调用NPOI的方法，代码如下：

 1 public static MemoryStream RenderToExcel(DataTable table)
 2 {
 3     MemoryStream ms = new MemoryStream();
 4     using (table)
 5     {
 6         IWorkbook workbook = new HSSFWorkbook();
 7         ISheet sheet = workbook.CreateSheet();
 8         IRow headerRow = sheet.CreateRow(0);
 9         foreach (DataColumn column in table.Columns)
10         {
11             var headCell = headerRow.CreateCell(column.Ordinal);
12             headCell.SetCellValue(column.Caption);
13         }
14         int rowIndex = 1;
15         foreach (DataRow row in table.Rows)
16         {
17             IRow dataRow = sheet.CreateRow(rowIndex);
18             foreach (DataColumn column in table.Columns)
19             {
20                 var cellell = dataRow.CreateCell(column.Ordinal);
21                 cellell.SetCellValue(row[column].ToString());
22             }
23             rowIndex++;
24         }
25         workbook.Write(ms);
26         ms.Flush();
27         ms.Position = 0;
28     }
29     return ms;
30 }

还有写文件流的方法RenderToBrowser：

 1 public static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
 2 {
 3     context.Response.Clear();
 4     context.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
 5     context.Response.ContentType = "application/octet-stream";
 6     context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
 7     context.Response.BinaryWrite(ms.ToArray());
 8     context.Response.Flush();
 9     context.Response.End();
10 }