When the report is rendered to Excel with “Paged/Sectioned” turn on, the worksheets will be named using the Report Name. E.g. Reportname Page 1, Reportname Page 2, Reportname Page 3, etc…
Below are the scripts to rename the worksheet names by different Sections in the report.
For a section with 1 page
Normally, we will use this page.setName("Section 1"); in “Section Header” > “OnLayout”.
However, this will only work when the section has only 1 page as the section header only appears on one sheet, so it only alters that page title.
For a section with more than 1 page
When you have a section with more than 1 page. We can do a page count for that.
1/ Add a variable at Function Definition
var s1pageNo = 0;
2/ Increment the page of the section, Page Header > OnRender Begin
++s1pageNo;
3/ For the section, the details appear on each sheet, so if you put the script in the Detail > OnLayout, then it will apply to every sheet that shows a detail.
page.setName("Section 1 - " + s1pageNo);
There are two things to be careful of:
1/ You must not have two sheets with the same name, a variable is added to count the page numbers and include them in the name, the script has moved the script into Detail instead. E.g. page.setName("Section 1 - " + s1pageNo);
2/ It won’t work if the sheet has no details. So if the details are empty, “Force Page Break” must be turned off in Detail, Section Header, and Section Footer.
If “Force Page Break” was Not set to None, this means that the first section has multiple pages, but because they are empty they are later removed.
However, the count still increases because of these.
If you take out the force page break option on all of these then it works as expected.