MS Office applications How do you manipulate MS Office applications? The Answer is to use TOLEContainer. Problem starts with accessing the correct object and their properties. Some samples are added to Delphi demos, but all of them are targeted at MSWord. Here is a sample or trick code for MSExcel. // procedure is activated when OleOject activates user interface // procedure copies TStringGrid content to an ( OleObject ) Excel sheet
Begin {first we read how many sheets are open in a specified Excel document} Count:=OleContainer1.OleObject.Application.Sheets.Count; {then we read the number of a sheet to witch user wants to add StringGrid content} Curent:=StrToInt(OKBottomDlg.Edit2.Text);
If Curent <> 0 Then If Curent <= Count then // if the sheet with index Curent exist then copy content Begin {first we activate the desiered sheet object} OleContainer1.OleObject.Application.Sheets[Count].Activate; {pass the object to a variant variable} ExcelSheet:=OleContainer1.OleObject.Application.ActiveSheet; {now we can do what ever we like with it} ExcelSheet.Name:=OKBottomDlg.Edit3.Text+IntToStr(Count); For i:=0 To StringGrid1.RowCount Do For j:=0 To StringGrid1.ColCount Do ExcelSheet.Cells(i,j):=StringGrid1.Cells[j,i]; // here we copy the content End else // else if the sheet we are trying to access doesn't exsist Begin // we add new sheets untill the requested // user's index is reached ( curent variable ) For i:=Count+1 To Curent do OleContainer1.OleObject.Application.Sheets.Add; // again we do as above OleContainer1.OleObject.Application.Sheets[Curent].Activate; ExcelSheet:=OleContainer1.OleObject.Application.ActiveSheet; ExcelSheet.Name:=OKBottomDlg.Edit3.Text+IntToStr(Count); For i:=0 To StringGrid1.RowCount Do For j:=0 To StringGrid1.ColCount Do ExcelSheet.Cells(i,j):=StringGrid1.Cells[j,i]; End; end; Now MSExcel has objects: Application - Through which we can reach menus, toolbars and all of its commands Workbooks - Is something like an array of all workbooks in a document you can use it to manipulate passwords of a document, sharing etc.. Workbook is an elemet of object specefied above Sheet(s) is a general object used to manipulate actual data. Worksheet(s) is something like above, but will raise an exception if doesn't exsist ( for instance if document contains graph sheet and you would try to access it through Worksheets or Worksheet object ) while Sheet object can be used on all types of sheets in MSExcel
this is a simple example:procedure TForm1.Button1Click(Sender: TObject); var xlApp, Sheet: Variant; begin xlApp := OleContainer1.OleObject.Application; Sheet := xlApp.WorkBooks[1].WorkSheets[1]; Sheet.Cells[1, 1] := '1234'; Sheet.Range['A1:C10'].Font.Bold := True; end;
How do you manipulate MS Office applications? The Answer is to use TOLEContainer. Problem starts with accessing the correct object and their properties. Some samples are added to Delphi demos, but all of them are targeted at MSWord. Here is a sample or trick code for MSExcel.
// procedure is activated when OleOject activates user interface
// procedure copies TStringGrid content to an ( OleObject ) Excel sheet
procedure TForm1.OleContainer1Activate(Sender: TObject);
var ExcelSheet : Variant;
Count, Curent : Variant;
i,j : Integer;
Begin
{first we read how many sheets are open in a specified
Excel document}
Count:=OleContainer1.OleObject.Application.Sheets.Count;
{then we read the number of a sheet to witch user wants
to add StringGrid content}
Curent:=StrToInt(OKBottomDlg.Edit2.Text);
If Curent <> 0 Then
If Curent <= Count then
// if the sheet with index Curent exist then copy content
Begin
{first we activate the desiered sheet object}
OleContainer1.OleObject.Application.Sheets[Count].Activate;
{pass the object to a variant variable}
ExcelSheet:=OleContainer1.OleObject.Application.ActiveSheet;
{now we can do what ever we like with it}
ExcelSheet.Name:=OKBottomDlg.Edit3.Text+IntToStr(Count);
For i:=0 To StringGrid1.RowCount Do
For j:=0 To StringGrid1.ColCount Do
ExcelSheet.Cells(i,j):=StringGrid1.Cells[j,i];
// here we copy the content
End
else // else if the sheet we are trying to access doesn't exsist
Begin
// we add new sheets untill the requested
// user's index is reached ( curent variable )
For i:=Count+1 To Curent do
OleContainer1.OleObject.Application.Sheets.Add;
// again we do as above
OleContainer1.OleObject.Application.Sheets[Curent].Activate;
ExcelSheet:=OleContainer1.OleObject.Application.ActiveSheet;
ExcelSheet.Name:=OKBottomDlg.Edit3.Text+IntToStr(Count);
For i:=0 To StringGrid1.RowCount Do
For j:=0 To StringGrid1.ColCount Do
ExcelSheet.Cells(i,j):=StringGrid1.Cells[j,i];
End;
end; Now MSExcel has objects:
Application - Through which we can reach menus, toolbars and all of its commands
Workbooks - Is something like an array of all workbooks in a document you can use it to manipulate passwords of a document, sharing etc..
Workbook is an elemet of object specefied above
Sheet(s) is a general object used to manipulate actual data.
Worksheet(s) is something like above, but will raise an exception if doesn't exsist ( for instance if document contains graph sheet and you would try to access it through Worksheets or Worksheet object ) while Sheet object can be used on all types of sheets in MSExcel
var
xlApp, Sheet: Variant;
begin
xlApp := OleContainer1.OleObject.Application;
Sheet := xlApp.WorkBooks[1].WorkSheets[1];
Sheet.Cells[1, 1] := '1234';
Sheet.Range['A1:C10'].Font.Bold := True;
end;