SQL中查询数据导出到EXCEL，文本格式化和中文乱码处理

我的类库或命名空间是 VanClean，你在用的时间自己修改哦！

用到的类 Connection.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace VanClean.Data
{
    /// <summary>
    /// 数据库链接类
    /// </summary>
    public class Connection
    {
        /// <summary>
        /// 定义SqlConnection公共变量,供其他数据库操作使用
        /// </summary>
        public SqlConnection cn;
        
        /// <summary>
        /// 初始化Connection,提供连接字符串
        /// </summary>
        /// <param name="ConnectionString">提供连接字符串</param>
        public Connection(string ConnectionString)
        {
            try
            {
                cn = new SqlConnection(ConnectionString);
            }
            catch (Exception er)
            {

            }
        }
        

        /// <summary>
        /// 执行SQL，返回DataSet,用作GridView等控件的数据源
        /// ds.Tables.Count值为返回表的数据，如果有一个SQL，则返回一个table
        /// 如果有多个，则返回多个table,
        /// ds.Tables[0].Rows.Count 的值为第一个表数据的条数
        /// ds.Tables[0].Rows[0].ItemArray[0]为第一个表，第一条数据的个值
        /// 如果ds.Tables[0].Rows.Count=0,则其他的取值就会报错
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>返回DataSet</returns>
        public DataSet getDataSet(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, cn);
                da.Fill(ds);
            }
            catch (Exception er)
            {

            }
            return ds;
        }        
    }
}

两个方法，导出的数据可文本化 和 中文乱码处理。

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;

namespace VanClean.Data
{
    /// <summary>
    /// 导出数据
    /// </summary>
    public class ExpertControlData
    {
        
        /// <summary>
        /// 导出Excel文件并对其中的数据进行文本格式化操作 方法一
        /// </summary>
        /// <param name="FileName">文件名</param>
        /// <param name="ConnectionString">数据库链接字符串</param>
        /// <param name="sql">SQL语句</param>
        public void ExpertXLSFormatTxt1(string FileName,string ConnectionString,string sql)
        {

            GridView gv = new GridView();
            gv.AllowPaging = false;
            Connection cn = new Connection(ConnectionString);
            gv.DataSource = cn.getDataSet(sql);
            gv.DataBind();

            for (int j = 0; j < gv.Rows.Count; j++)
            {
                gv.Rows[j].Attributes.Add("style", "vnd.ms-excel.numberformat:@");//将所有列都格式化文本类型45
            }

            Random rd = new Random();
            FileName += "[" + DateTime.Now.ToString("yyyyMMdd") + rd.Next(9999).ToString() + "]";
            FileName = HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            Page page = new Page();
            HtmlForm form = new HtmlForm();
            gv.EnableViewState = false;
            page.EnableEventValidation = false;
            page.DesignerInitialize();
            page.Controls.Add(form);
            form.Controls.Add(gv);
            page.RenderControl(htw);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;            
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = Encoding.Default;
            HttpContext.Current.Response.Write(sb.ToString());
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// 导出Excel文件并对其中的数据进行文本格式化操作 方法二
        /// </summary>
        /// <param name="FileName">文件名</param>
        /// <param name="ConnectionString">数据库链接字符串</param>
        /// <param name="sql">SQL语句</param>
        public void ExpertXLSFormatTxt2(string FileName, string ConnectionString, string sql)
        {
            Random rd = new Random();
            FileName += "[" + DateTime.Now.ToString("yyyyMMdd") + rd.Next(9999).ToString() + "]";
            FileName = HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);

            GridView gv = new GridView();
            gv.AllowPaging = false;
            Connection cn = new Connection(ConnectionString);
            gv.DataSource = cn.getDataSet(sql);
            gv.DataBind();

            for (int j = 0; j < gv.Rows.Count; j++)
            {
                gv.Rows[j].Attributes.Add("style", "vnd.ms-excel.numberformat:@");//将所有列都格式化文本类型45
            }

            System.Web.HttpContext obj = System.Web.HttpContext.Current;
            System.IO.StringWriter sw = new System.IO.StringWriter();
            obj.Response.Clear();
            obj.Response.Buffer = true;
            obj.Response.ContentEncoding = System.Text.Encoding.UTF8;
            obj.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
            obj.Response.ContentType = "application/vnd.ms-excel";
            obj.Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
            System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
            gv.RenderControl(htw);
            obj.Response.Write(sw.ToString());
            obj.Response.End();                    
        }
    }
}

调用就很简单了吧，也写一两句吧。

HTML代码 test.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="test.aspx.cs" Inherits="test" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
        </div>
    </form>
</body>
</html>

test.aspx.cs代码

protected void Button1_Click(object sender, EventArgs e)
    {
        VanClean.Data.ExpertControlData ex = new VanClean.Data.ExpertControlData();
        ex.ExpertXLSFormatTxt1("测试数据导出", "YourDataBaseConnectionString", "select * from testtable");
    }