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

我想实现这个功能
通过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)
    {

    }
}

11 个解决方案

#1

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

#2

                        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();
这里面没有路径吧

#3

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

#4


前台：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");

　　　　}

#5

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

#6

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

#7

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

#8

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

#9

先编写一个类调用数据库内容
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;
    }
}

#10

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

#11

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

#1