SQL Server Excel Workbench

引用 John 2011-9-1 10:31

--to drop the link, we do this!
--EXEC sp_dropserver 'CambridgePubDatabase', 'droplogins'

-- Get the spreadsheet data via OpenQuery
SELECT * FROM OPENQUERY
    (CambridgePubDatabase, 'select * from [CambridgePubs]')
GO
--or more simply, do this
SELECT * FROM CambridgePubDatabase...CambridgePubs

--so now we can insert our data into the Excel Spreadsheet
INSERT INTO CambridgePubDatabase...CambridgePubs
    (Pubname, Address, postcode)
    SELECT Pubname, Address, postcode FROM ##CambridgePubs

/*Synchronizing the Spreadsheet with SQL Server tables

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

As we are directly manipulating the Excel data in the worksheet as if it was a table we can do joins.

What about synchronising the table after editing the excel spreadsheet?

To try this out, you'll need to delete, alter and insert a few rows from the excel spreadsheet, remembering to close it after you've done it
*/


--Firstly, we'll delete any rows from ##CambridgePubs
-- that do not exist in the Excel spreadsheet

DELETE FROM ##CambridgePubs
FROM ##CambridgePubs c
LEFT OUTER JOIN CambridgePubDatabase...CambridgePubs ex
ON c.address LIKE ex.address
    AND c.pubname LIKE ex.pubname
    AND c.postcode LIKE ex.postcode
WHERE ex.pubname IS NULL

-- then we insert into #CambridgePubs any rows in the spreadsheet
-- that don't exist in #CambridgePubs

INSERT INTO ##CambridgePubs (Pubname,Address,Postcode)
SELECT ex.Pubname,ex.Address,ex.Postcode
FROM CambridgePubDatabase...CambridgePubs ex
LEFT OUTER JOIN ##CambridgePubs c
ON c.address LIKE ex.address
    AND c.pubname LIKE ex.pubname
    AND c.postcode LIKE ex.postcode
WHERE c.pubname IS NULL

--all done (reverse syncronisation would be similar)

/*Manipulating Excel data using OPENDATASOURCE and OPENROWSET

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

If you don't want to do the linking, you can also read the data like
this*/

SELECT *
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\CambridgePubs.xls";
  Extended properties=Excel 8.0')...CambridgePubs
--and write to it

UPDATE OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',
'Data Source="C:\CambridgePubs.xls";
extended Properties=Excel 8.0')...CambridgePubs
SET Address='St. Kilda Road, Cambridge'
WHERE Pubname = 'Jenny Wren'

INSERT INTO OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',
'Data Source="C:\CambridgePubs.xls";
extended Properties=Excel 8.0')...CambridgePubs
(Pubname,Address,Postcode )
SELECT 'The St George','65 Cavendish Road','CB2 4RT'

--You can read and write toExcel Sheet using OpenRowSet,
--if the mood takes you

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\CambridgePubs.xls', 'Select * from CambridgePubs')

UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\CambridgePubs.xls',
'Select * from CambridgePubs')
    SET Address='34 Glemsford Road' WHERE Address = '65 Cavendish Road'

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\CambridgePubs.xls',
'Select * from CambridgePubs')
(Pubname, Address, Postcode)
SELECT 'The Bull', 'Antioch Road','CB2 5TY'

/*Creating Excel Spreadsheets using sp_makewebtask

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

Instead of creating the Excel spreadsheet with OLEDB One can use the
sp_makewebtask

Users must have SELECT permissions to run a specified query and CREATE PROCEDURE permissions in the database in which the query will run. The SQL Server account must have permissions to write the generated HTML document to the specified location. Only members of the sysadmin server role can impersonate other users.
*/

sp_makewebtask @outputfile = 'c:\CambridgePubsHTML2.xls',
  @query = 'Select * from ##CambridgePubs',
  @colheaders =1,
    @FixedFont=0,@lastupdated=0,@resultstitle='Cambridge Pubs',
  @dbname ='MyDatabaseName'

/* This is fine for distributing information from databases but no good
if you subsequently want to open it via ODBC.*/

引用 John 2011-9-1 10:34

/*OLE Automation

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

So far, so good. However, we really want rather more than this. When we create an excel file for a business report, we want the data and we also want nice formatting, defined ranges, sums, calculated fields and pretty graphs. If we do financial reporting, we want a pivot table and so on in order to allow a degree of data mining by the recipient. A different approach is required.

We can, of course, use Excel to extract the data from the database. However, in this example, we'll create a spreadsheet, write the data into it, fit the columns nicely and define a range around the data

*/ ALTER PROCEDURE [dbo].[spDMOExportToExcel] (
@SourceServer VARCHAR(30),
@SourceUID VARCHAR(30)=NULL,
@SourcePWD VARCHAR(30)=NULL,
@QueryText VARCHAR(200),
@filename VARCHAR(100),
@WorksheetName VARCHAR(100)='Worksheet',
@RangeName VARCHAR(80)='MyRangeName'
)
AS
DECLARE @objServer INT,
@objQueryResults INT,
@objCurrentResultSet INT,
@objExcel INT,
@objWorkBooks INT,
@objWorkBook INT,
@objWorkSheet INT,
@objRange INT,
@hr INT,
@Columns INT,
@Rows INT,
@Output INT,
@currentColumn INT,
@currentRow INT,
@ResultSetRow INT,
@off_Column INT,
@off_Row INT,
@command VARCHAR(255),
@ColumnName VARCHAR(255),
@value VARCHAR(255),
@strErrorMessage VARCHAR(255),
@objErrorObject INT,
@Alphabet VARCHAR(27)

SELECT @Alphabet='ABCDEFGHIJKLMNOPQRSTUVWXYZ'

IF @QueryText IS NULL
    BEGIN
    RAISERROR ('A query string is required for spDMOExportToExcel',16,1)
    RETURN 1
    END

-- Sets the server to the local server
IF @SourceServer IS NULL SELECT @SourceServer = @@servername

SET NOCOUNT ON

SELECT @strErrorMessage = 'instantiating the DMO',
    @objErrorObject=@objServer
EXEC @hr= sp_OACreate 'SQLDMO.SQLServer', @objServer OUT

IF @SourcePWD IS NULL OR @SourceUID IS NULL
    BEGIN
    --use a trusted connection
    IF @hr=0 SELECT @strErrorMessage=
    'Setting login to windows authentication on '
    +@SourceServer, @objErrorObject=@objServer
    IF @hr=0 EXEC @hr=sp_OASetProperty @objServer, 'LoginSecure', 1
    IF @hr=0 SELECT @strErrorMessage=
    'logging in to the requested server using windows authentication on '
        +@SourceServer
    IF @SourceUID IS NULL AND @hr=0 EXEC @hr=sp_OAMethod @objServer,
        'Connect', NULL, @SourceServer
    IF @SourceUID IS NOT NULL AND @hr=0
        EXEC @hr=sp_OAMethod
            @objServer, 'Connect', NULL, @SourceServer ,@SourceUID
    END
ELSE
    BEGIN
    IF @hr=0
       SELECT @strErrorMessage = 'Connecting to '''+@SourceServer+
                              ''' with user ID '''+@SourceUID+'''',
              @objErrorObject=@objServer
    IF @hr=0
        EXEC @hr=sp_OAMethod @objServer, 'Connect', NULL,
            @SourceServer, @SourceUID, @SourcePWD
    END

--now we execute the query
IF @hr=0 SELECT @strErrorMessage='executing the query "'
        +@querytext+'", on '+@SourceServer,
        @objErrorObject=@objServer,
        @command = 'ExecuteWithResults("' + @QueryText + '")'
IF @hr=0
    EXEC @hr=sp_OAMethod @objServer, @command, @objQueryResults OUT

IF @hr=0
     SELECT @strErrorMessage='getting the first result set for "'
        +@querytext+'", on '+@SourceServer,
        @objErrorObject=@objQueryResults
IF @hr=0 EXEC @hr=sp_OAMethod
    @objQueryResults, 'CurrentResultSet', @objCurrentResultSet OUT
IF @hr=0
    SELECT @strErrorMessage='getting the rows and columns "'
        +@querytext+'", on '+@SourceServer
IF @hr=0
    EXEC @hr=sp_OAMethod @objQueryResults, 'Columns', @Columns OUT
IF @hr=0
    EXEC @hr=sp_OAMethod @objQueryResults, 'Rows', @Rows OUT

--so now we have the queryresults. We start up Excel
IF @hr=0
    SELECT @strErrorMessage='Creating the Excel Application, on '
        +@SourceServer, @objErrorObject=@objExcel
IF @hr=0
    EXEC @hr=sp_OACreate 'Excel.Application', @objExcel OUT
IF @hr=0 SELECT @strErrorMessage='Getting the WorkBooks object '
IF @hr=0
    EXEC @hr=sp_OAGetProperty @objExcel, 'WorkBooks',
        @objWorkBooks OUT
--create a workbook
IF @hr=0
    SELECT @strErrorMessage='Adding a workbook ',
        @objErrorObject=@objWorkBooks
IF @hr=0
    EXEC @hr=sp_OAGetProperty @objWorkBooks, 'Add', @objWorkBook OUT

--and a worksheet
IF @hr=0
    SELECT @strErrorMessage='Adding a worksheet ',
        @objErrorObject=@objWorkBook
IF @hr=0
    EXEC @hr=sp_OAGetProperty @objWorkBook, 'worksheets.Add',
        @objWorkSheet OUT

IF @hr=0
    SELECT @strErrorMessage='Naming a worksheet as "'
        +@WorksheetName+'"', @objErrorObject=@objWorkBook
IF @hr=0  
    EXEC @hr=sp_OASetProperty @objWorkSheet, 'name', @WorksheetName

SELECT @currentRow = 1

--so let's write out the column headings
SELECT @currentColumn = 1
WHILE (@currentColumn <= @Columns AND @hr=0)
        BEGIN
        IF @hr=0
            SELECT @strErrorMessage='getting column heading '
                                    +LTRIM(STR(@currentcolumn)) ,
                @objErrorObject=@objQueryResults,
                @Command='ColumnName('
                            +CONVERT(VARCHAR(3),@currentColumn)+')'
        IF @hr=0 EXEC @hr=sp_OAGetProperty @objQueryResults,
                                            @command, @ColumnName OUT
        IF @hr=0
            SELECT @strErrorMessage='assigning the column heading '+
              + LTRIM(STR(@currentColumn))
              + ' from the query string',
            @objErrorObject=@objExcel,
            @command='Cells('+LTRIM(STR(@currentRow)) +', '
                                + LTRIM(STR(@CurrentColumn))+').value'
        IF @hr=0
            EXEC @hr=sp_OASetProperty @objExcel, @command, @ColumnName
        SELECT @currentColumn = @currentColumn + 1
        END

引用 John 2011-9-1 10:34

--format the headings in Bold nicely
IF @hr=0
    SELECT @strErrorMessage='formatting the column headings in bold ',
        @objErrorObject=@objWorkSheet,
        @command='Range("A1:'
            +SUBSTRING(@alphabet,@currentColumn/26,1)
            +SUBSTRING(@alphabet,@currentColumn % 26,1)
            +'1'+'").font.bold'
IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 1
--now we write out the data

SELECT @currentRow = 2
WHILE (@currentRow <= @Rows+1 AND @hr=0)
    BEGIN
    SELECT @currentColumn = 1
    WHILE (@currentColumn <= @Columns AND @hr=0)
        BEGIN
        IF @hr=0
            SELECT
            @strErrorMessage=
                'getting the value from the query string'
                + LTRIM(STR(@currentRow)) +','
                + LTRIM(STR(@currentRow))+')',
            @objErrorObject=@objQueryResults,
            @ResultSetRow=@CurrentRow-1
        IF @hr=0
            EXEC @hr=sp_OAMethod @objQueryResults, 'GetColumnString',
                @value OUT, @ResultSetRow, @currentColumn
        IF @hr=0
            SELECT @strErrorMessage=
                    'assigning the value from the query string'
                + LTRIM(STR(@CurrentRow-1)) +', '
                + LTRIM(STR(@currentcolumn))+')' ,
                @objErrorObject=@objExcel,
                @command='Cells('+STR(@currentRow) +', '
                                    + STR(@CurrentColumn)+').value'
        IF @hr=0
            EXEC @hr=sp_OASetProperty @objExcel, @command, @value
        SELECT @currentColumn = @currentColumn + 1
        END
    SELECT @currentRow = @currentRow + 1
    END
--define the name range
--Cells(1, 1).Resize(10, 5).Name = "TheData"
IF @hr=0 SELECT @strErrorMessage='assigning a name to a range '
        + LTRIM(STR(@CurrentRow-1)) +', '
        + LTRIM(STR(@currentcolumn-1))+')' ,
    @objErrorObject=@objExcel,
    @command='Cells(1, 1).Resize('+STR(@currentRow-1) +', '
                                    + STR(@CurrentColumn-1)+').Name'
IF @hr=0 EXEC @hr=sp_OASetProperty @objExcel, @command, @RangeName

--Now autofilt the columns we've written to
IF @hr=0 SELECT @strErrorMessage='Auto-fit the columns ',
            @objErrorObject=@objWorkSheet,
            @command='Columns("A:'
                +SUBSTRING(@alphabet,(@Columns / 26),1)
                +SUBSTRING(@alphabet,(@Columns % 26),1)+
                '").autofit'

IF @hr=0 --insert into @bucket(bucket)
        EXEC @hr=sp_OAMethod @objWorkSheet, @command, @output out


IF @hr=0 SELECT @command ='del "' + @filename + '"'
IF @hr=0 EXECUTE master..xp_cmdshell @Command, no_output
IF @hr=0
    SELECT @strErrorMessage='Saving the workbook as "'+@filename+'"',
        @objErrorObject=@objRange,
        @command = 'SaveAs("' + @filename + '")'
IF @hr=0 EXEC @hr=sp_OAMethod @objWorkBook, @command
IF @hr=0 SELECT @strErrorMessage='closing Excel ',
        @objErrorObject=@objExcel
EXEC @hr=sp_OAMethod @objWorkBook, 'Close'
EXEC sp_OAMethod @objExcel, 'Close'

IF @hr<>0
    BEGIN
    DECLARE
        @Source VARCHAR(255),
        @Description VARCHAR(255),
        @Helpfile VARCHAR(255),
        @HelpID INT
   
    EXECUTE sp_OAGetErrorInfo @objErrorObject,
        @source output,@Description output,
        @Helpfile output,@HelpID output
    SELECT @hr, @source, @Description,@Helpfile,@HelpID output
    SELECT @strErrorMessage='Error whilst '
            +COALESCE(@strErrorMessage,'doing something')
            +', '+COALESCE(@Description,'')
    RAISERROR (@strErrorMessage,16,1)
    END
EXEC sp_OADestroy @objServer
EXEC sp_OADestroy @objQueryResults
EXEC sp_OADestroy @objCurrentResultSet
EXEC sp_OADestroy @objExcel
EXEC sp_OADestroy @objWorkBookks
EXEC sp_OADestroy @objWorkBook
EXEC sp_OADestroy @objRange
RETURN @hr
GO

--Now we can create our pubs spreadsheet, and can do it from any of
--our servers
--
spDMOExportToExcel @SourceServer='MyServer',
@SourceUID= 'MyUserID',
@SourcePWD = 'MyPassword',
@QueryText = 'use MyDatabase
select Pubname, Address, Postcode from ##CambridgePubs',
@filename = 'C:\MyPubDatabase.xls',
@WorksheetName='MyFavouritePubs',
@RangeName ='MyRangeName'

--or if you are using integrated security!
spDMOExportToExcel @SourceServer='MyServer',
@QueryText = 'use MyDatabase
select Pubname, Address, Postcode from ##CambridgePubs',
@filename = 'C:\MyPubDatabase.xls',
@WorksheetName='MyFavouritePubs',
@RangeName ='MyRangeName'


/* Although this is a very handy stored procedure, you'll probably need to modify and add to it for particular purposes.

We use the DMO method because we like to dump build data into Excel spreadsheets e.g. users, logins, Job Histories. However, an ADODB version is very simple to do and can be made much faster for reads and writes.

We have just inserted values, but you can insert formulae and formatting numberformat) and create or change borders. You can, in fact, manipulate the spreadsheet in any way you like. When we do this, we record macros in Excel and then convert these macros to TSQL! Using the above example, it should be simple */


http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

查看全部评论(3)