Build Great Looking Excel Spreadsheets

  • David Ziffer

    SSCommitted

    Points: 1513

    Comments posted to this topic are about the item Build Great Looking Excel Spreadsheets

  • kccasey

    SSC Rookie

    Points: 36

    SSMS > Tools > Options > Query Results > SQL Server > Results to Grid > 'Include column headers when copying or saving results'

    ... the macros good though 🙂

  • Gosta Munktell

    SSCertifiable

    Points: 5973

    "Excel is a great tool for formatting data that you've extracted from tables or views. You can copy such data into an Excel spreadsheet straight from SQL Management Studio's output grid. Unfortunately there's no way to also grab the column names, so unless you have some way of generating them, you're stuck either omitting them or filling them in by hand."

    David I like your articel but one question:

    As you incoporate VBA macros why not use ADO as alternative to get out the data from

    SQL Server. This is easier and you can get the column the names by a method like:

    Just part of the script

    I use this one to print TAB sep files with column headers

    Dim rs As New ADODB.Recordset

    Dim utskriftrad as string

    Dim K as long

    cmd.CommandText = sqltext 'An SQL statement

    rs.open cmd

    For K = 0 To rs.Fields.Count - 1

    utskriftrad = utskriftrad & rs.Fields(K).Name & Chr(9)

    Next K

    print utskriftrad etc

    I yesterday wrote an article to SQL ServerCentral maybe that one will be

    accepted. And there is a lot more to write how to interact with Excel.

    //Gosta

  • Phil Factor

    SSC-Insane

    Points: 20084

    I realise I'm a bit off-topic, as what you are aiming to do here is a semi-manual process. but all the things you do here, and more, can be done via OLE automation. Robyn and I wrote something a while back which will work in SQL Server 2000 as well

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

    The advantage in using the metadata from ADODB or odbc is that it will work for any result to give you your column titles, not just a table or view.

    Best wishes,
    Phil Factor
    Simple Talk

  • Gosta Munktell

    SSCertifiable

    Points: 5973

    Phil

    Thank you for pointing at your article. It was new for me.

    But still why this labor in T-SQL when you can do all in VBA coding?

    Best regards

    Gosta

  • Phil Factor

    SSC-Insane

    Points: 20084

    You can do a lot of this by simply using VBA from Excel. I've done it both ways. If the structure and formatting of the spreadsheet requires knowledge of the data, or maybe if it is a timed report, or requested report, to be sent out via email using an Excel file attachment, then I do it from TSQL. Where it is a much simpler report than VBA is the obvious strategy. There are a lot of ways of producing Excel spreadsheets from database data, and the more techniques you have in your armory, the more likely you are to find one that is a good fit for a particular requirement.

    Best wishes,
    Phil Factor
    Simple Talk

  • David Jackson

    SSCertifiable

    Points: 6407

    Nice Macro. 🙂

    The only thing I'd add to the Sub ColorAlternateDataRows is this line at the start

    Application.ScreenUpdating = False

    And at the end

    Application.ScreenUpdating = True

    Then it runs much quicker

    HTH

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • rascasse83

    SSC Rookie

    Points: 38

    The technique from this article doesn't seem to retain the correct data types from the database.

    For example a number (data type varchar in SQL) from the SQL Output grid with a leading zero, once copied into Excel with loose it's leading Zero as it will be converted to Number.

    Even if you format the whole spreadsheet to text before you copy the results, its will then mean real numbers are converted as text and you can't do any more sums in Excel.

    This means this technique is not really usefull as a fully automated technique. You will have to spend some time setting the Excel column cell data types acordinly. Which is a requirement from most of us i guess.

    However using the SQL Server data export functionality (right click on a table, "export") will retain column names and data types. And is really as automated as running the code from the article. I would say even more !

    Excel formatting bit is usefull though and the stored procedure / use of schema an eye opener.

    thanks

  • lweismantel

    SSC Rookie

    Points: 33

    The article states that unfortunately there's no way to also grab the column names from SQL Management Studio's output grid, but there is a checkbox option on the Query Options dialogue box that reads "Include column headers when copying or saving the results" that works great to bring along the column headers.

    Additionally, Excel 2007 now has built-in table formatting that allows you to automatically apply different colors based on conditional values from any column.

    Respectfully,

    LGW

  • corneld

    SSC Veteran

    Points: 234

    I concur with: :hehe:

    SSMS > Tools > Options > Query Results > SQL Server > Results to Grid > 'Include column headers when copying or saving results'

    Otherwise, good effort and nice article!!!

  • rascasse83

    SSC Rookie

    Points: 38

    Yes guys but even that technique is useless as it doesn't retain datatypes. What's the use of dumping the results in Excel if you have to spend time to reset all the columns formats datatypes?

    The one technique a collegue of mine uses with success is : Go into SQL Management Studio, right click on database, all tasks, export data.

    That will retains all datatypes and also headings.

  • WayneS

    SSC Guru

    Points: 95386

    Neat article.

    You can eliminate the cursor (though in this case there's really not a performance hit) by:

    -- construct an execution string

    declare @ExecString varchar(max)

    set @ExecString = ''

    select @ExecString = @ExecString + case when @ExecString = '' then '' else ',' end + COLUMN_NAME + '=''' + COLUMN_NAME + ''''

    from INFORMATION_SCHEMA.COLUMNS

    where TABLE_NAME = @TableName

    order by ORDINAL_POSITION

    set @ExecString = 'select ' + @ExecString

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Madhivanan-208264

    SSCertifiable

    Points: 7516

    You dont need a cursor to get column names

    Refer point 5

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926


    Madhivanan

    Failing to plan is Planning to fail

  • Leon Carpay

    SSC Rookie

    Points: 32

    Hi David,

    The SP doesn't work when there is a SPACE in the SQL-table name.

    gr leon

  • jpratt-797544

    SSC Eights!

    Points: 847

    I have been getting column names with the data when copying the grid to Excel all along. I just click the blank square in the upper left corner of the grid, which selects everything, then Ctrl+C, open Excel, hit Ctrl+V, and all the data plus column names show up.

    The technique of using the built-in views is great though, and more published info on those semi-hidden gems is great.

    J Pratt

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply