- 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
- {
- 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
- }
导出数据到Excel中
最新推荐文章于 2024-03-18 17:00:35 发布