Export To Excel With Sheet Names

Cory Fifield -

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;
 
mceclip1.png
 

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();
}
}
 
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.

mceclip2.png
 

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. 
 
 

mceclip3.png

 

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.**
 
mceclip4.png
 

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.
 
mceclip5.png
 

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).
 
mceclip6.png
 
Have more questions? Submit a request

Comments