Estoy haciendo una pequeña aplicación en Delphi y necesito conectar con un archivo excel. Necesito leer unos campos de una tabla de excel y escribir en otros, asi de simple. Pero no se como conectar con un archivo excel para leer o escribir sobre sus celdas.
1 Respuesta
Respuesta de antipauli
1
1
antipauli, Analista/Programador de Sistemas Oracle
Te recomiendo estas 2 webs sobre automatización, están muy bien: http://www.djpate.freeserve.co.uk/Index.htm http://freespace.virgin.net/graham.marshall/excel.htm#excel.htm En www.delphi3000.com encontrarás también ejemplos de ello. Registrarte es gratuito. Buscando en ella he encontrado lo siguiente: 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;