Title: Import Data from excel
Question: How To import data from excel with delphi?
Answer:
How To import data from excel with delphi?
Before import data from excel , be sure that tour field is the same as with field in file excel. Fields in excel mean the columns. we need unit ComObj to call excel object in delphi.
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComObj, Grids, StdCtrls, ExtCtrls, DBCtrls, DBGrids, DB,
DBTables;
type
TForm1 = class(TForm)
Button1: TButton;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
Table1: TTable;
Table1Sandibank: TStringField;
Table1NamaBank: TStringField;
Table1SandiKBI: TStringField;
procedure Button1Click(Sender: TObject);
function import(tab: Ttable; SFile: string): Boolean;
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
function TForm1.import(tab: Ttable; SFile: string): Boolean;
const
xlCellTypeLastCell = $0000000B;
var
XLApp, Sheet: OLEVariant;
x, y, r: Integer;
begin
Result := False;
XLApp := CreateOleObject(Excel.Application);
try
XLApp.Visible := False;
XLApp.Workbooks.Open(SFile); //open file
Sheet := XLApp.Workbooks[ExtractFileName(SFile)].WorkSheets[1];
Sheet.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate;
x := XLApp.ActiveCell.Row;
y := XLApp.ActiveCell.Column;
x := 2; //number of row in excel start import
repeat
tab.Append;
for r := 1 to y do
begin
tab.Fields[r-1].AsString:= XLApp.Cells.Item[x, r].Value;
end;
r:=1; // dont remove this value, to keep value columns
tab.Post;
Inc(x, 1);
until XLApp.Cells.Item[x, r].Value=;
finally
if not VarIsEmpty(XLApp) then
begin
XLApp.Quit;
XLAPP := Unassigned;
Sheet := Unassigned;
Result := True;
end;
end;
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
if import(table1, C:\huda\huda.xls) then
ShowMessage(C:\huda\huda.xls has been imported!);
end;
end.