整理代码,这个是生成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 }