Solved

Output multiple worksheets from Excel file to PDF

  • 30 May 2020
  • 3 replies
  • 265 views

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

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!

View original

3 replies

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!

Userlevel 4
Badge +11

Hi, brilliant, sounds like you have deep dived straight away.  Nice work!

Off the top of my head I think that landscape is a Excel page setting global, let me talk with my awesome engineers and get back to you.

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

Thanks!!

Reply