Build Great Looking Excel Spreadsheets

Question

Build Great Looking Excel Spreadsheets

David Ziffer

SSCommitted

Points: 1513
More actions
November 4, 2008 at 10:57 pm

#201142

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

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

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

kccasey SSC Rookie Points: 36 More actions · Answer 1

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: 6004 More actions · Answer 2

"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: 20244 More actions · Answer 3

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

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 4

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: 20244 More actions · Answer 5

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

David Jackson SSCertifiable Points: 6414 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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: 95460 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

Hi David,

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

gr leon

jpratt-797544 SSC Eights! Points: 899 More actions · Answer 14

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