Export To Excel With Sheet Names
Trouble viewing images? Click the image to make it bigger.
1. Add New "Before Print" Script On The Report
Select the Report's properties, and add a script for "Before Print" and paste the following script inside of the sub: PrintingSystem.XlSheetCreated += PrintingSystem_XlSheetCreated;
2. Add the following code
At the very top of your script page (Line 1), add the code below.
using System;
using System.Collections.Generic;
public List<string> GroupValues = new List<string>();
void PrintingSystem_XlSheetCreated(object sender, XlSheetCreatedEventArgs e) {
for (int i = 0; i < GroupValues.Count; i++)
{
if(e.Index == i)
e.SheetName = GroupValues[i].ToString();
}
}
using System.Collections.Generic;
public List<string> GroupValues = new List<string>();
void PrintingSystem_XlSheetCreated(object sender, XlSheetCreatedEventArgs e) {
for (int i = 0; i < GroupValues.Count; i++)
{
if(e.Index == i)
e.SheetName = GroupValues[i].ToString();
}
}
This code, creates a list to contain each of your group values, which is what will be used to name the excel sheets. The for loop loops through this list and sets the sheet name on export to the corresponding Group Value.
3. Add "Before Print" Script For The Label
Next is adding each group value that is printed in the report to the array list used to name our sheets. It's very important that we do this script on the group level with the page break. This label will return the Grouping ID as normal, but we'll add a before print script that adds the value to our array.
Add the script below into the label's before print section
GroupValues.Add(Convert.ToString(label44.Text));
**It's important to note that you will need to change the label number to your label's name.**
4. Set The Default Export Type
Next, we set the default export type inside of the reports "Export Options". We need to set the XLSX Export Options "Export Mode" to "Single File (Page-by-Page)". This defaults the Excel export to export to the same Excel file, but each page is it's own tab.
5. Set Custom Page Height
If each group can be across multiple pages, we'll need to set a custom page height, so that the sheets are not broken up when exporting to excel. We do this by setting the Reports "Paper Kind" to "Custom" and "maxing" out the page height (doesn't matter for excel as each has it's own page).
Comments
0 comments
Please sign in to leave a comment.