本文介绍了将数据从gridview导出到excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的网页上有按钮 - 当我点击按钮时我想显示网格，同时我想打开excel来导出数据。现在我只能导出excel方法或显示网格。我只是想知道当用户点击按钮时它是否有可能显示网格并打开excel以导出记录。
我能够将数据从网格视图导出到excel。在我的代码下面，我的问题是我想要显示网格并且同时想要打开excel，我只能做一个事件。
I have button in my web page - When I click button I want to display Grid and Same time I want to open excel to export data. Right now I can do only export excel method or display grid. I just want to know if it is possible when user click on button it display grid and also open excel for exporting records.
I am able to export data from grid view to excel. Below my code and my problem is that I want display grid and same time want to open excel, I can do only one event.
Protected Sub btnDisplayRecords_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDisplayRecords.Click
If validation() Then
Dim StartDateTime As DateTime
Dim EndDateTime As DateTime
StartDateTime = CDate(txtStartDate.Text).ToString
EndDateTime = CDate(txtEndDate.Text).ToString
DisplayGridview(StartDateTime, EndDateTime) --- but I am not able to see Grid
ExcelReport -- I can see excel open for export
Else
End If
End Sub
我不知道想要2个按钮用于显示网格视图并导出到excel。
[/编辑]
这是我的代码vb页面代码：
I just do not want 2 button for display grid view and export to excel.
[/EDIT]
Here is my code vb page code:
Public Sub ExcelReport()
lblMessage.Visible = False
Try
If ((grdComcast.Columns.Count = 0) Or (grdComcast.Rows.Count = 0)) Then
lblMessage.Visible = True
lblMessage.Text = "No Records available to export for Self Reported Sales Database"
Exit Sub
End If
lblMessage.Visible = False
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=ComcastResultsExport.csv")
Response.Charset = ""
Response.ContentType = "application/text"
Dim sb As New StringBuilder()
'This section gets the column headers and adds them to the file
For x As Integer = 0 To grdComcast.HeaderRow.Cells.Count - 1
sb.Append(grdComcast.HeaderRow.Cells(x).Text + ",")
Next
sb.Append(vbCr & vbLf)
'This section gets the data rows
For i As Integer = 0 To grdComcast.Rows.Count - 1
For k As Integer = 0 To grdComcast.Rows(0).Cells.Count - 1
If grdComcast.Rows(i).Cells(k).Text.ToString().Trim() = "" Then
sb.Append("NULL" & ",")
Else
sb.Append(grdComcast.Rows(i).Cells(k).Text + ",")
End If
Next
sb.Append(vbCr & vbLf)
Next
Response.Output.Write(sb.ToString())
Response.Flush()
Response.[End]()
Catch ex As Exception
LogEvent("W", 2007, 0, "Comcast SelfReported Project -SelfReportedSalesDatabase.aspx" & ex.Message)
lblMessage.Text = "There is error in Displaying Records, Please try again"
lblMessage.Visible = True
End Try
End Sub
Public Sub DisplayGridview(ByVal StartDateTime As DateTime, ByVal EndDateTime As DateTime)
Try
Dim objSelfReportedSales As New SelfReportedSales
objSelfReportedSales.DisplayProdData(StartDateTime, EndDateTime)
If objSelfReportedSales.Items.Count.ToString > 0 Then
grdComcast.DataSource = objSelfReportedSales.Items
grdComcast.DataBind()
grdComcast.Visible = True
lblMessage.Visible = False
Else
grdComcast.Visible = False
lblMessage.Visible = True
lblMessage.Text = "No Records available to display for Self Reported Sales Database"
End If
Catch ex As Exception
LogEvent("W", 2002, 0, "Comcast SelfReported Project -SelfReportedSalesDatabase.aspx" & ex.Message)
lblMessage.Text = "There is error in Displaying Records, Please try again"
lblMessage.Visible = True
End Try
End Sub
Aspx页码：
Aspx Page code :
<%@ Page Title="" Language="vb" AutoEventWireup="false" MasterPageFile="~/Site.Master" CodeBehind="SelfReportedSalesDatabase.aspx.vb" Inherits="COMCASTSelfReportedSalesReportWebForm.SelfReportedSalesDatabase" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
<style type="text/css">
.style10
{
width: 96%;
}
.style11
{
font-size: small;
color: #4B6C9E;
font-weight: bold;
}
.style13
{
margin-left: 17px;
}
.style14
{
font-weight: bold;
}
.style17
{
margin-left: 23px;
}
</style>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
<br />
<br />
<table class="style10">
<tr>
<td>
<asp:Label ID="lblStartEnddate" runat="server"
Text="Please Enter Start date and End Date to Export Data" CssClass="style14"
ForeColor="#4B6C9E"><br />
Note : Please add Time and date on this format (dd/mm/yyyy hh:mm:ss)
</td>
</tr>
<tr>
<td></td>
</tr>
</table>
<table>
<tr>
<td>
<span class="style11">Start Date : </span>
<asp:TextBox ID="txtStartDate" runat="server" BorderStyle="Double"
CssClass="style13">
<asp:Image ID="Image3" runat="server" img src="Images/calendar%20(2).gif" />
<asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="txtStartDate" PopupButtonID="Image3" DaysModeTitleFormat="MMDDYYYY HHMM">
</td>
</tr>
<tr>
<td>
<span class="style11">End Date : </span>
<asp:TextBox ID="txtEndDate" runat="server" BorderStyle="Double"
CssClass="style17">
<asp:CalendarExtender ID="CalendarExtender2" runat="server" TargetControlID="txtEndDate"
PopupButtonID="Image1">
<asp:Image ID="Image1" runat="server" img src="Images/calendar%20(2).gif" />
</td>
</tr>
<td>
</td>
<tr>
<td>
<asp:Button ID="btnDisplayRecords" runat="server" Text="Display Records"
BorderStyle="Double" Height="23px" Width="116px" BorderColor="Black" />
</td>
<td>
<asp:ImageButton ID="btnExportToExcel" ImageUrl="/Images/Excel.gif" AlternateText = "Export To Excel" runat="server" Visible ="false" />
</td>
</tr>
</table>
<br />
<asp:Label ID="lblMessage" runat="server" Visible = "False" Font-Size="Small"
ForeColor="#CC0000" Font-Bold="True" Font-Names="Calibri">
<br />
<br />
<div style="width: 875px; overflow:auto;height:160px;">
<asp:GridView ID="grdComcast" runat="server" AutoGenerateColumns = "False" align = "center" CssClass="scrollable"
AllowSorting = "True" Width ="250px" Visible = false
Height = "70px" Border="5px" BorderColor ="#4B6C9E"
CellPadding="4" ForeColor="#333333"
GridLines="None">
<columns>
<asp:TemplateField HeaderText="PartnerSite">
<edititemtemplate>
<asp:TextBox ID="txtPartnerSite" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.PartnerSite")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblPartnerSite" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.PartnerSite")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="Division">
<edititemtemplate>
<asp:TextBox ID="txtDivision" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.Division")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblDivision" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.Division")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="Region">
<edititemtemplate>
<asp:TextBox ID="txtRegion" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.Region")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblRegion" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.Region")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="AgentName">
<edititemtemplate>
<asp:TextBox ID="txtAgentName" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.AgentName")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblAgentName" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.AgentName")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="PID">
<edititemtemplate>
<asp:TextBox ID="txtPID" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.PID")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblPID" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.PID")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="OPRIDCSG">
<edititemtemplate>
<asp:TextBox ID="txtOPRIDCSG" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.OPRIDCSG")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblOPRIDCSG" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.OPRIDCSG")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="OPRIDComtrac">
<edititemtemplate>
<asp:TextBox ID="txtOPRIDComtrac" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.OPRIDComtrac")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblOPRIDComtrac" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.OPRIDComtrac")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="AccountNumberCSG">
<edititemtemplate>
<asp:TextBox ID="txtAccountNumberCSG" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.AccountNumberCSG")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblAccountNumberCSG" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.AccountNumberCSG")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="AccountNumberComtrac">
<edititemtemplate>
<asp:TextBox ID="txtAccountNumberComtrac" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.AccountNumberComtrac")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblAccountNumberComtrac" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.AccountNumberComtrac")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="CustomerPhoneNumber">
<edititemtemplate>
<asp:TextBox ID="txtCustomerPhoneNumber" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.CustomerPhoneNumber")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblCustomerPhoneNumber" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.CustomerPhoneNumber")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="CustomerName">
<edititemtemplate>
<asp:TextBox ID="txtCustomerName" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.CustomerName")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblCustomerName" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.CustomerName")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="WOCreateDate">
<edititemtemplate>
<asp:TextBox ID="txtWOCreateDate" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.WOCreateDate")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblWOCreateDate" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.WOCreateDate")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="WOScheduleDate">
<edititemtemplate>
<asp:TextBox ID="txtWOScheduleDate" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.WOScheduleDate")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblWOScheduleDate" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.WOScheduleDate")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="CurrentService">
<edititemtemplate>
<asp:TextBox ID="txtCurrentService" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.CurrentService")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblCurrentService" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.CurrentService")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="RGUUpgrade">
<edititemtemplate>
<asp:TextBox ID="txtRGUUpgrade" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.RGUUpgrade")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblRGUUpgrade" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.RGUUpgrade")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="TotalUpgradesCount">
<edititemtemplate>
<asp:TextBox ID="txtTotalUpgradesCount" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.TotalUpgradesCount")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblTotalUpgradesCount" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.TotalUpgradesCount")%>'>
</itemtemplate>
<asp:TemplateField HeaderText="Comments">
<edititemtemplate>
<asp:TextBox ID="txtComments" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.Comments")%>'>
</edititemtemplate>
<itemtemplate>
<asp:Label ID="lblComments" runat="server" Text= '<%# DataBinder.Eval(Container, "DataItem.Comments")%>'>
</itemtemplate>
</columns>
<alternatingrowstyle cssclass="AlternatingRow" backcolor="White">
ForeColor="#284775" />
<pagerstyle backcolor="#284775" forecolor="White" horizontalalign="Center" />
<rowstyle backcolor="#F7F6F3" forecolor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<sortedascendingcellstyle backcolor="#E9E7E2" />
<sortedascendingheaderstyle backcolor="#506C8C" />
<sorteddescendingcellstyle backcolor="#FFFDF8" />
<sorteddescendingheaderstyle backcolor="#6F8DAE" />
</alternatingrowstyle></div>
<br />
<br />
</form>
Here is my class :
Here is my class :
Public Sub DisplayProdData(ByVal StartDate As DateTime, ByVal EndDate As DateTime)
Dim objConnectionString As ConnectionString
Dim objConnection As SqlClient.SqlConnection
Dim objCommand As SqlClient.SqlCommand
Dim objDataReader As SqlClient.SqlDataReader
objConnectionString = New ConnectionString("SQLNET_OLTP_CLIENT_Comcast_DDL")
objConnection = New SqlClient.SqlConnection(objConnectionString.ConnString)
objConnection.Open()
objCommand = New SqlClient.SqlCommand
objCommand.Connection = objConnection
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = "[Comcast].[usp_Comcast_SelfReportedSalesReport_SelectByDate]"
objCommand.Parameters.Add(New SqlClient.SqlParameter("StartDateTime", StartDate))
objCommand.Parameters.Add(New SqlClient.SqlParameter("EndDateTime", EndDate))
objDataReader = objCommand.ExecuteReader()
If objDataReader.HasRows Then
While objDataReader.Read
Dim objSelfReportedSales As New SelfReportedSales ' create new object for new data'New datalist
objSelfReportedSales.ReportID = objDataReader("ReportID")
objSelfReportedSales.PartnerSite = objDataReader("PartnerSite")
objSelfReportedSales.Division = objDataReader("Division")
objSelfReportedSales.Region = objDataReader("Region")
objSelfReportedSales.AgentName = objDataReader("AgentName")
objSelfReportedSales.PID = objDataReader("PID")
If Not objDataReader("OPRIDCSG") Is DBNull.Value Then
objSelfReportedSales.OPRIDCSG = objDataReader("OPRIDCSG")
End If
If Not objDataReader("OPRIDComtrac") Is DBNull.Value Then
objSelfReportedSales.OPRIDComtrac = objDataReader("OPRIDComtrac")
End If
If Not objDataReader("AccountNumberCSG") Is DBNull.Value Then
objSelfReportedSales.AccountNumberCSG = objDataReader("AccountNumberCSG")
End If
If Not objDataReader("AccountNumberComtrac") Is DBNull.Value Then
objSelfReportedSales.AccountNumberComtrac = objDataReader("AccountNumberComtrac")
End If
objSelfReportedSales.CustomerPhoneNumber = objDataReader("CustomerPhoneNumber")
objSelfReportedSales.CustomerName = objDataReader("CustomerName")
objSelfReportedSales.WOCreateDate = objDataReader("WOCreateDate")
objSelfReportedSales.WOScheduleDate = objDataReader("WOScheduleDate")
objSelfReportedSales.CurrentService = objDataReader("CurrentService")
objSelfReportedSales.RGUUpgrade = objDataReader("RGUUpgrade")
objSelfReportedSales.TotalUpgradesCount = objDataReader("TotalUpgradesCount")
objSelfReportedSales.Comments = objDataReader("Comments")
arrItems.Add(objSelfReportedSales)
End While
End If
I am sorry I asked question first time so did not know all rules.
I am sorry I asked question first time so did not know all rules.
推荐答案
这篇关于将数据从gridview导出到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！