Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
simondachstr
Luminary Alumni
Luminary Alumni

Populate a pre-existing formatting template in Excel

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


1 Solution

Accepted Solutions
Daniel_Jenkins
Specialist III
Specialist III

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.

View solution in original post

10 Replies
Stephen_Jasionowski

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

simondachstr
Luminary Alumni
Luminary Alumni
Author

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.

Daniel_Jenkins
Specialist III
Specialist III

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.

simondachstr
Luminary Alumni
Luminary Alumni
Author

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

Not applicable

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.

simondachstr
Luminary Alumni
Luminary Alumni
Author


Thanks Aran - have you already tried this?

Andrea_Ghirardello

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 Vizub...

simondachstr
Luminary Alumni
Luminary Alumni
Author

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."

Daniel_Jenkins
Specialist III
Specialist III

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.