Mega Code Archive
SENDING DATA TO AND FROM EXCEL
<![CDATA[
Title: SENDING DATA TO AND FROM EXCEL
Question: How to exchange data between an Delphi Application and MSExcel
Answer:
With Delphi 5, to exchange data with an Application and Excel can be achieved easily by using the TExcelApplication component, available on the Servers Page of the Component Palette.
By way of example, let us take a Form with a TStringGrid, filled with some data and two Buttons, whose names are To Excel and From Excel. Then, let us put the TExcelApplication Component into this Form, and let us set its Name property to XLApp and its ConnectKind Property to ckNewInstance.
When we want to work with Excel, what we usually do is to open an ExcelApplication, then to open a WorkBook and finally to use a WorkSheet.
So, indubitably, the main object of interest in a real application is the collection of the WorkSheets of a WorkBook. Now, if we look closely on a particular WorkSheet, it is nothing else than a representation of a matrix, and indeed, behind the scene, in the MSExcel Object Model, this matrix is a variant. Hence, it is quite natural to use a variant in Delphi to send or to retrieve data to or from Excel.
Sending data to Excel
This is done in the following procedure :
procedure TForm1.BitBtnToExcelOnClick(Sender: TObject);
var
WorkBk : _WorkBook; // Define a WorkBook
WorkSheet : _WorkSheet; // Define a WorkSheet
I, J, K, R, C : Integer;
IIndex : OleVariant;
TabGrid : Variant;
begin
if GenericStringGrid.Cells[0,1] '' then
begin
IIndex := 1;
R := GenericStringGrid.RowCount;
C := GenericStringGrid.ColCount;
// Create the Variant Array
TabGrid := VarArrayCreate([0,(R - 1),0,(C - 1)],VarOleStr);
I := 0;
// Define the loop for filling in the Variant
repeat
for J := 0 to (C - 1) do
TabGrid[I,J] := GenericStringGrid.Cells[J,I];
Inc(I,1);
until
I (R - 1);
// Connect to the server TExcelApplication
XLApp.Connect;
// Add WorkBooks to the ExcelApplication
XLApp.WorkBooks.Add(xlWBatWorkSheet,0);
// Select the first WorkBook
WorkBk := XLApp.WorkBooks.Item[IIndex];
// Define the first WorkSheet
WorkSheet := WorkBk.WorkSheets.Get_Item(1) as _WorkSheet;
// Assign the Delphi Variant Matrix to the Variant associated with the WorkSheet
Worksheet.Range['A1',Worksheet.Cells.Item[R,C]].Value := TabGrid;
// Customise the WorkSheet
WorkSheet.Name := 'Customers';
Worksheet.Columns.Font.Bold := True;
Worksheet.Columns.HorizontalAlignment := xlRight;
WorkSheet.Columns.ColumnWidth := 14;
// Customise the first entire Column
WorkSheet.Range['A' + IntToStr(1),'A' + IntToStr(R)].Font.Color := clBlue;
WorkSheet.Range['A' + IntToStr(1),'A' + IntToStr(R)].HorizontalAlignment := xlHAlignLeft;
WorkSheet.Range['A' + IntToStr(1),'A' + IntToStr(R)].ColumnWidth := 31;
// Show Excel
XLApp.Visible[0] := True;
// Disconnect the Server
XLApp.Disconnect;
// Unassign the Delphi Variant Matrix
TabGrid := Unassigned;
end;
end;
Sending data from Excel
This is done in the following procedure :
procedure TForm1.BitBtnFromExcelOnClick(Sender: TObject);
var
WorkBk : _WorkBook;
WorkSheet : _WorkSheet;
K, R, X, Y : Integer;
IIndex : OleVariant;
RangeMatrix : Variant;
NomFich : WideString;
begin
NomFich := C:\MyDirectory\NameOfFile.xls;
IIndex := 1;
XLApp.Connect;
// Open the Excel File
XLApp.WorkBooks.Open(NomFich,EmptyParam,EmptyParam,EmptyParam,EmptyParam, EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,0);
WorkBk := XLApp.WorkBooks.Item[IIndex];
WorkSheet := WorkBk.WorkSheets.Get_Item(1) as _WorkSheet;
// In order to know the dimension of the WorkSheet, i.e the number of rows and the
// number of columns, we activate the last non-empty cell of it
WorkSheet.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;
// Get the value of the last row
X := XLApp.ActiveCell.Row;
// Get the value of the last column
Y := XLApp.ActiveCell.Column;
// Define the number of the columns in the TStringGrid
GenericStringGrid.ColCount := Y;
// Assign the Variant associated with the WorkSheet to the Delphi Variant Matrix
RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[X,Y]].Value;
// Quit Excel and Disconnect the Server
XLApp.Quit;
XLApp.Disconnect;
// Define the loop for filling in the TStringGrid
K := 1;
repeat
for R := 1 to Y do
GenericStringGrid.Cells[(R - 1),(K - 1)] := RangeMatrix[K,R];
Inc(K,1);
GenericStringGrid.RowCount := K + 1;
until
K X;
// Unassign the Delphi Variant Matrix
RangeMatrix := Unassigned;
end;
]]>