Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Build Great Looking Excel Spreadsheets Expand / Collapse
Author
Message
Posted Wednesday, November 5, 2008 5:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 30, 2013 8:30 PM
Points: 2, Visits: 41
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.
Post #597254
Posted Wednesday, November 5, 2008 6:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:47 AM
Points: 5,361, Visits: 8,925
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #597258
Posted Wednesday, November 5, 2008 6:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
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
Post #597259
Posted Wednesday, November 5, 2008 6:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 9:57 AM
Points: 2, Visits: 64
Hi David,

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

gr leon
Post #597280
Posted Wednesday, November 5, 2008 6:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:44 PM
Points: 21, Visits: 44
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.
Post #597287
Posted Wednesday, November 5, 2008 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 23, 2013 6:36 AM
Points: 1, Visits: 16
Hummm!
Must be missing something? Excel 2007 does a great job of getting data from SQL Server 2005, with the headers? No coding, very, very easy. Just click on Data->From Other Sources->SQL.

What am I missing?
Post #597290
Posted Wednesday, November 5, 2008 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 9:57 AM
Points: 2, Visits: 64
newbie, what you are missing is that David wrote his drill for <= Excel 2003. In Excel 2007 formatting and importing data is much, much easier...
Post #597331
Posted Wednesday, November 5, 2008 7:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:26 AM
Points: 205, Visits: 1,336
:)
I agree with Phil Factor. There is not just one method.
But what I miss sometimes is how is the customer of this application and
what are the needs etc.
I am involved in projects where the purpose is to provide users regulary
some time once a day (or many times a day) with data wraped in Excel.
The process must be fully automated! At first will it be a batch process or
a customer demand process?
I use T-SQL somtimes but I prefere VBA using all the methods available in VBA.

;)
Gosta



Post #597333
Posted Wednesday, November 5, 2008 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 1:45 PM
Points: 24, Visits: 175
The article was interesting, and the conversation it spawned:

Though I knew how to paste columns from SSMS, and I knew how to *automate* columns-inclusion using export tasks, what I did NOT know was how to *format* the Excel output.

Something I find especially interesting, from Phil's article:

--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


(I'm leaving out the context, which you can get from Phil's article.)

And I can see it would be useful, for me, to be able to do this kind of Excel formatting from ADO/VBScript...

... so, Gosta, does your article include Excel *formatting* from VBScript? If so, by all means please share the link here, if & when your article is published.

Thanks, alll...

Post #597365
Posted Wednesday, November 5, 2008 8:42 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
divison (11/5/2008)
The article was interesting, and the conversation it spawned:

...
And I can see it would be useful, for me, to be able to do this kind of Excel formatting from ADO/VBScript...

... so, Gosta, does your article include Excel *formatting* from VBScript? If so, by all means please share the link here, if & when your article is published.

Thanks, alll...



Here is a VBS Script that does exactly that.

Oh, and another tweak to the original macro. If you change
Set S = ThisWorkbook.ActiveSheet

to
Set S = ActiveSheet

you can store it in your PERSONAL.xls and use it in any workbook without pasting the code in everytime.

Here's the afore mentioned (and afore posted, albeit in a different thread) VB script
Dim connectionString
'The connection string goes here
connectionString = "Provider = SQLOLEDB;Data Source=SERVER_NAME;" & _
"Trusted_Connection=Yes;Initial Catalog=Northwind;"

Dim Query
' The query goes here
Query = "SELECT CompanyName, count(o.CustomerID) as Total" & vbCrLf & _
"FROM Northwind.dbo.Orders o" & vbCrLf & _
"Inner Join Northwind.dbo.Customers c on o.CustomerID = c.CustomerID" & vbCrLf & _
"Group by CompanyName"

Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim i

const xlColumnClustered = 51
const xl3DColumnClustered = 54
const xl3DColumn = -4100
const xlBarClustered = 57
const xl3DBarClustered = 60

const xlLineMarkers = 65
const xl3DLine = -4101

const xlPie = 5
const xlPieExploded = 69
const xl3DPie = -4102
const xl3DPieExploded = 70

const xlArea = 1
const xl3DArea = -4098

const xlSurface = 83

const xlCylinderColClustered = 92
const xlCylinderBarClustered = 95

const xlConeColClustered = 99
const xlConeBarClustered = 102

const xlPyramidBarClustered = 109
const xlPyramidColClustered = 106



Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open connectionString

' creating the Excel object application
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.ScreenUpdating = False

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

'msgBox Query

objRecordSet.Open Query , objConnection, adOpenStatic, adLockOptimistic
i = 1

objRecordSet.MoveFirst

Do Until objRecordset.EOF
i = i + 1

' This is setting the column names, font, colors, etc.
' This code can be simplified by ranging if desired.
objExcel.Cells(1, 1).Value = "Company Name"
objExcel.Cells(1, 1).Font.Size = 10
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Interior.ColorIndex = 6
objExcel.Cells(1, 2).Value = "Total"
objExcel.Cells(1, 2).Font.Size = 10
objExcel.Cells(1, 2).Font.Bold = TRUE
objExcel.Cells(1, 2).Interior.ColorIndex = 6

' Now we are getting the data and highlighting certain columns
objExcel.Cells(i, 1).Value = objRecordset.Fields.Item("CompanyName")
objExcel.Cells(i, 1).Font.Size = 10
objExcel.Cells(i, 1).Borders.LineStyle = True
objExcel.Cells(i, 2).Value = objRecordset.Fields.Item("Total")
objExcel.Cells(i, 2).Font.Size = 10
objExcel.Cells(i, 2).Borders.LineStyle = True

objRecordset.MoveNext

objExcel.Range("A1:B1").Borders.LineStyle = True

Loop

' automatically fits the data to the columns
Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit()

'Create a chart
objRange.Activate
objWorkbook.Charts.Add
objWorkbook.ActiveChart.ChartType = xlCylinderColClustered
objWorkbook.ActiveChart.SetSourceData objRange, 1
objWorkbook.ActiveChart.Location 2, "Sheet1"

objExcel.ScreenUpdating = True

' cleaning up
objRecordSet.Close
objConnection.Close





http://glossopian.co.uk/
"I don't know what I don't know."
Post #597408
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse