Batch Publishing: Output Excel using template files?

OliverB · March 9, 2015 1:00PM

Hi,

I am trying to create a set of Excel reports, each containing multiple sheets. The reports need to be formatted in a certain way (i.e. date formats, header rows shading etc)

I can do this using the data file output in DataManager, just by writing to a blank Excel file that already exists and has formatting applied. However, I have about 100 Excel reports, each with 6 worksheets, so this method will be very time consuming to set up and maintain.

I have tried to set this up using a batch output. I am able to actually create the raw data files, but these do not have the formatting that I need. I have created a template Excel file (I've tried .xls and .xlsx) which has all of the formatting applied. However, when I point the batch config to this template file and set the "Preserve template formatting and formulas" to 'true', this does not have any effect?

I have also tried to create a file with the same name as the output, the same way as I would do this using DataManager, but this doesn't work either, the existing worksheets are just totally overwritten.

Is there a way for me to accomplish this without having to create this all manually in DataManager?

chris · March 9, 2015 1:21PM

Hi,

What version of Omniscope are you using?

OliverB · March 9, 2015 1:32PM

Hi Chris,

2.9-rc[Hotfix_ContentViewAndChromiumUpdate] b1556 (22-Jan-2015 18:58:00)

chris · March 9, 2015 1:38PM

Hi,

Thanks for letting us know. We will investigate this and get back to you shortly.

chris · March 11, 2015 7:10AM

Hi Oliver,

The option: "Preserve template formatting and formulas" is only applicable for outputs that use a template file (Screenshot, PDF, PowerPoint).

There is currently no way to preserve the Excel worksheet formatting using the batch configuration output, however it is possible to do this in the Excel output.

Can you try the following:

Configure a single report in DataManager and connect to a "Data file output" block. For publish method select "Overwrite data". This should do what you need.

If you can confirm this works we can add this option to the batch configuration output.

Regards

Chris

OliverB · March 11, 2015 7:20AM

Hi Chris,

Yes, I have already tried the data output block in data manager and this does work. The problem is that I will need 600 output blocks to create this, which is why I am trying to use a batch output. It would be great if the batch output had the same options as the data manager output block.

chris · March 11, 2015 1:14PM

Hi Oliver,

We will add this feature to the batch output in the next couple of days. I'll let you know when it's available and provide you with a link to test it before we integrate it into Omniscope 2.9.

chris · March 19, 2015 6:36AM

Hi,

Appologies for the delay. We have now implemented this fix in a separate Omniscope 2.9 release. I'll email you the link to this shortly. In this release we have added "Excel publish method" to the batch configuration file. If you create a new configuration file you should see this column appear along with usage instructions. You can also add this to any existing batch configuration files. You can use one of the following settings:

"Replace workbook" to create a new workbook every time.
"Replace worksheet" to create a new worksheet every time.
"Replace data" to delete all the data in the worksheet and replace the data, preserving any existing formatting.
"Overwrite data" to overwrite the data, preserving formatting and any existing data outside the overwritten data.

After we have sent you this link please test this feature and let us know if it resolves your problems. Once you have confirmed that it does fix these problems we will look to integrate it into the main Omniscope release in the next 1-2 weeks, however in the meantime you can continue to use the private release that we sent you.

Please let me know if you have any questions.

Chris

OliverB · March 23, 2015 8:13AM

Hi Chris, I havent received a download link yet. Is this on its way?

Welcome!

Categories

Ideas Parade

Tagged