Gridview转换为Excel

18.GridView数据导入Excel/Excel数据读入GridView效果图：解决方案：页面增加一个按钮，单击事件添加如下方法：protected void Button1_Click(object sender, EventArgs e){Export("application/ms-excel", "学生成绩报表.xls");}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;StringWriter tw = new StringWriter();HtmlTextWriter hw = new HtmlTextWriter(tw);GridView1.RenderControl(hw);Response.Write(tw.ToString());Response.End();}//如果没有下面方法会报错类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内public override void VerifyRenderingInServerForm(Control control){}还有由于是文件操作所以要引入名称空间IO和Text后台代码：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.Data.SqlClient;using System.Drawing;using System.IO;using System.Text;public partial class Default7 : System.Web.UI.Page{SqlConnection sqlcon;SqlCommand sqlcom;string strCon = "Data Source=(local);Database=北风贸易;Uid=sa;Pwd=sa";protected void Page_Load(object sender, EventArgs e){if (!IsPostBack){bind();}}public void bind(){string sqlstr = "select top 5 * from 飞狐工作室";sqlcon = new SqlConnection(strCon);SqlDataAdapter myda = new SqlDataAdapter(sqlstr, sqlcon);DataSet myds = new DataSet();sqlcon.Open();myda.Fill(myds, "飞狐工作室");GridView1.DataSource = myds;GridView1.DataKeyNames = new string[] { "身份证号码" };GridView1.DataBind();sqlcon.Close();}protected void Button1_Click(object sender, EventArgs e){Export("application/ms-excel", "学生成绩报表.xls");}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;StringWriter tw = new StringWriter();HtmlTextWriter hw = new HtmlTextWriter(tw);GridView1.RenderControl(hw);Response.Write(tw.ToString());Response.End();}public override void VerifyRenderingInServerForm(Control control){}}前台：<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" Font-Size="12px" ><FooterStyle BackColor="White" ForeColor="#000066" /><Columns><asp:BoundField DataField="身份证号码" HeaderText="编号" ReadOnly="True" /><asp:BoundField DataField="姓名" HeaderText="姓名" /><asp:BoundField DataField="出生日期" HeaderText="邮政编码" /><asp:BoundField DataField="家庭住址" HeaderText="家庭住址" /><asp:BoundField DataField="邮政编码" HeaderText="邮政编码" /><asp:BoundField DataField="起薪" HeaderText="起薪" /></Columns><RowStyle ForeColor="#000066" /><SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" /><PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" CssClass="ms-formlabel DataGridFixedHeader"/><HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" /></asp:GridView><asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导出" />读取Excel数据的代码：这个很简单的private DataSet CreateDataSource(){string strCon;strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("excel.xls") + "; Extended Properties=Excel 8.0;";OleDbConnection olecon = new OleDbConnection(strCon);OleDbDataAdapter myda = new OleDbDataAdapter("Select * FROM [Sheet1$]", strCon);DataSet myds = new DataSet();myda.Fill(myds);return myds;}protected void Button1_Click(object sender, EventArgs e){GridView1.DataSource = CreateDataSource();GridView1.DataBind();}由于时间关系，这个文章先到此。有时间再写，其实还有很多技巧，不过我觉得如果能融汇贯通应该可以举一反三。<asp :BoundField DataField="CreationDate" DataFormatString="{0:M-dd-yyyy}" HeaderText="CreationDate" />主要是由于htmlencode属性默认设置为true,已防止XSS攻击，安全起见而用的，所以，可以有以下两种方法解决1、<asp :GridView ID="GridView1" runat="server"><columns><asp :BoundField DataField="CreationDate" DataFormatString="{0:M-dd-yyyy}" HtmlEncode="false"HeaderText="CreationDate" /></columns></asp>将htmlencode设置为false即可另外的解决方法为，使用模版列<asp :GridView ID="GridView3" runat="server" ><columns><asp :TemplateField HeaderText="CreationDate" ><edititemtemplate><asp :Label ID="Label1" runat="server" Text='<%# Eval("CreationDate", "{0:M-dd-yyyy}") %>'></asp></edititemtemplate><itemtemplate><asp :Label ID="Label1" runat="server" Text='<%# Bind("CreationDate", "{0:M-dd-yyyy}") %>'></asp></itemtemplate></asp></columns></asp>前台代码：<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="身份证号码"DataSourceID="SqlDataSource1" AllowSorting="True" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" Font-Size="12px" OnRowDataBound="GridView1_RowDataBound"><Columns><asp:BoundField DataField="身份证号码" HeaderText="身份证号码" ReadOnly="True" SortExpression="身份证号码" /><asp:BoundField DataField="姓名" HeaderText="姓名" SortExpression="姓名" /><asp:BoundField DataField="邮政编码" HeaderText="邮政编码" SortExpression="邮政编码" /><asp:BoundField DataField="出生日期" HeaderText="出生日期" SortExpression="出生日期" /><asp:BoundField DataField="起薪" HeaderText="起薪" SortExpression="起薪" /></Columns><FooterStyle BackColor="White" ForeColor="#000066" /><RowStyle ForeColor="#000066" /><SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" /><PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" /><HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" /></asp:GridView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:北风贸易ConnectionString1 %>"SelectCommand="Select top 5 [出生日期], [起薪], [身份证号码], [姓名], [家庭住址], [邮政编码] FROM [飞狐工作室]" DataSourceMode="DataReader"></asp:SqlDataSource>附录-常用格式化公式：{0:C} 货币；{0:D4}由0填充的4个字符宽的字段中显示整数；{0:000.0}四舍五入小数点保留第几位有效数字；{0:N2}小数点保留2位有效数字；{0:N2}% 小数点保留2位有效数字加百分号；{0:D}长日期；{0:d}短日期；{0:yy-MM-dd} 例如07-3-25；；{0:yyyy-MM-dd} 例如2007-3-25