使用 SQL Server 的 sp_OACreate,sp_OADestroy,sp_OAMethod等操作OLE对象即可,这儿有一个的例子,不过是把数据从表中到导出excel。当然,写个程序可能更简单 EXEC ExportToExcel @server = '.', @uname = 'sa', @QueryText = 'SELECT au_fname FROM pubs..authors', @filename = 'c:\ImportToExcel.xls' */IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel GOCREATE PROCEDURE ExportToExcel ( @server sysname = null, @uname sysname = null, @pwd sysname = null, @QueryText varchar(200) = null, @filename varchar(200) = 'c:\ImportToExcel.xls' ) AS DECLARE @SQLServer int, @QueryResults int, @CurrentResultSet int, @object int, @WorkBooks int, @WorkBook int, @Range int, @hr int, @Columns int, @Rows int, @indColumn int, @indRow int, @off_Column int, @off_Row int, @code_str varchar(100), @result_str varchar(255)IF @QueryText IS NULL BEGIN PRINT 'Set the query string' RETURN END-- Sets the server to the local server IF @server IS NULL SELECT @server = @@servername-- Sets the username to the current user name IF @uname IS NULL SELECT @uname = SYSTEM_USERSET NOCOUNT ONEXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT IF @hr <> 0 BEGIN PRINT 'error create SQLDMO.SQLServer' RETURN END-- Connect to the SQL Server IF @pwd IS NULL BEGIN EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname IF @hr <> 0 BEGIN PRINT 'error Connect' RETURN END END ELSE BEGIN EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd IF @hr <> 0 BEGIN PRINT 'error Connect' RETURN END ENDSELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")' EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT IF @hr <> 0 BEGIN PRINT 'error with method ExecuteWithResults' RETURN ENDEXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT IF @hr <> 0 BEGIN PRINT 'error get CurrentResultSet' RETURN ENDEXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT IF @hr <> 0 BEGIN PRINT 'error get Columns' RETURN ENDEXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT IF @hr <> 0 BEGIN PRINT 'error get Rows' RETURN ENDEXEC @hr = sp_OACreate 'Excel.Application', @object OUT IF @hr <> 0 BEGIN PRINT 'error create Excel.Application' RETURN ENDEXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT IF @hr <> 0 BEGIN PRINT 'error create WorkBooks' RETURN ENDEXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT IF @hr <> 0 BEGIN PRINT 'error with method Add' RETURN ENDEXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT IF @hr <> 0 BEGIN PRINT 'error create Range' RETURN ENDSELECT @indRow = 1 SELECT @off_Row = 0 SELECT @off_Column = 1WHILE (@indRow <= @Rows) BEGIN SELECT @indColumn = 1WHILE (@indColumn <= @Columns) BEGINEXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn IF @hr <> 0 BEGIN PRINT 'error get GetColumnString' RETURN ENDEXEC @hr = sp_OASetProperty @Range, 'value', @result_str IF @hr <> 0 BEGIN PRINT 'error set value' RETURN ENDEXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column IF @hr <> 0 BEGIN PRINT 'error get Offset' RETURN ENDSELECT @indColumn = @indColumn + 1ENDSELECT @indRow = @indRow + 1 SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")' EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT IF @hr <> 0 BEGIN PRINT 'error create Range' RETURN ENDENDSELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output' EXEC(@result_str) SELECT @result_str = 'SaveAs("' + @filename + '")' EXEC @hr = sp_OAMethod @WorkBook, @result_str IF @hr <> 0 BEGIN PRINT 'error with method SaveAs' RETURN ENDEXEC @hr = sp_OAMethod @WorkBook, 'Close' IF @hr <> 0 BEGIN PRINT 'error with method Close' RETURN ENDEXEC @hr = sp_OADestroy @object IF @hr <> 0 BEGIN PRINT 'error destroy Excel.Application' RETURN ENDEXEC @hr = sp_OADestroy @SQLServer IF @hr <> 0 BEGIN PRINT 'error destroy SQLDMO.SQLServer' RETURN END GO
sql2000提供导数据的功能。 保险一点。方便一点。
直接用SQLSever2000的导入导出功能就行了
用「TADOConnection」控件連接「Sql2000」procedure TForm1.Button1Click(Sender: TObject); begin ADOConnection1.Execute('SELECT * INTO newTable FROM [Sheet1$] IN "C:\...\myFile.xls" "Excel 8.0;" '); end;注意:在「Sql2000」要用一個不存在的新表名(newTable) 在「Excel」表名是: Sheet名+'$'再用[]括起来
EXEC ExportToExcel @server = '.',
@uname = 'sa',
@QueryText = 'SELECT au_fname FROM pubs..authors',
@filename = 'c:\ImportToExcel.xls'
*/IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel
GOCREATE PROCEDURE ExportToExcel (
@server sysname = null,
@uname sysname = null,
@pwd sysname = null,
@QueryText varchar(200) = null,
@filename varchar(200) = 'c:\ImportToExcel.xls'
)
AS
DECLARE @SQLServer int,
@QueryResults int,
@CurrentResultSet int,
@object int,
@WorkBooks int,
@WorkBook int,
@Range int,
@hr int,
@Columns int,
@Rows int,
@indColumn int,
@indRow int,
@off_Column int,
@off_Row int,
@code_str varchar(100),
@result_str varchar(255)IF @QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END-- Sets the server to the local server
IF @server IS NULL SELECT @server = @@servername-- Sets the username to the current user name
IF @uname IS NULL SELECT @uname = SYSTEM_USERSET NOCOUNT ONEXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT
IF @hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END-- Connect to the SQL Server
IF @pwd IS NULL
BEGIN
EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname
IF @hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
ELSE
BEGIN
EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd
IF @hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
ENDSELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")'
EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT
IF @hr <> 0
BEGIN
PRINT 'error with method ExecuteWithResults'
RETURN
ENDEXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT
IF @hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
ENDEXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT
IF @hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
ENDEXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT
IF @hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
ENDEXEC @hr = sp_OACreate 'Excel.Application', @object OUT
IF @hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
ENDEXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT
IF @hr <> 0
BEGIN
PRINT 'error create WorkBooks'
RETURN
ENDEXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT
IF @hr <> 0
BEGIN
PRINT 'error with method Add'
RETURN
ENDEXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
ENDSELECT @indRow = 1
SELECT @off_Row = 0
SELECT @off_Column = 1WHILE (@indRow <= @Rows)
BEGIN
SELECT @indColumn = 1WHILE (@indColumn <= @Columns)
BEGINEXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn
IF @hr <> 0
BEGIN
PRINT 'error get GetColumnString'
RETURN
ENDEXEC @hr = sp_OASetProperty @Range, 'value', @result_str
IF @hr <> 0
BEGIN
PRINT 'error set value'
RETURN
ENDEXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column
IF @hr <> 0
BEGIN
PRINT 'error get Offset'
RETURN
ENDSELECT @indColumn = @indColumn + 1ENDSELECT @indRow = @indRow + 1
SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")'
EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
ENDENDSELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output'
EXEC(@result_str)
SELECT @result_str = 'SaveAs("' + @filename + '")'
EXEC @hr = sp_OAMethod @WorkBook, @result_str
IF @hr <> 0
BEGIN
PRINT 'error with method SaveAs'
RETURN
ENDEXEC @hr = sp_OAMethod @WorkBook, 'Close'
IF @hr <> 0
BEGIN
PRINT 'error with method Close'
RETURN
ENDEXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'error destroy Excel.Application'
RETURN
ENDEXEC @hr = sp_OADestroy @SQLServer
IF @hr <> 0
BEGIN
PRINT 'error destroy SQLDMO.SQLServer'
RETURN
END
GO
保险一点。方便一点。
begin
ADOConnection1.Execute('SELECT * INTO newTable FROM [Sheet1$] IN "C:\...\myFile.xls" "Excel 8.0;" ');
end;注意:在「Sql2000」要用一個不存在的新表名(newTable)
在「Excel」表名是: Sheet名+'$'再用[]括起来