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

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

 

 

 

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

-- happyflystone

-- 2010.04

-- 转载注明出处--->http://blog.csdn.net/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