In this article we shall learn how to transforming the report output into MS Excel.
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 />
<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.