topic Re: Conditional formatting not working from Excel to PDF in Qlik NPrinting

Conditional formatting not working from Excel to PDF

lauranvh — Wed, 25 Sep 2019 18:40:54 GMT

I found a weird bug when using Excel conditional formatting.

I made a Excel report that is transformed to a PDF document.

When i preview the document in Excel the conditional formatting is used and shown

It's a simple conditional formatting rule. When the data date is smaller than date today than the cell turns red.

But a problem arises when I preview it to PDF

The complete conditional formatting isn't shown anymore.

This also comes out this way when I start the task and output the report.

Anybody with the same problem, who can help me with a solution?

Re: Conditional formatting not working from Excel to PDF

Chanty4u — Thu, 16 Nov 2017 13:14:19 GMT

in left side panel

select the table right clik properties and uncheck the option keep source format and preview

Re: Conditional formatting not working from Excel to PDF

lauranvh — Thu, 16 Nov 2017 13:19:23 GMT

Thank you for the quick reaction.

The option was already unchecked. I used my own formatting on this column and the table

Re: Conditional formatting not working from Excel to PDF

Chanty4u — Thu, 16 Nov 2017 13:22:51 GMT

is it working for excel report?

Re: Conditional formatting not working from Excel to PDF

lauranvh — Thu, 16 Nov 2017 13:24:48 GMT

Yes. The second screenshot in my startpost shows the Excel Preview and that works.

Re: Conditional formatting not working from Excel to PDF

Chanty4u — Thu, 16 Nov 2017 13:27:09 GMT

yes i worked in excel its working fine am not sure about pdf lech_miszkiewicz‌ will help you on this

Re: Conditional formatting not working from Excel to PDF

Lech_Miszkiewicz — Thu, 16 Nov 2017 20:44:43 GMT

PDFs from Excel are basicly Prints of Excel for the user settings running NPrinting Engine. One thing i can think of is that you have some printing settings where for example:

your pdf printer is printing in black and white
or in your excel when printing colors are changed to patterns etc...

I suggest you do following to test my suspisions:

RDP to Enprinting Machine
Generate report in XLS format
open it on NPrinting Box and go to File -> Print -> Bullzip PDF printer
- see how the print looks like. If it is similar to what you are getting when previewing in PDF format thane you have an answer what you need to change

cheers

Lech

Re: Conditional formatting not working from Excel to PDF

Lech_Miszkiewicz — Sun, 19 Nov 2017 23:04:15 GMT

Hi Lauran - any updates regarding this?

Re: Conditional formatting not working from Excel to PDF

lauranvh — Mon, 20 Nov 2017 06:43:31 GMT

I do have a difference when i change the workflow.

When i Preview the document to XLSX and then print it with the Qlik NPrinting PDF Printer. I do get the conditional formatting but i also get watermarks in all of the corners.

When i Preview the document directly to PDF. The conditional formatting doesn't work.

I am very certain the conditional formatting just doesn't work when i directly convert to PDF. Let me give you an example:

In the report I have conditional formatting with traffic lights that turn red when i go over time, Orange when I'm 5 days within today's date and Green when i still have time.

This is a preview in Xlsx

This is the preview when i preview to PDF

All the traffic lights are green.

What could be the next step to check?

I don't have the Bullzip PDF printer only the Qlik NPrinting PDF Printer

Re: Conditional formatting not working from Excel to PDF

Lech_Miszkiewicz — Mon, 20 Nov 2017 22:18:38 GMT

Hi,

strange - how do you calculate your conditional formatting? Does your report have to refresh something (for example recalculate data "on open"?

keep in mind that NPrinting PDF is a PRINT to PDF of you Excel so any actions "On Open, Macros etc" will not work.

regarding Bullzip printer - that is OK as you have NPrinting Printer. Make sure you check its settings when logged in as a NPrintng service account - as this is a user specific setting belonging to a user profile.

Re: Conditional formatting not working from Excel to PDF

lauranvh — Mon, 18 Dec 2017 15:22:14 GMT

A very late reaction but I just fixed this problem.

I used the NUMBERVALUE function in the conditional formatting rule. I changed it to the VALUE function and that works when i preview to PDF.

The precise conditional formatting rule is: VALUE(TODAY())

Very weird problem but it works.