2、把SQL Server数据库某表的内容导出到EXCEL表
我比较弱,第一次做导入导出,希望大虾们说详细点,感激!
15 个解决方案
#1
给出代码就太感谢了!
#2
Procudure ExcelToSqlserver;
var
i,j: integer;
ExcelApplication1:TExcelApplication;
ExcelWorksheet1:TExcelWorksheet;
ExcelWorkbook1:TExcelWorkbook;
filename: string;
begin
btn2Click(Self);
filename:=Edt_1.Text;//文件名
try
ExcelApplication1:=TExcelApplication.Create(Application);
ExcelWorksheet1:=TExcelWorksheet.Create(Application);
ExcelWorkbook1:=TExcelWorkbook.Create(Application);
ExcelApplication1.Connect;
except
Application.Messagebox('Excel没有安装!', 'Hello', MB_ICONERROR + mb_Ok);
Abort;
end;
Try
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Open(fileName,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0));
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
Except
Exit;
end;
i :=2;//开始导入行数
// j :=100;//默认导入总行数
j := ExcelApplication1.Rows.Count;
try
tblTA.Open; //要导入的表
while i<=j do
begin
if trim(ExcelWorksheet1.cells.item[i,1])<>'' then
begin
tblTA.AppendRecord([
ExcelWorksheet1.Cells.Item[i,1],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,2],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,3],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,4],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,5],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,6],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,7],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,8],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,9],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,10],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,11]//第i行第3列.
]);
end else Break;
Inc(i);
end;
finally
tblTA.close;
tblTA.Open;
showMessage('数据导入完毕!');
end;
try
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
ExcelApplication1.Free;
ExcelWorksheet1.Free;
ExcelWorkbook1.Free;
except
showMessage('关闭出错!');
end;
end;
var
i,j: integer;
ExcelApplication1:TExcelApplication;
ExcelWorksheet1:TExcelWorksheet;
ExcelWorkbook1:TExcelWorkbook;
filename: string;
begin
btn2Click(Self);
filename:=Edt_1.Text;//文件名
try
ExcelApplication1:=TExcelApplication.Create(Application);
ExcelWorksheet1:=TExcelWorksheet.Create(Application);
ExcelWorkbook1:=TExcelWorkbook.Create(Application);
ExcelApplication1.Connect;
except
Application.Messagebox('Excel没有安装!', 'Hello', MB_ICONERROR + mb_Ok);
Abort;
end;
Try
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Open(fileName,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0));
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
Except
Exit;
end;
i :=2;//开始导入行数
// j :=100;//默认导入总行数
j := ExcelApplication1.Rows.Count;
try
tblTA.Open; //要导入的表
while i<=j do
begin
if trim(ExcelWorksheet1.cells.item[i,1])<>'' then
begin
tblTA.AppendRecord([
ExcelWorksheet1.Cells.Item[i,1],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,2],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,3],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,4],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,5],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,6],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,7],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,8],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,9],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,10],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,11]//第i行第3列.
]);
end else Break;
Inc(i);
end;
finally
tblTA.close;
tblTA.Open;
showMessage('数据导入完毕!');
end;
try
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
ExcelApplication1.Free;
ExcelWorksheet1.Free;
ExcelWorkbook1.Free;
except
showMessage('关闭出错!');
end;
end;
#3
procedure ToExcel;
var
RangeE:Excel97.Range;
Row:Integer;
Bookmark1:TBookmarkStr;
procedure InsertRecord();
var
I:Integer;
str : string;
begin
with ADOQuery1 do
begin
//加入字段标题
RangeE:=ExcelApplication1.ActiveCell;
for I:=0 to Fields.Count -1 do
begin
RangeE.Value:=Fields[I].DisplayLabel;
RangeE:=RangeE.Next;
end;
//加入字段数据
DisableControls;
try
Bookmark1:=Bookmark;
try
First;
Row:=2;
ProgressBar1.Visible := true;
ProgressBar1.Max := ADOQuery1.RecordCount;
while not EOF do
begin
RangeE:=ExcelApplication1.Range['A'+IntToStr(Row),'A'+IntToStr(Row)];
for I:=0 to Fields.Count -1 do
begin
RangeE.Value:=''''+Fields[I].AsString;
RangeE:=RangeE.Next;
end;
Next;
Inc(Row);
ProgressBar1.StepBy(1);
end;
finally
Bookmark1:=Bookmark;
end;
finally
EnableControls;
end;
end;
end;
begin
try
ExcelApplication1.Connect;//打开Excel应用程序
ExcelApplication1.Workbooks.Add(NULL,0);
//创建Excel工作簿
InsertRecord();//加入记录到工作簿中
ExcelApplication1.Visible[0]:=True;
showmessage('数据导出成功!');
ProgressBar1.Visible := false;
//使Excel应用程序可见
finally
ExcelApplication1.disConnect;
//关闭Excel应用程序
end;
end;
var
RangeE:Excel97.Range;
Row:Integer;
Bookmark1:TBookmarkStr;
procedure InsertRecord();
var
I:Integer;
str : string;
begin
with ADOQuery1 do
begin
//加入字段标题
RangeE:=ExcelApplication1.ActiveCell;
for I:=0 to Fields.Count -1 do
begin
RangeE.Value:=Fields[I].DisplayLabel;
RangeE:=RangeE.Next;
end;
//加入字段数据
DisableControls;
try
Bookmark1:=Bookmark;
try
First;
Row:=2;
ProgressBar1.Visible := true;
ProgressBar1.Max := ADOQuery1.RecordCount;
while not EOF do
begin
RangeE:=ExcelApplication1.Range['A'+IntToStr(Row),'A'+IntToStr(Row)];
for I:=0 to Fields.Count -1 do
begin
RangeE.Value:=''''+Fields[I].AsString;
RangeE:=RangeE.Next;
end;
Next;
Inc(Row);
ProgressBar1.StepBy(1);
end;
finally
Bookmark1:=Bookmark;
end;
finally
EnableControls;
end;
end;
end;
begin
try
ExcelApplication1.Connect;//打开Excel应用程序
ExcelApplication1.Workbooks.Add(NULL,0);
//创建Excel工作簿
InsertRecord();//加入记录到工作簿中
ExcelApplication1.Visible[0]:=True;
showmessage('数据导出成功!');
ProgressBar1.Visible := false;
//使Excel应用程序可见
finally
ExcelApplication1.disConnect;
//关闭Excel应用程序
end;
end;
#4
谢谢````导出呢?
#5
哦呵呵````看慢了一步!
太感谢了!
太感谢了!
#6
不客气
#7
这么麻烦?用sql一句就搞定。具体可以到大富翁去找找碧血剑的帖子。
#8
楼上的能说说简单的方法吗?
Insert temp SELECT * FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source=" '+FileListBox1.FileName+' ";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False'')...sheet1$ '
帮我改下!!!谢谢
Insert temp SELECT * FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source=" '+FileListBox1.FileName+' ";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False'')...sheet1$ '
帮我改下!!!谢谢
#9
大虾们 拜托一下
#10
mark
#11
sql server导出到excel
ADOConnection 指向excel, 用Jet4.0 ,Extended properties设为Excel 8.0
SELECT * into table FROM Tab1 IN [ODBC]
[ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=Demo;]
insert into gds_order select * from gds_order
in [ODBC][ODBC;dsn=jbcmis;User Name=informix;password=informix;]
ADOConnection 指向excel, 用Jet4.0 ,Extended properties设为Excel 8.0
SELECT * into table FROM Tab1 IN [ODBC]
[ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=Demo;]
insert into gds_order select * from gds_order
in [ODBC][ODBC;dsn=jbcmis;User Name=informix;password=informix;]
#12
//用SQL直接导Excel表到Sql server2000
Select * Into tempdb..表A From'#$D'OpenDataSource('MICROSOFT.JET.OLEDB.4.0','Data Source=D:\ExcelTmp.xls;Extended Properties="Excel 8.0";Persist Security Info=False')...表A
Select * Into tempdb..表A From'#$D'OpenDataSource('MICROSOFT.JET.OLEDB.4.0','Data Source=D:\ExcelTmp.xls;Extended Properties="Excel 8.0";Persist Security Info=False')...表A
#13
gzclove(西二小风) 能否提供一下全部的源程序啊.
#14
还不够全吗???
#15
够狠的啊!!!!
#1
给出代码就太感谢了!
#2
Procudure ExcelToSqlserver;
var
i,j: integer;
ExcelApplication1:TExcelApplication;
ExcelWorksheet1:TExcelWorksheet;
ExcelWorkbook1:TExcelWorkbook;
filename: string;
begin
btn2Click(Self);
filename:=Edt_1.Text;//文件名
try
ExcelApplication1:=TExcelApplication.Create(Application);
ExcelWorksheet1:=TExcelWorksheet.Create(Application);
ExcelWorkbook1:=TExcelWorkbook.Create(Application);
ExcelApplication1.Connect;
except
Application.Messagebox('Excel没有安装!', 'Hello', MB_ICONERROR + mb_Ok);
Abort;
end;
Try
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Open(fileName,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0));
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
Except
Exit;
end;
i :=2;//开始导入行数
// j :=100;//默认导入总行数
j := ExcelApplication1.Rows.Count;
try
tblTA.Open; //要导入的表
while i<=j do
begin
if trim(ExcelWorksheet1.cells.item[i,1])<>'' then
begin
tblTA.AppendRecord([
ExcelWorksheet1.Cells.Item[i,1],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,2],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,3],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,4],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,5],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,6],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,7],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,8],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,9],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,10],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,11]//第i行第3列.
]);
end else Break;
Inc(i);
end;
finally
tblTA.close;
tblTA.Open;
showMessage('数据导入完毕!');
end;
try
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
ExcelApplication1.Free;
ExcelWorksheet1.Free;
ExcelWorkbook1.Free;
except
showMessage('关闭出错!');
end;
end;
var
i,j: integer;
ExcelApplication1:TExcelApplication;
ExcelWorksheet1:TExcelWorksheet;
ExcelWorkbook1:TExcelWorkbook;
filename: string;
begin
btn2Click(Self);
filename:=Edt_1.Text;//文件名
try
ExcelApplication1:=TExcelApplication.Create(Application);
ExcelWorksheet1:=TExcelWorksheet.Create(Application);
ExcelWorkbook1:=TExcelWorkbook.Create(Application);
ExcelApplication1.Connect;
except
Application.Messagebox('Excel没有安装!', 'Hello', MB_ICONERROR + mb_Ok);
Abort;
end;
Try
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Open(fileName,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0));
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
Except
Exit;
end;
i :=2;//开始导入行数
// j :=100;//默认导入总行数
j := ExcelApplication1.Rows.Count;
try
tblTA.Open; //要导入的表
while i<=j do
begin
if trim(ExcelWorksheet1.cells.item[i,1])<>'' then
begin
tblTA.AppendRecord([
ExcelWorksheet1.Cells.Item[i,1],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,2],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,3],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,4],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,5],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,6],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,7],//第i行第1列.
ExcelWorksheet1.Cells.Item[i,8],//第i行第2列.
ExcelWorksheet1.Cells.Item[i,9],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,10],//第i行第3列.
ExcelWorksheet1.Cells.Item[i,11]//第i行第3列.
]);
end else Break;
Inc(i);
end;
finally
tblTA.close;
tblTA.Open;
showMessage('数据导入完毕!');
end;
try
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
ExcelApplication1.Free;
ExcelWorksheet1.Free;
ExcelWorkbook1.Free;
except
showMessage('关闭出错!');
end;
end;
#3
procedure ToExcel;
var
RangeE:Excel97.Range;
Row:Integer;
Bookmark1:TBookmarkStr;
procedure InsertRecord();
var
I:Integer;
str : string;
begin
with ADOQuery1 do
begin
//加入字段标题
RangeE:=ExcelApplication1.ActiveCell;
for I:=0 to Fields.Count -1 do
begin
RangeE.Value:=Fields[I].DisplayLabel;
RangeE:=RangeE.Next;
end;
//加入字段数据
DisableControls;
try
Bookmark1:=Bookmark;
try
First;
Row:=2;
ProgressBar1.Visible := true;
ProgressBar1.Max := ADOQuery1.RecordCount;
while not EOF do
begin
RangeE:=ExcelApplication1.Range['A'+IntToStr(Row),'A'+IntToStr(Row)];
for I:=0 to Fields.Count -1 do
begin
RangeE.Value:=''''+Fields[I].AsString;
RangeE:=RangeE.Next;
end;
Next;
Inc(Row);
ProgressBar1.StepBy(1);
end;
finally
Bookmark1:=Bookmark;
end;
finally
EnableControls;
end;
end;
end;
begin
try
ExcelApplication1.Connect;//打开Excel应用程序
ExcelApplication1.Workbooks.Add(NULL,0);
//创建Excel工作簿
InsertRecord();//加入记录到工作簿中
ExcelApplication1.Visible[0]:=True;
showmessage('数据导出成功!');
ProgressBar1.Visible := false;
//使Excel应用程序可见
finally
ExcelApplication1.disConnect;
//关闭Excel应用程序
end;
end;
var
RangeE:Excel97.Range;
Row:Integer;
Bookmark1:TBookmarkStr;
procedure InsertRecord();
var
I:Integer;
str : string;
begin
with ADOQuery1 do
begin
//加入字段标题
RangeE:=ExcelApplication1.ActiveCell;
for I:=0 to Fields.Count -1 do
begin
RangeE.Value:=Fields[I].DisplayLabel;
RangeE:=RangeE.Next;
end;
//加入字段数据
DisableControls;
try
Bookmark1:=Bookmark;
try
First;
Row:=2;
ProgressBar1.Visible := true;
ProgressBar1.Max := ADOQuery1.RecordCount;
while not EOF do
begin
RangeE:=ExcelApplication1.Range['A'+IntToStr(Row),'A'+IntToStr(Row)];
for I:=0 to Fields.Count -1 do
begin
RangeE.Value:=''''+Fields[I].AsString;
RangeE:=RangeE.Next;
end;
Next;
Inc(Row);
ProgressBar1.StepBy(1);
end;
finally
Bookmark1:=Bookmark;
end;
finally
EnableControls;
end;
end;
end;
begin
try
ExcelApplication1.Connect;//打开Excel应用程序
ExcelApplication1.Workbooks.Add(NULL,0);
//创建Excel工作簿
InsertRecord();//加入记录到工作簿中
ExcelApplication1.Visible[0]:=True;
showmessage('数据导出成功!');
ProgressBar1.Visible := false;
//使Excel应用程序可见
finally
ExcelApplication1.disConnect;
//关闭Excel应用程序
end;
end;
#4
谢谢````导出呢?
#5
哦呵呵````看慢了一步!
太感谢了!
太感谢了!
#6
不客气
#7
这么麻烦?用sql一句就搞定。具体可以到大富翁去找找碧血剑的帖子。
#8
楼上的能说说简单的方法吗?
Insert temp SELECT * FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source=" '+FileListBox1.FileName+' ";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False'')...sheet1$ '
帮我改下!!!谢谢
Insert temp SELECT * FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source=" '+FileListBox1.FileName+' ";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False'')...sheet1$ '
帮我改下!!!谢谢
#9
大虾们 拜托一下
#10
mark
#11
sql server导出到excel
ADOConnection 指向excel, 用Jet4.0 ,Extended properties设为Excel 8.0
SELECT * into table FROM Tab1 IN [ODBC]
[ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=Demo;]
insert into gds_order select * from gds_order
in [ODBC][ODBC;dsn=jbcmis;User Name=informix;password=informix;]
ADOConnection 指向excel, 用Jet4.0 ,Extended properties设为Excel 8.0
SELECT * into table FROM Tab1 IN [ODBC]
[ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=Demo;]
insert into gds_order select * from gds_order
in [ODBC][ODBC;dsn=jbcmis;User Name=informix;password=informix;]
#12
//用SQL直接导Excel表到Sql server2000
Select * Into tempdb..表A From'#$D'OpenDataSource('MICROSOFT.JET.OLEDB.4.0','Data Source=D:\ExcelTmp.xls;Extended Properties="Excel 8.0";Persist Security Info=False')...表A
Select * Into tempdb..表A From'#$D'OpenDataSource('MICROSOFT.JET.OLEDB.4.0','Data Source=D:\ExcelTmp.xls;Extended Properties="Excel 8.0";Persist Security Info=False')...表A
#13
gzclove(西二小风) 能否提供一下全部的源程序啊.
#14
还不够全吗???
#15
够狠的啊!!!!