三步将Asp.Net页面输出到EXCEL里

下面是“三步将Asp.Net页面输出到Excel里”的完整攻略，包含两个示例。

1. 引用Excel操作库

在输出Asp.Net页面到Excel前，需要先引用Excel操作库。常用的Excel操作库包括：

• NPOI（Nuget包名：NPOI）
• EPPlus（Nuget包名：EPPlus）

这里以NPOI为例。我们可以通过Nuget引入NPOI：

Install-Package NPOI

或者在项目的Packages.config文件中手动添加：

<packages>
  <package id="NPOI" version="2.5.3" targetFramework="net461" />
</packages>

2. 编写输出代码

输出代码的核心是将Asp.Net页面转换成Excel数据格式。下面是一个示例代码，用于将GridView中的数据输出到Excel：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ExcelSample : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGrid();
        }
    }

    private void BindGrid()
    {
        var data = new List<Customer>()
        {
            new Customer {Name = "Tom", Age = 18},
            new Customer {Name = "Jerry", Age = 20},
            new Customer {Name = "Lucy", Age = 22},
        };
        gvData.DataSource = data;
        gvData.DataBind();
    }

    protected void btnExport_Click(object sender, EventArgs e)
    {
        var wb = new HSSFWorkbook();
        var sheet = wb.CreateSheet("Sheet1");

        var row = sheet.CreateRow(0);
        for (int i = 0; i < gvData.HeaderRow.Cells.Count; i++)
        {
            row.CreateCell(i).SetCellValue(gvData.HeaderRow.Cells[i].Text);
        }

        for (int i = 0; i < gvData.Rows.Count; i++)
        {
            row = sheet.CreateRow(i + 1);
            for (int j = 0; j < gvData.Rows[i].Cells.Count; j++)
            {
                row.CreateCell(j).SetCellValue(gvData.Rows[i].Cells[j].Text);
            }
        }

        var fileName = "Export" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
        var ms = new MemoryStream();
        wb.Write(ms);
        Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
        Response.ContentType = "application/vnd.ms-excel";
        Response.BinaryWrite(ms.ToArray());
        wb = null;
        ms.Close();
        ms = null;
        Response.End();
    }
}

public class Customer
{
    public string Name { get; set; }
    public int Age { get; set; }
}

这个例子中，我们首先在页面加载时绑定GridView的数据，然后通过按钮的点击事件将数据输出到Excel文件。输出的过程中，我们创建一个Workbook对象，再创建一个Sheet对象，然后逐行逐列地将数据填入Sheet中，并最终将Workbook对象写入内存流中，并输出给客户端。在Response对象中设置Content-Disposition、Content-Type等属性，后在BinaryWrite方法中输出二进制数据。

3. 测试输出结果

当你运行代码，点击"Export"按钮时，Excel文件会被下载到本地。你可以双击打开它，看看输出结果是否与你预期一致。

下面是一个输出进阶版的示例代码，用于将自定义的数据集合输出到Excel：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ExcelSample : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }

    private void BindData()
    {
        var data = new List<Student>()
        {
            new Student {Name = "Tom", Age = 18, Gender = "M", Score = 85},
            new Student {Name = "Jerry", Age = 20, Gender = "M", Score = 90},
            new Student {Name = "Lucy", Age = 22, Gender = "F", Score = 88},
        };
        gvData.DataSource = data;
        gvData.DataBind();
    }

    protected void btnExport_Click(object sender, EventArgs e)
    {
        var fileName = "Export" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
        var ms = ExportDataToExcel(PrepareData(), fileName);
        Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
        Response.ContentType = "application/vnd.ms-excel";
        Response.BinaryWrite(ms.ToArray());
        ms.Close();
        ms = null;
        Response.End();
    }

    private MemoryStream ExportDataToExcel(IEnumerable<Student> data, string fileName)
    {
        var wb = new HSSFWorkbook();
        var sheet = wb.CreateSheet("Sheet1");

        var rowIndex = 0;

        // header
        var row = sheet.CreateRow(rowIndex++);
        row.CreateCell(0).SetCellValue("Name");
        row.CreateCell(1).SetCellValue("Age");
        row.CreateCell(2).SetCellValue("Gender");
        row.CreateCell(3).SetCellValue("Score");

        // data
        foreach (var item in data)
        {
            row = sheet.CreateRow(rowIndex++);
            row.CreateCell(0).SetCellValue(item.Name);
            row.CreateCell(1).SetCellValue(item.Age);
            row.CreateCell(2).SetCellValue(item.Gender);
            row.CreateCell(3).SetCellValue(item.Score);
        }

        var ms = new MemoryStream();
        wb.Write(ms);
        wb = null;
        ms.Seek(0, SeekOrigin.Begin);
        return ms;
    }

    private IEnumerable<Student> PrepareData()
    {
        var data = new List<Student>()
        {
            new Student {Name = "Tom", Age = 18, Gender = "M", Score = 85},
            new Student {Name = "Jerry", Age = 20, Gender = "M", Score = 90},
            new Student {Name = "Lucy", Age = 22, Gender = "F", Score = 88},
            new Student {Name = "Amy", Age = 19, Gender = "F", Score = 92},
            new Student {Name = "Bob", Age = 21, Gender = "M", Score = 84},
            new Student {Name = "John", Age = 20, Gender = "M", Score = 85},
            new Student {Name = "Lily", Age = 22, Gender = "F", Score = 90},
        };

        // apply filter
        if (!string.IsNullOrEmpty(txtNameFilter.Text))
        {
            data = data.Where(s => s.Name.IndexOf(txtNameFilter.Text.Trim(), StringComparison.OrdinalIgnoreCase) >= 0).ToList();
        }

        return data;
    }
}

public class Student
{
    public string Name { get; set; }
    public int Age { get; set; }
    public string Gender { get; set; }
    public int Score { get; set; }
}

这个例子中，我们精简了页面结构，仅保留了一个GridView和一个输出按钮。数据绑定和输出的核心代码分别封装在了BindData、PrepareData、ExportDataToExcel三个方法中。PrepareData用于准备数据，ExportDataToExcel用于将数据导出到Excel文件中。这里我们加入了一个简单的查询功能，可以根据姓名来筛选学生。

这里举的是两个例子，只是涵盖了Excel导出的两个方面，但实际使用中，需要根据具体情况进行探索、迭代和优化。