使用asp.net将SQL数据导出到Microsoft Excel(使用Visual Studio 2008，c#) [英] Export SQL Data to Microsoft Excel (using Visual Studio 2008, c#) using asp.net
问题描述
我的数据库中有表列表.
如何将包含数据的表列表检索到数据绑定控件和
如何将表数据分别导出到excelsheet.
a)获取数据库中的表列表
b)获取数据
c)将数据导出到excel工作表(定义了2种方法(1)范围方法(2)逐个单元
d.)保存Excel工作表
I have list of tables in my database.
how to retrieve list of tables including data into databound control and
how to export the tables data to excelsheet individually..
a)Gets the list of tables within the database
b)Gets data
c)Exports the data to excel worksheets (there are 2 methods defined (1) range method (2) cell by cell
d.) Saves the excel sheet
<asp:Content ID="Content3" ContentPlaceHolderID="MainContentPanel" runat="Server">
<script language="javascript" type="text/javascript">
function selectOne(rdoId,gridName)
{
var rdo = document.getElementById(rdoId);
var all = document.getElementsByTagName("input");
for(i=0;i< all.length;i++)
{
if(all[i].type=="radio" && all[i].id != rdo.id)
{
var count=all[i].id.indexOf(gridName);
if(count!=-1)
{
all[i].checked=false;
}
}
}
rdo.checked=true;
}
</script>
<asp:Panel ID="panelPostListings" runat="server" class="MainUserContentPanel">
<div>
<fieldset style="background-color: #FEF0C9">
<legend>
<span class="h_txt16_2"><font color="red">Export Data To Excel Sheet</font></span>
</legend>
<div style="width: 300px; float: left; padding-right: 20px;">
<table>
<tr>
<td>
<asp:Label ID="lbldatabase" runat="server" Text="DatabaseName"></asp:Label>
</td>
</tr>
<tr>
<td>
<asp:TextBox ID="txtdbname" runat="server" Width="239px"></asp:TextBox>
Enter DataBase Name
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnsend" runat="server" Text="submit" CausesValidation="false"
Width="73px" onclick="btnsend_Click" />
</td>
</tr>
</table>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true" PageSize="10" OnPageIndexChanging="GridView1_PageIndexChanging">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:RadioButton ID="chktable" runat="server" OnCheckedChanged="Changed" OnClick="javascript:selectOne(this.id,'GridView1');" GroupName="radio" AutoPostBack="true" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="TableName" DataField="TableName" />
</Columns>
</asp:GridView>
<asp:GridView ID="GridView2" runat="server" >
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%--<asp:CheckBox runat="server" ID="chkcolumn" OnCheckedChanged="chkChanged" AutoPostBack="true"/>--%>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:GridView ID="GridView3" runat="server" >
</asp:GridView>
</fieldset>
</div>
</asp:Panel>
</asp:Content>
aspx.cs:-
-----------------
使用系统；
使用System.Collections;
使用System.Configuration;
使用System.Data;
使用System.Linq;
使用System.Web;
使用System.Web.Security;
使用System.Web.UI;
使用System.Web.UI.HtmlControls;
使用System.Web.UI.WebControls;
使用System.Web.UI.WebControls.WebParts;
使用System.Xml.Linq;
使用System.Data.SqlClient;
使用System.IO;
公共局部类DataEntry_Exportdata:System.Web.UI.Page
{
字符串用户ID；
DataSet ds = new DataSet();
字符串selectedtable;
字符串sqlconn = ConfigurationManager.ConnectionStrings ["cnstr"].ConnectionString;
受保护的void Page_Load(对象发送者，EventArgs e)
{
if(Session ["UserID"]！= null&& Session ["UserID"]！=")
{
userid = Convert.ToString(Session ["UserID"]);
}
其他
{
Response.Redirect(〜/Login.aspx?ReturnUrl =" + Request.Url.PathAndQuery.Replace(&"，"^"));
}
//bindgridview();
}
受保护的无效btnsend_Click(对象发送者，EventArgs e)
{
bindgridview();
}
公共无效的bindgridview()
{
SqlConnection cn =新的SqlConnection(sqlconn);
//string dbname ="stanzoo";
//字符串s ="SELECT SysObjects.[Name]作为TableName，SysColumns.[Name]作为ColumnName，SysTypes.[Name]作为DataType，SysColumns.[Length]作为从SysObjects的长度INNER JOIN SysColumns ON SysObjects.[Id] = SysColumns.[Id]内联SysTypes on SysTypes.[xtype] = SysColumns.[xtype] WHERE SysObjects.[type] =``U''ORDER BY SysObjects.[名称];
字符串s =选择SysObjects.[名称]作为TableName，从SysObjects中获取SysObjects.[type] =``U" ORDER BY SysObjects.[名称]";
SqlDataAdapter da =新的SqlDataAdapter(s，cn);
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
受保护的void GridView1_PageIndexChanging(对象发送者，GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
bindgridview();
}
受保护的void已更改(对象发送者，EventArgs e)
{
for(int i = 0; i< GridView1.Rows.Count; i ++)
{
RadioButton chkb =(RadioButton)GridView1.Rows [i] .Cells [0] .FindControl("chktable");
如果(chkb.Checked)
{
selectedtable = GridView1.Rows [i] .Cells [1] .Text.ToString();
//Application ["a"] = selectedtable;
SqlConnection cn =新的SqlConnection(sqlconn);
//string dbname ="stanzoo";
字符串s ="select * from" + selectedtable +";
//string s ="SELECT SysColumns.[Name] as ColumnName from SysObjects INNER JOIN SysColumns on SysObjects.[Id] = SysColumns.[Id] WHERE SysObjects.[type] =``U"和SysObjects.[Name] = ""+ selectedtable +"'';
//string s ="SELECT SysObjects.[Name] as TableName from SysObjects WHERE SysObjects.[type] =``U" ORDER BY SysObjects.[Name]";
SqlDataAdapter da =新的SqlDataAdapter(s，cn);
da.Fill(ds);
GridView2.DataSource = ds;
GridView2.DataBind();
}
}
}
受保护的void chkChanged(对象发送者，EventArgs e)
{
for(int i = 0; i&GridView2.Rows.Count; i ++)
{
CheckBox chkb =(CheckBox)GridView2.Rows [i] .Cells [0] .FindControl("chkcolumn");
如果(chkb.Checked)
{
字符串selectedcolumn = GridView2.Rows [i] .Cells [1] .Text.ToString();
字符串selectedtable1 = Application ["a"].ToString();
SqlConnection cn =新的SqlConnection(sqlconn);
//string dbname ="stanzoo";
//字符串s ="SELECT SysColumns.[Name] as ColumnName from SysObjects INNER JOIN SysColumns on SysObjects.[Id] = SysColumns.[Id] WHERE SysObjects.[type] =``U"和SysObjects.[Name] = ""+ selectedtable +"'';
//string s ="SELECT SysObjects.[Name] as TableName from SysObjects WHERE SysObjects.[type] =``U" ORDER BY SysObjects.[Name]";
字符串s ="select" + selectedcolumn +"from" + selectedtable1 +";
SqlDataAdapter da =新的SqlDataAdapter(s，cn);
da.Fill(ds);
GridView3.DataSource = ds;
GridView3.DataBind();
}
}
}
}
使用上面的代码会获取表和数据..但是如何不能将其转换为Excel工作表..plz给出了asuggetstion
aspx.cs:--
-----------------
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;
public partial class DataEntry_Exportdata : System.Web.UI.Page
{
string userid;
DataSet ds = new DataSet();
string selectedtable;
string sqlconn = ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] != null && Session["UserID"] != "")
{
userid = Convert.ToString(Session["UserID"]);
}
else
{
Response.Redirect("~/Login.aspx?ReturnUrl=" + Request.Url.PathAndQuery.Replace("&", "^"));
}
//bindgridview();
}
protected void btnsend_Click(object sender, EventArgs e)
{
bindgridview();
}
public void bindgridview()
{
SqlConnection cn = new SqlConnection(sqlconn);
//string dbname="stanzoo";
// string s = " SELECT SysObjects.[Name] as TableName,SysColumns.[Name] as ColumnName,SysTypes.[Name] As DataType,SysColumns.[Length] As Length FROM SysObjects INNER JOIN SysColumns ON SysObjects.[Id] = SysColumns.[Id] INNER JOIN SysTypes ON SysTypes.[xtype] = SysColumns.[xtype] WHERE SysObjects.[type] = ''U'' ORDER BY SysObjects.[Name] ";
string s = " SELECT SysObjects.[Name] as TableName FROM SysObjects WHERE SysObjects.[type] = ''U'' ORDER BY SysObjects.[Name] ";
SqlDataAdapter da = new SqlDataAdapter(s, cn);
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
bindgridview();
}
protected void Changed(object sender, EventArgs e)
{
for (int i = 0; i < GridView1.Rows.Count; i++)
{
RadioButton chkb = (RadioButton)GridView1.Rows[i].Cells[0].FindControl("chktable");
if (chkb.Checked)
{
selectedtable = GridView1.Rows[i].Cells[1].Text.ToString();
//Application["a"] = selectedtable;
SqlConnection cn = new SqlConnection(sqlconn);
//string dbname="stanzoo";
string s = "select * from " + selectedtable + " ";
//string s = " SELECT SysColumns.[Name] as ColumnName FROM SysObjects INNER JOIN SysColumns ON SysObjects.[Id] = SysColumns.[Id] WHERE SysObjects.[type] = ''U'' and SysObjects.[Name]=''" + selectedtable + "''";
//string s = " SELECT SysObjects.[Name] as TableName FROM SysObjects WHERE SysObjects.[type] = ''U'' ORDER BY SysObjects.[Name] ";
SqlDataAdapter da = new SqlDataAdapter(s, cn);
da.Fill(ds);
GridView2.DataSource = ds;
GridView2.DataBind();
}
}
}
protected void chkChanged(object sender, EventArgs e)
{
for (int i = 0; i < GridView2.Rows.Count; i++)
{
CheckBox chkb = (CheckBox)GridView2.Rows[i].Cells[0].FindControl("chkcolumn");
if (chkb.Checked)
{
string selectedcolumn = GridView2.Rows[i].Cells[1].Text.ToString();
string selectedtable1 = Application["a"].ToString();
SqlConnection cn = new SqlConnection(sqlconn);
//string dbname="stanzoo";
// string s = " SELECT SysColumns.[Name] as ColumnName FROM SysObjects INNER JOIN SysColumns ON SysObjects.[Id] = SysColumns.[Id] WHERE SysObjects.[type] = ''U'' and SysObjects.[Name]=''" + selectedtable + "''";
//string s = " SELECT SysObjects.[Name] as TableName FROM SysObjects WHERE SysObjects.[type] = ''U'' ORDER BY SysObjects.[Name] ";
string s = "select " + selectedcolumn + " from " + selectedtable1 + " ";
SqlDataAdapter da = new SqlDataAdapter(s, cn);
da.Fill(ds);
GridView3.DataSource = ds;
GridView3.DataBind();
}
}
}
}
using the above code iam getting tables and data..but how cant tehse dat convetrt into excel sheet..plz give asuggetstion
推荐答案
好，在不使用/的情况下请求代码在这里不鼓励付出努力.
这是询问者的期望:
1. 先尝试您要做什么！
2.制定看起来像问题/无法解决的问题.
试试看，告诉他们所面临的具体问题.
Well, asking for code without making/showing effort is not encouraged here.
Here is what is expected by enquirers:
1. TRY first what you want to do!
2. Formulate what was done by you that looks like an issue/not working.
Try them and tell about specific issues faced.
这篇关于使用asp.net将SQL数据导出到Microsoft Excel(使用Visual Studio 2008，c#)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
