2

I am currently using TinyButStrong's Excel plugin to create numerous Excel docs that are sent out on a scheduled basis. I have been requested to break the information into separate worksheets, within a single Excel document, where each report could have a different number of worksheets (so I can't just use multiple sheets in a template file since the number of sheets is unpredictable).

I can easily create separate Excel documents and use a naming convention to identify which Excel docs are to be merged, but I can not find a way to merge the documents using TinyButStrong Excel.

NOTE: Due to our environment, PHP Excel is not an option.

Please let me know if you know of a way to merge multiple Exc3el documents or to programatically create an Excel document with numerous worksheets created from a single worksheet template.

Thank you.

Skrol29
  • 5,402
  • 1
  • 20
  • 25
David Dodson
  • 41
  • 1
  • Welcome to Stack Overflow - nice to have you. Please read [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) and [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) to help keeping Stack Overflows content on the highest possible level and increase your chances getting an appropriate answer. – Axel Oct 11 '17 at 00:13

1 Answers1

2

For now you cannot merge multiple XLSX documents into a single one. This is because despite reading a worksheet XML source is possible with OpenTBS, you cannot simply copy the XML from an XLSX source file into another XLSX target file because such XML uses several internal ids for styles, comments, printing setup, ... Those ids are stored in complicated places in the XML source files. So such a simple copy will produce an invalid target XLSX file.

Another point is that OpenTBS cannot (for now) create a new worksheet in an XLSX. It can only modify, delete or hide a existing worksheet in the template.

So the solution for your problem is to have a XLSX template file containing all the possible worksheets you could have in the final XLSX file. Then you can merge the wanted worksheets (using OPENTBS_SELECT_SHEET) and delete the unwanted worksheets (using OPENTBS_DELETE_SHEETS).

Skrol29
  • 5,402
  • 1
  • 20
  • 25
  • Thank you! This is helping me with a similar issue... I still wish OpenTBS could create multiple worksheets in XLSX :) – AAA Oct 18 '17 at 20:01
  • Thank you. I am trying this out and now running into an issue of merging multiple blocks. When I try separate MergeBlock statements as follows: $TBS->MergeBlock('b1', $block_b1); $TBS->MergeBlock('b2', $block_b2); ...the results ignore all but the first block. I see notes on merging multiple blocks but using the same data in each: $TBS->MergeBlock('b1,b2,b3', $block_data_used_in_each); ...but nothing in the TBS docs about how to merge different arrays in each block. – David Dodson Oct 31 '17 at 15:36