数据库:sqlserver
表名称:tperson 字段:code varchar(10) name varchar(20)
创建一个结构体:
type
PPerson = ^TPerson;
TPerson = record
FCode: String;
FName: string;
end;
相关函数及变量
FPerson: PPerson;
FPersonList: TList; //用来存数据
function LoadExcel: Boolean; //加载Excel
function SaveToDB: Boolean; //写到数据库
function DBToExcel: Boolean; //导出数据
两个按钮,一个adoconnection,一个adoquery
看到好多人问关于Excel方面的问题,所以写了这个比较简单的例子,有好多细节也没考虑,供参考
在真正写的时候,要注意指针的释放等.
全部源码:
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ComOBJ, DB, ADODB;
type
PPerson = ^TPerson;
TPerson = record
FCode: String;
FName: string;
end;
type
TForm1 = class(TForm)
Button1: TButton;
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
Button2: TButton;
procedure FormCreate(Sender: TObject);
procedure FormDestroy(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
FPerson: PPerson;
FPersonList: TList;
function LoadExcel: Boolean;
function SaveToDB: Boolean;
function DBToExcel: Boolean;
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
function TForm1.LoadExcel: Boolean;
var
RangeMatrix: Variant;
OpenDialog1: TOpenDialog;
iRE: integer;
IN_TYPE,vStockName: String;
sStockName,sCompany: String;
i: Integer;
MsExcel, MsExcelWorkBook, MsExcelWorkSheet: Variant;
Col1,Col2: String;
begin
OpenDialog1:=TOpenDialog.Create(nil);
try
OpenDialog1.FileName:='*.csv;*.xls';
if not OpenDialog1.Execute then
begin
Result := false;
exit;
end;
MsExcel := CreateOleObject('Excel.Application');
MsExcelWorkBook := msExcel.Workbooks.Open(OpenDialog1.FileName);
MsExcelWorkSheet := msExcel.Worksheets.Item[1];
except
Result := false;
Exit;
end;
//开始从EXCEL文件读取相关的信息
try
try
Application.ProcessMessages;
for i := 1 to MsExcelWorkSheet.Rows.Count do
begin
if trim(MsExcelWorkSheet.Range['A' + IntToStr(i)].Value)='' then break;
New(FPerson);
FPerson.FCode := trim(MsExcelWorkSheet.Range['A' + IntToStr(i)].Value);
FPerson.FName := trim(MsExcelWorkSheet.Range['B' + IntToStr(i)].Value);
FPersonList.Add(FPerson);
end;
except
Result := false;
Exit;
end;
finally
MsExcel.WorkBooks.Close;
end;
Result := true;
MsExcel.Quit;
OpenDialog1.Free;
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
//创建一个list列表,以用来存取表数据
FPersonList := TList.create;
end;
procedure TForm1.FormDestroy(Sender: TObject);
begin
FPersonList.Free;//释放资源
end;
function TForm1.SaveToDB: Boolean;
var
i: integer;
begin
AdoConnection1.BeginTrans;
try
for i:= 0 to FPersonList.Count-1 do
begin
New(FPerson);
FPerson := FPersonList[i];
with adoquery1 do
begin
close;
sql.Text := ' insert into tperson(code,name) values(:code,:name)';
parameters.ParamByName('code').Value := FPerson.FCode;
Parameters.ParamByName('name').Value := FPerson.FName;
ExecSql;
end;
end;
AdoConnection1.CommitTrans;
Result := true;
except
AdoConnection1.RollbackTrans;
Result := false;
end;
end;
function TForm1.DBToExcel: Boolean;
var
SaveDialog: TSaveDialog;
curname,Str: String;
CsvFile: TextFile;
begin
SaveDialog := TSaveDialog.Create(nil);
SaveDialog.Filter := '.csv';
try
if SaveDialog.Execute then
begin
//开始保存文件
curname:=SaveDialog.FileName+'.csv'; //生成存储文件名
if FileExists(curname) then
begin
if (Application.MessageBox(PChar('文件【'+curname+'】已存在,要覆盖吗?'),'提
示',MB_ICONQUESTION+MB_YESNO)=IDYES) then
DeleteFile(PChar(curname))
else
begin
exit;
end;
end;
//写标题
AssignFile(CsvFile,curname);
Rewrite(CsvFile);
try
str:='';
str:='"'+'编号';
str:=str+'","'+'姓名';
str:=str+'"';
Writeln(CsvFile,str);
with adoquery1 do
begin
close;
sql.Text := ' select code,name from tperson';
Open;
while not eof do
begin
str:='"';
str:=str+StringReplace(adoquery1.FieldByName('code').AsString,'"','''''',[rfReplaceAll]);
str:=str+'","'+StringReplace(adoquery1.FieldByName('name').AsString,'"','''''',[rfReplaceAll]);
str:=str+'"';
Writeln(CsvFile,str);
Next;
end;
end;
finally
CloseFile(CsvFile);
end;
end;
finally
Savedialog.Free;
end;
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
LoadExcel;
SaveToDB;
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
DBToExcel;
end;
end.
表名称:tperson 字段:code varchar(10) name varchar(20)
创建一个结构体:
type
PPerson = ^TPerson;
TPerson = record
FCode: String;
FName: string;
end;
相关函数及变量
FPerson: PPerson;
FPersonList: TList; //用来存数据
function LoadExcel: Boolean; //加载Excel
function SaveToDB: Boolean; //写到数据库
function DBToExcel: Boolean; //导出数据
两个按钮,一个adoconnection,一个adoquery
看到好多人问关于Excel方面的问题,所以写了这个比较简单的例子,有好多细节也没考虑,供参考
在真正写的时候,要注意指针的释放等.
全部源码:
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ComOBJ, DB, ADODB;
type
PPerson = ^TPerson;
TPerson = record
FCode: String;
FName: string;
end;
type
TForm1 = class(TForm)
Button1: TButton;
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
Button2: TButton;
procedure FormCreate(Sender: TObject);
procedure FormDestroy(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
FPerson: PPerson;
FPersonList: TList;
function LoadExcel: Boolean;
function SaveToDB: Boolean;
function DBToExcel: Boolean;
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
function TForm1.LoadExcel: Boolean;
var
RangeMatrix: Variant;
OpenDialog1: TOpenDialog;
iRE: integer;
IN_TYPE,vStockName: String;
sStockName,sCompany: String;
i: Integer;
MsExcel, MsExcelWorkBook, MsExcelWorkSheet: Variant;
Col1,Col2: String;
begin
OpenDialog1:=TOpenDialog.Create(nil);
try
OpenDialog1.FileName:='*.csv;*.xls';
if not OpenDialog1.Execute then
begin
Result := false;
exit;
end;
MsExcel := CreateOleObject('Excel.Application');
MsExcelWorkBook := msExcel.Workbooks.Open(OpenDialog1.FileName);
MsExcelWorkSheet := msExcel.Worksheets.Item[1];
except
Result := false;
Exit;
end;
//开始从EXCEL文件读取相关的信息
try
try
Application.ProcessMessages;
for i := 1 to MsExcelWorkSheet.Rows.Count do
begin
if trim(MsExcelWorkSheet.Range['A' + IntToStr(i)].Value)='' then break;
New(FPerson);
FPerson.FCode := trim(MsExcelWorkSheet.Range['A' + IntToStr(i)].Value);
FPerson.FName := trim(MsExcelWorkSheet.Range['B' + IntToStr(i)].Value);
FPersonList.Add(FPerson);
end;
except
Result := false;
Exit;
end;
finally
MsExcel.WorkBooks.Close;
end;
Result := true;
MsExcel.Quit;
OpenDialog1.Free;
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
//创建一个list列表,以用来存取表数据
FPersonList := TList.create;
end;
procedure TForm1.FormDestroy(Sender: TObject);
begin
FPersonList.Free;//释放资源
end;
function TForm1.SaveToDB: Boolean;
var
i: integer;
begin
AdoConnection1.BeginTrans;
try
for i:= 0 to FPersonList.Count-1 do
begin
New(FPerson);
FPerson := FPersonList[i];
with adoquery1 do
begin
close;
sql.Text := ' insert into tperson(code,name) values(:code,:name)';
parameters.ParamByName('code').Value := FPerson.FCode;
Parameters.ParamByName('name').Value := FPerson.FName;
ExecSql;
end;
end;
AdoConnection1.CommitTrans;
Result := true;
except
AdoConnection1.RollbackTrans;
Result := false;
end;
end;
function TForm1.DBToExcel: Boolean;
var
SaveDialog: TSaveDialog;
curname,Str: String;
CsvFile: TextFile;
begin
SaveDialog := TSaveDialog.Create(nil);
SaveDialog.Filter := '.csv';
try
if SaveDialog.Execute then
begin
//开始保存文件
curname:=SaveDialog.FileName+'.csv'; //生成存储文件名
if FileExists(curname) then
begin
if (Application.MessageBox(PChar('文件【'+curname+'】已存在,要覆盖吗?'),'提
示',MB_ICONQUESTION+MB_YESNO)=IDYES) then
DeleteFile(PChar(curname))
else
begin
exit;
end;
end;
//写标题
AssignFile(CsvFile,curname);
Rewrite(CsvFile);
try
str:='';
str:='"'+'编号';
str:=str+'","'+'姓名';
str:=str+'"';
Writeln(CsvFile,str);
with adoquery1 do
begin
close;
sql.Text := ' select code,name from tperson';
Open;
while not eof do
begin
str:='"';
str:=str+StringReplace(adoquery1.FieldByName('code').AsString,'"','''''',[rfReplaceAll]);
str:=str+'","'+StringReplace(adoquery1.FieldByName('name').AsString,'"','''''',[rfReplaceAll]);
str:=str+'"';
Writeln(CsvFile,str);
Next;
end;
end;
finally
CloseFile(CsvFile);
end;
end;
finally
Savedialog.Free;
end;
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
LoadExcel;
SaveToDB;
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
DBToExcel;
end;
end.