What's on my spreadsheet?
The ability for separate applications to
‘communicate and interact’ is well, neat. I have recently found and increasing need to create/manipulate some pretty involved
Microsoft® Excel spreadsheets from a
Delphi® application. The drive for this post is to show some simple sample code lines to accomplish tasks within Excel.
Besides searching
newsgroups you can also search
MSDN for additional commands. I have found the simplest way is to record a macro and the use the Visual Basic Editor to see the code commands created in the macro.
Delphi® includes
Microsoft® Office® server controls. Keep in mind that these controls work with a specific version of Office. If you need to target additional versions, I have found variants to work well for manually creating the right version, for example:
uses
ComObj;
var
ExcelApplication: Variant;
ExcelApplication := CreateOleObject('Excel.Application.7');
ExcelApplication := CreateOleObject('Excel.Application.8');
ExcelApplication := CreateOleObject('Excel.Application.9');
For the most part I generally drop a
TExcelApplication control onto a form and go from there. I guess you could also use
TExcelWorkbook and
TExcelworksheet components as well, but I prefer to manually create those. To create a new Workbook and Worksheet:
var
ExcelWorkBook1: TExcelWorkbook;
ExcelWorkSheet1: TExcelWorksheet;
lcid: integer;
begin
lcid := GetUserDefaultLCID;
ExcelApplication1.Visible[lcid] := true;
ExcelWorkBook1 := TExcelWorkBook.Create(ExcelApplication1);
ExcelWorkBook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam, lcid));
ExcelWorksheet1 := TExcelWorksheet.Create(ExcelApplication1);
{The next line creates a new worksheet in a workbook}
ExcelWorkbook1.Sheets.Add(EmptyParam, EmptyParam, EmptyParam, EmptyParam, LCID);
{The next line attaches to the active worksheet in the workbook}
ExcelWorkSheet1.ConnectTo(ExcelWorkbook1.ActiveSheet as _Worksheet);
ExcelWorkSheet1.Name := 'My Sheet';
end;
The following are additional syntax examples:
var
{r and c are the row and column indexes}
r, c: Integer;
begin
{Freeze panes}
ExcelApplication1.ActiveWindow.FreezePanes:= true;
{Select range and merge cells}
ExcelWorkSheet1.Range[Excelworksheet1.Cells.Item[r-1,c-2], Excelworksheet1.Cells.Item[r,c]].Merge(true);
{Autofit a column}
Excelworksheet1.Cells.Item[r,c].EntireColumn.AutoFit;
{Hide column}
Excelworksheet1.Cells.Item[r,c].EntireColumn.Hidden:= True;
{Format cells}
{I typically never use with statements, in fact I despise them. I generally only use them when I am working with Excel cells. The following all don’t need to be used together, they are meant to be used as examples to pick and choose from.}
with Excelworksheet1.Cells do
begin
Item[r,c].HorizontalAlignment:= xlRight;
Item[r,c].Value:= 'somevalue';
Item[r,c].Font.Bold:= False;
Item[r,c].Font.Underline:= False;
Item[r,c].Font.Size:= 8;
Item[r,c].Font.ColorIndex:= 2;
Item[r,c].Interior.ColorIndex:= 1;
Item[r,c].NumberFormat:= '#,##0.00_);[Red](#,##0.00);-';
end;
{Add a formula to a cell, the cell ranges are offset from the current cell }
Excelworksheet1.Cells.Item[r,c].FormulaR1C1:= ‘=SUM(R[-12]C:R[-1]C)';
{Open a preexisting file}
ExcelApplication1.Workbooks.Add(filename,0);
ExcelWorksheet1.Free;
ExcelWorkBook1.Free;
end;
This sample was merely intended to give some general ideas and hopefully spark interest in working with
Excel worksheets from within a
Delphi® application. There seems to be an endless array of commands that you can use to communicate with a spreadsheet.
Labels: Code, Delphi, WIN32
AddrMon
October 2005
