0 Replies Latest reply: Nov 14, 2012 1:04 PM by Linda Tran RSS

Why Does Export to Excel Break Conditional Formatting?

Linda Tran
Currently Being Moderated

I have a function to conditionally format the background colour of a field based on it's value (using Crystal Reports 2008) much like the way excel does it's "Color Scale" conditional formatting.

 

The report will generate this formatting in preview perfectly, also export to PDF and Word work fine, however when I export it to Excel about 50% of the fields export with black backgrounds instead of their correct colour. I have summarised my function below and attached an example of the export and the preview.

 

Has anyone experienced this issue also? More importantly any ideas on how it can be fixed? Thanks in advance.

 

local Numbervar max := maximum({field});

local Numbervar min := minimum({field});

local numbervar ave_of := Average({field});

local numbervar current_val := {field};

 

ToText(max,"#");

ToText(min,"#");

 

local Numbervar cRed := 0;

local Numbervar cGreen := 0;

local Numbervar cBlue := 0;

 

local Numbervar redR := 248;

local Numbervar redG := 105;

local Numbervar redB := 107;

 

local Numbervar yellowR := 255;

local Numbervar yellowG := 235;

local Numbervar yellowB := 132;

 

local Numbervar greenR := 99;

local Numbervar greenG := 190;

local Numbervar greenB := 132;

 

// Current value percent of maximum.

if (max = 0 or max = min) then

(

    local Numbervar pc := 0;

    local Numbervar midPointPc := 0.5;  

)

else

(

    local Numbervar pc := (current_val - min) / (max-min);

    local Numbervar midPointPc := ave_of/max;

);

 

// If current value less than 50% of maximum scale from red to yellow.

if pc <= midPointPc then

(

pc := pc * (1/midPointPc);

if pc > 1 then pc := 1;

 

cRed := Floor(redR + (yellowR-redR) * pc);

cGreen := Floor(redG + (yellowG-redG) * pc);

cBlue := Floor(redB + (yellowB-redB) * pc);

)

 

// If current value greater than or equal to 50% of maximum scale from yellow to green.

else

(

pc := (pc - midPointPc) * (1 / (1 - midPointPc));

if pc > 1 then pc := 1;

 

cRed := Floor(yellowR + (greenR-yellowR) * pc);

cGreen := Floor(yellowG + (greenG-yellowG) * pc); 

cBlue := Floor(yellowB + (greenB-yellowB) * pc);

);

 

color(cRed, cGreen, cBlue)

Actions