请问各位大量虾们,怎样才能将EXCEL文件里面的数据导入到数据库里面呀?
小弟先在此谢过了.
小弟先在此谢过了.
解决方案 »
- 2000元左右(不高于)能配什么样的机子?
- 高分请教,如何做一个有焦点的超链接控件?
- 谁有画五角星的函数????????????分多多的
- 传一个指针给线程的变量,线程结束后应该如何操作线程中的变量 ?可以释放吗?
- 查找到的字符串如何明显表示出来?
- 新年快乐,向您请教,regsvr32 XXX.DLL时提示"loadlibrary没有XXX.DLL失败,找不到指定的模块"
- 捕捉窗口最小化的那个事件是怎么实现?
- wwDBGrid的标题栏能在排序时显示一个正三角或倒三角图标吗
- 有哪位仁兄搞过基于DIALOGIC语音卡的delphi开发,有问题请教,给出本人最多分61分
- delphi将pdf文件存入sql数据库中
- 关于单元互相调用的问题。
- 关于指针的简单问题!来看的就有分!
记得只要选择有数据的那页就可以了
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=远程ip;User ID=sa;Password=密码'
).库名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
uses :comobj,db;
Procedure TForm1.Excel_2_db(str :string);
var eclApp,WorkBook :variant ;
xlsFileName :string;
a_FiledCount:integer; //数据库表中的列数
b_filedCount:integer; //excel 文件中的 列数
b_row :integer; // excel 文件的行熟
i,j :integer;
a_flag :boolean;
begin
Form1.OpenDialog1.Title :='Excel文件 导入到数据库'+str+'表';
Form1.OpenDialog1.InitialDir :=ExtractFilePath(Application.ExeName );
if (Form1.OpenDialog1.Execute )
then xlsFileName :=ExtractFileName(Form1.OpenDialog1.FileName)
else exit; try
eclApp := CreateOleObject('Excel.Application');
WorkBook :=CreateOleObject('Excel.Sheet');
except
showmessage('您系统未安装MS-EXCEL');
exit;
end;
try
workBook :=eclApp.WorkBooks.add ;
eclApp.workBooks.open(Form1.OpenDialog1.FileName );
except
on EOleException do
begin
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
exit;
end;
end;
eclApp.visible :=false;
try //try ..finally
try //try ..except
With Data_Mod.DataModule1.kcinfo_Tab do
begin
close ;
TableName :=str;
active :=true;
a_FiledCount :=FieldCount; end; b_filedCount :=eclApp.ActiveSheet.UsedRange.columns.Count;//返回excel 表中的列数
b_row :=eclApp.activesheet.UsedRange.rows.count; //返回excel 表中的行数 if (a_FiledCount <>b_FiledCount) //当数据 表和导入的excel表中的列数不一样,说明导入的excel文件不是正确的
then begin
showmessage('您选择导入的excel文件错误'+#13+#10+'请您重新选择');
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
exit;
end
else begin //列数正确,但是还要继续判断每列的字段名是否一致
for i :=1 to b_filedCount do
begin
//showmessage(eclApp.activesheet.cells.item[1,i].value);
//showmessage(DataMod.ADO_basic.Fields.Fields[i-1].FieldName ); if eclApp.activesheet.cells.item[2,i].value<>Data_Mod.DataModule1.kcinfo_Tab.Fields[i-1].FieldName //判断字段名是否相等
//if eclApp.activesheet.cells.item[1,i].value<>DataMod.ADO_basic.Fields.Fields[i-1].FieldName //判断中文title.caption 是否相等
then begin
showmessage('您选择导入的excel文件错误'+#13+#10+'请您重新选择');
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
Data_Mod.DataModule1.kcinfo_Tab.Close ;
exit;
end;
end; //for i:=.....
end; //end with else for i :=3 to b_row do //行
begin
a_flag :=Data_Mod.DataModule1.kcinfo_Tab.Locate(eclApp.activesheet.cells.item[2,1],eclApp.activesheet.cells.item[i,1],[loCaseInsensitive]);
if (a_flag =true)
then begin
showmessage('该记录已经存在');
Data_Mod.DataModule1.kcinfo_Tab.Next ;
continue;
end; With Data_Mod.DataModule1.kcinfo_Tab do
begin
close ;
TableName :=str;
active :=true;
Append;
end;
For j :=1 to b_filedCount do //列
begin //开始导入数据库
//showmessage(eclApp.activesheet.cells.item[1,j]);//.Value);
////showmessage(eclApp.activesheet.cells.item[i,j].value);
//showmessage(eclApp.activesheet.cells[i,j].value); Data_Mod.DataModule1.kcinfo_Tab.FieldByName(eclApp.activesheet.cells.item[2,j]).Value :=eclApp.activesheet.cells[i,j].value; end ; //end with For j :=1 to b_filedCount do
Data_Mod.DataModule1.kcinfo_Tab.Post ;
Data_Mod.DataModule1.kcinfo_Tab.Refresh ;
end;
showmessage('导入数据成功');
except
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
Data_Mod.DataModule1.kcinfo_Tab.Close ;
end; //end try except
finally //操作错误,退出
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
Data_Mod.DataModule1.kcinfo_Tab.Close ;
end;
end;
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
使用Delphi 5.0最新提供的Servers栏控件可以很容易开发OLE自动化控制器实现对OLE自动化服务器的调用,发挥Word、Excel、Powerpoint的强大功能,从而起到在自己程序中操纵Office 的目的,以下是操纵Excel的几个简单的操作,如有疑问,可来信询问。 方法如下:1、创建一个普通Application。2、在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet。3、对Excel的一些操作,具体方法如下:【打开Excel97】 ExcelApplication1.Connect; ExcelApplication1.Visible[0]:=True; 【增加一个Workbook】 ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,0)); 【添加一个Worksheet】 var Temp_Worksheet: _WorkSheet; begin Try Temp_Worksheet:=ExcelWorkbook1. WorkSheets.Add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _WorkSheet;//(注意) ExcelWorkSheet1.ConnectTo(Temp_WorkSheet); Except ShowMessage('Failure'); End; end; 【关闭Excel】 ExcelApplication1.Quit; ExcelWorksheet1.Disconnect; ExcelWorkbook1.Disconnect; ExcelApplication1.Disconnect; 【选择当前Workbook的某一Worksheet】 procedure TForm1.ComboBox1DropDown(Sender: TObject); var i: Integer; begin ComboBox1.Clear; For i:=1 to ExcelWorkbook1.Worksheets.Count do ComboBox1.Items.Add((ExcelWorkbook1.Worksheets.Item[i] as _WorkSheet).Name); end; procedure TForm1.ComboBox1Change(Sender: TObject); begin ExcelWorkSheet1.ConnectTo(ExcelWorkbook1.Worksheets.Item [ComboBox1.ItemIndex+1] as _WorkSheet); ExcelWorkSheet1.Activate; end;【选择某一Workbook】 procedure TForm1.ComboBox2DropDown(Sender: TObject); var i: Integer; begin ComboBox2.Clear; if ExcelApplication1.Workbooks.Count >0 then For i:=1 to ExcelApplication1.Workbooks.Count do Combobox2.Items.Add(ExcelApplication1.Workbooks.Item[i].Name); end; procedure TForm1.ComboBox2Change(Sender: TObject); begin ExcelWorkSheet1.Disconnect; ExcelWorkBook1.ConnectTo(ExcelApplication1.Workbooks.Item[Combobox2.ItemIndex+1]); ExcelWorkBook1.Activate; ExcelWorksheet1.ConnectTo(ExcelWorkBook1.ActiveSheet as _WorkSheet); ExcelWorkSheet1.Activate; end;【对某一单元格进行赋值及取值】 procedure TForm1.Button5Click(Sender: TObject); begin ExcelWorksheet1.Cells.Item[SpinEdit2.Value,SpinEdit1.Value]:=Edit1.Text; end; procedure TForm1.Button6Click(Sender: TObject); begin Edit1.Text:=ExcelWorksheet1.Cells.Item[SpinEdit2.Value,SpinEdit1.Value]; end;【选择某一区域】 ExcelWorkSheet1.Range['A1','C1'].Select;【打开一个Excel文件】 if OpenDialog1.Execute then Begin ExcelWorkBook1.ConnectTo( ExcelApplication1.Workbooks.Open( OpenDialog1.FileName,EmptyParam,EmptyParam,EmptyParam, EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam, EmptyParam,EmptyParam,EmptyParam,EmptyParam,0)); ExcelWorkSheet1.ConnectTo(ExcelWorkBook1.Activesheet as _Worksheet); End; 【存入一个Excel文件】 procedure TForm1.button6Click(Sender: TObject); var i,row,column:integer; begin Try ExcelApplication1.Connect; Except MessageDlg('Excel may not be installed',mtError, [mbOk], 0); Abort; End; ExcelApplication1.Visible[0]:=True; ExcelApplication1.Caption:='Excel Application'; ExcelApplication1.Workbooks.Add(Null,0); ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]); ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet); Table1.Open; row:=1; While Not(Table1.Eof) do begin column:=1; for i:=1 to Table1.FieldCount do begin ExcelWorksheet1.Cells.Item[row,column]: =Table1.fields[i-1].AsString; column:=column+1; end; Table1.Next; row:=row+1; end; End;【打印预览一个Excel文件】 procedure TForm1.Button7Click(Sender: TObject); begin ExcelWorksheet1.PrintPreview; end;【打印一个Excel文件】 procedure TForm1.Button8Click(Sender: TObject); begin ExcelWorksheet1.PrintOut; end; 【说明】 这些程序在Win98+Delphi 5+Excel 97下运行通过。这些例子还可以作适当的扩充,如DDE、执行宏调用、保存文件、打印文件及对Excel的设置等,其具体的实现及设置方法请参阅Microsoft Excel Visual Basic参考中的Microsoft Excel对象。