实现信息在excel中输出

方法1:实现html输出到excel的方法
     public static void RunHtmlToExcel(System.Data.DataTable dt, string ExcelHeaderName, string ExcelFieldName, string ExcelName, System.Web.UI.Page resp)
    {
        int excelColumnCount = ExcelHeaderName.Split(',').Length;
        string[] arrayExcelHeaderName = ExcelHeaderName.Split(',');
        System.Text.StringBuilder html = new System.Text.StringBuilder();
        html.AppendLine("<table width=/"750/" border=/"1/" style=/"font-size:12px/">");
      
        html.AppendLine("<tr><td colspan=/"" + excelColumnCount + "/" style=/"font-size:25px/" align=/"center/"><strong>" + ExcelName + "</strong></td></tr>");
        html.AppendLine("<tr align=/"center/">");
        for (int i = 0; i < excelColumnCount; i++)
        {
            html.AppendLine("<td >" + arrayExcelHeaderName[i] + "</td>");
        }
        html.AppendLine("</tr>");

        int dtRowCount = dt.Rows.Count;
        string[] columnName = ExcelFieldName.Split(',');
        for (int j = 0; j < dtRowCount; j++)
        {
            html.Append("<tr align=/"center/">");
            for (int n = 0; n < excelColumnCount; n++)
            {
                html.AppendLine("<td >" + dt.Rows[j][columnName[n]] + "</td>");
            }
            html.AppendLine("</tr>");

        }
        html.AppendLine("</table>");

 

        //Http输出流对象
        System.Web.HttpResponse httpResponse = resp.Response; //this.Page.Response;
        //resp.ClientScript.RegisterClientScriptBlock(resp.GetType(), "getest", "<script type='text/javascript' language='javascript'>HidDivLoading();</script>");
        httpResponse.Clear();
        httpResponse.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
        resp.Response.AppendHeader("content-disposition", "attachment;filename=/"" + HttpUtility.UrlEncode("" + ExcelName + "[" + DateTime.Now.ToString("yyyy-MM-dd") + "]", System.Text.Encoding.UTF8) + ".xls/"");
        httpResponse.Write(html.ToString());
        httpResponse.End();
    }

 

    protected void Button3_Click(object sender, EventArgs e)
    {
       
       
       string connectionString= SQLHelper.connectionString;
       OleDbConnection mconnection = new OleDbConnection(connectionString);
       mconnection.Open();
       string sql = "select * from view_log";
       OleDbCommand myCommand = new OleDbCommand(sql, mconnection);
       OleDbDataAdapter DA = new OleDbDataAdapter();
       DA.SelectCommand = myCommand;
       mconnection.Close();
       DataSet ds = new DataSet();
       DA.Fill(ds, "view_log");
       System.Data.DataTable dt = new System.Data.DataTable();
       dt = ds.Tables["view_log"];

       //WorkLog w1 = new WorkLog();
       //System.Data.DataTable dt1 = w1.GetTable(string.Empty);
       RunHtmlToExcel(dt, "用户名,标题,时间,内容", "userName,logTitle,AddDate,LogContent", "日志汇总", this);
    }

 

 

方法二:Gridview输出到excel(自已后台构建一个Gridview)

    public void getExcel(string strWhere1)
    {
        /***********************/

        Response.Clear();
        Response.BufferOutput = true;
        //设定输出的字符集

        Response.Charset = "UTF-8";
        Response.ContentEncoding = System.Text.Encoding.UTF8;

        //假定导出的文件名为FileName.xls
        Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");

        //设置导出文件的格式
        Response.ContentType = "application/ms-excel";

        //关闭ViewState
        EnableViewState = false;

 

        System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("ZH-CN", true);
        System.IO.StringWriter stringWriter = new System.IO.StringWriter(cultureInfo);
        System.Web.UI.HtmlTextWriter textWriter = new System.Web.UI.HtmlTextWriter(stringWriter);

 

   

        backStrWhere();//这个函数据返回strWhere1

        DataSet ds1 = new LogCategory().GetList1(strWhere1);
        grid1.DataSource = ds1;
        grid1.DataBind();

        grid1.RenderControl(textWriter);
        ////把HTML写回浏览器
        Response.Write(stringWriter.ToString());
        Response.End();
    }

 

方法二补充(把页面上的GridView输出到Excel)

 #region aa
        //Response.Clear();
        //Response.BufferOutput = true;
        ////设定输出的字符集

        //Response.Charset = "UTF-8";
        //Response.ContentEncoding = System.Text.Encoding.UTF8;

        ////假定导出的文件名为FileName.xls
        //Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
        //Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");

        ////Response.ContentType = "application/vnd.xls";

        ////设置导出文件的格式
        //Response.ContentType = "application/ms-excel";

        ////关闭ViewState
        //EnableViewState = false;

 

        //System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("ZH-CN", true);
        //System.IO.StringWriter stringWriter = new System.IO.StringWriter(cultureInfo);
        //System.Web.UI.HtmlTextWriter textWriter = new System.Web.UI.HtmlTextWriter(stringWriter);

        ////当导出时取消分页(处理分页问题),设定哪些字段是你需要的就让其显示
      
        //gridLogList.AllowPaging = false;

        //gridLogList.Columns[2].Visible = false;
        //gridLogList.Columns[4].Visible = false;
        //gridLogList.Columns[5].Visible = false;
        ////gridLogList.Columns[6].Visible = true;
        //BindData();

        //gridLogList.RenderControl(textWriter);
        ////把HTML写回浏览器
        //Response.Write(stringWriter.ToString());
        //Response.End();

        ////导出完成后继续分页
       
        //gridLogList.AllowPaging = true;
 
       
        ////gridLogList.Columns[2].Visible = true;
        ////gridLogList.Columns[4].Visible = true;
        ////gridLogList.Columns[5].Visible = true;
        ////gridLogList.Columns[6].Visible = false;
        //BindData();

 

方法三

/// <summary>   
        /// DataTable To Excel   
        /// </summary>   
        /// <param name="dt">DataTable Name</param>   
        /// <param name="typeid">1，Excel 2，XML</param>   
        /// <param name="FileName">文件名</param>   
        public void CreateExcel(System.Data.DataTable dt, string typeid, string FileName)  
        {  
            HttpResponse resp;  
            resp = Page.Response;  
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");  
            resp.ContentType = "application/ms-excel";  
            resp.AddHeader("Content-Disposition",  
            "attachment; filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");  
            this.EnableViewState = false;  
            string colHeaders = "", Is_item = "";  
            int i = 0;  
            //定义表对象与行对象，同时使用DataSet对其值进行初始化   
            DataRow[] myRow = dt.Select("");  
            //typeid=="1"时导出为Excel格式文件;typeid=="2"时导出为XML文件   
            if (typeid == "1")  
            {  
                //取得数据表各列标题，标题之间以/t分割，最后一个列标题后加回车符   
                for (i = 0; i < dt.Columns.Count; i++)  
                {  
                    colHeaders += dt.Columns[i].Caption.ToString() + "/t";  
                }  
                colHeaders += "/n";  
                resp.Write(colHeaders);  
                //逐行处理数据   
                foreach (DataRow row in myRow)  
                {  
                    //在当前行中，逐列取得数据，数据之间以/t分割，结束时加回车符/n   
                    for (i = 0; i < dt.Columns.Count; i++)  
                    {  
                        Is_item += row[i].ToString() + "/t";  
                    }  
                    Is_item += "/n";  
                    resp.Write(Is_item);  
                    Is_item = "";  
                }  
            }  
            else 
            {  
                if (typeid == "2")  
                {  
                    //从DataSet中直接导出XML数据并且写到HTTP输出流中   
                    resp.Write(dt.DataSet.GetXml());  
                }  
            }  
            //写缓冲区中的数据到HTTP头文件中   
            resp.End();  
        }

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zhengmingli/archive/2010/12/07/6059795.aspx

 

更全面一篇http://www.cnblogs.com/xiaotao823/archive/2008/09/26/1299364.html

方法一和方法二本人实践过