Output multiple worksheets from Excel file to PDF

Question

Just getting my feet wet with this tool. The report templates are a great start, but I’d like to modify them to include a bit more of the detail contained within the Excel worksheets into the PDF file.

For example… I’ve been working with a template I copied from DNS: Blocked - Domains and Users. I’d like to output not only the Overview sheet, but also the BlockedLog and Pivots into the PDF that will go to the client. I found a statement in the script which appears to control this.

-sheetsToExport @("Overview")

I changed it to:

-sheetsToExport @("Overview","BlockedLog","Pivots")

I was thinking this would append the additional two worksheets to the PDF under the Overview section. What it actually did was to only output the last variable to the PDF…. i.e. only the Pivots worksheet appears in the PDF. When I remove the Pivots parameter, it then outputs only the BlockedLog worksheet to the PDF file.

How do you make it so the script appends worksheets to the PDF file?

Thanks!

icon

Best answer by kfranklin 1 June 2020, 19:43

View original

kfranklin · Accepted Answer

Okay…. sorta answered my own question. I found an alternate “Common Functions” script in the Webroot Reporting\Templates\Common\Scripts\Modules directory called “CommonFunctionsModule202004.ps1. This script had a function that the CommonFunctionsModule.ps1 file did not have. It was called Export-MultiSheetsToPDF and this seemed to do the trick when I:

Replaced the call to CommonFunctionsModule.ps1 with CommonFunctionsModule202004.ps1 in my template.ps1 file
Replaced the call to Export-SheetsToPDF with Export-MultiSheets.PDF in my template.ps1 file

Now I get the Overview , BlockedLog, and Pivots sheets all output to PDF as desired.

Next hurdle…. How do I format the PDF output as landscape instead of portrait? Either for a single worksheet (i.e. BlockedLog) or for the whole document would probably work. The problem is the BlockedLog worksheet in particular is too wide to fit on a single page without either shrinking it, or printing that page in landscape.

Thanks!

kfranklin · Answer

You’re absolutely right…just simply changing the print layout within the Excel template got me what I needed. I was overthinking it!  Thanks!!

Output multiple worksheets from Excel file to PDF

3 replies

Reply

Reply

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded