procedure TLoadFromExcelObject.SetVisible(const Value: boolean); begin if not HasStarted then exit; FVisible := Value; if Xls.Application.Visible <> FVisible then Xls.Application.Visible := FVisible; end;
//得到表格标题最大的叠加层数
procedure TLoadFromExcelObject.LoadFromExcel (ADataSet: TDBDataSet); var I, J: Integer; Index: Integer; Sheet: Variant; ECol, ERow: Integer; //fDate: TDatetime; begin Sheet := Xls.Application.ActiveWorkBook.ActiveSheet; ADataSet.Active := true; if ADataSet.State in dsEditModes then ADataSet.Post;
ECol := Sheet.UsedRange.Columns.Count; if EndCol <> 1 then ECol := EndCol;
ERow := Sheet.UsedRange.Rows.Count; if EndRow <> 1 then ERow := EndRow;
for I := StartRow to ERow do begin ADataSet.Append; Index := 0; J := StartCol; while J <= ECol do begin if J > ADataSet.Fields.Count + 1 then Break; try ADataSet.Fields[Index].AsString := VarToStr(Sheet.Cells[I, J].Value); except end; Inc(J); Inc(Index); end; ADataSet.Post; end; end;
procedure TLoadFromExcelObject.SetEndCol(const Value: Word); begin FEndCol := Value; end;
procedure TLoadFromExcelObject.SetEndRow(const Value: Word); begin FEndRow := Value; end;
procedure TLoadFromExcelObject.SetFileName(const Value: TFileName); begin FFileName := Value; end;
procedure TLoadFromExcelObject.SetStartCol(const Value: Word); begin FStartCol := Value; end;
procedure TLoadFromExcelObject.SetStartRow(const Value: Word); begin FStartRow := Value; end;
destructor TLoadFromExcelObject.Destroy; var i: integer; begin try for i := 1 to Xls.Application.WorkBooks.Count - 1 do Xls.Application.WorkBooks[i].Close(True, ); Xls.Application.Quit; HasStarted := False; finally end; inherited; end;
end.
另外: 这两个例子,异构数据库之间导数据
第一种方法 在Form上放一个ADOConnection,连结指向目标Access库 先说,导入txt,比如txt文件在c:\temp\aaaa.txt 就执行 ADOConnection.Connected := True; ADOConnection.Execute(Select * Into abcd From [Text;Database=c:\temp].aaaa.txt);
把Excel导入到Access中,同txt类似 select * into from [excel 8.0;database=].[$]
第二种方法 从Excel写数据到Access库 //excel到access uses comobj,excel97,excel2000; //从Excel写数据到Access库 prodedure ExcelToMdb(EXLfile:string;); var sheet,XLApp,workbook : variant; iRow,MaxRow:integer; begin screen.Cursor:=crHourGlass; try //创建对象 XLApp:=createOleObject(Excel.Application); XLApp.displayAlerts:=false; XLApp.ScreenUpdating:=false; XLApp.WorkBooks.Add(EXLfile); workbook := XLApp.workbooks[1]; sheet:=workbook.worksheets[1]; //sheet:=XLApp.WorkBooks[1].worksheets[1]; //取得最大行数 maxRow XLApp.ActiveCell.SpecialCells(xlLastCell).Select; maxRow:=XLApp.ActiveCell.Row; //最大行数 //写数据到Access库 ADOTable1.open; for iRow:=2 to MaxRow do if sheet.cells[iRow,1]<> then //关键字不为空 begin ADOTable1.Append ; ADOTable1.fieldByName(ID).asInteger:= strToInt(sheet.cells[iRow,1]); ADOTable1.fieldByName(code).asString:=sheet.cells[iRow,2]; //编码 ADOTable1.fieldByName(name).asString:=sheet.cells[iRow,3]; //名称 ADOTable1.post; end; finally if not VarIsEmpty(XLApp) then begin //释放对象 XLApp.displayAlerts:=false; XLApp.ScreenUpdating:=true; XLApp.quit; end; screen.Cursor:=crDefault; end; end.
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, Db, Grids, DBGrids, DBTables, ComObj, DBGridEh, Excel97;
const
X1NumberFormat = G/通用格式;
type TLoadFromExcelObject = class(TObject)
private
Xls: Variant; //内建EXCEL对象
FVisible: boolean; //进行操作时EXCEL是否可见
HasStarted: boolean;
FFileName: TFileName;
FEndRow: Word;
FStartCol: Word;
FStartRow: Word;
FEndCol: Word; //内部用于记录EXCEL对象是否启动
procedure SetVisible(const Value: boolean);
//得到表格标题最大的叠加层数
procedure SetEndRow(const Value: Word);
procedure SetFileName(const Value: TFileName);
procedure SetStartCol(const Value: Word);
procedure SetStartRow(const Value: Word);
procedure SetEndCol(const Value: Word);
public
//将一个数据集转到EXCEL
procedure LoadFromExcel(ADataSet: TDBDataSet);
constructor Create(AFileName: string); overload;
destructor Destroy; override;
property Visible: Boolean read FVisible write SetVisible;
property FileName: TFileName read FFileName write SetFileName;
property StartRow: Word read FStartRow write SetStartRow;
property EndRow: Word read FEndRow write SetEndRow;
property StartCol: Word read FStartCol write SetStartCol;
property EndCol: Word read FEndCol write SetEndCol;
end;
procedure LoadFromExcel(ADataSet: TDBDataSet; XlsFile: TFileName; StartRow: Integer = 1;
EndRow: Integer = 1; StartCol: Integer = 1; EndCol: Integer = 1);
implementation
procedure LoadFromExcel(ADataSet: TDBDataSet; XlsFile: TFileName; StartRow: Integer = 1;
EndRow: Integer = 1; StartCol: Integer = 1; EndCol: Integer = 1);
var
LoadExcel: TLoadFromExcelObject;
begin
LoadExcel := TLoadFromExcelObject.Create(XlsFile);
LoadExcel.StartRow := StartRow;
LoadExcel.EndRow := EndRow;
LoadExcel.StartCol := StartCol;
LoadExcel.EndCol := EndCol;
LoadExcel.LoadFromExcel(ADataSet);
LoadExcel.Free;
end;
{ TExcelObject }
//为了便于扩展,以后可在此增加属性初始化
constructor TLoadFromExcelObject.Create(AFileName: string);
begin
inherited Create;
Xls := CreateOleObject(Excel.Application);
Xls.Application.Workbooks.Open(AFileName);
Xls.Application.Visible := False;
StartRow := 1;
EndRow := 1;
StartCol := 1;
EndCol := 1;
end;
procedure TLoadFromExcelObject.SetVisible(const Value: boolean);
begin
if not HasStarted then exit;
FVisible := Value;
if Xls.Application.Visible <> FVisible then
Xls.Application.Visible := FVisible;
end;
//得到表格标题最大的叠加层数
procedure TLoadFromExcelObject.LoadFromExcel
(ADataSet: TDBDataSet);
var
I, J: Integer;
Index: Integer;
Sheet: Variant;
ECol, ERow: Integer;
//fDate: TDatetime;
begin
Sheet := Xls.Application.ActiveWorkBook.ActiveSheet;
ADataSet.Active := true;
if ADataSet.State in dsEditModes then
ADataSet.Post;
ECol := Sheet.UsedRange.Columns.Count;
if EndCol <> 1 then
ECol := EndCol;
ERow := Sheet.UsedRange.Rows.Count;
if EndRow <> 1 then
ERow := EndRow;
for I := StartRow to ERow do
begin
ADataSet.Append;
Index := 0;
J := StartCol;
while J <= ECol do
begin
if J > ADataSet.Fields.Count + 1 then
Break;
try
ADataSet.Fields[Index].AsString := VarToStr(Sheet.Cells[I, J].Value);
except
end;
Inc(J);
Inc(Index);
end;
ADataSet.Post;
end;
end;
procedure TLoadFromExcelObject.SetEndCol(const Value: Word);
begin
FEndCol := Value;
end;
procedure TLoadFromExcelObject.SetEndRow(const Value: Word);
begin
FEndRow := Value;
end;
procedure TLoadFromExcelObject.SetFileName(const Value: TFileName);
begin
FFileName := Value;
end;
procedure TLoadFromExcelObject.SetStartCol(const Value: Word);
begin
FStartCol := Value;
end;
procedure TLoadFromExcelObject.SetStartRow(const Value: Word);
begin
FStartRow := Value;
end;
destructor TLoadFromExcelObject.Destroy;
var i: integer;
begin
try
for i := 1 to Xls.Application.WorkBooks.Count - 1 do
Xls.Application.WorkBooks[i].Close(True, );
Xls.Application.Quit;
HasStarted := False;
finally
end;
inherited;
end;
end.
这两个例子,异构数据库之间导数据
第一种方法
在Form上放一个ADOConnection,连结指向目标Access库
先说,导入txt,比如txt文件在c:\temp\aaaa.txt
就执行
ADOConnection.Connected := True;
ADOConnection.Execute(Select * Into abcd From [Text;Database=c:\temp].aaaa.txt);
把Excel导入到Access中,同txt类似
select * into from [excel 8.0;database=].[$]
第二种方法 从Excel写数据到Access库
//excel到access
uses comobj,excel97,excel2000;
//从Excel写数据到Access库
prodedure ExcelToMdb(EXLfile:string;);
var
sheet,XLApp,workbook : variant;
iRow,MaxRow:integer;
begin
screen.Cursor:=crHourGlass;
try
//创建对象
XLApp:=createOleObject(Excel.Application);
XLApp.displayAlerts:=false;
XLApp.ScreenUpdating:=false;
XLApp.WorkBooks.Add(EXLfile);
workbook := XLApp.workbooks[1];
sheet:=workbook.worksheets[1];
//sheet:=XLApp.WorkBooks[1].worksheets[1];
//取得最大行数 maxRow
XLApp.ActiveCell.SpecialCells(xlLastCell).Select;
maxRow:=XLApp.ActiveCell.Row; //最大行数
//写数据到Access库
ADOTable1.open;
for iRow:=2 to MaxRow do
if sheet.cells[iRow,1]<> then //关键字不为空
begin
ADOTable1.Append ;
ADOTable1.fieldByName(ID).asInteger:=
strToInt(sheet.cells[iRow,1]);
ADOTable1.fieldByName(code).asString:=sheet.cells[iRow,2]; //编码
ADOTable1.fieldByName(name).asString:=sheet.cells[iRow,3]; //名称
ADOTable1.post;
end;
finally
if not VarIsEmpty(XLApp) then begin //释放对象
XLApp.displayAlerts:=false;
XLApp.ScreenUpdating:=true;
XLApp.quit;
end;
screen.Cursor:=crDefault;
end;
end.