Tutorial How To Connect Excel Server From Scratch VBA By Delphi

Case 1.

From Component Palete/panel, drag from office's server tab, components;
TExcelApplication as oXL,
TExelWorkbooks as oWB,
TExcelWorksheet as oSheet

on the declaration section, declare these procedures as public

 public
    procedure conXL;
    procedure disconXL;
    procedure bindXL;

procedure bindXL;
begin
 oXL.Workbooks.Add (EmptyParam, lcid);
 oWB.ConnectTo (oXL.ActiveWorkbook);
 oSheet.ConnectTo (oWB.Worksheets [1] as _Worksheet);
end;

procedure disconXL;
begin
 oXL.ScreenUpdating[lcid] := True;

 oSheet.Disconnect;
 oWB.Disconnect;
 oXL.Disconnect;
end;

procedure conXL;
begin
 oXL.ConnectKind:=ckRunningOrNew;
 oXL.Connect;
 lcid := GetUserDefaultLCID;
 oXL.Visible[lcid]:=True;
end;


the call it on implementation section, such as this procedure below for example;

procedure TmnuFRM.prnCOL;
var
 //... declare variables to be use later
 xrow:integer;
 npos:integer;
 
begin

 //... declared variables initiation/assigning value

 //... call excel server object
 conXL;

 //... make a binding to server object
 bindXL;


 //... form header
 oSheet.Range['A1','A1'].Value:='HEADER LINE 1';
 oSheet.Range['A2','A2'].Value:='HEADER LINE 2';
 oSheet.RAnge['A1','A2'].Font.Bold:=true;


 xrow:=5;
 npos:=xrow;
 oSheet.Range['A'+trim(intToStr(xrow)),'A'+trim(intToStr(xrow))].Value:='Category';
 oSheet.Range['B'+trim(intToStr(xrow)),'B'+trim(intToStr(xrow))].Value:='Class';
 oSheet.Range['C'+trim(intToStr(xrow)),'C'+trim(intToStr(xrow))].Value:='This Year';
 oSheet.Range['E'+trim(intToStr(xrow)),'E'+trim(intToStr(xrow))].Value:='Last Year';
 inc(xrow);
 oSheet.Range['C'+trim(intToStr(xrow)),'C'+trim(intToStr(xrow))].Value:='Qty';
 oSheet.Range['D'+trim(intToStr(xrow)),'D'+trim(intToStr(xrow))].Value:='Sales';
 oSheet.Range['E'+trim(intToStr(xrow)),'E'+trim(intToStr(xrow))].Value:='Qty';
 oSheet.Range['F'+trim(intToStr(xrow)),'F'+trim(intToStr(xrow))].Value:='Sales';

 //format ['A5','F6']
 with oSheet.Range['A'+trim(intToStr(npos)),'F'+trim(intToStr(xrow))] do
 begin
   Select;
   Font.Bold := True;

   With Borders[xlEdgeLeft] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   end;

   With Borders[xlEdgeTop] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;

   With Borders[xlEdgeBottom] do
   begin
     LineStyle := xlDouble;
     Weight := xlThick;
     ColorIndex := xlAutomatic;
   End;

   With Borders[xlEdgeRight] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
   With Borders[xlInsideVertical] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
 end;

 with oSheet.Range['C'+trim(intToStr(npos)),'F'+trim(intToStr(npos))] do
 begin
    HorizontalAlignment := xlCenter;
    VerticalAlignment := xlBottom;
 end;
 oSheet.Range['C'+trim(intToStr(npos)),'D'+trim(intToStr(npos))].MergeCells:=true;
 oSheet.Range['E'+trim(intToStr(npos)),'F'+trim(intToStr(npos))].MergeCells:=true;
 with oSheet.Range['C'+trim(intToStr(npos)),'F'+trim(intToStr(npos))] do
 begin
   Select;
   With Borders[xlEdgeLeft] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
   With Borders[xlEdgeTop] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
   With Borders[xlEdgeBottom] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
   With Borders[xlEdgeRight] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
   With Borders[xlInsideVertical] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
end;

with oSheet.Range['A'+trim(intToStr(npos)),'F'+trim(intToStr(xrow))] do
begin
  Select;
  With Interior do
  begin
    ColorIndex := 15;
    Pattern    := xlSolid;
  End;
end;

inc(xrow);
npos:=xrow;

....some code and calculation here....


//format rows
with oSheet.Range['A'+trim(intToStr(npos)),'F'+trim(intToStr(xrow))] do
begin
  Select;
  With Borders[xlEdgeLeft] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeTop] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeBottom] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeRight] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlInsideVertical] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlInsideHorizontal] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
end;

//display total
inc(xrow);

oSheet.Range['A'+trim(intToStr(xrow)),'A'+trim(intToStr(xrow))].Value:='Ttl Class';
xcol:=ord('A')+1;
for x:=1 to ttlLST.Count do
begin
  colTMP:=chr(xcol+x-1)+trim(intToStr(xrow));
  ttlLST.Strings[x-1]:='0';
end;
with oSheet.Range['C'+trim(intToStr(xrow)),'F'+trim(intToStr(xrow))] do
begin
  FormulaR1C1:='=SUM(R[-'+trim(intToSTr(xrow-npos-1))+']C:R[-1]C)';
end;

//format total
with oSheet.Range['A'+trim(intToStr(xrow)),'F'+trim(intToStr(xrow))] do
begin
  Select;
  HorizontalAlignment := xlRight;
  VerticalAlignment   := xlBottom;
  WrapText            := False;
  Orientation         := 0;
  AddIndent           := False;
  IndentLevel         := 0;
  ShrinkToFit         := False;
  ReadingOrder        := xlContext;
  MergeCells          := False;
  Font.Bold           := True;
  With Interior do
  begin
    ColorIndex        := 15;
    Pattern           := xlSolid;
  End;
end;

with oSheet.Range['C'+trim(intToStr(xrow)),'F'+trim(intToStr(xrow))] do
begin
  Select;
  With Borders[xlEdgeLeft] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeTop] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeBottom] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeRight] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlInsideVertical] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
end;

//... disconnect excel
disconXL;


end;
that's it. you will get an excel window with a worksheet formated.