procedure TF_Main.ToolButtonExcelClick(Sender: TObject);
var
OpenDialog:TOpenDialog;
iCount:integer;
begin
if ShowIYN('æ¯å¦ççè¦å¯¼å
¥Excelæ°æ®,è¦ççæ°æ®å°è¢«å é¤?')<>IDCANCEL then
try
OpenDialog:=TOpenDialog.Create(Self);
OpenDialog.DefaultExt := 'xls';
OpenDialog.Filter := 'Microsoft Excel æ件 (*.xls)|*.xls';
OpenDialog.Execute;
if OpenDialog.FileName<>'' then
try
Screen.Cursor:=crSqlWait;
ADOConExcel.Close;
ADOConExcel.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='
+OpenDialog.FileName+';Extended
Properties=Excel 8.0;Persist Security Info=False';
ADOConExcel.Open;
with ADOQryExcel do
begin
Close;
Sql.Clear;
Sql.Add('SELECT ææç¼å·,ç¾å¹³ç±³ç¨é,é¢ç®ä»·,æä½ä»· FROM
[Sheet1$]');
Prepared:=true;
Open;
end;
iCount:=ADOQryExcel.RecordCount;
ADOConExcel.BeginTrans;
with F_DataModule.ADOCom do
begin
CommandText:='DELETE FROM MatPriCal WHERE
cYearMonth='+''''+FormatDateTime('yyyymm',DateTimePicker.DateTime)+'''';
Execute;
end;
ADOQryExcel.First;
while not ADOQryExcel.Eof do
begin
with F_DataModule.ADOCom do
begin
CommandText:='INSERT INTO MatPriCal
VALUES('+''''+FormatDateTime('yyyymm',DateTimePicker.DateTime)+''''+','+''
''+Trim(ADOQryExcel.FieldByName('ææç¼å·
').AsString)+''''+','+Trim(ADOQryExcel.FieldByName('ç¾å¹³ç±³ç¨é
').AsString)+','+Trim(ADOQryExcel.FieldByName('é¢ç®ä»·
').AsString)+','+Trim(ADOQryExcel.FieldByName('æä½ä»·').AsString)+')';
Execute;
end;
ADOQryExcel.Next;
F_Main.StatusBar.Refresh;
F_Main.StatusBar.Panels[2].text:='å½åæ°
:'+IntToStr(ADOQryExcel.RecNo)+'(å
±'+IntToStr(iCount)+'æ¡)';
end;
ADOConExcel.CommitTrans;
ShowWIE(2,'导å
¥æå!');
except
ADOConExcel.RollbackTrans;
ShowWIE(2,'导å
¥å¤±è´¥!');
end;
finally
ADOQryExcel.Close;
ADOConExcel.Close;
OpenDialog.Free;
Screen.Cursor:=crDefault;
F_Main.StatusBar.Panels[2].Text:='';
end;
end;
æç¨sql èªå¸¦çopenrowset,åèç¨åº
if ShowIYN('æ¯å¦ççè¦å¯¼å
¥Excelæ°æ®?')<>IDCANCEL then
try
ADOConInfoExcel.Open;
OpenDialog:=TOpenDialog.Create(Self);
OpenDialog.DefaultExt := 'xls';
OpenDialog.Filter := 'Microsoft Excel æ件 (*.xls)|*.xls';
OpenDialog.Execute;
if OpenDialog.FileName<>'' then
try
with ADOQryInfoExcel do
begin
Close;
Sql.Clear;
Sql.Add(' SELECT TOP 1 * FROM OPENROWSET');
Sql.Add('(');
Sql.Add('''MSDASQL.1''');
Sql.Add(',');
Sql.Add('''DRIVER=Microsoft Excel Driver (*.xls);');
Sql.Add('DBQ='+OpenDialog.FileName+'''');
Sql.Add(',');
Sql.Add('''SELECT * FROM [Sheet1$]''');
Sql.Add(')');
Open;
end;
if Copy(ADOQryInfoExcel.Fields[2].AsString,1,4)<>mUser.UserArea
then
begin
ShowWIE(1,'ä¸è½å¯¼å
¥å
¶å®å°åºæ°æ®!');
ADOQryInfoExcel.Close;
ADOConInfoExcel.Close;
exit;
end;
//ADOConInfoExcel.BeginTrans;
with ADOComInfoExcel do
begin
CommandText:=' UPDATE C';
CommandText:=CommandText+' SET C.MNo=A.ç±»å«ç¼ç ,C.MName=A.ææå称
,C.spec=A.ææè§æ ¼,C.unit=A.ææåä½,C.Factory=A.ç产å家,C.TradeMark=A.å
ç,C.Price=A.ææåä»·,C.pingyin=A.æææ¼é³,C.hs=A.æ¢ç®ç³»æ° FROM
InfoPrice'+mUser.UserArea;
CommandText:=CommandText+' C, OPENROWSET';
CommandText:=CommandText+'(';
CommandText:=CommandText+'''MSDASQL.1''';
CommandText:=CommandText+',';
CommandText:=CommandText+'''DRIVER=Microsoft Excel Driver
(*.xls);';
CommandText:=CommandText+'DBQ='+OpenDialog.FileName+'''';
CommandText:=CommandText+',';
CommandText:=CommandText+'''SELECT * FROM [Sheet1$]''';
CommandText:=CommandText+') A WHERE C.RDate=A.åå¸æ¥æ AND
C.RNO=A.åå¸ç¼å· AND C.Area=A.å°åºç¼ç ';
Execute;
CommandText:=' INSERT INTO InfoPrice'+mUser.UserArea;
CommandText:=CommandText+'(RDate,RNO,Area,MNo,MName,spec,unit,Factory,Trad
eMark,Price,pingyin,hs) ';
CommandText:=CommandText+' SELECT åå¸æ¥æ,åå¸ç¼å·,å°åºç¼ç ,ç±»å«
ç¼ç ,ææå称,ææè§æ ¼,ææåä½,ç产å家,åç,ææåä»·,æææ¼é³,æ¢ç®ç³»æ°
FROM OPENROWSET';
CommandText:=CommandText+'(';
CommandText:=CommandText+'''MSDASQL.1''';
CommandText:=CommandText+',';
CommandText:=CommandText+'''DRIVER=Microsoft Excel Driver
(*.xls);';
CommandText:=CommandText+'DBQ='+OpenDialog.FileName+'''';
CommandText:=CommandText+',';
CommandText:=CommandText+'''SELECT * FROM [Sheet1$]''';
CommandText:=CommandText+') A';
CommandText:=CommandText+' WHERE NOT EXISTS(SELECT 1 FROM
InfoPrice'+mUser.UserArea+' C';
CommandText:=CommandText+' WHERE C.RDate=A.åå¸æ¥æ AND C.RNO=A.å
å¸ç¼å· AND C.Area=A.å°åºç¼ç )';
Execute;
end;
//ADOConInfoExcel.CommitTrans;
RefreshGridData;
ShowWIE(2,'导å
¥æå!');
except
//ADOConInfoExcel.RollbackTrans;
ShowWIE(2,'导å
¥å¤±è´¥!');
end;
finally
ADOQryInfoExcel.Close;
ADOConInfoExcel.Close;
OpenDialog.Free;
end;
温馨提示:答案为网友推荐,仅供参考