Transforming a SSRS(RDLC) Report report output to Excel format

Sksamantaray
Posted by in ASP.NET category on for Beginner level | Points: 250 | Views : 14155 red flag

In this article we shall learn how to transforming the report output into MS Excel.


 Download source code for Transforming a SSRS(RDLC) Report report output to Excel format

Introduction

Sql Server Reporting service has got a wonderful option called export which in fact helps to export the report output to PDF or Excel. However unfortunately it does not allow user to generate any specific type of output like PDF or excel. So in this article we are going to see how to create only Excel file from the report output.

Objective

To transform a RDLC report output to an Excel file 

Requisites

1.Create a SSRS RDLC Report
2.Create Function Which can convert Report output to Excel file.

Using the code

In this code we are going to create a report to show top 20 employee details.After the report is created, it needs to be converted to Excel file manually.  So in the under mentioned code the export control is removed ,intentionally ,by using  ShowExportControls=false.

The getData() method of the code behind populates employee details in the report-viewer.
When one clicks on Export2Excel button the output of Report-Viewer gets converted to Excel file  and the user is asked to save the file to physical location or directly open it .

   <form id="form1" runat="server">
    <div>
    
        <br />
        &nbsp;<asp:Button ID="Button2" runat="server" onclick="Button2_Click" 
            Text="Export2Excel" />
        <br />
    
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" ShowExportControls="false" 
            Font-Size="8pt" Height="400px" Width="100%">
            <LocalReport ReportPath="Report1.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" 
                        Name="DataSet1_Employees" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
            SelectMethod="GetDataItem" TypeName="RDLC_webApplication1._Default">
        </asp:ObjectDataSource>
    
    </div>
    </form>                                                                          

// Code Behind                                                                     
public partial class _Default : System.Web.UI.Page
    {

        string connn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString();

        protected void Page_Load(object sender, EventArgs e)
        {
          
            if (!Page.IsPostBack)
            {
                getData();
            }
        }

        protected void getData()
        {

       SqlDataAdapter da = new SqlDataAdapter("select top 20 * from Employees", connn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            ReportDataSource rdc = new ReportDataSource("DataSet1_Employees", ds.Tables[0]);
            ReportViewer1.LocalReport.DataSources.Clear();
            ReportViewer1.LocalReport.DataSources.Add(rdc);
            ReportViewer1.Visible = true;
            ReportViewer1.LocalReport.Refresh();
        }
    }                                                                                //To generate Excel file                                                              protected void Button2_Click(object sender, EventArgs e)
    {

        Response.Clear();
        Microsoft.Reporting.WebForms.Warning[] warnings = null;
        string[] streamids = null;
        String mimeType = null;
        String encoding = null;
        String extension = null;
        Byte[] bytes = null;

        bytes = ReportViewer1.LocalReport.Render("Excel", null, out mimeType, out encoding, out extension, out streamids, out warnings);
        Response.Clear();
        Response.Buffer = true;
        Response.Clear();

        Response.AddHeader("Content-Disposition", "attachment; filename=abc.xls");
        Response.AddHeader("Content-Length", bytes.Length.ToString());
        Response.BinaryWrite(bytes);
        Response.End();
    }  

When user first loads the page ,the getData() method is called,which takes care of loading employee details in the report-viewer. Here you cannot see any export option since it is made to false as i mention earlier in this article. To create an Excel file  , user needs to click on Export2Excel button. 




 When user clicks on Export2Excel ,the following screen asks to open or save the file to physical location.




Conclusion

In this way an Excel file  can be created manually  from SSRS- RDLC Report output. 


Page copy protected against web site content infringement by Copyscape

About the Author

Sksamantaray
Full Name: Sanjaya Kumar Samantaray
Member Level: Silver
Member Status: Member
Member Since: 11/6/2011 3:05:05 PM
Country: India
Thanks, Sanjay

Wipro Technology

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)