topic Re: Populate a pre-existing formatting template in Excel in Qlik NPrinting

Populate a pre-existing formatting template in Excel

simondachstr — Wed, 29 Jul 2015 14:00:44 GMT

Hi all,

I'm currently having a go at nPrinting and I have a very straightforward question concerning their capability to build Excel reports.

On my .qvw I have a Pivottable/Straighttable with a fixed number of rows & columns and I want to populate a pre-formatted template within an Excel spreadsheet (See attached).

Populating the spreadsheet works fine, however, nPrinting keeps overriding the existing format template with whatever way the cell containing the <CH28> is formatted. I'm aware there are workarounds to this but I'm wondering if I'm missing something here.

Regards,

Martin

Re: Populate a pre-existing formatting template in Excel

Stephen_Jasionowski — Wed, 29 Jul 2015 14:10:48 GMT

Martin -

Is CH28 a pivot table? If so, this may be the cause as NPrinting does not support pivots.

You may also want to try deselecting "Keep Source Formats" in the properties grid for the object in NPrinting. That should maintain the Excel formatting in your pre-existing template.

See the following: Can I apply Excel formatting to my Excel reports? – Customer Feedback for Vizubi

Re: Populate a pre-existing formatting template in Excel

simondachstr — Wed, 29 Jul 2015 14:14:00 GMT

Hi Stephen,

nPrinting does support Pivots, however you are not able to select any specific columns. This option is only available in straight tables.

The "Keep Source Formats" in the properties removes the initial QlikView formatting, which is fine, but it should inherit the target formatting also, instead of overwritting it.

I have worked through the example you provided, however there you are only able to define the format of the output - I simply want to populate a pre-existing format on the spreadsheet.

Re: Populate a pre-existing formatting template in Excel

Daniel_Jenkins — Wed, 29 Jul 2015 23:08:18 GMT

Hi Martin,

If you RMB on your pivot table chart in your QlikView document and select Send to Excel, you will end up with a straight table in Excel. NPrinting uses the QlikView API so when you use a pivot table in an NPrinting Excel report template, you will again get a straight table. I think this is what Stephen meant by pivot tables not being supported in NPrinting.

To get more control over the formatting, I suggest that you clone your pivot table in your QlikView document and convert the clone to a straight table. When you use that straight table in NPrinting you can use its columns, as you have pointed out in your last post, with your pre-existing formatting. You can then hide the cloned chart in your QVW.

In case someone reading this is not aware, you can also create a pivot table in your NPrinting Excel report template using a straight table as the source: How to Create a Pivot Table from Excel Table Columns – Customer Feedback for Vizubi

- Daniel.

Re: Populate a pre-existing formatting template in Excel

simondachstr — Thu, 30 Jul 2015 08:52:54 GMT

Hi Daniel,

Thank you for your reply. Converting it to a straight table and importing the chart column by column is not an issue (even setting custom rows to import). But again, the output overwrites any pre-existing formatting on the Excel spreadsheet. Instead what I wish to achieve is to simply populate a template with values only.

Let's say as an example, I want to apply custom format to only one row in the output (line 21). The only way I see to be able to achieve this is by:

1. Import the chart

-> Output lines 1-20, normal formatting

2. Import the chart again

-> Output line 21, custom format

3. Import the chart a third time

-> Output lines 22-100, normal formatting

This seems to me to be an overkill just to format one line separately from the others.

Can you advise if this is the right approach.

Thanks,

Martin

Re: Populate a pre-existing formatting template in Excel

Thu, 30 Jul 2015 12:49:51 GMT

Martin,

To achieve this you will need to use Excel's conditional formatting, build a rule which identifies the row(s) which you want to format in a specific way, and apply that rule to all the columns which should have that row formatted in that particular way.

Re: Populate a pre-existing formatting template in Excel

simondachstr — Thu, 30 Jul 2015 14:26:05 GMT

Thanks Aran - have you already tried this?

Re: Populate a pre-existing formatting template in Excel

Andrea_Ghirardello — Thu, 30 Jul 2015 14:37:16 GMT

I think that the best way is to use the "Expand range" property.

You could also combine it with the "Custom rows" property. In this way you should be able to manage any kind of formatting with "fixed" data.

See here: Select Top Ranks from QlikView and Use Them With Custom Excel Formulas – Customer Feedback for Vizubi

Re: Populate a pre-existing formatting template in Excel

simondachstr — Thu, 30 Jul 2015 14:45:49 GMT

See my post from above:

"Let's say as an example, I want to apply custom format to only one row in the output (line 21). The only way I see to be able to achieve this is by:

1. Import the chart

-> Output lines 1-20, normal formatting

2. Import the chart again

-> Output line 21, custom format

3. Import the chart a third time

-> Output lines 22-100, normal formatting

This seems to me to be an overkill just to format one line separately from the others."

Re: Populate a pre-existing formatting template in Excel

Daniel_Jenkins — Thu, 30 Jul 2015 23:42:50 GMT

Hi Martin,

Aran is correct. In the attached sample project I have used a conditional formatting rule to format cells in row 21 to have bold text and a yellow background.

Please extract to C:\Temp\MM to preserve the paths used or you will need to change them in the NSQ.

HTH - Daniel.

Re: Populate a pre-existing formatting template in Excel

simondachstr — Fri, 31 Jul 2015 10:19:31 GMT

A bit tedious work but this could work.

Many thanks for the example.