通过asp.net把SQL sever中的数据表导出成excel文件

[HL_GT]

我想实现这个功能
通过FileUpload选择保存文件的路径，单机导出按钮会把SQL sever中的数据表导出成一个excel文件到选择的位置
后台代码已经把数据库中的数据读取到datatable中然后在怎么写 Button1_Click事件
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public partial class Default2 : System.Web.UI.Page
{ DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
DropDownList1.Items.Add("教师考勤表");
DropDownList1.Items.Add("本校教师");
DropDownList1.Items.Add("外校教师");
dt=getdt("select * from TABTEACHERS");
}
protected void Button1_Click(object sender, EventArgs e)
{

}
public DataTable getdt(string strsql)
{
string strconn = "data source=; initial catalog=;uid=;password=";
SqlConnection conn = new SqlConnection(strconn);
conn.Open();

DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(strsql,conn);
da.Fill(dt);
conn.Close();
return dt;
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{

}
}

[exception92]

http://blog.csdn.net/duanzi_peng/article/details/17414629 修改下导出路径

[HL_GT]

GridView gv = new GridView(); gv.DataSource = dtError; gv.DataBind(); gv.Attributes.Add("style", "vnd.ms-excel.numberformat:@"); HttpResponse hResponse = this.Response; string fileName1 = "新员工格式验证错误统计" + DateTime.Now.ToString("yyyyMMdd"); hResponse.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName1, System.Text.Encoding.UTF8) + ".xls"); hResponse.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); hResponse.ContentType = "application/ms-excel"; this.EnableViewState = false; StringWriter tw = new StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); gv.RenderControl(hw); hResponse.Write(tw); hResponse.End(); 这里面没有路径吧

[Eiceblue]

参考C# Excel数据导入和导出简单方案，修改下路径workbook.SaveToHttpResponse("Output.xlsx", Response, HttpContentType.Excel2010);

[changjiangzhibin]

前台：window.location.href = "../Temp.aspx";

后台：　try{

　　　　dtSource = Session["MyDataTable"] //假设数据在Session中　　　　

　　　　if (dtSource == null || dtSource.Rows.Count == 0)
            {
                return;
            }
            DataGrid dg = new DataGrid();
            dg.DataSource = dtSource;
            dg.DataBind();

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Charset = "UTF-8";//GB2312
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";//text/csv
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");

            System.IO.StringWriter oSW = new System.IO.StringWriter();
            HtmlTextWriter oHW = new HtmlTextWriter(oSW);
            dg.RenderControl(oHW);
            HttpContext.Current.Response.Write(oSW.ToString());
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.Close();

　　　　}catch(e){

　　　　log.Error(e);
            Response.Redirect("原页面.aspx");

　　　　}

[HL_GT]

你们这个导出excel文件是自己在导出之前先新建一个excel文件还是在单击导出按钮自动生成的excel文件啊，第一个已经实现，想用第二种方法

[qq_35468565]

把绑定语句再走一遍，然后用2楼的方法稍微修改一下就能导出了

[cainiao13579]

网上找点导出的代码资料吧

[yeyongyy123]

是先生成excel，然后下载，选择保存的位置吧，怎么还没开始就浏览。

[qq_37733294]

先编写一个类调用数据库内容 using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using System.Data.SqlClient; using System.Windows.Forms; /// <summary> ///GetData 的摘要说明 /// </summary> public class GetData { public GetData() { // //TODO: 在此处添加构造函数逻辑 // } public static DataTable Getdata() { SqlConnection conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=Sales;Integrated Security=true"); SqlDataAdapter adapter = new SqlDataAdapter("select employee_id,employee_name from employee", conn); DataSet ds = new DataSet(); try { adapter.Fill(ds, "employee"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } return ds.Tables[0]; } } 之后进行导出excel using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using Microsoft.Office.Interop.Excel; using System.Data; using System.Data.SqlClient; using System.Windows.Forms; public partial class _Default : System.Web.UI.Page { //private System.Data.DataTable GetData() { // SqlConnection conn=new SqlConnection(@"Data Source=localhost;Initial Catalog=Sales;Integrated Security=true"); // SqlDataAdapter adapter=new SqlDataAdapter("select employee_id,employee_name from employee",conn); // DataSet ds=new DataSet(); // try{ // adapter.Fill(ds,"employee"); // } // catch(Exception ex) // { // MessageBox.Show(ex.ToString()); // } // return ds.Tables[0]; //} protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); int rowindex = 1; int colindex = 0; excel.Application.Workbooks.Add(true); System.Data.DataTable table = GetData.Getdata(); foreach (DataColumn col in table.Columns) { colindex++; excel.Cells[1, colindex] = col.ColumnName; } foreach (DataRow row in table.Rows) { rowindex++; colindex = 0; foreach (DataColumn col in table.Columns) {colindex++; excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString(); } } excel.Visible = true; } }

[never_morer]

有个叫npoi的库类lz可以参考一下，以前写wpf的时候实现过你描述的这种功能

[Jason_Mao1]

datatable 和 路径都有了 还有什么问题吗？用NPOI 把datatable 转成 EXCEL 然后保存到指定的位置就OK了，希望能帮助到你。