Export data from gridview to excel

Question

0.00/5 (No votes)

See more:

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.

[EDIT - moved from comment]
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.

VB

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

I just do not want 2 button for display grid view and export to excel.
[/EDIT]

Here is my code vb page code:

VB

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 Page code :

ASP.NET

<%@ 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 :

VB

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.

Posted 15-May-13 5:17am

Kinjal Noko

Updated 15-May-13 7:46am

Maciej Los

v4

Add a Solution

Comments

Richard C Bishop 15-May-13 11:27am

Yes, it is possible. Post the code you have tried where it is attempting both in order for us to help you.

adriancs 15-May-13 11:49am

This is a hot question.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-05-15T05:42:00

Solution 1

Please, see similar question:
Export Gridview to Excel in ASP.NET[^]

Other resources:
http://www.aspxcode.net/free-asp-net-data-sample-source-code.aspx?Topics=How%20to%20Export%20GridView%20to%20Excel[^]
http://www.dotnetfunda.com/forums/thread5429-export-grid-view-to-excel.aspx[^]
http://www.aspnet101.com/2009/05/gridview-export-to-excel-vb-net/[^]

Does it enough for start?

Posted 15-May-13 5:42am

Maciej Los

Comments

Kinjal Noko 15-May-13 12:02pm

Thanks for reply. 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
I just do not want 2 button for display grid view and export to excel.

Maciej Los 15-May-13 13:02pm

Next time, please, use "Improve question" widget.

How can i help you, if you do not post a code for DisplayGridview() procedure?

Member 9581488 15-May-13 12:34pm

You can export your grid to excel after binding data to gridview.

Maciej Los 15-May-13 13:01pm

Who, me?

Member 9581488 15-May-13 13:09pm

oh.. not you! my mistake.....its for Kinjal Noko

Kinjal Noko 15-May-13 13:36pm

I have modify my question and also add my Aspx and VB code

Maciej Los 15-May-13 13:48pm

Do not post all code, but only this part which occurs error or with wich you have a problem.
Please, use formatting to make code more readible!

Member 9581488 16-May-13 14:05pm

If objSelfReportedSales.Items.Count.ToString > 0 Then
grdComcast.DataSource = objSelfReportedSales.Items
grdComcast.DataBind()// after this line call your export method.
grdComcast.Visible = True
lblMessage.Visible = False

Export data from gridview to excel

1 solution

Solution 1

Add your solution here

Preview 0

Export data from gridview to excel

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us