procedure TOfficeform.Button2Click(Sender: TObject); var MSExcel:Variant; i,j:Integer; begin SaveDialog1.Filter:='*.XLS|*.XLS'; SaveDialog1.DefaultExt:='XLS'; if SaveDialog1.Execute then begin MSExcel:=CreateOLEObject('Excel.Application'); MSExcel.WorkBooks.Add; MSExcel.Visible:=False; Table1.Open; j:=Table1.RecordCount; Table1.First; for i:=1 to j do begin MSExcel.Cells[i,1].NumberFormat:='@'; MSExcel.Cells[i,1].Value:=Table1.FieldByName('CODE').AsString; MSExcel.Cells[i,2].Value:=Table1.FieldByName('COLOR').AsString; Table1.Next; end; MSExcel.ActiveWorkBook.SaveAs(SaveDialog1.FileName); MSExcel.ActiveWorkBook.Saved:=True; MSExcel.Quit; end; end;我手头只有一个将数据导到EXCEL的,具体要导出你自己研究一下,很简单,逆操作一下就可以了。再有不明白的地方,加我MSN:[email protected]
procedure TImportStudenNo_frm.BTimportStudnoClick(Sender: TObject); var strsql,sFileName:string; sheet:Variant; XLApp:Variant; iErrorCount:integer; i:integer; begin BTimportStudno.enabled:=false ; sFileName:=trim(pnlFileName.Caption); if not FileExists(sFileName) then begin MessageDlg('Excel文件不存在。 ', mtError, [mbOK], 0); BTimportStudno.Enabled:=true; Exit; end; try xlApp:=CreateOleObject('Excel.Application'); except xlApp:=UnAssigned; MessageDlg('没有安装Excel程序', mtInformation, [mbOK], 0); BTimportStudno.Enabled:=true; Exit; end; try xlApp.Workbooks.Open(sFileName); except MessageDlg('打开Excel文档失败。不是Excel文件。 ', mtInformation, [mbOK], 0); xlApp.Quit; xlApp:=UnAssigned; BTimportStudno.Enabled:=true; Exit; end; iErrorCount:=0; iTotalCount:=0; d.dbCon.StartTransaction; try for i:=1 to xlApp.WorkBooks[1].WorkSheets.Count do begin Sheet:=xlApp.WorkBooks[1].WorkSheets[i]; if not ProcessSheet(Sheet,i,oldStudnoList.Lines) then begin if d.dbCon.InTransaction then d.dbCon.Rollback; bbtImport.Enabled:=true; exit; end; end; except if d.dbCon.InTransaction then d.dbCon.Rollback; exit; end; if d.dbCon.InTransaction then d.dbCon.Commit; xlApp.Quit; xlApp:=UnAssigned;
pbImport1.Position:=0; MessageDlg(Format('导入成功。(导入%4d人)',[iTotalCount]), mtInformation, [mbOK], 0); BTimportStudno.Enabled:=true; end;function ProcessSheet(sheet:Variant;iPage:integer; ErrorList:TStrings):boolean; var idx:integer; sStudNO,sName:string; sGrade:string; sSpecially:string; sAcademy:string; // cSubsidy:Currency; iMaxRows:integer; StuInfoList: TList; t:integer; ires:boolean;begin Result:=false; iMaxRows:=0; for idx:=1 to Sheet.Rows.Count do begin if Trim(Sheet.Cells[idx,1])='' then begin break; end else Inc(iMaxRows); end; ImportStudenNo_frm.pbimport1.Min:=0; ImportStudenNo_frm.pbimport1.Max:=iMaxRows; try for idx:=2 to iMaxRows do //第一行为字段名信息。不取。 begin ImportStudenNo_frm.pbimport1.StepBy(1); Application.ProcessMessages; sStudNo:=Trim(Sheet.Cells[idx, 1]); sName:=Trim(Sheet.Cells[idx, 2]); sGrade:=Trim(Sheet.Cells[idx,3]); sAcademy:=Trim(Sheet.Cells[idx,4]); sSpecially:=Trim(Sheet.Cells[idx,5]); //插入全院新学号表 d.q.Close; d.q.SQL.Clear; d.q.SQL.Add('insert into 全院新学号 (学号,姓名,年级,系,班级) values (:学号,:姓名,:年级,:系,:班级)'); d.q.ParamByName('学号').asstring:=sStudNo; d.q.ParamByName('姓名').asstring:=sName; d.q.ParamByName('年级').asstring:=sGrade; d.q.ParamByName('系').asstring:=sAcademy; d.q.ParamByName('班级').asstring:=sSpecially; d.q.ExecSQL; iTotalCount:=iTotalCount+1; end; except end; if not Result then Result:=true;
那建议使用OLE,在单元中引用 comobj
procedure TForm1.FormCreate(Sender: TObject);
var
oleObj:oleVariant;
begin
oleObj:=application.createoleObject('Excel.Application');
oleObj.Workbooks.add;
.........end;用大概以上的方法,可以将Excel中的数据读出来,然后你定义一个连接后台SQL的数据组件,设置循环可以导进去.
var MSExcel:Variant;
i,j:Integer;
begin
SaveDialog1.Filter:='*.XLS|*.XLS';
SaveDialog1.DefaultExt:='XLS';
if SaveDialog1.Execute then
begin
MSExcel:=CreateOLEObject('Excel.Application');
MSExcel.WorkBooks.Add;
MSExcel.Visible:=False;
Table1.Open;
j:=Table1.RecordCount;
Table1.First;
for i:=1 to j do
begin
MSExcel.Cells[i,1].NumberFormat:='@';
MSExcel.Cells[i,1].Value:=Table1.FieldByName('CODE').AsString;
MSExcel.Cells[i,2].Value:=Table1.FieldByName('COLOR').AsString;
Table1.Next;
end;
MSExcel.ActiveWorkBook.SaveAs(SaveDialog1.FileName);
MSExcel.ActiveWorkBook.Saved:=True;
MSExcel.Quit;
end;
end;我手头只有一个将数据导到EXCEL的,具体要导出你自己研究一下,很简单,逆操作一下就可以了。再有不明白的地方,加我MSN:[email protected]
var
strsql,sFileName:string;
sheet:Variant;
XLApp:Variant;
iErrorCount:integer;
i:integer;
begin
BTimportStudno.enabled:=false ;
sFileName:=trim(pnlFileName.Caption);
if not FileExists(sFileName) then
begin
MessageDlg('Excel文件不存在。 ', mtError, [mbOK], 0);
BTimportStudno.Enabled:=true;
Exit;
end; try
xlApp:=CreateOleObject('Excel.Application');
except
xlApp:=UnAssigned;
MessageDlg('没有安装Excel程序', mtInformation, [mbOK], 0);
BTimportStudno.Enabled:=true;
Exit;
end; try
xlApp.Workbooks.Open(sFileName);
except
MessageDlg('打开Excel文档失败。不是Excel文件。 ', mtInformation, [mbOK], 0);
xlApp.Quit;
xlApp:=UnAssigned;
BTimportStudno.Enabled:=true;
Exit;
end; iErrorCount:=0;
iTotalCount:=0; d.dbCon.StartTransaction;
try
for i:=1 to xlApp.WorkBooks[1].WorkSheets.Count do
begin
Sheet:=xlApp.WorkBooks[1].WorkSheets[i];
if not ProcessSheet(Sheet,i,oldStudnoList.Lines) then
begin
if d.dbCon.InTransaction then
d.dbCon.Rollback;
bbtImport.Enabled:=true;
exit;
end;
end;
except
if d.dbCon.InTransaction then
d.dbCon.Rollback;
exit;
end; if d.dbCon.InTransaction then
d.dbCon.Commit; xlApp.Quit;
xlApp:=UnAssigned;
pbImport1.Position:=0; MessageDlg(Format('导入成功。(导入%4d人)',[iTotalCount]), mtInformation, [mbOK], 0);
BTimportStudno.Enabled:=true;
end;function ProcessSheet(sheet:Variant;iPage:integer;
ErrorList:TStrings):boolean;
var idx:integer;
sStudNO,sName:string;
sGrade:string;
sSpecially:string;
sAcademy:string;
// cSubsidy:Currency;
iMaxRows:integer;
StuInfoList: TList;
t:integer;
ires:boolean;begin
Result:=false;
iMaxRows:=0; for idx:=1 to Sheet.Rows.Count do
begin
if Trim(Sheet.Cells[idx,1])='' then
begin
break;
end
else
Inc(iMaxRows); end; ImportStudenNo_frm.pbimport1.Min:=0;
ImportStudenNo_frm.pbimport1.Max:=iMaxRows;
try for idx:=2 to iMaxRows do
//第一行为字段名信息。不取。
begin
ImportStudenNo_frm.pbimport1.StepBy(1);
Application.ProcessMessages; sStudNo:=Trim(Sheet.Cells[idx, 1]);
sName:=Trim(Sheet.Cells[idx, 2]);
sGrade:=Trim(Sheet.Cells[idx,3]);
sAcademy:=Trim(Sheet.Cells[idx,4]);
sSpecially:=Trim(Sheet.Cells[idx,5]);
//插入全院新学号表
d.q.Close;
d.q.SQL.Clear;
d.q.SQL.Add('insert into 全院新学号 (学号,姓名,年级,系,班级) values (:学号,:姓名,:年级,:系,:班级)');
d.q.ParamByName('学号').asstring:=sStudNo;
d.q.ParamByName('姓名').asstring:=sName;
d.q.ParamByName('年级').asstring:=sGrade;
d.q.ParamByName('系').asstring:=sAcademy;
d.q.ParamByName('班级').asstring:=sSpecially;
d.q.ExecSQL; iTotalCount:=iTotalCount+1;
end;
except
end; if not Result then
Result:=true;
end;