unit Yindee;
function addzero(s: string; i: integer): string;//เป็น function สำหรับปรับ hn โดยเติม 0 ข้างหน้าให้ตรงตามจำนวนหลักของ รพที่ตั้งไว้ (7 หรือ 9 หลัก)
begin
//result:=s;
while length(s) < i do
begin
s := '0' + s;
end;
result := s;
end;
procedure main;
var
Excel : Variant;
Workbook : Variant;
Worksheet : Variant;
Cells : Variant;
Item : Variant;
X, Y : Integer;
row_count : Integer;
cell_data : string;
hn_int,i:integer;
fhn : string;
fhcode,fhname,fprovince_id : string;
fdatapath:string;
begin
i:=0;
fdatapath:='d:\book1.xls';//path ที่เก็บ excel file ที่ต้องการ
showmessage('Excel import data By Yindee'+#13+
'Data path = '+fdatapath);
if messagedlg('Please confirm import , old data will be replace ?',
mtconfirmation,[mbyes,mbno],0)<>mryes then exit;
Excel := CreateOleObject('Excel.Application');
try
Workbook := Excel.Workbooks.open(fdatapath);
Cells := Excel.Cells;
x:=1;
y:=1;
row_count:=0;
repeat
cell_data := cells.item(y,x);
row_count:=row_count+1;
y:=y+1;
until cell_data='';
Item := Cells.Item(2,2); // row, col
//showmessage(formatdatetime('yyyy-mm-dd',item));
row_count:=row_count-1;
showmessage('Found '+inttostr(row_count-1)+' Records');
for x:=2 to row_count do
begin
i:=i+1;
fhn:=cells.item(x,4); // HN in column number 4
SetStatusLabel('Processing HN '+fhn);
SetProgressBar(x,row_count);
ShowDebugText('Record no.'+inttostr(i)+' import HN '+fhn+'..... OK');
if length(fhn)>0 then
begin
fhcode:=cells.item(x,1);
fhname:=cells.item(x,2);
fprovince_id:=cells.item(x,3);
fcds.close;
fcds.datarequest('select * from zdata where hn="'+fhn+'"');
fcds.open;
fcds.first;
if fcds.recordcount=0 then
fcds.insert
else
fcds.edit;
fcds.fieldbyname('hn').asstring:=addzero(fhn,7);
fcds.fieldbyname('hcode').asstring:=fhcode;
fcds.fieldbyname('hname').asstring:=fhname;
fcds.fieldbyname('province_id').asstring:=fprovince_id;
fcds.post;
fcds.datarequest('select * from zdata where hn="'+fhn+'"');
applyupdate_fcds(-1);
fcds.close;
end;
application.processmessages;
end;
finally
Excel.Quit;
end;
ShowDebugtext('====================================================');
showDebugtext('Import Done.');
end;
end.