请教在ＷＥＢ里的导入导出功能？

//数据表导出为txt
        private void ExportTXT()
        {
            string str_File="";
            SqlConnection cn = new SqlConnection("server=.; database=test;uid=sa;pwd=");
            string s_temp="select *  from login";
            string SaveFileName="test.txt";
            try
            {
                cn.Open();
                SqlCommand cmtemp=new SqlCommand(s_temp,cn);
                SqlDataReader drtemp=cmtemp.ExecuteReader();
                while(drtemp.Read())
                {
                    str_File=str_File+drtemp["ID"].ToString().Trim()+",";
                    str_File=str_File+drtemp["uid"].ToString().Trim()+",";
                    str_File=str_File+drtemp["pwd"].ToString().Trim()+",";
                    str_File=str_File+"\r\n";
                }
                if(File.Exists("c:\\"+SaveFileName))
                {
                    Response.Write("该文件已经存在!已经被删除,请重新再次导出即可");
                    File.Delete("c:\\"+SaveFileName);
                    return;
                }
                else
                {
                    StreamWriter sw=File.CreateText("c:\\"+SaveFileName);
//                    //创建文本文件
                    sw.WriteLine(str_File);
                    sw.Close();
                    Response.Write("写入文件成功!");
                }
            }
            catch(Exception ex)
            {
                Response.Write("错误"+ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }        //DataGrid导出为Excel
        private void ExportExcel()
        {
            if(this.DataGrid1.Items.Count==0)
            {
                Response.Write("<script>alert('对不起,你没有查询到任何记录,不能导出数据')</script>");
            }
            else
            {
                Response.Clear();
                Response.Buffer= true;
                Response.Charset="GB2312";
                Response.AppendHeader("Content-Disposition","attachment;filename=report.xls");
                Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
                Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
                this.EnableViewState = false;
                System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
                System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
                System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
                this.DataGrid1.RenderControl(oHtmlTextWriter);
                Response.Write(oStringWriter.ToString());
                Response.End();
            }
        }
  OutPutExcel.aspx   <%@ Page language="c#" Codebehind="OutPutExcel.aspx.cs"
           AutoEventWireup="false" Inherits="eMeng.Exam.OutPutExcel" %>
  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
  <HTML>
  <HEAD>
  <title>OutPutExcel</title>
  </HEAD>
  <body>
  <form id="Form1" method="post" runat="server">
  <asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>
  <asp:Button id="Button1" runat="server" Text="输出到Excel"></asp:Button>
  </form>
  </body>
  </HTML>  OutPutExcel.aspx.cs   using System;
  using System.Collections;
  using System.ComponentModel;
  using System.Data;
  using System.Drawing;
  using System.Web;
  using System.Web.SessionState;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  using System.Web.UI.HtmlControls;  namespace eMeng.Exam
      {
  /// <summary>
  /// OutPutExcel 的摘要说明。
  /// </summary>
  public class OutPutExcel : System.Web.UI.Page
  {
   protected System.Web.UI.WebControls.Button Button1;
   protected System.Web.UI.WebControls.DataGrid DataGrid1;   private void Page_Load(object sender, System.EventArgs e)
   {
    // 在此处放置用户代码以初始化页面
    DataGrid1.DataSource=CreateDataSource();
    DataGrid1.DataBind();
   }
   /// <summary>
   /// 创建数据源
   /// </summary>
   /// <returns>DataView</returns>
   ICollection CreateDataSource()
   {    DataTable dt = new DataTable();
    DataRow dr;
    dt.Columns.Add(new DataColumn("身份证号码", typeof(string)));
    dt.Columns.Add(new DataColumn("图书单价",typeof(decimal)));
    dt.Columns.Add(new DataColumn("购买数量",typeof(Int32)));
    dt.Columns.Add(new DataColumn("总价格",typeof(decimal)));
    for (int i = 0; i < 30; i++)
    {
     dr = dt.NewRow();     dr[0] = "123456789123456789";
     dr[1] = 100 * i /3.0;
     dr[2] = i + 5;
     dr[3] = (decimal)dr[1] * (Int32)dr[2];
     dt.Rows.Add(dr);
    }
    DataView dv = new DataView(dt);
    return dv;
   }
   /// <summary>
   /// 输出到Excel
   /// </summary>
   /// <param name="sender"></param>
   /// <param name="e"></param>
   private void Button1_Click(object sender, System.EventArgs e)
   {
    Response.Clear();
    Response.Buffer= true;
    Response.Charset="GB2312";
    Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
    Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
    Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
    this.EnableViewState = false;
    System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
    System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
    System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
    this.DataGrid1.RenderControl(oHtmlTextWriter);
    Response.Write(oStringWriter.ToString());
    Response.End();
   }   #region Web 窗体设计器生成的代码
   override protected void OnInit(EventArgs e)
   {
    //
    // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
    //
    InitializeComponent();
    base.OnInit(e);
   }   /// <summary>
   /// 设计器支持所需的方法 - 不要使用代码编辑器修改
   /// 此方法的内容。
   /// </summary>
   private void InitializeComponent()
   {
    this.Button1.Click += new System.EventHandler(this.Button1_Click);
    this.DataGrid1.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DataGrid1_ItemDataBound);
    this.Load += new System.EventHandler(this.Page_Load);   }
   #endregion   private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
   {
    if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
    {
     e.Item.Cells[0].Attributes.Add("style","vnd.ms-excel.numberformat:@");
     e.Item.Cells[3].Attributes.Add("style","vnd.ms-excel.numberformat:￥#,###.00");
    }
   }
  }
} //出处孟子E章http://dotnet.aspx.cc/
datagrid数据导出到excel文件给客户端下载
方法一：导出到csv文件，存放在服务器端任一路径，然后给客户下载优点：
1、可以进行身份认证后给客户下载，如果放到非web目录就没有对应的url，客户无法随时下载。
2、也是因为生成了文件，所以占用了服务器的空间，但是可以把文件名存放到数据库，再次给客户下载的时候不需要重复生成文件。
3、csv文件是文本文件，逗号隔开字段，回车隔开行，易于数据导入导出。实现方法：
   SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
   SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn);
   DataSet ds=new DataSet();
   da.Fill(ds,"table1");
   DataTable dt=ds.Tables["table1"];
   string name=System.Configuration.ConfigurationSettings.AppSettings["downloadurl"].ToString()+DateTime.Today.ToString("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next(10000).ToString()+".csv";//存放到web.config中downloadurl指定的路径，文件格式为当前日期+4位随机数
   FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write);
   StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
   sw.WriteLine("自动编号,姓名,年龄");
   foreach(DataRow dr in dt.Rows)
   {
    sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]);
   }
   sw.Close();
   Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));
   Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载
   Response.WriteFile(name); // 把文件流发送到客户端
   Response.End(); 方法二：导出到csv文件，不存放到服务器，直接给浏览器输出文件流优点：
1、随时生成，不需要占用资源
2、可以结合身份认证
3、同样利于数据交换实现方法：
SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
   SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn);
   DataSet ds=new DataSet();
   da.Fill(ds,"table1");
   DataTable dt=ds.Tables["table1"];
   StringWriter sw=new StringWriter();
   sw.WriteLine("自动编号,姓名,年龄");
   foreach(DataRow dr in dt.Rows)
   {
    sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]);
   }
   sw.Close();
   Response.AddHeader("Content-Disposition", "attachment; filename=test.csv");
   Response.ContentType = "application/ms-excel";
   Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
   Response.Write(sw);
   Response.End(); 对方法一，二补充一点，如果你希望导出的是xls文件分隔符用\t就可以了，不要用逗号代码修改如下：
sw.WriteLine("自动编号\t姓名\t年龄");
   foreach(DataRow dr in dt.Rows)
   {
    sw.WriteLine(dr["ID"]+"\t"+dr["vName"]+"\t"+dr["iAge"]);
   }
另外，修改输出的文件扩展名为xls即可。方法三：从datagrid导出html代码，生成excel文件，给客户端下载优点：
1、有固定的格式，样子好看（datagrid的样子）局限性：
1、不适合数据交换，里面有html代码，比较乱，没有固定格式
2、datagrid不能有分页、排序等，否则出错实现方法：
Response.Clear();
   Response.Buffer= false;
   Response.Charset="GB2312";
   Response.AppendHeader("Content-Disposition","attachment;filename=test.xls");
   Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");    Response.ContentType = "application/ms-excel";    this.EnableViewState = false;
   System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
   System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
   this.DataGrid1.RenderControl(oHtmlTextWriter);
   Response.Write(oStringWriter.ToString());
   Response.End(); 在这里说明一点：有的网友反映代码出现“没有dr["id"]”之类的错误，这个代码是按照我的数据结构来写的，到时候相关的字段要换成你自己的才是。还有就是如果文件名需要中文的话，这么修改Response.AddHeader("Content-Disposition", "attachment; filename="+System.Web.HttpUtility.UrlEncode("中文",System.Text.Encoding.UTF8)+".xls"); 摘自lovecherry.cnblogs.com/

调试易

请教在ＷＥＢ里的导入导出功能？

解决方案 »