How to Output to Excel into 1 worksheet only with headers and footers

[kgcognos]

My requirement is to output a report studio report to 1 Excel worksheet only. And I need it to contain the Heading and Footer on each page like when displaying in PDF.

At first my issue is the heading only appears once on top of the page. I searched online and found a solution to add a page break after displaying 50 rows.
Now when I output to Excel, I am getting the Heading and Footer for every 50 rows which is what I need, however, it is putting a page in separate worksheets.

Is there a way that I can set up to not create a separate worksheet? It should only go to 1 worksheet but it should show heading and footer every 50 rows.
 
Thank you in advance.

[squish88]

Hmmm... well, for starters, you could replace the "every 50 rows" fix with a new boolean variable:

ReportOutput () = 'PDF'

That, when used as a style variable, changes the "Box Type" property of the page header/footer to none when false.

Then you could make a 2nd boolean variable:

ReportOutput () IN ('singleXLS', 'spreadsheetML', 'XLS')

and apply it as a style variable of the list/crosstab, so that when it's true, the rows per page is set to some absurdly high number like 500,000 or w/e.

I'm sure that there's a newer/better way to fix the output of the excel piece to ignore w/e default # of rows Cognos displays is, but this is a somewhat viable hack in the mean time.   :-)