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.
