导出数据到Excel

Question

protected void Button1_Click(object sender, EventArgs e)
    {
        Export("application/ms-excel", "book1.xls");　application/ms-excel－－这是说明导出为Excel吗？    }    //将GridView控件中数据以反指定格式输出
    private void Export(string FileType, string FileName)
    {
        Response.Charset = "gb2312";
        Response.ContentEncoding = System.Text.Encoding.UTF7;
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
        Response.ContentType = FileType;
        this.EnableViewState = false;
        System.IO.StringWriter tw = new System.IO.StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        this.GridView1.RenderControl(hw);
        Response.End();
    }    public override void VerifyRenderingInServerForm(Control control)
    {
       // base.VerifyRenderingInServerForm(control);
    }
这是我写的代码，能实出导出，可是没有数据，是一个空的Excel?为什么？

Answer 1

你确定有数据吗 一般应该是编码问题吧
  Response.Charset = "gb2312"; 
        Response.ContentEncoding = System.Text.Encoding.UTF7; 

Answer 2

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:demoConnectionString2 %>"
            SelectCommand="SELECT * FROM [book]"></asp:SqlDataSource>
GridView参定了一个SqlDateSource

Answer 3

 #region 导出导入Excel
    protected void Button3_Click(object sender, EventArgs e)
    {//在页面文件中增加EnableEventValidation="false"
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        //attachment改成online，成为在线打开
        Response.AddHeader("Content-Disposition", "attachment;filename=" + System.DateTime.Now.ToShortDateString() + ".xls");
        //Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.ContentType = "application/ms-excel";
        GridView1.EnableViewState = false;
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        GridView1.AllowPaging = false;
        GridView1.RenderControl(oHtmlTextWriter);
        Response.Write(oStringWriter.ToString());
        Response.End();
        GridView1.AllowPaging = true;
    }    public override void VerifyRenderingInServerForm(Control control)
    {
    }
    #endregion

Answer 4

private void Export(string FileType, string FileName) 
    { 
        Response.Charset = "gb2312"; 
        Response.ContentEncoding = System.Text.Encoding.UTF7; 
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString()); 
        Response.ContentType = FileType; 
        this.EnableViewState = false; 
        System.IO.StringWriter tw = new System.IO.StringWriter(); 
        HtmlTextWriter hw = new HtmlTextWriter(tw); 
        this.GridView1.RenderControl(hw); 
        Response.Write(tw.ToString()); //-----------------******************
        Response.End(); 
    }

Answer 5

    /// <summary>
    /// 将GridView中的数据导入到Excel文件中
    /// </summary>
    /// <param name="page">调用此方法的页面对象</param>
    /// <param name="gv">相关的GridView对象</param>
    /// <param name="FileName">导出的文件名</param>
    /// <param name="error">导出失败时的原因</param>
    /// <returns>成功：true,失败：false</returns>
    public static bool OutPutToExcel(Page page,GridView gv, string FileName,out string error)
    {
        try
        {
            page.Response.Charset = "GB2312";
            //Encoding en = Encoding.GetEncoding("GB2312");
            page.Response.ContentEncoding =  System.Text.Encoding.UTF7;
            page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString() + ".xls");
            page.Response.ContentType = "application/ms-excel";
            page.EnableViewState = false;
            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            gv.RenderControl(hw);
            page.Response.Write(tw.ToString());
            page.Response.End();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

Answer 6

用脚本
//将选择区域的内容导出为EXCEL
function AutomateExcel(tableArea)
{
    var elTable = document.getElementById(tableArea); //outtable 为导出数据所在的表格ID；
    var oRangeRef = document.body.createTextRange(); 
    oRangeRef.moveToElementText( elTable );
    oRangeRef.execCommand( "Copy" );
    try
    {
        var appExcel = new ActiveXObject( "Excel.Application" ); 
    }
    catch(e)
    {
        alert("无法调用Office对象，请确保您的机器已安装了Office并已将本系统的站点名加入到IE的信任站点列表中！");
        return;
    }
    appExcel.Visible = true;
    appExcel.Workbooks.Add().Worksheets.Item(1).Paste();
    appExcel = null;
}

Answer 7

这不是顾名思义么？
送一个方法：        #region 普通的 DataTable To Excel        /// <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, Page Page)
        {
            //TextWriter stringWriter = new StringWriter();
            ////using (TextWriter streamWriter = new StreamWriter("test.txt")) ;
            //HttpResponse resp = new HttpResponse(stringWriter);            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();
        }        #endregion    }