当前位置：主页>仓库管理软件> 列表

请问：如何将Excel表中的数据引入Access数据库中 找财务软件终身免费版

进销存软件版1楼: 问题如标题。我用的是ADO。

2楼: {---------------------------------------------------}
{ 操作 EXCEL }
{ 作者 : SUBIN }
{ 时间 : 2006.01.04 }
{---------------------------------------------------}

unit UExcel;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs,ComObj,DB, ADODB,IniFiles ;

type
TBaseExcel=Class
private
FSheetTitle : String;
FSheetIndex : Integer;
FFileName : String;
FExcelApp : Variant;
FExcelSheet : Variant;
function GetColColunt : Integer;
function GetRowCount : Integer;
function GetSheetCount : Integer;
procedure SetSheetTitle(const Value : String);
procedure SetSheetIndex(const Value : Integer);
procedure SetFileName (const Value : String);
procedure SetExcelApp (const Value : Variant);


procedure SetExcelSheet(const Value : Variant);
protected
function InitExcel : Boolean;virtual;
function FinallyExcel : Boolean;virtual;
function GetCellValue (Row,Col:Integer):String;
procedure SetCellValue(Row,Col:Integer;Value:String);

property SheetTitle : String read FSheetTitle write SetSheetTitle;
property SheetIndex : Integer read FSheetIndex write SetSheetIndex;
property FileName : String read FFileName write SetFileName;
property ExcelApp : Variant read FExcelApp write SetExcelApp;
property ExcelSheet : Variant read FExcelSheet write SetExcelSheet;
property ColCount : Integer read GetColColunt;
property RowCount : Integer read GetRowCount;
property SheetCount : Integer read GetSheetCount;
public
end;

const
DateType: array[0..2] of string=(＇＇yyyy-m-d h:mm＇＇,＇＇yyyy-m-d＇＇,＇＇h:mm:ss＇＇);

type
TFieldInfo=Record
MDB_FieldName : String[20];
XLS_FieldName : String[20];
FieldIndex : Integer;
end;

TExcelToDataBase=Class(TBaseExcel)
private
FTabelName : String;
FColumnTitleRow : Integer;
FDataStartRow : Integer;
FDataEndRow: Integer;
FAdoConn: TADOConnection;
FExcelID: String;
function GetFieldCount : Integer;
function GetFieldName : Boolean;
function GetFieldIndex : Boolean;
function GetDataRow : Boolean;
function CheckDateType (DateTypeStr:String):Boolean;
procedure SetTableName (const Value: String);
procedure SetColumnTitleRow(const Value: Integer);
procedure SetDataStartRow (const Value: Integer);
procedure SetDataEndRow (const Value: Integer);
procedure SetADOConn (const Value: TADOConnection);
procedure SetExcelID (const Value: String);
protected
FieldInfo : Array of TFieldInfo;
property ColCount; {Excel列}

property RowCount; {Excel行}
property ExcelApp;
property ExcelSheet;
property SheetTitle; {Excel标题}
property FieldCount : Integer read GetFieldCount; {字段数}
property TabelName : String read FTabelName write SetTableName;
property ColumnTitleRow : Integer read FColumnTitleRow write SetColumnTitleRow;{Column标题所在行}
property DataStartRow : Integer read FDataStartRow write SetDataStartRow; {数据开始行}
property DataEndRow : Integer read FDataEndRow write SetDataEndRow; {数据结束行}
property FileName;
property ExcelID : String read FExcelID write SetExcelID; {在Excel里的编号}

function InitExcel : Boolean;overload; {初始化Excel}
function FinallyExcel : Boolean;overload; {结束Excel}
function DataBaseRelation :Boolean;

function ExcelToDataBase(SheetTitle,TabelName:String) : Boolean; {导数据}
public
property ADOConn : TADOConnection read FAdoConn write SetADOConn;
end;

type
TLoadConfigType = (FromParams,FromWindow);
TOnWriteToDataBase = procedure(var TableName:String);{On导入}

TReadConfigExcelToDataBase=Class(TExcelToDataBase)
private
FConfigType : TLoadConfigType;
FOnWriteToDataBase : TOnWriteToDataBase;
procedure SetConfigType (const Value: TLoadConfigType);
procedure SetOnWriteToDataBase (const Value: TOnWriteToDataBase);
function ExcelToDataBase_FromParams:Boolean;
function ExcelToDataBase_FromWindow:Boolean;
protected
public
Function BeginExcelToDataBase:Boolean;
property ConfigType:TLoadConfigType read FConfigType write SetConfigType Default FromParams;
property OnWriteToDataBase:TOnWriteToDataBase read FOnWriteToDataBase write SetOnWriteToDataBase;

end;



implementation

uses GlobalData;

{ TBaseExcel }
function TBaseExcel.InitExcel: Boolean;
begin
Result:=False;
try
ExcelApp := CreateOleObject( ＇＇Excel.Application＇＇ );
except
exit;
end;

if not FileExists(FileName) then Exit;
ExcelApp.Visible := false;
try
ExcelApp.WorkBooks.Open(FileName);
except
exit;
end;
Result:=True;
end;

function TBaseExcel.FinallyExcel: Boolean;
begin
Result:=False;
try
ExcelApp.WorkBooks.Close;
ExcelApp.Quit;
ExcelApp:=UnAssigned;
ExcelSheet:=UnAssigned;
Result:=True;
except
end;
end;

function TBaseExcel.GetCellValue(Row, Col: Integer): String;
begin
Result:=ExcelSheet.Cells[Row,Col].Value;
end;

function TBaseExcel.GetColColunt: Integer;
begin
Result:=ExcelSheet.UsedRange.columns.Count;
end;

function TBaseExcel.GetRowCount: Integer;
begin
Result:=ExcelSheet.UsedRange.Rows.Count;

end;

function TBaseExcel.GetSheetCount: Integer;
begin
Result:=ExcelApp.WorkSheets.Count;
end;

procedure TBaseExcel.SetCellValue(Row, Col: Integer; Value: String);
begin
ExcelSheet.Cells[Row,Col].Value:=Value;
end;

procedure TBaseExcel.SetExcelApp(const Value: Variant);
begin
FExcelApp := Value;
end;

procedure TBaseExcel.SetExcelSheet(const Value: Variant);
begin
FExcelSheet := Value;
end;

procedure TBaseExcel.SetFileName(const Value: String);
begin
FFileName := Value;
end;

procedure TBaseExcel.SetSheetIndex(const Value: Integer);
begin
FSheetIndex := Value;
end;

procedure TBaseExcel.SetSheetTitle(const Value: String);
begin
FSheetTitle := Value;
end;

{ TExcelToDataBase }

function TExcelToDataBase.FinallyExcel: Boolean;
begin
Result:=inherited FinallyExcel;
end;

function TExcelToDataBase.InitExcel: Boolean;
begin
Result:=inherited InitExcel;
end;


function TExcelToDataBase.GetFieldCount: Integer;
var
IniFile:TIniFile;
begin
IniFile:=TIniFile.Create(GetCurrentDir+＇＇＼＇＇+＇＇FieldConfig.ini＇＇);
try
Result:= IniFile.ReadInteger(TabelName,＇＇fieldCount＇＇,0);
finally
IniFile.Free;
end;
end;

function TExcelToDataBase.CheckDateType(DateTypeStr: String): Boolean;
var
I:Integer;
begin
Result:=False;
for I:=0 to 2 do
begin
if DateType[i]=DateTypeStr then Result:=True;
end;
end;

{数据导入}
function TExcelToDataBase.ExcelToDataBase(SheetTitle,
TabelName: String): Boolean;
var
I,J:Integer;
CmdQuery:TAdoQuery;
V:Variant;
begin
Result:=True;
Self.TabelName:=TabelName;
Self.SheetTitle:=SheetTitle;
if FieldCount>0 then
begin
{Select Excel ActiveSheet}
ExcelApp.WorkSheets[Self.SheetTitle].Activate;
ExcelSheet:=ExcelApp.ActiveSheet;
{Init FieldInfo}
SetLength(FieldInfo,FieldCount);


if not GetDataRow then Exit;
if not GetFieldName then Exit;
if not GetFieldIndex then Exit;

try
CmdQuery:=TAdoQuery.Create(Nil);
CmdQuery.Close;
CmdQuery.Connection:=ADOConn;
CmdQuery.SQL.Text:=＇＇Select * From ＇＇+TabelName;
CmdQuery.Open;

try
for I:=DataStartRow to DataEndRow do
begin
CmdQuery.Insert;
{Insert ExcelID}
if ExcelID<>＇＇＇＇ then
begin
CmdQuery.FieldByName(ExcelID).AsString:=IntToStr(I);
end;

for J:=0 to FieldCount-1 do
begin
if FieldInfo[j].FieldIndex>=0 then
begin
V:=ExcelSheet.Cells[i,FieldInfo[j].FieldIndex].Value;
if String(V)<>＇＇＇＇ then
begin

if CmdQuery.FieldByName(FieldInfo[j].MDB_FieldName).DataType=ftDateTime then
begin
{检测是否为时间类型}
if CheckDateType(ExcelSheet.Cells[i,FieldInfo[j].FieldIndex].NumberFormatLocal) then
begin
CmdQuery.FieldByName(FieldInfo[j].MDB_FieldName).AsString
:=ExcelSheet.Cells[i,FieldInfo[j].FieldIndex].Value;
end else
begin
{不标准时间转换}
end;
end else
begin
CmdQuery.FieldByName(FieldInfo[j].MDB_FieldName).AsString
:=ExcelSheet.Cells[i,FieldInfo[j].FieldIndex].value;
end;

end;
end;
end;
CmdQuery.Post;
end;
except
Result:=False;
end;
finally
CmdQuery.Free;
end;
end;
end;

{数据关联}
function TExcelToDataBase.DataBaseRelation: Boolean;
var
SearchQuery,UPDataQuery:TAdoQuery;
I:Integer;
str:string;
begin
SearchQuery:=TAdoQuery.Create(Nil);
UPDataQuery:=TAdoQuery.Create(Nil);
try
SearchQuery.Close;
SearchQuery.Connection:=ADOConn;
UPDataQuery.Close;
UPDataQuery.Connection:=ADOConn;

SearchQuery.SQL.Text:=＇＇Select * From 车辆信息表＇＇;
SearchQuery.Open;
SearchQuery.First;
for I:=0 to SearchQuery.RecordCount-1 do
begin
UPDataQuery.Close;
UPDataQuery.SQL.Text:=＇＇UpDate 车辆信息表 set ＇＇
+＇＇车主编号=(Select top 1 编号 from 车主信息表 where ＇＇
+ExcelID
+＇＇=＇＇
+SearchQuery.FieldByName(ExcelID).AsString
+＇＇＇＇
+＇＇)＇＇
+＇＇,＇＇
+＇＇ 联系人编号=(Select top 1 编号 from 联系人信息表 where ＇＇
+ExcelID
+＇＇=＇＇
+＇＇＇＇
+SearchQuery.FieldByName(ExcelID).AsString
+＇＇＇＇
+＇＇)＇＇
+＇＇ where ＇＇
+ExcelID


+＇＇=＇＇＇＇＇＇
+SearchQuery.FieldByName(ExcelID).AsString
+＇＇＇＇＇＇＇＇;
str:=UPDataQuery.SQL.Text;
UPDataQuery.ExecSQL;
SearchQuery.Next;
end;
finally
SearchQuery.Free;
UPDataQuery.Free;
end;
end;

function TExcelToDataBase.GetFieldIndex: Boolean;
var
I:Integer;
J:Integer;
begin
Result:=False;
for I:=1 to ColCount do
begin
for J:=0 to FieldCount-1 do
begin
if ExcelSheet.Cells[ColumnTitleRow,i].value=FieldInfo[j].XLS_FieldName then
begin
FieldInfo[j].FieldIndex:=I;
end;
end;
end;
Result:=True;
end;

function TExcelToDataBase.GetFieldName: Boolean;
var
IniFile:TIniFile;
I:Integer;
begin
Result:=False;
IniFile:=TIniFile.Create(GetCurrentDir+＇＇＼＇＇+＇＇FieldConfig.ini＇＇);
try
for i:=1 to FieldCount do
begin
FieldInfo[i-1].MDB_FieldName:=IniFile.ReadString(TabelName,＇＇MDB_FieldName＇＇+IntToStr(i),＇＇＇＇);
FieldInfo[i-1].XLS_FieldName:=IniFile.ReadString(TabelName,＇＇XLS_FieldName＇＇+IntToStr(i),＇＇＇＇);
FieldInfo[i-1].FieldIndex:=-1;
end;
ExcelID:=IniFile.ReadString(TabelName,＇＇ExcelID＇＇,＇＇＇＇);
Result:=True;
finally
IniFile.Free;
end;
end;

function TExcelToDataBase.GetDataRow: Boolean;
var
IniFile:TIniFile;
begin
Result:=False;
IniFile:=TIniFile.Create(GetCurrentDir+＇＇＼＇＇+＇＇FieldConfig.ini＇＇);
try
ColumnTitleRow:=IniFile.ReadInteger(TabelName,＇＇TitleRow＇＇,0);
DataStartRow:=IniFile.ReadInteger(TabelName,＇＇DataStartRow＇＇,0);
DataEndRow:=IniFile.ReadInteger(TabelName,＇＇DataEndRow＇＇,0);
Result:=True;
finally
IniFile.Free;
end;
end;

procedure TExcelToDataBase.SetTableName(const Value: String);
begin
FTabelName := Value;
end;

procedure TExcelToDataBase.SetColumnTitleRow(const Value: Integer);
begin
FColumnTitleRow := Value;


end;

procedure TExcelToDataBase.SetDataStartRow(const Value: Integer);
begin
FDataStartRow := Value;
end;

procedure TExcelToDataBase.SetDataEndRow(const Value: Integer);
begin
FDataEndRow := Value;
end;

procedure TExcelToDataBase.SetADOConn(const Value: TADOConnection);
begin
FAdoConn := Value;
end;

procedure TExcelToDataBase.SetExcelID(const Value: String);
begin
FExcelID := Value;
end;

{ TReadConfigExcelToDataBase }

function TReadConfigExcelToDataBase.BeginExcelToDataBase: Boolean;
begin
Result:=False;
if ConfigType=FromParams then
begin
{导入}
ExcelToDataBase_FromParams;
{关联}
if ExcelID<>＇＇＇＇ then DataBaseRelation;
end;
if ConfigType=FromWindow then
begin
ExcelToDataBase_FromWindow;
end;
Result:=True;
end;

{从配置文件里读配置 然后导入}
function TReadConfigExcelToDataBase.ExcelToDataBase_FromParams: Boolean;
var
IniFile:TIniFile;
TableCount:Integer;
I:Integer;
ResultStr:String;
begin
Result:=False;
IniFile:=TIniFile.Create(GetCurrentDir+＇＇＼＇＇+＇＇FieldConfig.ini＇＇);
try
{GetFileName}
FileName:=＇＇＇＇;
FileName:=IniFile.ReadString(＇＇TableInfo＇＇,＇＇XlsFileName＇＇,＇＇kehu.xls＇＇);
FileName:=GetCurrentDir+＇＇＼＇＇+FileName;
{Init}
if not InitExcel then
begin
ResultStr:=＇＇初始化失败！＇＇;
OnWriteToDataBase(ResultStr);
exit;
end;
{GetTableCount}
TableCount:=IniFile.ReadInteger(＇＇TableInfo＇＇,＇＇TableCount＇＇,0);
if TableCount=0 then
begin
ResultStr:=＇＇数据表数量为0！＇＇;
OnWriteToDataBase(ResultStr);
Exit;
end;
{Do}
for I:=1 to TableCount do
begin
try
if ExcelToDataBase(IniFile.ReadString(＇＇TableInfo＇＇,＇＇SheetTitle＇＇+IntTOStr(I),＇＇＇＇)
,IniFile.ReadString(＇＇TableInfo＇＇,＇＇TableName＇＇+IntTOStr(I),＇＇＇＇)) then


ResultStr:=IniFile.ReadString(＇＇TableInfo＇＇,＇＇TableName＇＇+IntToStr(I),＇＇＇＇)+＇＇ 导入成功＇＇
else
ResultStr:=IniFile.ReadString(＇＇TableInfo＇＇,＇＇TableName＇＇+IntToStr(I),＇＇＇＇)+＇＇ 导入失败＇＇;
except
ResultStr:=IniFile.ReadString(＇＇TableInfo＇＇,＇＇TableName＇＇+IntToStr(I),＇＇＇＇)+＇＇ 导入失败＇＇;
end;
OnWriteToDataBase(ResultStr);
end;
finally
IniFile.Free;
FinallyExcel;
end;
Result:=True;
end;

{从窗体上读配置 然后导入}
function TReadConfigExcelToDataBase.ExcelToDataBase_FromWindow: Boolean;
begin
Result:=False;
{......}
Result:=True;
end;

procedure TReadConfigExcelToDataBase.SetConfigType(
const Value: TLoadConfigType);
begin
FConfigType := Value;
end;

procedure TReadConfigExcelToDataBase.SetOnWriteToDataBase(
const Value: TOnWriteToDataBase);
begin
FOnWriteToDataBase := Value;
end;

end. 如财务软件终身免费版

3楼: 先导出到SQL，再从SQL导出到ACCESS

4楼: 怎么这么麻烦啊，其实直接用sql语法在sql查询分析器就可以完成了：
insert into OPENROWSET(＇＇Microsoft.Jet.OLEDB.4.0＇＇,
＇＇E:＼DataTransmit＼DataTrans＼Datatrans-V3＼TransDataDB1.mdb＇＇;＇＇admin＇＇;＇＇＇＇, testdata)
(userid,synum,grou_table,xmid,curorder,maxload,testdate,testtime)

select userid,synum,grou_table,xmid,curorder,maxload,testdate,testtime
FROM OpenDataSource( ＇＇Microsoft.Jet.OLEDB.4.0＇＇,
＇＇Data Source=＂E:＼DataTransmit＼DataTrans＼Datatrans-V3＼Data20050929.xls＂;User ID=Admin;Password=;Extended properties=Excel 8.0＇＇)...Data20050929$

5楼: 一楼的是不是太麻烦了？你这样做是不是有什么好处？
二楼的说得好像很容易，但如何导入到sql？
楼上的朋友，不明白你说的，能不能加上注释。这样更好懂。

6楼: 不会吧，不明白？！！
insert into
OPENROWSET(＇＇Microsoft.Jet.OLEDB.4.0＇＇,
＇＇E:＼DataTransmit＼DataTrans＼Datatrans-V3＼TransDataDB1.mdb＇＇;＇＇admin＇＇;＇＇＇＇, testdata)
(userid,synum,grou_table,xmid,curorder,maxload,testdate,testtime)
--E:＼DataTransmit＼DataTrans＼Datatrans-V3＼TransDataDB1.mdb为access数据库，testdata为要倒入数据的数据表
--userid,synum,grou_table,xmid,curorder,maxload,testdate,testtime为字段
select userid,synum,grou_table,xmid,curorder,maxload,testdate,testtime
FROM OpenDataSource( ＇＇Microsoft.Jet.OLEDB.4.0＇＇,
＇＇Data Source=＂E:＼DataTransmit＼DataTrans＼Datatrans-V3＼Data20050929.xls＂;User ID=Admin;Password=;Extended properties=Excel 8.0＇＇)...Data20050929$
--E:＼DataTransmit＼DataTrans＼Datatrans-V3＼Data20050929.xls为excel文件
--Data20050929$为工作表，包含userid,synum,grou_table,xmid,curorder,maxload,testdate,testtime这些列，当然如果excel中没有标题（即列名），可以用f1,f2...代替，但字段类型、长度必须与前者一致


其实基本语法就是：
insert into table1(field1,filed2..)
select(field1,filed2..) from table2

这里table1就是：
OPENROWSET(＇＇Microsoft.Jet.OLEDB.4.0＇＇,
＇＇E:＼DataTransmit＼DataTrans＼Datatrans-V3＼TransDataDB1.mdb＇＇;＇＇admin＇＇;＇＇＇＇, testdata)

table2就是：
OpenDataSource( ＇＇Microsoft.Jet.OLEDB.4.0＇＇,
＇＇Data Source=＂E:＼DataTransmit＼DataTrans＼Datatrans-V3＼Data20050929.xls＂;User ID=Admin;Password=;Extended properties=Excel 8.0＇＇)...Data20050929$



field1,filed2..就是：
userid,synum,grou_table,xmid,curorder,maxload,testdate,testtime

还不明白那我也没办法了

进销存软件版7楼: 其实基本语法就是：
insert into table1(field1,filed2..)
select(field1,filed2..) from table2

有这个就非常明白了。谢谢！先收下。研究通过给分。