Hi kprasad127,

To export all page to single excel you need to export the DataSet that you are assigned to the ReportDataSource.

Check this example. Now please take its reference and correct your code.

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here

Download and install Northwind Database

For this example i have used ClosedXml library for exporting to excel.

You can refer below article for more details.

How to create RDLC report step by step in ASP.Net using C# and VB.Net

Export DataTable to Excel in ASP.Net using C# and VB.Net

HTML

<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="600" Height="320">
</rsweb:ReportViewer>
<asp:Button Text="Export" ID="btnExport" runat="server" OnClick="btnExport_Click" />

Namespaces

C#

using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WebForms
Imports ClosedXML.Excel

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        ReportViewer1.ProcessingMode = ProcessingMode.Local;
        ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
        Customers dsCustomers = GetData("select * from customers");
        ReportDataSource datasource = new ReportDataSource("Customers", dsCustomers.Tables[0]);
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
    }
}

private Customers GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;

            sda.SelectCommand = cmd;
            using (Customers dsCustomers = new Customers())
            {
                sda.Fill(dsCustomers, "DataTable1");
                return dsCustomers;
            }
        }
    }
}

protected void btnExport_Click(object sender, EventArgs e)
{
    Customers dsCustomers = GetData("SELECT CustomerId,ContactName,City,Country FROM Customers");
    DataTable dt = dsCustomers.Tables[0];
    XLWorkbook wb = new XLWorkbook();
    wb.Worksheets.Add(dt, "Customers");
    wb.SaveAs(@"C:\Customers.xlsx");
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        ReportViewer1.ProcessingMode = ProcessingMode.Local
        ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc")
        Dim dsCustomers As Customers = GetData("select * from customers")
        Dim datasource As New ReportDataSource("Customers", dsCustomers.Tables(0))
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(datasource)
    End If
End Sub

Private Function GetData(query As String) As Customers
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim cmd As New SqlCommand(query)
    Using con As New SqlConnection(conString)
        Using sda As New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using dsCustomers As New Customers()
                sda.Fill(dsCustomers, "DataTable1")
                Return dsCustomers
            End Using
        End Using
    End Using
End Function

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim rowPerPage As Integer = 10
    Dim dsCustomers As Customers = GetData("SELECT CustomerId,ContactName,City,Country FROM Customers")
    Dim dt As DataTable = dsCustomers.Tables(0)
    Dim wb As XLWorkbook = New XLWorkbook()
    wb.Worksheets.Add(dt, "Customers")
    wb.SaveAs("C:\Customers.xlsx")
End Sub

Screenshot

The Exported Excel