In this blog, we will discuss how to generate a single word or
excel file from multiple RDLC report by using sub report control.
A sub report is a report control which is used to display
another report inside a main report, it helps to re-use the existing RDLC
report
Here are few basic steps to create a single excel file with
multiple spreadsheet from multiple RDLC reports
Steps:
1.
Create
a main RDLC report – Summary.RDLC :
2.
Add
Rectangle Table Control :
3.
Add
Sub-Report control inside rectangle control and include the existing RDLC
report ( Child1.rdlc & Child2.rdlc) :
4.
Set
Page Break Option for each rectangle control :
a.
Break
Location - (End option as shown below )
b.
Page
Name (Excel Spreadsheet Name : Child1)
5.
Set
Data For Child1 and Child2 rdlc report :
There is a SubreportProcessingEventHandler
event, which is getting fired for each sub reports, what Main RDLC report have.
It means, if Main Report have two sub Report, it is getting called two times
In event handler, we can easily bind data
source for each sub-reports.
Here is an example, how to bind the
DataSource for two child reports :
viewer.LocalReport.SubreportProcessing
+=
new SubreportProcessingEventHandler(exampleSubreportProcessingEventHandler);
void
exampleSubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e)
{
if (e.DataSourceNames.FirstOrDefault() == "dsChild1")
{
e.DataSources.Add(new ReportDataSource("dsChild1",
itemCollection1));
}
if (e.DataSourceNames.FirstOrDefault() == "dsChild2")
{
e.DataSources.Add(new ReportDataSource("dsChild2",
itemCollection2));
}
}
6.
Generate
Summary report :
Run the below code to generate
Summary.xlsx report, which contain two sheet (child1 and Child2 as shown below)
static void Main(string[] args)
{
MemoryStream
memoryStream = GenerateReport();
WriteFile("Summary.xlsx",
memoryStream);
}
public static MemoryStream GenerateReport()
{
// Variables
Microsoft.Reporting.WinForms.Warning[] warnings;
string[] streamIds;
string mimeType = string.Empty;
string encoding = string.Empty;
string extension = string.Empty;
string deviceInfo =
"" +
"
PDF" +
"
"
;
// Setup the report viewer object and get the array of bytes
ReportViewer viewer = new ReportViewer();
try
{
string fileDownloadType = "EXCELOPENXML";
string exeFolder =
System.AppDomain.CurrentDomain.BaseDirectory;
string reportPath =
Path.Combine(exeFolder, string.Format(@"Summary.rdlc"));
viewer.ProcessingMode = ProcessingMode.Local;
viewer.LocalReport.ReportPath = reportPath;
List- itemCollection1 =
new List
{
new Item{ Name = "Rajeev"}
};
List- itemCollection2 =
new List
{
new Item{ Name = "Rajeev Tiwari"},
new Item{ Name = "Shyam Tiwari"}
};
// Set Sub-reports Dataset
viewer.LocalReport.SubreportProcessing +=
new
SubreportProcessingEventHandler(exampleSubreportProcessingEventHandler);
void exampleSubreportProcessingEventHandler(object sender,
SubreportProcessingEventArgs e)
{
e.DataSources.Add(new
ReportDataSource("dsChild1", itemCollection1));
e.DataSources.Add(new
ReportDataSource("dsChild2", itemCollection2));
}
viewer.LocalReport.Refresh();
byte[] bytes =
viewer.LocalReport.Render(fileDownloadType, deviceInfo, out mimeType, out encoding, out extension, out streamIds, out warnings);
return new MemoryStream(bytes);
}
catch (Exception ex)
{
throw;
}
finally
{
viewer.LocalReport.Dispose();
viewer = null;
}
}
protected static void WriteFile(string filename, MemoryStream notice)
{
try
{
using (FileStream file = new FileStream(filename,
FileMode.Create))
{
notice.WriteTo(file);
file.Close();
notice.Dispose();
notice =
null;
}
}
catch (Exception ex)
{
throw ex;
}
}
