取得指定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'  --显示列数

                     --SET @cmd = 'Sheets('+ltrim(@i)+').UsedRange.Rows.Count'  --显示行数

                     --SET @cmd = 'Sheets('+ltrim(@i)+').UsedRange.cells.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