• Home
  • Forums
  • FME Form
  • Transformers
  • Hello, I have an Excel table with data in separate worksheets (i.e. each worksheet is a grid). I'd like to export these grids/worksheets into separate pages in a single PDF or MS Word document - assistance needed!
Solved

Hello, I have an Excel table with data in separate worksheets (i.e. each worksheet is a grid). I'd like to export these grids/worksheets into separate pages in a single PDF or MS Word document - assistance needed!

W
Badge

My attempt to export to PDF seemed fairly impossible, so I changed my approach to export to MS Word instead. I've made some progress; I've managed to create a single table that has all records from all of the worksheets - I need to be able to split the data out by these grids (i.e. separate sheets/tables for each grid). I also need these sheets to be in landscape rather than portrait orientation. However, I'm a bit stuck and I'd appreciate any advice that can be offered on how to achieve the result I desire! Thanks!

icon

Best answer by debbiatsafe 31 May 2022, 02:27

View original
2 Attachments

3 replies

debbiatsafe
Rank
Userlevel 3
Badge +17

Hi @watts​ 

Instead of your current workflow where a page break is created for each row of the Excel workbook, I would recommend creating n-1 page breaks (where n is the total number of worksheets). Sort the features so that it goes feature type 1, page break, feature type 2, page break, etc. as the order of features entering the Word writer affects the output. This should create the separate sheets/tables for each of your grids.

 

For a landscape orientation, use a base Word file where the orientation has been changed to landscape. I have attached an example workspace demonstrating this approach.

 

Note: if you're comfortable with using Powershell and have Excel installed/licensed on the machine running the workspace, the approach in this Q&A should also work for transformer Excel to PDF.

W
Badge

Hi @watts​ 

Instead of your current workflow where a page break is created for each row of the Excel workbook, I would recommend creating n-1 page breaks (where n is the total number of worksheets). Sort the features so that it goes feature type 1, page break, feature type 2, page break, etc. as the order of features entering the Word writer affects the output. This should create the separate sheets/tables for each of your grids.

 

For a landscape orientation, use a base Word file where the orientation has been changed to landscape. I have attached an example workspace demonstrating this approach.

 

Note: if you're comfortable with using Powershell and have Excel installed/licensed on the machine running the workspace, the approach in this Q&A should also work for transformer Excel to PDF.

Thank you Debbie, I greatly appreciate your assistance! With a couple minor adjustments I was able to get your workflow to work. However, I wondered if you could please help me to understand the logic - particularly with the Sampler that removes 1 feature from the total # of pages, and the Attribute Creator that adds 0.5 to the existing order attribute?

1 Attachment
debbiatsafe
Rank
Userlevel 3
Badge +17

Thank you Debbie, I greatly appreciate your assistance! With a couple minor adjustments I was able to get your workflow to work. However, I wondered if you could please help me to understand the logic - particularly with the Sampler that removes 1 feature from the total # of pages, and the Attribute Creator that adds 0.5 to the existing order attribute?

Hello @watts​ 

When the total number of pages is used to create the page breaks (ie. no Sampler), the output Word file contains a blank last page. This is due to the presence of a page break after the last table. The Sampler removes one feature from the total number of pages so there is no page break after the last table and hence no blank last page.

 

The logic of adding 0.5 to the existing order attribute is to ensure the order of features is correct following the Sorter:

  • table (order = 1)
  • page break (order = 1.5)
  • table (order = 2)
  • page break (order = 2.5)

 

As features are written to the Word file in the order they are passed to the Word writer, adding 0.5 to page break features ensure a page break occurs after every table. Otherwise, you could potentially end up with:

  • page break, table, table, page break or
  • table, page break, page break, table

 

Note that you can add any value as long as it is less than 1. For example, instead of 0.5, you could use 0.001 and the same output order will be maintained.

Reply


Community Stats

28,723
Posts
109,511
Replies
36,559
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.