I want to achieve within excel file specified on the number of sheet and each column number of the sheet, refer to the following command, but there is a problem

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- in the SQL Server to get all the Sheet worksheet in the Excel file name

- create a function:

Use SSQ


If the exists (select * from dbo. Sysobjects where id=object_id (N '[dbo]. [f_sheetname]) and xtype in (N' FN 'N' if ', N 'TF'))
The drop function [dbo] [f_sheetname]
GO
The create function f_sheetname (
@ ExcelFname nvarchar (260)
(id) returns @ re table int identity (1, 1), sheetname nvarchar (100))
As
The begin
Declare @ err int, @ SRC varchar (255), @ desc varchar (255)
Declare @ obj int, @ icount int, @ sheetname varchar (200)

The exec @ err=sp_oacreate 'Excel. Application, @ obj out
If @ err<> 0 goto lb_err

The exec @ err=sp_oamethod @ obj, 'Workbooks. Open'
, @ icount out
, @ ExcelFname
If @ err<> 0 goto lb_err

The exec @ err=sp_oagetproperty @ obj, 'ActiveWorkbook. Sheets. The Count' @ icount out
If @ err<> 0 goto lb_err
While @ icount> 0
The begin
Set @ SRC="https://bbs.csdn.net/topics/ActiveWorkbook.Sheets (' + cast (@ icount the as varchar) + '). The Name '
The exec @ err=sp_oagetproperty @ obj, @ SRC, @ sheetname out
If @ err<> 0 goto lb_err
Insert the @ re values (@ sheetname)
The set @ icount=@ icount - 1
End

The exec @ err=sp_oadestroy @ obj
Goto lb_re

Lb_err:
The exec sp_oageterrorinfo 0, @ SRC out, @ desc out
Insert the @ re
Select the cast (@ err as varbinary (4)) as error number
Union all select @ SRC as error source
Union all select @ desc as error description
Lb_re:
Return
End
Go
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --


I carried out
Select * from f_sheetname (' f: \ aa. XLS)
But return

1 euro
2 Microsoft Excel
3 Microsoft Excel cannot access the file "f: \ aa. XLS", has the following several possible reasons:? The file name or path, you do not have? File is used by other programs,? You are about to save the workbook with the current open workbook with the same name,

I use the following command test is normal
Select * from opendatasource (' Microsoft. ACE. The OLEDB. 12.0 ', 'data source="f: \ aa. XLS"; Extended Properties=Excel 12.0 ')... [sheet3 $]

CodePudding user response:

Don't ah, jacking

CodePudding user response:

Still hope friends great glad

CodePudding user response:

Don't ah, jacking