62,047
社区成员
发帖
与我相关
我的任务
分享如何将网页中的表单数据下载到客户端的Excel
我用VB.NET写了一个网页。
网页中有：
1，几个文本框用来输入查询条件；
2，有一个GridView控件输出查询结果；
3，有两个按钮，一个“运行”，一个“下载到Excel”。
设想：
1，按“运行”，将结果输出到GridView控件，这个已实现；
2，按“下载到Excel”，将结果输出到客户端的Excel文档。这个有问题，结果实际输出在服务器端的一个打开的Excel文档里。
网页中有：
1，几个文本框用来输入查询条件；
2，有一个GridView控件输出查询结果；
3，有两个按钮，一个“运行”，一个“下载到Excel”。
设想：
1，按“运行”，将结果输出到GridView控件，这个已实现；
2，按“下载到Excel”，将结果输出到客户端的Excel文档。这个有问题，结果实际输出在服务器端的一个打开的Excel文档里。
...全文
请发表友善的回复…
发表回复
greenhong 2016-03-08
- 打赏
- 举报
感谢各位用心回帖的同学
结贴，给分
等有空再仔细研究其他同学的方法
greenhong 2016-03-07
- 打赏
- 举报
使用了 torontosky 的这个方法
谢谢 torontosky
有点担心，只能存为.xls，存成.xlsx则打不开，一旦记录数超过65535，不知道会不会有问题
greenhong 2016-03-04
- 打赏
- 举报
谢谢qbilbo
代码是百度来的：）
保存文件是保存在客户端还是服务器端啊？
能给出这两句的写法吗？
刚找了一段网上C#写的“C#在客户端和服务端操作Excel文件实现代码”，我给改成了VB，没有出错，但是客户端和服务器端都没有结果。
Sub dlDataTableToExcel(ByVal MyPage As System.Web.UI.Page, ByVal sSQL As String, ByVal sHeader As String)
cnn.Open()
Dim cmd As New SqlCommand(sSQL, cnn)
dr = cmd.ExecuteReader
dt.Load(dr)
If IsDBNull(dt) Then
Else
'服务端创建StringBuilder对象
Dim sb As New System.Text.StringBuilder
Try
'指定客户端执行语言
sb.Append("<Script Language=VBScript>")
sb.Append("<!--/r/n")
sb.Append("dim xls/r/n")
'创建Excel application对象
sb.Append("Set xls=CreateObject(/'Excel.Application/')/r/n")
'打开Excel文件
sb.Append("xls.WorkBooks.Open(C://'" & sHeader & "'.xls)/r/n")
'选定欲操作的Excel表
sb.Append("xls.Sheets(1).Select/r/n")
'将所得到的表的列名,赋值给单元格
Dim wRow As Data.DataRow
Dim wCol As Data.DataColumn
Dim wRowIndex, wColIndex As Integer
wRowIndex = 1
wColIndex = 1
sb.Append("xls.Sheets(1).Cells(1, wColIndex)= '序号'") '数据集之外增加的第一列：序号
For Each wCol In dt.Columns
wColIndex = wColIndex + 1
sb.Append("xls.Sheets(1).Cells(1, wColIndex)= wCol.ColumnName")
Next
'得到的表所有行,赋值给单元格
For Each wRow In dt.Rows
wRowIndex = wRowIndex + 1
wColIndex = 1
sb.Append("xls.Sheets(1).Cells(wRowIndex, wColIndex) = wRowIndex - 1") '设置序号
For Each wCol In dt.Columns
wColIndex = wColIndex + 1
sb.Append("xls.Sheets(1).Cells(wRowIndex, wColIndex) = wRow(wCol.ColumnName)")
Next
Next
'设置标题（第一行）
sb.Append("xls.Sheets(1).Range(.Cells(1, 1), .Cells(1, wColIndex)).Font.Name = '宋体'") '设标题为黑体字
sb.Append("xls.Sheets(1).Range(.Cells(1, 1), .Cells(1, wColIndex)).Font.Bold = True") '标题字体加粗
sb.Append("xls.Sheets(1).Range(.Cells(1, 1), .Cells(wRowIndex, wColIndex)).Borders.LineStyle = 1") '设表格边框样式
'设置页眉页脚
'sb.Append("xls.Sheets(1).PageSetup.CenterHeader = "&""楷体_GB2312,常规""&10 " & sHeader & "" )
'.CenterFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"
' Show the sheet.
'显示Excel文件
sb.Append("xls.visible=true/r/n")
'释放创建的Excel application对象
sb.Append("set xls=nothing/r/n")
sb.Append("-->")
sb.Append("</script>")
'将代码写到客户端
'this.Page.RegisterClientScriptBlock("",sb.ToString ());
Catch
MyPage.Response.Write("<script>alert('输出Excel出现错误！')</script>")
End Try
End If
End Sub
来自故乡的风 2016-03-04
- 打赏
- 举报
这是我们的vb.net程序，点击《输出到Excel》按钮事件。当然是保存在客户端。前提是把数据存到 DataTable中。
Protected Sub btnPrintExcel_Click(sender As Object, e As EventArgs) Handles btnPrintExcel.Click
Dim ds As DataSet = '返回一个DataSet
Dim table1 As DataTable = ds.Tables(4)
Dim printDate As String
printDate = Now.ToString("D") 'Monday, November 08, 2010
printDate = Now.ToString("MMM d,yyyy") 'Nov 8, 2010
printDate = Now.ToString("d-MMM-yy") 'Nov 8, 2010
printDate += "; " & Now.ToString("t") '4:47 PM
Dim text As String = "<div align=" & Chr(34) & "Center" & Chr(34) & "> <font style='FONT-SIZE: 12pt; FONT-WEIGHT: bold;' >LINC Home Study - Additional Conversatin List</font> Print Date: " & printDate & "</div>"
Response.ClearContent()
Response.ClearHeaders()
Response.Clear()
Response.AddHeader("content-disposition", "filename=AddConWaitList.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Dim stringWrite As New System.IO.StringWriter()
Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)
'Dim g As New DataGrid()
Dim g As New GridView()
g.DataSource = table1
g.DataBind()
Response.Write(text)
'Change the header row back color
g.HeaderStyle.BackColor = Color.White
For i As Integer = 0 To g.Rows.Count - 1
Dim r As GridViewRow = g.Rows(i)
r.Attributes.Add("class", "textmode")
'If (i Mod 2) <> 0 Then
' r.Cells(0).Style.Add("background-color", "#C2D69B")
'End If
'r.Cells(3).Style.Add("text-align", "center")
'r.Cells(4).Style.Add("text-align", "center")
'r.Cells(5).Style.Add("text-align", "center")
'r.Cells(7).Style.Add("text-align", "center")
'r.Cells(8).Style.Add("text-align", "center")
Next
g.RenderControl(htmlWrite)
'style to format numbers to string.
Dim style As String = "<style> .textmode { mso-number-format:\@; } </style> "
Response.BinaryWrite(System.Text.Encoding.UTF8.GetBytes(stringWrite.ToString()))
Response.Write(style)
Response.End()
Response.Flush()
End Sub
qbilbo 2016-03-04
- 打赏
- 举报
dlDataTableToExcel 这个方法不是你写的吧，
xlApp.Visible = True
xlSheet.Application.Visible = Truegreenhong 2016-03-04
- 打赏
- 举报
好像大致的意思是：
1，将记录写到Html；
2，下载Html到Excel；
3，将Excel Email给用户。
我想问一下，第2步是下载到客户端还是服务器端啊？
我希望解决的是下载到客户端的Excel
greenhong 2016-03-04
- 打赏
- 举报
谢谢u012394290
以前也改写过一些C#代码成VB，但是你这个完全看不懂耶，容我仔细研究几日
程序员-阿勇 2016-03-04
- 打赏
- 举报
你可以做一个常识 就是直接写一个txt <table><tr><td>列一</td></tr></table>保存 改下txt后缀.xls 其实就是一个excel 然后推送到前台就可以了啊
程序员-阿勇 2016-03-03
- 打赏
- 举报
public ActionResult DataStatisticsDayListExcel()
{
DateTime DateStart = Request.QueryString["DateStart"].ToDateTime() == DateTime.MinValue ? DateTime.Now : Request.QueryString["DateStart"].ToDateTime(); ;
DateTime DateEnd = Request.QueryString["DateEnd"].ToDateTime() == DateTime.MinValue ? DateTime.Now.AddDays(1) : Request.QueryString["DateEnd"].ToDateTime(); ;
string LineNO = Request.QueryString["txtlineNo"] ?? "";
MovementlineInfo moveinfo = imovementline.GetMovementline(LineNO);
moveinfo=moveinfo ?? new MovementlineInfo();
SearchLinesManage linesmanegesearch = new SearchLinesManage
{
SearchType = SearchTypeEnum.Total,
LinedateStart = DateStart,
LinedateEnd = DateEnd,
MoveID = moveinfo.MovementlineID
};
IList<LinesManageInfo> linesManageList = ilinesmanage.SearchLinesManage(linesmanegesearch);
//查看线路计划
//linesManageList=linesManageList.Where(s=>s.LineStatus==LineStatusEnum)
SearchMovecarDetail movedetailsearch = new SearchMovecarDetail
{
SearchType = SearchTypeEnum.Total,
};
IList<MovecarDetailInfo> movecardertailList = imovecardetail.SearchMovecarDetail(movedetailsearch);
movecardertailList = movecardertailList.Where(m => linesManageList.Select(s => s.LinemanageID).Contains(m.PristineCarriercarID)).ToList();
//ViewData["MoveList"] = movecardertailList;
StringBuilder Html = new StringBuilder();
Html.Append("<table><tr>");
Html.Append("<th width='100'>线路日期</th>");
Html.Append("<th width='100'>线路号</th>");;
Html.Append("<th width='100'>路单数</th>");
Html.Append("<th width='100'>计划车次</th>");
Html.Append("<th width='100'>实际车次</th>");
Html.Append("<th width='160'>无GPS信号车辆</th>");
Html.Append("<th width='160'>线路状态</th>");
Html.Append("<th width='160'>最佳路线</th></tr>");
foreach (var item in linesManageList)
{
Html.Append("<tr>");
Html.Append("<td>" + item.LineDate.ToString("yyyy-MM-dd") + "</td>");
Html.Append("<td>" + (BManage.BmEngine.GetProvider<BManage.IMovementline>().GetMovementline(item.LineID) == null ? "" : BManage.BmEngine.GetProvider<BManage.IMovementline>().GetMovementline(item.LineID).MovementlineNo) + "</td>");
Html.Append("<td>" + item.ReceiptNums + "</td>");
Html.Append("<td>" + item.LineTrainnum + "</td>");
Html.Append("<td>" + (movecardertailList.Where(s => s.PristineCarriercarID == item.LinemanageID).ToList().Count) + "</td>");
Html.Append("<td>" + (movecardertailList.Where(s => s.PristineGps == 2).ToList().Count) + "</td>");
Html.Append("<td>" + (Sundear.Base.AttributesHelper.GetEnumDescription<BManage.Enumeration.LineStatusEnum>(item.LineStatus)) + "</td>");
Html.Append("<td>" + item.Ordernos + "</td>");
Html.Append("</tr>");
}Html.Append("</table>");
string PathName = DateTime.Now.ToString("yyyyMMddHHmmss");
ExcelCreate.ExcelCreateXml(Html, PathName);
ExcelCreate.CreateExcelByXml(Html, Server.MapPath("../Upload/" + PathName + ".xls"));
RoleInfo roleinfo = PermissionEngine.GetProvider<IRoleAction>(1).GetRoleDictionary().Values.ToList().Where(s => s.RoleName == "运作跟踪日报收件人").FirstOrDefault();
IList<UserInfo> userlist = iuseraccount.GetUserList(1);
userlist = userlist.Where(w => w.ContainsRole(roleinfo.RoleID)).ToList();
List<string> Emailaddress = userlist.Select(s => s.Profile.Email).ToList();
string Path = Server.MapPath("../Upload/" + PathName + ".xls");
sendEmail.SendEmail(Emailaddress, DateTime.Now.ToString("yyyyMMdd") + "运作跟踪日报", Html.ToString(), Path);
return new EmptyResult();
}程序员-阿勇 2016-03-03
- 打赏
- 举报
我有c#的 你要看吗
程序员-阿勇 2016-03-03
- 打赏
- 举报
你这不是c#吧
greenhong 2016-03-03
- 打赏
- 举报
后台代码是VB
greenhong 2016-03-03
- 打赏
- 举报
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop
Imports System.Web.UI.HtmlControls
greenhong 2016-03-03
- 打赏
- 举报
下面是前台代码：
<%@ Page Title="" Language="VB" MasterPageFile="~/pgTools/Tools.master" AutoEventWireup="false" CodeFile="WIUSDIN.aspx.vb" Inherits="pgTools_WIUSDIN"%>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
<style type="text/css">
.auto-style13
{
}
.auto-style14
{
width: 26px;
}
.auto-style15
{
width: 102px;
height: 25px;
}
.auto-style16
{
width: 143px;
height: 25px;
}
.auto-style17
{
width: 26px;
height: 25px;
}
.auto-style18
{
height: 25px;
}
.auto-style19
{
width: 102px;
height: 17px;
}
</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder2" Runat="Server">
<%-- <script lang="javascript" src="<%: ResolveClientUrl("JavaScript.aspx")%>" type="text/javascript"></script>--%>
<table class="auto-style9">
<tr>
<td class="auto-style19"> </td>
<td class="auto-style13" colspan="3">
<asp:Label ID="lblHeader" runat="server" Text="Item Used In Models、Customers、CBUs" Font-Bold="True" Font-Names="幼圆" Font-Size="Larger"></asp:Label>
</td>
</tr>
<tr>
<td class="auto-style19"> </td>
<td class="auto-style13">
</td>
<td class="auto-style14"> </td>
<td> </td>
</tr>
<tr>
<td class="auto-style19">Facility</td>
<td class="auto-style13">
<asp:DropDownList ID="ddlFac" runat="server">
<asp:ListItem>SZ</asp:ListItem>
<asp:ListItem>XF</asp:ListItem>
</asp:DropDownList>
</td>
<td class="auto-style14"> </td>
<td> </td>
</tr>
<tr>
<td class="auto-style19">Item From</td>
<td class="auto-style13">
<asp:TextBox ID="txtItm1" runat="server"> </asp:TextBox>
</td>
<td class="auto-style14"> To</td>
<td>
<asp:TextBox ID="txtItm2" runat="server">99999999999999999999</asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style15">
<asp:Button ID="btnDownLoadToExcel" runat="server" Text="下载到Excel" ForeColor="#3399FF" />
</td>
<td class="auto-style16">
<asp:Button ID="btnRun" runat="server" Text="运行" ForeColor="#3399FF" />
</td>
<td class="auto-style17"></td>
<td class="auto-style18"></td>
</tr>
</table>
<p>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True" ForeColor="#333333" CellPadding="4" CellSpacing="1" OnSorting="GridView1_Sorting" Width="950px">
<Columns>
<asp:BoundField DataField="料号" HeaderText="料号" SortExpression="料号" />
<asp:BoundField DataField="工厂" HeaderText="工厂" />
<asp:BoundField DataField="产品数" HeaderText="产品数" ReadOnly="True" />
<asp:BoundField DataField="用于产品" HeaderText="用于产品" SortExpression="用于产品" />
<asp:BoundField DataField="客户数" HeaderText="客户数" ReadOnly="True" />
<asp:BoundField DataField="用于客户" HeaderText="用于客户" SortExpression="用于客户" />
<asp:BoundField DataField="CBU数" HeaderText="CBU数" ReadOnly="True" />
<asp:BoundField DataField="用于CBU" HeaderText="用于CBU" SortExpression="用于CBU" />
</Columns>
<EmptyDataTemplate>
您选择的记录为空！
</EmptyDataTemplate>
<HeaderStyle BackColor="#3399FF" HorizontalAlign="Left" />
<RowStyle HorizontalAlign="Left" />
</asp:GridView>
<%-- <asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyBPCSConnectionString %>"
SelectCommand="GetItemUsedIn"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="wFac" DbType="String" DefaultValue="SZ" />
<asp:Parameter Name="wItm1" DbType="String" DefaultValue=" " />
<asp:Parameter Name="wItm2" DbType="String" DefaultValue="99999999999999999999" />
</SelectParameters>
</asp:SqlDataSource>--%>
</p>
<p>
</p>
</asp:Content>
greenhong 2016-03-03
- 打赏
- 举报
Protected Sub btnDownLoadToExcel_Click(sender As Object, e As EventArgs) Handles btnDownLoadToExcel.Click
Dim dl As New MyObject
sSQL = "EXEC dbo.GetItemUsedIn '" & ddlFac.Text & "','" & txtItm1.Text & "','" & txtItm2.Text & "'"
dl.dlDataTableToExcel(Me, sSQL, lblHeader.Text)
End Sub
Sub dlDataTableToExcel(ByVal MyPage As System.Web.UI.Page, ByVal sSQL As String, ByVal sHeader As String)
cnn.Open()
Dim cmd As New SqlCommand(sSQL, cnn)
dr = cmd.ExecuteReader
dt.Load(dr)
If IsDBNull(dt) Then
Else
Dim xlApp As New Excel.Application()
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Try
'创建Excel对象
xlApp = CreateObject("Excel.Application")
xlBook = xlApp.Workbooks().Add
xlSheet = xlBook.Worksheets("sheet1")
'将所得到的表的列名,赋值给单元格
Dim wRow As Data.DataRow
Dim wCol As Data.DataColumn
Dim wRowIndex, wColIndex As Integer
wRowIndex = 1
wColIndex = 1
xlApp.Cells(1, wColIndex) = "序号" '数据集之外增加的第一列：序号
For Each wCol In dt.Columns
wColIndex = wColIndex + 1
xlApp.Cells(1, wColIndex) = wCol.ColumnName
Next
'得到的表所有行,赋值给单元格
For Each wRow In dt.Rows
wRowIndex = wRowIndex + 1
'colIndex = 0
wColIndex = 1
xlApp.Cells(wRowIndex, wColIndex) = wRowIndex - 1 '设置序号
For Each wCol In dt.Columns
wColIndex = wColIndex + 1
xlApp.Cells(wRowIndex, wColIndex) = wRow(wCol.ColumnName)
Next
Next
'设置标题（第一行）
With xlSheet
.Range(.Cells(1, 1), .Cells(1, wColIndex)).Font.Name = "宋体" '设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, wColIndex)).Font.Bold = True '标题字体加粗
.Range(.Cells(1, 1), .Cells(wRowIndex, wColIndex)).Borders.LineStyle = 1 '设表格边框样式
End With
'设置页眉页脚
With xlSheet.PageSetup
.CenterHeader = "&""楷体_GB2312,常规""&10 " & sHeader
.CenterFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"
End With
' Show the sheet.
xlApp.Visible = True
xlSheet.Application.Visible = True
'关闭Excel对象
xlApp = Nothing
xlBook = Nothing
xlSheet = Nothing
Catch
MyPage.Response.Write("<script>alert('输出Excel出现错误！')</script>")
End Try
End If
End Sub
