Friday, April 15, 2016

Export RDLC report to Excel programmatically in asp.net

In this article I am going to explain how we can export RDLC report data to Excel file programmatically in asp.net.


Description:

Commonly we are using report viewer control to show the data. From Reportviewer we can save or export the displaying records into Excel, PDF and Word document.  In this example I am going to explain to export the data into excel without showing in reportviewer.

Implementation:
I have a table Tb_Movie. I want to export the export the data of this table into Excel file.

Create Store Procedure:
Create proc SpGetMovie
AS
BEGIN
Select * from dbo.Tb_Movie
End

Create a RDLC report
To create a RDLC report refers to this article: simple steps to Create RDLC Reportin asp.net.

Add the namespace:

C# Code:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Reporting.WebForms;
VB.net Code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports Microsoft.Reporting.WebForms

Export data to Excel file:
Create a method to get the data and export into excel file. And call it on page load.

C# Code:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            ExportReoprtoExecl();
        }
    }
    private void ExportReoprtoExecl()
    {
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter("SpGetMovie", con);          
            DemoDataSet ds = new DemoDataSet();
            adp.Fill(ds, "SpGetMovie");
            ReportDataSource datasource = new ReportDataSource("DataSet1", ds.Tables[0]);

            Warning[] warnings;
            string[] streams;
            string MIMETYPE = string.Empty;
            string encoding = string.Empty;
            string extension = string.Empty;

            ReportViewer rptviewer = new ReportViewer();

            rptviewer.ProcessingMode = ProcessingMode.Local;
            rptviewer.LocalReport.ReportPath = "Report.rdlc";
            rptviewer.LocalReport.DataSources.Add(datasource);
            byte[] bytes = rptviewer.LocalReport.Render("Excel", null, out MIMETYPE, out encoding, out extension, out streams, out warnings);
            Response.Buffer = true;
            Response.Clear();
            Response.ContentType = MIMETYPE;
            Response.AddHeader("content-disposition","attachment; filename=" + DateTime.Now.ToString("ddMMyyyyhhmmss") + "." + extension);
            Response.BinaryWrite(bytes);
            Response.Flush();            
        }
        catch(Exception ex)
        {
        }
    }

VB.net Code:
Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            ExportReoprtoExecl()
        End If
    End Sub
    Private Sub ExportReoprtoExecl()
        Try
            Dim adp As New SqlDataAdapter("SpGetMovie", con)
            Dim ds As New DemoDataSet()
            adp.Fill(ds, "SpGetMovie")
            Dim datasource As New ReportDataSource("DataSet1", ds.Tables(0))

            Dim warnings As Warning()
            Dim streams As String()
            Dim MIMETYPE As String = String.Empty
            Dim encoding As String = String.Empty
            Dim extension As String = String.Empty

            Dim rptviewer As New ReportViewer()
            rptviewer.ProcessingMode = ProcessingMode.Local
            rptviewer.LocalReport.ReportPath = "Report.rdlc"
            rptviewer.LocalReport.DataSources.Add(datasource)
            Dim bytes As Byte() = rptviewer.LocalReport.Render("Excel", Nothing, MIMETYPE, encoding, extension, streams, warnings)
            Response.Buffer = True
            Response.Clear()
            Response.ContentType = MIMETYPE
            Response.AddHeader("content-disposition", Convert.ToString("attachment; filename=" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".") & extension)
            Response.BinaryWrite(bytes)
            Response.Flush()
        Catch ex As Exception
        End Try
    End Sub

No comments:

Post a Comment