1 .建立测试文件

//建立text.aspx文件
//修改text.aspx.cs文件，如下：
//只列出主要的程序：
//声明调用的类别
DataTableToExcel dt = new DataTableToExcel();
//在页面点击导出数据时执行以下函数
    protected void Button1_Click(object sender, EventArgs e)
    {
            str_sql =”select UserName,Name,sex,age from user”;
            DataTableToExcel.DataToExcel(dt.GetCompanyAll(str_sql), Page.Response, Session["Field_Desc"].ToString().Trim());
          /*此处的session[“Field_Desc”]的值必须和选择出来的字段一一对应，此处的值为Session[“Field_Desc”]=”用户名,姓名,性别,年龄”,此处的赋值最好在Global.asax中声明。当然也可以直接赋值。*/
}

2.建立一个类文件 DataTableToExcel.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
/// <summary>
///DataTableToExcel 的摘要说明
/// </summary>
public class DataTableToExcel
{
    private SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString);
    private SqlCommand cmd = null;
public DataTableToExcel()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }
///导出到Excel的主程序
public static void DataToExcel(DataTable dtCompany, HttpResponse pageResponse, string sField_Desc)
    {//使用写数据流的方式更具用灵活性，更容易控制。
        HttpResponse resp;
        resp = pageResponse;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        resp.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
        DataRow[] myRow = dtCompany.Select("");
        string colHeaders = "";
        string ls_item = "";
        string[] array = sField_Desc.Split(',');
        #region 写表头，即标题行
        for (int i = 0; i < array.Length; i++)
        {
            if (i == (array.Length - 1))//最后一列，加\n,换行
            {
                colHeaders += array[i].ToString().Trim() + "\n";
            }
            else
            {
                colHeaders += array[i].ToString().Trim() + "\t";
            }
        }
        resp.Write(colHeaders);
        #endregion
        //把数据写入Excel中
        int jsq = 1;
        foreach (DataRow row in myRow)
        {
            ls_item += jsq++ + "\t";
            //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据     
            for (int i = 1; i < array.Length; i++)
            {
                if (i == (array.Length - 1))//最后一列，判断最后一行的值，根据不同的值写入不//同的数据，并且加 \n &nbsp;
                {
                    int row1 = Convert.ToInt32(row[i]);
                    if (row1 == 1) ls_item += "很好\n";
                    if (row1 == 2) ls_item += "好\n";
                    if (row1 == 3) ls_item += "一般\n";
                    if (row1 == 4) ls_item += "差\n";
                    if (row1 == 5) ls_item += "很差\n";
                    else  ls_item += "\n";
                }
                else
                {
                    ls_item += row[i].ToString().Trim() + "\t";
                }
            }
            resp.Write(ls_item);
            ls_item = "";
        }
        resp.Flush();
        resp.Close();
    }
    #region  获得要导出的数据集
    public DataTable GetCompanyAll(string sql)
    {
        if (sc.State == ConnectionState.Closed)
        {
            sc.Open();
        }
        cmd = new SqlCommand(sql, sc);
        cmd.CommandType = CommandType.Text;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        int rn = da.Fill(ds);
        if (rn > 0)
        {
            return ds.Tables[0];
        }
        return null;
    }
#endregion
}