数据导出

         我们知道在系统或者网站的后台管理中，有时候为了需要，不如打印，查看详细数据等，需要将列别中的信息导出来，可以导入到excel或者word等里去

          

导出代码：

 

  protected void Button1_Click(object sender, EventArgs e)
    {
        Export("application/ms-excel", "打印报表.excl");
    }
    private void Export(string FileType, string FileName)
    {
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        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)
    {
    }

html：

 

   <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
             ForeColor="#333333" GridLines="None" Visible="false"  Width="80%">
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <Columns>
                <asp:BoundField DataField="sno" HeaderText="编号" />
                <asp:BoundField DataField="jkf" HeaderText="借款方" />
                <asp:BoundField DataField="smoney" HeaderText="金额" />
                <asp:BoundField DataField="cjr" HeaderText="出借人" />
                <asp:BoundField DataField="jkdate" HeaderText="借款日期" />
            </Columns>
            <RowStyle BackColor="#EFF3FB" BorderWidth="1" HorizontalAlign="Center" />
            <EditRowStyle BackColor="#2461BF" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" Font-Size="9pt" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>

 

 

数据都来源于GridView1中，FileType可以根据需要修改类型.....

或者采用下面的方式

 

    protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
    {
        Scoresmr score = new Scoresmr();        //创建Scoresmr对象       
        DataSet ds = score.QueryScore();     //调用QueryScore方法查询成绩并将查询结果放到DataSet数据集中
        DataTable DT = ds.Tables[0];
        //生成将要存放结果的Excel文件的名称
        string NewFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
        //转换为物理路径
        NewFileName = Server.MapPath("Temp/" + NewFileName);
        //根据模板正式生成该Excel文件
        File.Copy(Server.MapPath("../Modulemr.xls"), NewFileName, true);
        //建立指向该Excel文件的数据库连接
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + NewFileName + ";Extended Properties='Excel 8.0;'";
        OleDbConnection Conn = new OleDbConnection(strConn);
        //打开连接，为操作该文件做准备
        Conn.Open();
        OleDbCommand Cmd = new OleDbCommand("", Conn);

        foreach (DataRow DR in DT.Rows)
        {
            string XSqlString = "insert into [Sheet1$]";
            XSqlString += "([用户姓名],[试卷],[成绩],[考试时间]) values(";
            XSqlString += "'" + DR["UserName"] + "',";
            XSqlString += "'" + DR["PaperName"] + "',";
            XSqlString += "'" + DR["Score"] + "',";
            XSqlString += "'" + DR["ExamTime"] + "')";
            Cmd.CommandText = XSqlString;
            Cmd.ExecuteNonQuery();
        }

        //操作结束，关闭连接
        Conn.Close();
        //打开要下载的文件，并把该文件存放在FileStream中
        System.IO.FileStream Reader = System.IO.File.OpenRead(NewFileName);
        //文件传送的剩余字节数：初始值为文件的总大小
        long Length = Reader.Length;

        Response.Buffer = false;
        Response.AddHeader("Connection", "Keep-Alive");
        Response.ContentType = "application/octet-stream";
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("学生成绩.xls"));
        Response.AddHeader("Content-Length", Length.ToString());

        byte[] Buffer = new Byte[10000];        //存放欲发送数据的缓冲区
        int ByteToRead;                                            //每次实际读取的字节数

        while (Length > 0)
        {
            //剩余字节数不为零，继续传送
            if (Response.IsClientConnected)
            {
                //客户端浏览器还打开着，继续传送
                ByteToRead = Reader.Read(Buffer, 0, 10000);                    //往缓冲区读入数据
                Response.OutputStream.Write(Buffer, 0, ByteToRead);    //把缓冲区的数据写入客户端浏览器
                Response.Flush();                                                                        //立即写入客户端
                Length -= ByteToRead;                                                                //剩余字节数减少
            }
            else
            {
                //客户端浏览器已经断开，阻止继续循环
                Length = -1;
            }
        }

        //关闭该文件
        Reader.Close();
        //删除该Excel文件
        File.Delete(NewFileName);
    }