＋设为首页 ＋添加到收藏夹
我用delphi+access，现在要把excel数据按要求导入数据库中间来，还要把数据库中的数据导出来，
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Excel97, OleServer, ADODB, Db, Grids, StdCtrls;
type
TForm1 = class(TForm)
Button1: TButton;
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
OpenDialog1: TOpenDialog;
ExcelApplication1: TExcelApplication;
ExcelWorksheet1: TExcelWorksheet;
ExcelWorkbook1: TExcelWorkbook;
ADOQuery1Schedule_No: TWideStringField;
ADOQuery1OrderDate: TWideStringField;
ADOQuery1Order_no: TWideStringField;
ADOQuery1Po_No: TWideStringField;
ADOQuery1Customer: TWideStringField;
ADOQuery1Part_No: TWideStringField;
ADOQuery1Qty: TWideStringField;
ADOQuery1Deliver: TWideStringField;
ADOQuery1Remarks: TWideStringField;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.DFM}
procedure TForm1.Button1Click(Sender: TObject);
var
i : integer;
j : integer;
EParam : OleVariant;
DocuType : OleVariant;
wkbk : _WorkBook;
begin
opendialog1.InitialDir:=ExtractFileDir(paramstr(0));
//文件打开的初始路径
opendialog1.Execute;
Try
ExcelApplication1.Connect;
Except
Showmessage(Excel文件打开失败！);
Exit;
End;
ExcelApplication1.Visible[0]:=False;
ExcelApplication1.Caption:=Excel数据文件;
EParam:=EmptyParam;
DocuType:=0;
try
wkBk:=ExcelApplication1.Workbooks.Add(EParam, DocuType);
wkBk:=ExcelApplication1.WorkBooks.Open(opendialog1.FileName,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,
EmptyParam,EmptyParam,DocuType,EmptyParam,DocuType);
except
begin
ExcelApplication1.Disconnect;//出现异常情况时关闭
ExcelApplication1.Quit;
showmessage(请选择EXCEL数据表格！);
exit;
end;
end;
ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);//ExcelWorkbook1与Eexcelapplication1建立连接
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1与Excelworkbook1建立连接
//开始从EXCEL中取数，放到stringgrid中，取完数后关闭EXCEL
for i:=2 to 10 do
begin
if trim(excelworksheet1.cells.item[i,1])<> then
begin
ADOQuery1.Append;
ADOQuery1.fieldbyname(Schedule_No).value:=ExcelWorksheet1.Cells.Item[i,1];
ADOQuery1.fieldbyname(OrderDate).value:=ExcelWorksheet1.Cells.Item[i,2];
ADOQuery1.fieldbyname(Order_no).value:=ExcelWorksheet1.Cells.Item[i,3];
ADOQuery1.fieldbyname(Po_No).value:=ExcelWorksheet1.Cells.Item[i,4];
ADOQuery1.fieldbyname(Customer).value:=ExcelWorksheet1.Cells.Item[i,5];
ADOQuery1.fieldbyname(Part_No).value:=ExcelWorksheet1.Cells.Item[i,6];
ADOQuery1.fieldbyname(Qty).value:=ExcelWorksheet1.Cells.Item[i,7];
ADOQuery1.fieldbyname(Deliver).value:=ExcelWorksheet1.Cells.Item[i,8];
ADOQuery1.fieldbyname(Remarks).value:=ExcelWorksheet1.Cells.Item[i,9];
ADOQuery1.Post;
end
end;
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
showmessage(数据导入完毕！);
end;
end.
引用ComObj和Excel97
procedure TForm_tj_dkhgzqk.OuttoExcel;
var
ExcelApplication1: TExcelApplication;
ExcelWorkbook1: TExcelWorkbook;
ExcelWorksheet1: TExcelWorksheet;
i,j:integer;
reportname: string;
begin
try
ExcelApplication1 := TExcelApplication.Create(Application);
ExcelWorkbook1 := TExcelWorkbook.Create(Application);
ExcelWorksheet1 := TExcelWorksheet.Create(Application);
ExcelApplication1.Connect;
except
Application.Messagebox(对不起，你的计算机上还没有安装 Excel !+chr(13)+数据无法导出到 Excel 文件！, 系统提示, MB_ICONERROR + mb_Ok);
Abort;
end;
try
ExcelApplication1.Workbooks.Add(EmptyParam,0);
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
query1.First;
for j := 0 to query1.Fields.Count - 1 do
begin
ExcelWorksheet1.Cells.item[3,j+1]:=query1.Fields[j].DisplayLabel;
ExcelWorksheet1.Cells.item[3,j+1].font.size:=10;
end;
for i := 4 to query1.RecordCount + 3 do
begin
for j := 0 to query1.Fields.Count-1 do
begin
ExcelWorksheet1.Cells.item[i,j+1] :=
query1.Fields[j].Asstring;
ExcelWorksheet1.Cells.item[i,j+1].font.size :=10;
end;
query1.Next;
end;
ExcelWorksheet1.Columns.AutoFit;
ExcelWorksheet1.Cells.item[1,2]:= caption;
ExcelWorksheet1.Cells.Item[1,2].font.size :=14;
if SaveDialog1.Execute then
begin
reportname:=SaveDialog1.FileName;
ExcelWorksheet1.SaveAs(reportname);
Application.Messagebox(pchar(您的数据已经成功导出到：+chr(13)+chr(13)+ reportname), 系统提示,mb_Ok);
end
else
Abort;
// ExcelWorksheet1.SaveAs(reportname);
// Application.Messagebox(pchar(您的数据已经成功导出到：+chr(13)+chr(13)+ reportname), 系统提示,mb_Ok);
finally
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
ExcelApplication1.Free;
ExcelWorksheet1.Free;
ExcelWorkbook1.Free;
end;
end;
