.net利用NPOI生成excel文件

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

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

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

GetNewTbl方法的代码如下：

 public static DataTable GetNewTbl(string tableName)
 {
     var dt = new DataTable();
     switch (tableName)
     {
          case "ConfigModel":
                 dt.Columns.Add("CorpCode", typeof(String));
                 dt.Columns.Add("GroupName", typeof(String));
                 dt.Columns.Add("Guid", typeof(String));
                 break;
          case "dtExcelModel":
                 dt.Columns.Add("id", typeof(String));
                 dt.Columns.Add("状态", typeof(String));
                 break;
     }
     return dt;
 }

接着生成要打印的文件

 DataTable dtExcel = Dt.GetNewTbl("dtExcelModel");
 dtExcel.TableName = "Sheet1";
 for (int i = ; i < ds.Tables[].Rows.Count; i++)
 {
     DataRow dr = dtExcel.NewRow();
     dr["id"] = ds.Tables[].Rows[i]["id"].ToString();
     dr["状态"] = ds.Tables[].Rows[i]["Status"].ToString();
 }
 string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
 RenderToExcel(dtExcel, Context, fileName);

RenderToExcel的方法

 public static void RenderToExcel(DataTable table, HttpContext context, string fileName)
 {
     using (MemoryStream ms = RenderToExcel(table))
     {
         RenderToBrowser(ms, context, fileName);
     }
 }

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

 public static MemoryStream RenderToExcel(DataTable table)
 {
     MemoryStream ms = new MemoryStream();
     using (table)
     {
         IWorkbook workbook = new HSSFWorkbook();
         ISheet sheet = workbook.CreateSheet();
         IRow headerRow = sheet.CreateRow();
         foreach (DataColumn column in table.Columns)
         {
             var headCell = headerRow.CreateCell(column.Ordinal);
             headCell.SetCellValue(column.Caption);
         }
         int rowIndex = ;
         foreach (DataRow row in table.Rows)
         {
             IRow dataRow = sheet.CreateRow(rowIndex);
             foreach (DataColumn column in table.Columns)
             {
                 var cellell = dataRow.CreateCell(column.Ordinal);
                 cellell.SetCellValue(row[column].ToString());
             }
             rowIndex++;
         }
         workbook.Write(ms);
         ms.Flush();
         ms.Position = ;
     }
     return ms;
 }

还有写文件流的方法RenderToBrowser：

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