Highlighting cells in Excel based on their absolute value via Delphi

As part of the management program which is an ever continuing project for the Occupational Psychologist, there is one module which compares values (obtained from our flagship program) from one customer/settlement with those obtained from another customer. In statistical terms, this is known as 'comparison of two means'. Those interested in the statistics of the subject can find an explanation here. For those interested, if the absolute value of t is greater than 1.96 than the difference is statistically significant at the 95% level, and if the absolute value is greater than 2.576, then the difference is statistically significant at the 99% level.

After I wrote the code to obtain the correct values (surprisingly hard at first), I decided that it would nice if the statistically significant values were displayed in different colours from normal values. In Delphi, the values are displayed in a grid which obtains its values from a clientdataset, so this code isn't particularly difficult:

procedure TTwoCustQ400.grMaleDrawColumnCell(Sender: TObject; 
          const Rect: TRect; DataCol: Integer; 
          Column: TColumn; State: TGridDrawState);
var
 avalue: single;

begin
...
 with sender as TDBGrid do
  begin
   if avalue >= 2.576 then canvas.font.color:= clRed
   else if avalue >= 1.96 then canvas.font.color:= clGreen
   else canvas.font.color:= clNavy;
   DefaultDrawColumnCell (Rect, DataCol, Column, State);
  end;
end;

The only complication in this code is obtaining 'avalue' from the clientdataset, but that's just fiddly. Of course, code was added to export the data displayed on the screen to Excel. This is easy now that I have library code which does this - as far as this module is concerned, it only needs to make one procedure call and that's it. The real complications began when the OP wanted the values in Excel to be coloured as they are in the grid. This isn't something with which I've had much experience, so it required a few false starts.

First of all, the naive version:

Procedure ColourCell (xrow, xcol: word);
 var
  avalue: single;
  col: word;

 begin
  avalue:= abs (strtofloat (XLApp.ActiveSheet.Cells[xrow, xcol].value));
  if avalue >= 1.96 then
   begin
    col:= 6;
    if avalue >= 2.576 then col:= 3;
    XLApp.ActiveSheet.Cells[xrow, xcol].interior.ColorIndex:= col;
   end;
 end;

 for i:= 2 to max do   
  begin
   ColourCell (i, 6);
   ColourCell (i, 10);
   ColourCell (i, 12);
  end;

It took me a while to realise that I didn't need to obtain the value of 'max' - the number of rows in the spreadsheet -from the spreadsheet itself; as I was creating the sheet from a csv file, I could obtain the number directly from the stringlist from which the file was created.

Whilst this code works, it is theoretically slow as it requires the xlApp instance to be accessed twice for every cell; as three columns have to be checked, this means that there are many accesses. Fortunately there are only about 40 rows in the spreadsheet so the inefficiency wasn't too apparent.

My researches showed that Excel has a conditional formatting function of its own; I could use this function to create a macro which I could then translate into Delphi. This site showed some interesting code, but it wasn't overly clear and it contained only two conditions.

RangeString := 'B'+inttostr(Num1+1)+':M'+inttostr(Num1+1);
Ranger := ws.range[rangestring];
ranger.FormatConditions.Delete;
ranger.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4)); 
ranger.FormatConditions[1].Interior.ColorIndex:= 7;
ranger.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
ranger.FormatConditions[2].Interior.ColorIndex:= 3;

When I created a macro in Excel using conditional formatting, the above code became clearer. I discovered that in Excel I could create three different conditions - but I needed four. This is why I initially abandoned using a 'native' approach in favour of the naive approach (the difference is only 't', which ironically is the value that the statistics code calculates - never mind if you don't get the joke).

When walking the dog - always a good time for thinking about programming problems whose solution elude me when facing the screen - it occurred to me that I don't have four conditions (less than -2.576, less than -1.96, greater than 1.96, greater than 2.576) but rather two conditions: abs (x) greater than 1.96 and abs (x) greater than 2.576. Once I realised this, I tried creating such a function with the conditional formatter in Excel - success! I also decided that instead of painting each cell's background in an appropriate colour (that's the 'interior' part in the above code), I could keep the same background (important when printing) and simply change the font's colour.

One more improvement: instead of calling the function three times for three non-sequential columns, I could create one range containing all three columns. Here is the fast, native version:

ranger:= sheet.range['F:F,J:J,L:L'];
ranger.FormatConditions.Delete;
ranger.FormatConditions.Add (XLExpression, EmptyParam, '= ABS(A1) > 2.576');
ranger.FormatConditions[1].Font.ColorIndex:= 3;
ranger.FormatConditions.Add (XLExpression, EmptyParam,'= ABS(A1) > 1.96');
ranger.FormatConditions[2].Font.ColorIndex:= 6;

3 is the code for red, 6 for green and 7 for yellow. A yellow font made the numbers almost invisible which is why I changed the colour values.