-- 导入Excel到SQL数据库的方法
SELECT * INTO XLImport6
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:/abc.xls;
Extended Properties=Excel 8.0') //[Sheet1$]
SELECT * INTO XLImport7
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:/abc.xls', [Sheet1$])
SELECT * INTO XLImport8
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:/abc.xls',
'SELECT * FROM [Sheet1$]'
SQL导出到Excel
1)导出整个表:
use bcp, write out a csv file, :-), something like
xp_cmdshell "bcp <dbname>..<tablename> out c:/file.csv -Usa -P<password> -c"
2)导出表的部分内容:
no need to use a temporary table, you can use straight sql statement like this
master..xp_cmdshell 'bcp "SELECT * FROM pubs.dbo.t1" queryout c:/t1.csv -c -t, -Usa -P[password] -S[server name]'
[+RED]EXCEL怎样导入导出数据库[+black]
//导出数据到excel
procedure CopyDbDataToExcel(Target: TDbgrid);
var
iCount, jCount: Integer;
XLApp: Variant;
Sheet: Variant;
begin
Screen.Cursor := crHourGlass;
if not VarIsEmpty(XLApp) then
begin
XLApp.DisplayAlerts := False;
XLApp.Quit;
VarClear(XLApp);
end;
//通过ole创建Excel对象
try
XLApp := CreateOleObject('Excel.Application');
except
Screen.Cursor := crDefault;
Exit;
end;
XLApp.WorkBooks.Add[XLWBatWorksheet];
XLApp.WorkBooks[1].WorkSheets[1].Name := '对帐工作薄';
Sheet := XLApp.Workbooks[1].WorkSheets['对帐工作薄'];
if not Target.DataSource.DataSet.Active then
begin
Screen.Cursor := crDefault;
Exit;
end;
Target.DataSource.DataSet.first;
for iCount := 0 to Target.Columns.Count - 1 do
begin
Sheet.cells[1, iCount + 1] := Target.Columns.Items[iCount].Title.Caption;
end;
jCount := 1;
while not Target.DataSource.DataSet.Eof do
begin
for iCount := 0 to Target.Columns.Count - 1 do
begin
Sheet.cells[jCount + 1, iCount + 1] := Target.Columns.Items[iCount].Field.AsString;
end;
Inc(jCount);
Target.DataSource.DataSet.Next;
end;
XlApp.Visible := True;
Screen.Cursor := crDefault;
end;
procedure TfrmMain.Button1Click(Sender: TObject);
var i,j:integer;
opendialog1:Topendialog;
s0,s1,s2:string;
begin
try
opendialog1:=Topendialog.Create(self);
opendialog1.InitialDir:=ExtractFileDir(paramstr(0));//文件的打存放初始路径
if opendialog1.Execute then
begin
Try
ExcelApplication1.Connect;//EXCEL应用程序
Except
Messagebox(0,'Excel 可能没有安装!!','提示!',mb_Ok);
exit;
End;
ExcelApplication1.Visible[0]:=false;
ExcelApplication1.Caption:='Excel Application';
try
excelapplication1.Workbooks.Open(opendialog1.FileName,
null,null,null,null,null,null,null,null,null,null,null,null,0);//打开指定的EXCEL 文件
except
begin
ExcelApplication1.Disconnect;//出现异常情况时关闭
ExcelApplication1.Quit;showmessage('请选择EXCEL电子表格!');
exit;
end;
end;
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
//ExcelWorkbook1与Eexcelapplication1建立连接
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);
//Excelworksheet1与Excelworkbook1建立连接
//开始从EXCEL中取数,取完数后关闭EXCEL
for i:=1 to 10000 do//最大取值10000
begin
if trim(excelworksheet1.cells.item[i+1,1])<>'' then
begin
s0:= excelworksheet1.cells.item[i+1,1];
s1:= excelworksheet1.cells.item[i+1,2];
s2:= excelworksheet1.cells.item[i+1,3];
with qry do
begin
close;
sql.Clear;
sql.add('insert into xsz(PZNO,PNO,TCount,inday)values('+
''''+s0+''''+','+''''+s1+''''+','+''''+s2+''''+','+''''+datetostr(date)+''')');
ExecSQL;
end;
end
end;
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
end;
with qryxsz do
begin
sql.Clear;
sql.Add('select * from xsz order by PZNO,PNO,TCount,inday');
open;
end;
for j:=0 to grdxsz.Columns.Count do
grdxsz.Columns[j].Width:=64;//缩小宽度
messagebox(0,'文件已经导入数据库!','完成!',mb_ok);
except
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
exit;
// messagebox(0,'文件已经导入数据库失败!','完成!',mb_iconError+mb_ok);
end;
end;
try
if qryxsz.RecordCount<1 then exit;
CopyDbDataToExcel(Grdxsz);
except
// messagebox(0,'请安装EXCEL软件,再使用本功能。','提示',mb_ok);
exit;
end;
这是我在一个对帐系统中用到的将两张excel表中的数据到如数据库进行相应处理后再导出excel.
SELECT * INTO XLImport6
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:/abc.xls;
Extended Properties=Excel 8.0') //[Sheet1$]
SELECT * INTO XLImport7
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:/abc.xls', [Sheet1$])
SELECT * INTO XLImport8
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:/abc.xls',
'SELECT * FROM [Sheet1$]'
SQL导出到Excel
1)导出整个表:
use bcp, write out a csv file, :-), something like
xp_cmdshell "bcp <dbname>..<tablename> out c:/file.csv -Usa -P<password> -c"
2)导出表的部分内容:
no need to use a temporary table, you can use straight sql statement like this
master..xp_cmdshell 'bcp "SELECT * FROM pubs.dbo.t1" queryout c:/t1.csv -c -t, -Usa -P[password] -S[server name]'
[+RED]EXCEL怎样导入导出数据库[+black]
//导出数据到excel
procedure CopyDbDataToExcel(Target: TDbgrid);
var
iCount, jCount: Integer;
XLApp: Variant;
Sheet: Variant;
begin
Screen.Cursor := crHourGlass;
if not VarIsEmpty(XLApp) then
begin
XLApp.DisplayAlerts := False;
XLApp.Quit;
VarClear(XLApp);
end;
//通过ole创建Excel对象
try
XLApp := CreateOleObject('Excel.Application');
except
Screen.Cursor := crDefault;
Exit;
end;
XLApp.WorkBooks.Add[XLWBatWorksheet];
XLApp.WorkBooks[1].WorkSheets[1].Name := '对帐工作薄';
Sheet := XLApp.Workbooks[1].WorkSheets['对帐工作薄'];
if not Target.DataSource.DataSet.Active then
begin
Screen.Cursor := crDefault;
Exit;
end;
Target.DataSource.DataSet.first;
for iCount := 0 to Target.Columns.Count - 1 do
begin
Sheet.cells[1, iCount + 1] := Target.Columns.Items[iCount].Title.Caption;
end;
jCount := 1;
while not Target.DataSource.DataSet.Eof do
begin
for iCount := 0 to Target.Columns.Count - 1 do
begin
Sheet.cells[jCount + 1, iCount + 1] := Target.Columns.Items[iCount].Field.AsString;
end;
Inc(jCount);
Target.DataSource.DataSet.Next;
end;
XlApp.Visible := True;
Screen.Cursor := crDefault;
end;
procedure TfrmMain.Button1Click(Sender: TObject);
var i,j:integer;
opendialog1:Topendialog;
s0,s1,s2:string;
begin
try
opendialog1:=Topendialog.Create(self);
opendialog1.InitialDir:=ExtractFileDir(paramstr(0));//文件的打存放初始路径
if opendialog1.Execute then
begin
Try
ExcelApplication1.Connect;//EXCEL应用程序
Except
Messagebox(0,'Excel 可能没有安装!!','提示!',mb_Ok);
exit;
End;
ExcelApplication1.Visible[0]:=false;
ExcelApplication1.Caption:='Excel Application';
try
excelapplication1.Workbooks.Open(opendialog1.FileName,
null,null,null,null,null,null,null,null,null,null,null,null,0);//打开指定的EXCEL 文件
except
begin
ExcelApplication1.Disconnect;//出现异常情况时关闭
ExcelApplication1.Quit;showmessage('请选择EXCEL电子表格!');
exit;
end;
end;
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
//ExcelWorkbook1与Eexcelapplication1建立连接
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);
//Excelworksheet1与Excelworkbook1建立连接
//开始从EXCEL中取数,取完数后关闭EXCEL
for i:=1 to 10000 do//最大取值10000
begin
if trim(excelworksheet1.cells.item[i+1,1])<>'' then
begin
s0:= excelworksheet1.cells.item[i+1,1];
s1:= excelworksheet1.cells.item[i+1,2];
s2:= excelworksheet1.cells.item[i+1,3];
with qry do
begin
close;
sql.Clear;
sql.add('insert into xsz(PZNO,PNO,TCount,inday)values('+
''''+s0+''''+','+''''+s1+''''+','+''''+s2+''''+','+''''+datetostr(date)+''')');
ExecSQL;
end;
end
end;
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
end;
with qryxsz do
begin
sql.Clear;
sql.Add('select * from xsz order by PZNO,PNO,TCount,inday');
open;
end;
for j:=0 to grdxsz.Columns.Count do
grdxsz.Columns[j].Width:=64;//缩小宽度
messagebox(0,'文件已经导入数据库!','完成!',mb_ok);
except
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
exit;
// messagebox(0,'文件已经导入数据库失败!','完成!',mb_iconError+mb_ok);
end;
end;
try
if qryxsz.RecordCount<1 then exit;
CopyDbDataToExcel(Grdxsz);
except
// messagebox(0,'请安装EXCEL软件,再使用本功能。','提示',mb_ok);
exit;
end;
这是我在一个对帐系统中用到的将两张excel表中的数据到如数据库进行相应处理后再导出excel.