取得指定excel文件内的sheet数及每一个sheet的列数

取得指定excel文件内的sheet数及每一个sheet的列数

---------------------------------------------------------

-- happyflystone

-- 2010.04

-- 转载注明出处--->http://blog.youkuaiyun.com/happyflystone

-- 取得指定excel文件内的sheet数及每一个sheet的列数。

------------------------------------------------------

CREATE PROC sp_operate_excel

@excelfilename NVARCHAR(100),

@strErrorMessage VARCHAR(1000) OUT

AS

BEGIN

SET NOCOUNT ON

DECLARE @hr INT;

DECLARE @objExcel INT;

DECLARE @objWorkBooks INT;

DECLARE @objWorkBook INT;

DECLARE @cmd NVARCHAR(4000);

DECLARE @i INT,@j int;

DECLARE @IndexCount int;

DECLARE @sheetname varchar (255 ) ;

SET @strErrorMessage = '';

DECLARE @tb TABLE(id INT identity,sheetname VARCHAR(100),colnums int);

--创建Excel.Application对象

EXEC @hr = sp_OACreate 'Excel.Application',@objExcel OUTPUT;

IF @hr = 0

BEGIN

--创建Workbooks对象

EXEC @hr = sp_OAGetProperty @objExcel,'Workbooks',@objWorkbooks OUTPUT;

IF @hr = 0

BEGIN

--分解

--打开Excel文件

SET @cmd = 'Open("'+@excelfilename+'")';

EXEC @hr = sp_OAMethod @objWorkbooks,@cmd,@objWorkBook OUTPUT;

IF @hr = 0

BEGIN

SET @i = 1;

EXEC @hr = sp_oagetproperty @objWorkbook , 'Sheets.Count' , @IndexCount OUT

--循环多少个Sheet

WHILE @i <= @IndexCount

BEGIN

SET @cmd = 'Sheets('+ltrim(@i)+').Name';

EXEC @hr = sp_OAGetProperty @objWorkbook,@cmd,@sheetname OUT--@objSheet OUTPUT;

SET @cmd = 'Sheets('+ltrim(@i)+').UsedRange.Columns.Count'

EXEC @hr = sp_OAGetProperty @objWorkbook,@cmd,@j OUTPUT;

INSERT @tb SELECT @sheetname,@j

SET @i = @i +1;

END

END

END

ELSE

SET @strErrorMessage = '创建工作薄对象失败!';

END

ELSE

SET @strErrorMessage = '创建Excel对象失败!'

IF @hr = 0

EXEC @hr = sp_OAMethod @objExcel,'Quit';

--消除Excel对象

IF @hr = 0

EXEC @hr = sp_OADestroy @objWorkbooks;

IF @hr = 0

EXEC @hr = sp_OADestroy @objExcel;

select * from @tb

END

GO

DECLARE @RET VARCHAR(100)

EXEC sp_operate_excel 'C:\BOOK1.XLS',@RET OUT

/*

id sheetname colnums

----------- ------------ -----------

1 Sheet1 4

2 Sheet2 2

3 Sheet3 5

*/

DROP PROC sp_operate_excel