NPOI导出excel web输出及输出到文件

    公司突然用到大数据量的导出问题，之前写的导出excel的方法有点儿shit，经过查资料，找到了NPOI导出excel的高效方法并分享给大家，希望大家共同进步。

     1.//从datereader（也可以使用datatable） 读取数据添加到excel工作簿，然后转换到内存流  

//从datereader（也可以使用datatable） 读取数据添加到excel工作簿，然后转换到内存流

public static MemoryStream RenderToExcel(IDataReader reader)
{
    MemoryStream ms = new MemoryStream();

    using (reader)
    {
        using (IWorkbook workbook = new HSSFWorkbook())
        {
            using (ISheet sheet = workbook.CreateSheet())
            {
                IRow headerRow = sheet.CreateRow(0);
                int cellCount = reader.FieldCount;

                // handling header.
                for (int i = 0; i < cellCount; i++)
                {
                    headerRow.CreateCell(i).SetCellValue(reader.GetName(i));
                }

                // handling value.
                int rowIndex = 1;
                while (reader.Read())
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    for (int i = 0; i < cellCount; i++)
                    {
                        dataRow.CreateCell(i).SetCellValue(reader[i].ToString());
                    }

                    rowIndex++;
                }

                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
            }
        }
    }
    return ms;
}

public static MemoryStream RenderToExcel(IDataReader reader)
{
    MemoryStream ms = new MemoryStream();
 
    using (reader)
    {
        using (IWorkbook workbook = new HSSFWorkbook())
        {
            using (ISheet sheet = workbook.CreateSheet())
            {
                IRow headerRow = sheet.CreateRow(0);
                int cellCount = reader.FieldCount;
 
                // handling header.
                for (int i = 0; i < cellCount; i++)
                {
                    headerRow.CreateCell(i).SetCellValue(reader.GetName(i));
                }
 
                // handling value.
                int rowIndex = 1;
                while (reader.Read())
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
 
                    for (int i = 0; i < cellCount; i++)
                    {
                        dataRow.CreateCell(i).SetCellValue(reader[i].ToString());
                    }
 
                    rowIndex++;
                }
 
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
            }
        }
    }
    return ms;
}

 

//将内存流保存到文件

static void SaveToFile(MemoryStream ms, string fileName)
{
    using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
    {
        byte[] data = ms.ToArray();

        fs.Write(data, 0, data.Length);
        fs.Flush();

        data = null;
    }
}

static void SaveToFile(MemoryStream ms, string fileName)
{
    using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
    {
        byte[] data = ms.ToArray();
 
        fs.Write(data, 0, data.Length);
        fs.Flush();
 
        data = null;
    }
}

//将内存流输出为下载文件

//将内存流输出为下载文件

{
    if (context.Request.Browser.Browser == "IE")
        fileName = HttpUtility.UrlEncode(fileName);
    context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
    context.Response.BinaryWrite(ms.ToArray());
}