CODEWITHSUNDEEP
exceldelphicom
user1009073
user1009073

Reputation: 3238

Delphi - Setting Excel Cell Background Color Gradient

Delphi Seattle, Excel 2013. I need to set a cell's background to be a gradient. I can set the background color if it is a single color, but I cannot get the syntax right for gradients. Part of the challenge is that the Gradient for a cell is an IDispatch. The following code will set a single background color.

    procedure TForm1.GradientTestClick(Sender: TObject);
var
  oExcel : ExcelApplication;
  RawDataSheet :_Worksheet;
  ThisCell : ExcelRange;
begin
    oExcel := CreateOleObject('Excel.Application') as ExcelApplication;
    oExcel.Visible[LOCALE_USER_DEFAULT] := True;

   // Add a New Workbook, with a single sheet
   oExcel.Workbooks.Add(EmptyParam, LOCALE_USER_DEFAULT);
   // Get the handle to the active Sheet, and insert some dummy data
   RawDataSheet :=  oExcel.ActiveSheet as _Worksheet;
   ThisCell := RawDataSheet.Range['A1', EmptyParam];
   ThisCell.Value2 := 10;


   // To set ONE Color
   ThisCell.Interior.Pattern := xlSolid;
   ThisCell.Interior.ColorIndex := 3;

   // To Set Gradient...


end;

When I record an EXCEL Macro setting the gradiant I want (Linear, 2 color, Green to Yellow), the macro is

Sub Macro1()
'
' Macro1 Macro
'

'
    With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 0
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .Color = 5296274
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .Color = 65535
        .TintAndShade = 0
    End With
End Sub

What I should be able to do in Delphi is ...

  ThisCell.Interior.Pattern := xlPatternLinearGradient;
  ThisCell.Interior.Gradient.Degree := 0;
  ThisCell.Interior.Gradient.ColorStops.Clear;
  ThisCell.Interior.Gradient.ColorStops.Add[0].Color := 5296274;
  ThisCell.Interior.Gradient.ColorStops.Add[1].Color := 65535;

My challenge is that ThisCell.Interior.Gradient is an IDispatch. How do I set the other "sub-properties" such as Degree and Colorstops?

Thanks

Upvotes: 1

Views: 3332

Answers (1)

Sertac Akyuz
Sertac Akyuz

Reputation: 54812

Use late binding to access methods/properties on the IDispatch interface.

  ...
  Gradient: OleVariant;
begin
   ....

   // To Set Gradient...

  ThisCell.Interior.Pattern := xlPatternLinearGradient;
  Gradient := ThisCell.Interior.Gradient;
  Gradient.Degree := 45;
  Gradient.ColorStops.Clear;
  Gradient.ColorStops.Add(0).Color := 5296274;
  Gradient.ColorStops.Add(1).Color := 65535;

Upvotes: 2

Related Questions