.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 = 0; i < ds.Tables[0].Rows.Count; i++)
{
    DataRow dr = dtExcel.NewRow();
    dr["id"] = ds.Tables[0].Rows[i]["id"].ToString();
    dr["状态"] = ds.Tables[0].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(0);
        foreach (DataColumn column in table.Columns)
        {
            var headCell = headerRow.CreateCell(column.Ordinal);
            headCell.SetCellValue(column.Caption);
        }
        int rowIndex = 1;
        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 = 0;
    }
    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();
}