整理代码，这个是生成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();
}