现在我要做一个程序,将Excel中的数据导入数据库。使用的是Delphi中的Server组件。请问应该如何实现。
解决方案 »
- delphi 调用 ezweb.dl 出现了问题...initialize properly(0xc0000007b). 是什么原因呢?
- 帮我解读一下这小段程序
- 关于XML的问题
- 关于报表的一个小问题!
- 关于用Quick report 打印列表,备注字段关于用Quick report 打印列表,备注字段导致行高不统一,怎样动态的控制列竖线的Height呢,答者有
- 如何把菜单做成这种效果【S系统】
- Delphi中如何调用VB编的ActiveX控件?
- (千两狂死郎)收!
- 请问数据库数据的打包socket发送用sendbuf如何实现呢?
- 三层结构访问数据库效率非常的问题
- 那位老大用过BusinessSkinForm,里面标题菜单的字体大小怎么改变?
- 关于模拟按键的问题!
数据的操作就可以了
unit wnquerytoexcel;interfaceuses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Grids, DBGrids, StdCtrls, Db, DBTables, OleServer, Excel97,ComObj;type
TForm1 = class(TForm)
adb: TDatabase;
Query1: TQuery;
Button1: TButton;
DBGrid1: TDBGrid;
Button2: TButton;
DataSource1: TDataSource;
Button3: TButton;
procedure Button2Click(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button3Click(Sender: TObject); private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.DFM}procedure TForm1.Button2Click(Sender: TObject);
begin
with Query1 do
begin
Close;
Open;
end;
end;procedure TForm1.Button1Click(Sender: TObject);
var
ExcelApp : Variant; //声明ExcelApp为Variant
RecordCounts : Integer;
i:Integer;
begin
try
ExcelApp := CreateOleObject('Excel.Application'); //创建Excel.Application
except
ShowMessage('本机没有安装EXCEL!');
exit;
end;
ExcelApp.WorkBooks.Add;//新加一个工作簿
ExcelApp.WorkSheets[1].Activate; //激活第一个Sheet
Query1.Close;
Query1.Open;
Query1.First;
ExcelApp.Cells[1,1].value :='纳税人识别号';
ExcelApp.Cells[1,2].value :='单位名称';
RecordCounts := Query1.RecordCount; //取QUERY1的记录数用于循环
for i:=2 to RecordCounts+1 do
begin
if not Query1.Eof then
begin
ExcelApp.Cells[i,1].Value :=Query1.FieldByName('TAXNO').AsString;
ExcelApp.Cells[i,2].Value :=Query1.FieldByName('UNITNAME').AsString;
Query1.Next;
end;
end;
ExcelApp.ActiveWorkBook.SaveAs('c:\list.xls');
if MessageDlg('文件 c:\list.xls 保存成功,是否要打开编辑此文件?',mtConfirmation,[mbYes,mbNo,mbCancel],0)= mrYes then
ExcelApp.Visible := True //设置EXCEL窗口为可见
else
begin
ExcelApp.Visible := False;
ExcelApp.Quit;
end;
end;
procedure TForm1.Button3Click(Sender: TObject);
var
ExcelApp : Variant; //声明ExcelApp为Variant
//RecordCounts : Integer;
//i:Integer;
begin
try
ExcelApp := CreateOleObject('Excel.Application'); //创建Excel.Application
except
ShowMessage('本机没有安装EXCEL!');
exit;
end;
try
try
ExcelApp.WorkBooks.open('c:\list.xls');//打开一个工作簿
ShowMessage(ExcelApp.Cells[2,1].Value);
ShowMessage(ExcelApp.Cells[2,2].Value);
except
ShowMessage('c:\list.xls 不存在!');
exit;
end;
// ExcelApp.WorkSheets[1].Activate; //激活第一个Sheet
// ExcelApp.Visible := True;
finally
ExcelApp.Quit;
end;// ShowMessage(ExcelApp.Cells[2,2].Value);
{ Query1.Close;
Query1.Open;
Query1.First;
ExcelApp.Cells[1,1].value :='纳税人识别号';
ExcelApp.Cells[1,2].value :='单位名称';
RecordCounts := Query1.RecordCount; //取QUERY1的记录数用于循环
for i:=2 to RecordCounts+1 do
begin
if not Query1.Eof then
begin
ExcelApp.Cells[i,1].Value :=Query1.FieldByName('TAXNO').AsString;
ExcelApp.Cells[i,2].Value :=Query1.FieldByName('UNITNAME').AsString;
Query1.Next;
end;
end;
ExcelApp.ActiveWorkBook.SaveAs('c:\list.xls');
if MessageDlg('文件 c:\list.xls 保存成功,是否要打开编辑此文件?',mtConfirmation,[mbYes,mbNo,mbCancel],0)= mrYes then
ExcelApp.Visible := True //设置EXCEL窗口为可见
else
begin
ExcelApp.Visible := False;
ExcelApp.Quit;
end;}
end;end.
可以联系读取不同的表
unit OPexcel;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, Grids, Excel2000, OleServer, Buttons, FileCtrl, DB,
DBTables, ExtCtrls, ImgList, ComCtrls, ToolWin;type
TDExcel = class(TForm)
ExcelApplication1: TExcelApplication;
ExcelWorkbook1: TExcelWorkbook;
ExcelWorksheet1: TExcelWorksheet;
StringGrid1: TStringGrid;
DExcel_TB: TToolBar;
TB_DEExcel: TToolButton;
DExcel_Image: TImageList;
TB_DERetrun: TToolButton;
TB_DEHelp: TToolButton;
GroupBox1: TGroupBox;
DriveComboBox1: TDriveComboBox;
DirectoryListBox1: TDirectoryListBox;
FileListBox1: TFileListBox;
GroupBox2: TGroupBox;
Label1: TLabel;
Edt_DEStart: TEdit;
Label2: TLabel;
Edt_DEEnd: TEdit;
ProgressBar1: TProgressBar;
procedure ReadExcel;
procedure SaveToOrcal;
procedure TB_DEExcelClick(Sender: TObject);
procedure FileListBox1Change(Sender: TObject);
procedure TB_DERetrunClick(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
private
{ Private declarations }
public
{ Public declarations }
end;var
DExcel: TDExcel;
BRow:integer;//选择的行
currentfile:integer;
lastfile:integer; //选择的列
implementationuses Dldata, U_DM, U_Select;{$R *.dfm}
procedure TDExcel.ReadExcel;
var
i,j:integer;
k:integer;
begin
try
ExcelApplication1.Workbooks.Open (FileListBox1.Directory + '\' + FileListBox1.Items[currentfile],
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
//showmessage(FileListBox1.Directory + '\' + FileListBox1.Items[currentfile]);//excelapplication1.Workbooks.Open(FileListBox1.Directory + '\' + FileListBox1.Items[Currentfile],
//null,null,null,null,null,null,null,null,null,null,null,null,0); //打开指定的EXCEL 文件
except
begin
ExcelApplication1.Disconnect;//出现异常情况时关闭
ExcelApplication1.Quit;
k:=Application.MessageBox('打开文件错误', 'Look',MB_ABORTRETRYIGNORE);
if k=3 then
currentfile:=currentfile+1;
end;
end;
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);//ExcelWorkbook1与Eexcelapplication1建立连接
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1与Excelworkbook1建立连接
//开始从EXCEL中取数,放到stringgrid1中,取完数后关闭EXCEL
BRow:=1;
for i:=1 to 14 do
begin
if trim(excelworksheet1.cells.item[i+1,1])<>'序号' then
begin
BRow:=BRow+1;
end
else
break;
end;for i:=1 to 1000 do
for j:=1 to 5 do
begin
if trim(excelworksheet1.cells.item[i+1+BRow,3])<>'' then
begin
stringgrid1.rowCount:=i+1;
stringgrid1.Cells[j,i]:=ExcelWorksheet1.Cells.Item[i+BRow+1,j];
end
else
begin
break;
end;
end;
end;procedure TDExcel.SaveToOrcal;
var
i:integer;
s:String;
begin
for i:=1 to stringgrid1.rowCount-1 do
begin
DM.DEcel_Query.Close;
DM.DEcel_Query.sql.Clear;
S := 'insert into part values(''' + stringgrid1.Cells[1,1] + ''',''' + stringgrid1.Cells[2,i] + ''',''' + stringgrid1.Cells[3,i] + ''',''' + stringgrid1.Cells[4,i] + ''',''' + stringgrid1.Cells[5,i] + ''' , ''1'')';
DM.DEcel_Query.SQL.Add(S);
DM.DEcel_Query.ExecSQL;
end;
end;
procedure TDExcel.TB_DEExcelClick(Sender: TObject);
var
m:integer;
begin
Try
ExcelApplication1.Connect;//EXCEL应用程序
Except
MessageDlg('Excel may not be installed',mtError, [mbOk], 0);
Abort;
End;
ExcelApplication1.Visible[0]:=false;
ExcelApplication1.Caption:='Excel Application';
Lastfile:=StrToInt(Edt_DEEnd.Text);
currentfile:=StrToInt(Edt_DEStart.Text);
ProgressBar1.Min := 0;
ProgressBar1.Max :=lastfile-currentfile+1;
for m:=currentfile to Lastfile do
begin
Label2.Caption:=FileListBox1.Directory + '\' + FileListBox1.Items[currentfile];
readexcel;
SaveToOrcal;
ExcelApplication1.Workbooks.Close(0);
currentfile:=currentfile+1;
ProgressBar1.Position:=ProgressBar1.Position+1;
end;
showmessage('success');
ProgressBar1.Position:=0;
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
end;procedure TDExcel.FileListBox1Change(Sender: TObject);
begin
Edt_DEStart.Text:='0';
Edt_DEEnd.Text:=IntToStr(FileListBox1.Items.Count);
end;procedure TDExcel.TB_DERetrunClick(Sender: TObject);
begin
DExcel.Close;
end;procedure TDExcel.FormClose(Sender: TObject; var Action: TCloseAction);
begin
DExcel.Release;
selectfrm.Show;
end;end.
http://218.56.9.158/default.aspx
下载基地->动态调用Excel数据表;
下载一下参考吧。注意事项:
在用ado将excel连上数据表后,调试必须退出delphi,在外边直接执行exe,否则报错。