<script runat="server"> Dim gname As String Dim types As StringPrivate Sub ToExcel(ByVal ctl As System.Web.UI.Control) Response.Charset = "utf-8" Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8") Response.AppendHeader("Content-Disposition", "attachment;filename=" & formatdatetime(now(),2) & "_" & HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(gname)) & ".xls") Response.ContentType = "application/ms-excel" ctl.Page.EnableViewState = False Dim tw As System.IO.StringWriter = New System.IO.StringWriter() Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(tw) ctl.RenderControl(hw) Response.Write(tw.ToString()) Response.End() End SubPrivate Function typename(typeid As Integer) As String Dim revalues As String
If types = 1 Then gname = "所有数据" Return gname ElseIf types = 2 Then gname = "当月数据" Return gname End If
sql = "select top 1 name from group_type where id=" & typeid cm = New SqlCommand(sql,conn) dr = cm.ExecuteReader() If dr.Read Then revalues = dr("name") dr.Close Else dr.Close revalues = "未知数据" End If gname = revalues Return revalues End Function </script><% types = Request("types") Dim groupid As String = Request("groupid") Dim insql As StringIf Not IsNumeric(types) Then Response.Write("exit.aspx") If IsNumeric(groupid) Then insql = " and groupid=" & groupid & " " End IfSelect Case types Case 1 sql = "select username,senduser,sendtouser,content,addtime,types,groupid from smslog order by id desc" Case 2 sql = "select username,senduser,sendtouser,content,addtime,types,groupid from smslog where month(addtime)=" & Month(Now()) & " order by id desc" Case 3 sql = "select username,senduser,sendtouser,content,addtime,types,groupid from smslog where month(addtime)=" & Request("a_month") & " and year(addtime)=" & Request("a_year") & " and day(addtime)=" & Request("a_day") & " " & insql & " order by id desc" Case 4 sql = "select username,senduser,sendtouser,content,addtime,types,groupid from smslog where addtime>='" & Request("b_year") & "-" & Request("b_month") & "-" & Request("b_day") & " ' and addtime<='" & Request("c_year") & "-" & Request("c_month") & "-" & Request("c_day") & " 23:59:59' " & insql & " order by id desc" Case Else Response.Write("exit.aspx") End Select ad = New SqlDataAdapter(sql,conn) Try ad.Fill(ds,"execl") Catch ex As Exception Response.Write("<script>alert('未有找到该时间段的数据')</script>" & ex.ToString) Response.End End Try If ds.Tables("execl").Rows.Count = 0 Then Response.Write("<script>alert('未有找到该时间的数据')</script>") Response.End End IfDim oView As New DataView(ds.Tables("execl")) DataGrid1.DataSource = oView DataGrid1.DataBind() call toexcel(datagrid1) %> <form id="Form1" method="post" runat="server"> <asp:datagrid id="DataGrid1" AutoGenerateColumns="False" CellPadding="3" runat="server" nowrap> <columns> <asp:boundcolumn headertext="发送者" datafield="senduser"/> <asp:boundcolumn headertext="接收者" datafield="sendtouser" /> <asp:boundcolumn headertext="内容" datafield="content" /> <asp:boundcolumn headertext="发送时间" datafield="addtime" /> <asp:templatecolumn> <headertemplate>发送类型</headertemplate> <itemtemplate> <%# iif(Container.DataItem("types")=true,"上行","下行") %> </itemtemplate> </asp:templatecolumn> <asp:templatecolumn> <headertemplate>组类别</headertemplate> <itemtemplate> <%# typename(Container.DataItem("groupid")) %> </itemtemplate> </asp:templatecolumn> <asp:boundcolumn headertext="操作者" datafield="username" /> </columns> </asp:datagrid> </form>
然后点击button就把这个页面存为excel文件
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset ="";
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=DocLibrary.xls");
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.GetEncoding("GB2312");
//更改ContentType的值为ms-word即可实现导出到Word
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
ctl.Page.EnableViewState =false;
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
我实现了另存 但是动态的表格却消失了
http://dotnet.aspx.cc/ShowDetail.aspx?id=BF0A54F9-C7C7-4200-BD9A-802AC1F5DE50
{
//写入Excel的方法:
//定义需要参数。
string SourceFile="Data.XLS"; //源文件名称。
string TemplatePath=Server.MapPath("ExcelTemplate"); //存放源文件的文件夹路径。
string DownloadPath=Server.MapPath("ExcelDownload"); //副本的文件夹路径。
//副本的文件名。
string TempFileName = DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".XLS";
object missing = System.Reflection.Missing.Value;
Excel.Application myExcel=new Excel.Application();
//打开新文件
myExcel.Application.Workbooks.Open(TemplatePath+"\\"+SourceFile,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing, missing,missing);
Excel.Workbook myBook=myExcel.Workbooks[1];
Excel.Worksheet curSheet = (Excel.Worksheet)myBook.Sheets[2];
string DownloadFilePath=DownloadPath+"\\"+TempFileName;
int i=0;
while (i<=10)
{
myExcel.Cells[4+i,2]=i.ToString();
myExcel.Cells[4+i,3]=i.ToString();
myExcel.Cells[4+i,4]=i.ToString();
myExcel.Cells[4+i,5]=i.ToString();
myExcel.Cells[4+i,6]=i.ToString();
i++;
}
myBook.Saved=true;
//myBook.SaveAs(DownloadFilePath,missing,"","",false,false,Excel.XlSaveAsAccessMode.xlNoChange,1,false,missing,missing);
myBook.PrintPreview(0);
//myBook.PrintOut(missing,missing,missing,missing,missing,missing,missing,missing);
myBook.Close(false, null,null);
myExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myBook = null;
myExcel = null;
GC.Collect();
//Response.Redirect("ExcelDownload//"+TempFileName); //下载文件
}
你可以dataset 导入 excel参考
http://community.csdn.net/Expert/topic/3077/3077526.xml?temp=.8746912
http://www.dev-club.com/club/bbs/showEssence.asp?id=26350http://dev.csdn.net/Develop/article/18/18623.shtm
http://community.csdn.net/Expert/topic/3112/3112296.xml?temp=.926861
http://dotnet.aspx.cc/ShowDetail.aspx?id=BF0A54F9-C7C7-4200-BD9A-802AC1F5DE50
http://expert.csdn.net/Expert/TopicView1.asp?id=2928057www.foxhis.com/powermjtest/
Dim gname As String
Dim types As StringPrivate Sub ToExcel(ByVal ctl As System.Web.UI.Control)
Response.Charset = "utf-8"
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8")
Response.AppendHeader("Content-Disposition", "attachment;filename=" & formatdatetime(now(),2) & "_" & HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(gname)) & ".xls")
Response.ContentType = "application/ms-excel" ctl.Page.EnableViewState = False
Dim tw As System.IO.StringWriter = New System.IO.StringWriter()
Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(tw) ctl.RenderControl(hw) Response.Write(tw.ToString())
Response.End()
End SubPrivate Function typename(typeid As Integer) As String
Dim revalues As String
If types = 1 Then
gname = "所有数据"
Return gname
ElseIf types = 2 Then
gname = "当月数据"
Return gname
End If
sql = "select top 1 name from group_type where id=" & typeid
cm = New SqlCommand(sql,conn)
dr = cm.ExecuteReader()
If dr.Read Then
revalues = dr("name")
dr.Close
Else
dr.Close
revalues = "未知数据"
End If
gname = revalues
Return revalues
End Function
</script><%
types = Request("types")
Dim groupid As String = Request("groupid")
Dim insql As StringIf Not IsNumeric(types) Then Response.Write("exit.aspx")
If IsNumeric(groupid) Then
insql = " and groupid=" & groupid & " "
End IfSelect Case types
Case 1
sql = "select username,senduser,sendtouser,content,addtime,types,groupid from smslog order by id desc"
Case 2
sql = "select username,senduser,sendtouser,content,addtime,types,groupid from smslog where month(addtime)=" & Month(Now()) & " order by id desc"
Case 3
sql = "select username,senduser,sendtouser,content,addtime,types,groupid from smslog where month(addtime)=" & Request("a_month") & " and year(addtime)=" & Request("a_year") & " and day(addtime)=" & Request("a_day") & " " & insql & " order by id desc"
Case 4
sql = "select username,senduser,sendtouser,content,addtime,types,groupid from smslog where addtime>='" & Request("b_year") & "-" & Request("b_month") & "-" & Request("b_day") & " ' and addtime<='" & Request("c_year") & "-" & Request("c_month") & "-" & Request("c_day") & " 23:59:59' " & insql & " order by id desc"
Case Else
Response.Write("exit.aspx")
End Select
ad = New SqlDataAdapter(sql,conn)
Try
ad.Fill(ds,"execl")
Catch ex As Exception
Response.Write("<script>alert('未有找到该时间段的数据')</script>" & ex.ToString)
Response.End
End Try
If ds.Tables("execl").Rows.Count = 0 Then
Response.Write("<script>alert('未有找到该时间的数据')</script>")
Response.End
End IfDim oView As New DataView(ds.Tables("execl"))
DataGrid1.DataSource = oView
DataGrid1.DataBind()
call toexcel(datagrid1)
%>
<form id="Form1" method="post" runat="server">
<asp:datagrid id="DataGrid1" AutoGenerateColumns="False" CellPadding="3" runat="server" nowrap>
<columns>
<asp:boundcolumn headertext="发送者" datafield="senduser"/>
<asp:boundcolumn headertext="接收者" datafield="sendtouser" />
<asp:boundcolumn headertext="内容" datafield="content" />
<asp:boundcolumn headertext="发送时间" datafield="addtime" />
<asp:templatecolumn>
<headertemplate>发送类型</headertemplate>
<itemtemplate>
<%# iif(Container.DataItem("types")=true,"上行","下行") %>
</itemtemplate>
</asp:templatecolumn>
<asp:templatecolumn>
<headertemplate>组类别</headertemplate>
<itemtemplate>
<%# typename(Container.DataItem("groupid")) %>
</itemtemplate>
</asp:templatecolumn>
<asp:boundcolumn headertext="操作者" datafield="username" />
</columns>
</asp:datagrid>
</form>