Hi，大家好，我是编程小6，很荣幸遇见你，我把这些年在开发过程中遇到的问题或想法写出来，今天说一说
gridview导出excel 数据怎么去掉筛选_excel,希望能够帮助你!!!。

--------客户端取出gridview 所有数据------
<script language="javascript">
    function showview()
    {

        var gvMain = document.getElementById("GridView1");
        var rows = gvMain.rows.length;
        var str = "";
        for(i = 0;i <gvMain.rows.length;i++) 
        { 
               for(var j=0;j<gvMain.rows[0].cells.length;j++)
               {

                    str += gvMain.rows[i].cells[j].innerHTML+" ";
               }
               str += "<br> ";
        }
        //alert(str);
    }
  
    //showview();   
    </script>
---------gridview 导出到excel--------
GridView导出Excel

先把gridview 绑定数据

现在，GridView中已经绑定了数据，接下来的任务就是导出到Excel。下面是button事件中的代码

Response.ClearContent();

Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");

Response.ContentType = "application/excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvUsers.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();
 

并且还需要override一下VerifyRenderingInServerForm方法(这一点非常重要，否则在点击按钮后

会报错)，代码如下:

public override void VerifyRenderingInServerForm(Control control)

{

}
 

点击导出按钮后会弹出对话框，询问您打开或保存。

您是否注意到了以上代码存在一些的问题？是的，ID列开头的0都被截去了。如果你的ID是000345，

导出后就编程了345。这个问题可以通过把css添加到输出流中来解决。为了使ID列正确显示，您需

要将其储存为文本格式。Excel中的文本格式表示为"mso-number-format:"/@"。

protected void Btn_ExportClick(object sender, EventArgs e)

{

string style = @"<style> .text { mso-number-format:/@; } </script> ";

Response.ClearContent();

Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");

Response.ContentType = "application/excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvUsers.RenderControl(htw);

// Style is added dynamically

Response.Write(style);

Response.Write(sw.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

}
 

 在上面的代码中，我通过”style”变量来控制GridView列的样式。并通过Respnose.Write方法将

其添加到输出流中。最后把样式添加到ID列。这一步需要在RowDataBound事件中完成

protected void gvUsers_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

e.Row.Cells[1].Attributes.Add("class", "text");

}

}

Exporting GridView With LinkButtons and Paging: 

如果要导出的GridView中包含LinkButton或者分页(出现分页码时) 则将出现错误：

通过修改页文件可以修正这个问题：EnableEventValidation = "false".

<%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true"

CodeFile="Default.aspx.cs" Inherits="_Default" %>
 

看一下导出的文件

在导出的文件中可以看见linkbutton和dropdownlist控件，虽然dropdownlist控件显示的数据的确

是用户所选的项，但怎么看也不像是一个导出文件，现在应如何移除dropdownlist并显示选择的文

字呢？

 
我写了一个DisableControls函数，用使循环的方法将linkbutton和dropdownlist替换成literal控

件

private void DisableControls(Control gv)

{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)

{

if (gv.Controls[i].GetType() == typeof(LinkButton))

{

l.Text = (gv.Controls[i] as LinkButton).Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(DropDownList))

{

l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

 

if (gv.Controls[i].HasControls())

{

DisableControls(gv.Controls[i]);

}

}

}
 

方法非常简单，只需将linkbuton和dropdownlist替换成literal控件，并将选择项赋值给literal控

件的文本属性。该方法需要在导出前调用

protected void Btn_ExportExcelPaging(object sender, EventArgs e)

{

DisableControls(gvUsers);

Response.ClearContent();

Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");

Response.ContentType = "application/excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvUsers.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

现在的Excel中就只剩下选中文本了
---------datatable 导出到excel-------
string[] str = new string[6];
  str[0] = "ID";
        str[1] = "TypeID";
        str[2] = "玩家账号ID";
  str[3] = "消费godian";
  str[4] = "用途";
  str[5] = "时间";
  string cols = "6";
        if (dt != null)
        {

            if (dt.Rows.Count > 0)
            {

                Common.ExcelObj.DownloadExcel(this.Page, str, dt, cols);
            }
        }

  /// <summary>
  /// 导出excel文件
  /// </summary>
  /// <param name="page"></param>
  /// <param name="tb">数据集</param>
  /// <param name="filename">文件名</param>
  public static void DownloadExcel(System.Web.UI.Page page, string[] sts,

DataTable tb, string cols)
  {

   page.EnableViewState = false;
   string str = DownloadExcel(sts, tb, cols);
   //将要生成的Excel文件
   string path = "";
   path = System.Configuration.ConfigurationSettings.AppSettings

["ExcelPath"];
   path = path + "data.xls";
   //string ExcelFileName = HttpContext.Current.Server.MapPath

(path);
   string ExcelFileName = path;
   //string ExcelFileName = Path.Combine

(page.Request.PhysicalApplicationPath, path+"/guestData.xls");
   if (File.Exists(ExcelFileName))
   {

    File.Delete(ExcelFileName);
   }
   StreamWriter writer = new StreamWriter(ExcelFileName, false);
   writer.WriteLine(str);
   writer.Close();
   DownloadFile(page, "", "data.xls", "");

  }

  /// <summary>
  /// 导入EXCEL文件
  /// </summary>
  /// <param name="page"></param>
  /// <param name="sts">列数数组</param>
  /// <param name="tb">数据集</param>
  /// <param name="cols">列数</param>
  /// <param name="rows">行数</param>
  /// <param name="filename">导入的文件名</param>
  public static string DownloadExcel(string[] sts, DataTable tb, string

cols)
  {

   StringBuilder st = new StringBuilder();
   try
   {

    string rows = (tb.Rows.Count + 1).ToString();
    st.Append("<?xml version=/"1.0/"?>");
    st.Append("<?mso-application progid=/"Excel.Sheet/"?

>");
    st.Append("<Workbook xmlns=/"urn:schemas-microsoft-

com:office:spreadsheet/"");
    st.Append(" xmlns:o=/"urn:schemas-microsoft-

com:office:office/"");
    st.Append(" xmlns:x=/"urn:schemas-microsoft-

com:office:excel/"");
    st.Append(" xmlns:ss=/"urn:schemas-microsoft-

com:office:spreadsheet/"");
    st.Append(" xmlns:html=/"http://www.w3.org/TR/REC-

html40//">");
    st.Append("<DocumentProperties xmlns=/"urn:schemas-

microsoft-com:office:office/">");
    st.Append("<Author>Automated Report Generator

Example</Author>");
    st.Append(string.Format("  <Created>{0}T{1}

Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
    st.Append("<Company>www.szseasons.com</Company>");
    st.Append("<Version>11.6408</Version>");
    st.Append("</DocumentProperties>");
    st.Append(" <ExcelWorkbook xmlns=/"urn:schemas-

microsoft-com:office:excel/">");
    st.Append("  <WindowHeight>8955</WindowHeight>");
    st.Append("  <WindowWidth>11355</WindowWidth>");
    st.Append("  <WindowTopX>480</WindowTopX>");
    st.Append("  <WindowTopY>15</WindowTopY>");
    st.Append(" 

<ProtectStructure>False</ProtectStructure>");
    st.Append("  <ProtectWindows>False</ProtectWindows>");
    st.Append(" </ExcelWorkbook>");
    st.Append(" <Styles>");
    st.Append("  <Style ss:ID=/"Default/"

ss:Name=/"Normal/">");
    st.Append("   <Alignment ss:Vertical=/"Bottom/"/>");
    st.Append("   <Borders/>");
    st.Append("   <Font/>");
    st.Append("   <Interior/>");
    st.Append("   <Protection/>");
    st.Append("  </Style>");
    st.Append("  <Style ss:ID=/"s21/">");
    st.Append("  <Alignment ss:Vertical=/"Bottom/"

ss:WrapText=/"1/"/>");
    st.Append("  </Style>");
    st.Append(" </Styles>");
    st.Append(" <Worksheet ss:Name=/"Sheet1/">");
    st.Append(string.Format("  <Table

ss:ExpandedColumnCount=/"{0}/" ss:ExpandedRowCount=/"{1}/" x:FullColumns=/"1/"",

cols.ToString(), rows.ToString()));
    st.Append("   x:FullRows=/"1/">");
    //生成标题
    st.Append(ShowDataTableTitle(sts).ToString());
    //生成主体
    st.Append(ShowDataTableData(tb, cols).ToString());
    //生成尾部
    st.Append("  </Table>");
    st.Append("  <WorksheetOptions xmlns=/"urn:schemas-

microsoft-com:office:excel/">");
    st.Append("   <Selected/>");
    st.Append("   <Panes>");
    st.Append("    <Pane>");
    st.Append("     <Number>3</Number>");
    st.Append("     <ActiveRow>1</ActiveRow>");
    st.Append("    </Pane>");
    st.Append("   </Panes>");
    st.Append("   <ProtectObjects>False</ProtectObjects>");
    st.Append("  

<ProtectScenarios>False</ProtectScenarios>");
    st.Append("  </WorksheetOptions>");
    st.Append(" </Worksheet>");
    st.Append(" <Worksheet ss:Name=/"Sheet2/">");
    st.Append("  <WorksheetOptions xmlns=/"urn:schemas-

microsoft-com:office:excel/">");
    st.Append("   <ProtectObjects>False</ProtectObjects>");
    st.Append("  

<ProtectScenarios>False</ProtectScenarios>");
    st.Append("  </WorksheetOptions>");
    st.Append(" </Worksheet>");
    st.Append(" <Worksheet ss:Name=/"Sheet3/">");
    st.Append("  <WorksheetOptions xmlns=/"urn:schemas-

microsoft-com:office:excel/">");
    st.Append("   <ProtectObjects>False</ProtectObjects>");
    st.Append("  

<ProtectScenarios>False</ProtectScenarios>");
    st.Append("  </WorksheetOptions>");
    st.Append(" </Worksheet>");
    st.Append("</Workbook>");
   }
   catch (Exception ex)
   {

//    Logger log = new Logger("FunsObj");
//    log.Exp(ex);
//    log.Info(ex);
   }
   return st.ToString();
  }

  public static void DownloadFile(System.Web.UI.Page page, string dir,

string filename, string filenamess)
  {

   string path = "";
   path = System.Configuration.ConfigurationSettings.AppSettings

["ExcelPath"];
   if (dir.Length > 0)
   {

    path = path + "/" + dir + "/";
   }
   path = path + filename;
   page.Response.Write("path:" + path);
   if (!System.IO.File.Exists(path))
   {

    MessageBox.ShowAndRedirect(page, "文件不存在！",

filenamess);
   }
   else
   {

    FileInfo f = new FileInfo(path);
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.AddHeader("Content-

Disposition", "attachment; filename=" + f.Name);
    HttpContext.Current.Response.AddHeader("Content-

Length", f.Length.ToString());
    HttpContext.Current.Response.AddHeader("Content-

Transfer-Encoding", "binary");
    HttpContext.Current.Response.ContentType =

"application/octet-stream";
    HttpContext.Current.Response.WriteFile(f.FullName);
    HttpContext.Current.Response.End();
   }
  }

-------------数据先放在定义的datagrid,再导出excel--------------

protected void btnOutExcel_Click(object sender, EventArgs e)
    {

        System.Web.UI.WebControls.DataGrid dgExport = null;
        // 当前对话
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
        // IO用于导出并返回excel文件
        System.IO.StringWriter strWriter = null;
        System.Web.UI.HtmlTextWriter htmlWriter = null;

        // 设置编码和附件格式
        curContext.Response.ContentType = "application/vnd.ms-excel";
        curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
        curContext.Response.Charset = "";

        // 导出excel文件
        strWriter = new System.IO.StringWriter();
        htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

        dgExport = new System.Web.UI.WebControls.DataGrid();
        string filename = "memberInfo";
        dgExport.DataSource = outDataTable;
        dgExport.AllowPaging = false;
        dgExport.DataBind();

        // 返回客户端
        dgExport.RenderControl(htmlWriter);
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
        curContext.Response.Write(strWriter.ToString());
        curContext.Response.End();
    }

 

今天的分享到此就结束了，感谢您的阅读，如果确实帮到您，您可以动动手指转发给其他人。