34,591
社区成员
发帖
与我相关
我的任务
分享
Option Explicit
Dim Ex As New Excel.Application
Dim Wk As New Excel.Workbook
Public Function GetSheetName(PathT As String) As String
Dim I As Integer
Dim StrName As String
StrName = ""
Set Wk = Ex.Workbooks.Open(PathT)
If Wk.Worksheets.Count < 1 Then
StrName = ""
Else
For I = 1 To Wk.Worksheets.Count
StrName = StrName & Wk.Worksheets(I).Name & "|"
Next
End If
Wk.Save
Ex.Quit
Set Wk = Nothing
Set Ex = Nothing
GetSheetName = StrName
End Function
declare @objC int,@hr int
declare @x varchar(100)
exec @hr=master..sp_OACreate 'GetSheet.getS',@objC output
if @hr<>0
begin
set @hr=1
goto Error
end
exec @hr=master..sp_OAMethod @objC,'GetSheetName',@x out,'e:\test.xls'
if @hr<>0
begin
set @hr=2
goto Error
end
select '表名为:'+@x
exec master..sp_OAdestroy @objc
Error:
print 'error: ' +ltrim(@hr)
exec master..sp_OAdestroy @objc
--result
/*--------------
Sheet1|Sheet2|Sheet3|Sheet4|
*/
select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Reporttemplate\PPVAPO.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
--如果导入数据并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
SELECT *
FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
’Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions