January 18, 2006 at 9:21 pm
I couldn't find an appropriate forum on SQL Server Central to post this question but here it goes.
I have a DTS Package that executes a query and stores the result set to an Excel Spreadsheet. The 1 row of the SPreadsheet is the Column names.
Does anyone know how to make the first row the BOLD font?
I'm thinking that it could mabe be done with VBScript?
Anyone?
January 19, 2006 at 1:30 am
You could use the sp_OACreate, sp_OAMethod and sp_OAProperty stored procedures to access the Excel object model however I wouldn't recommend it.
Excel does not seem to have the concept of a global template in the same way that Word has NORMAL.DOT therefore you don't have the option to give your users a globally available macro that they can use.
The only way I can think of doing it reliably is to set up something in the XLSTART directory on you user's machines see http://office.microsoft.com/en-us/assistance/HA010346281033.aspx
January 19, 2006 at 7:49 am
Anytime I wanted to format sql data in Excel I transformed it to xml and wrote a stylesheet. Lot of gotchas around xml and a learning curve though so for one line of bold it might not be worth the trouble.
January 19, 2006 at 8:09 am
You can do it in VBScript... Here's a sample script. Bad news is I hard coded the column names, but you could modify to build dynamically.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim i
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
' this is using the trusted connection if you use sql logins
' add username and password, but I would then encrypt this
' using Windows Script Encoder
objConnection.Open "Provider = SQLOLEDB;Data Source=dba03;" & _
"Trusted_Connection=Yes;Initial Catalog=pubs;user=reader;password=reader"
' creating the Excel object application
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
' The query goes here
objRecordSet.Open "select * from authors", _
objConnection, adOpenStatic, adLockOptimistic
i = 0
objRecordSet.MoveFirst
Do Until objRecordset.EOF
i = i + 1
objExcel.Cells(1, 1).Value = "Last Name"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 2).Value = "First Name"
objExcel.Cells(1, 2).Font.Bold = TRUE
objExcel.Cells(i, 1).Value = objRecordset.Fields.Item("au_lname")
objExcel.Cells(i, 2).Value = objRecordset.Fields.Item("au_fname")
objRecordset.MoveNext
Loop
' automatically fits the data to the columns
Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit()
' cleaning up
objRecordSet.Close
objConnection.Close
objworksheet.SaveAs("C:\test.xls")
objExcel.Quit
January 19, 2006 at 12:08 pm
Doesn't this mean that Excel has to be installed on the server?
If not then wouldn't it be better to break out the task so that one task writes the Excel file (Bulk Export) and the other opens up the workbook and formats the first row?
January 19, 2006 at 2:36 pm
if you create an html document and give it the .xls extension, then when excel opens it, it will maintain the format...so if you make a <TD BGCOLOR="#EEEEEE"><B>COLUMN NAME</B></TD>
the cell will be formatted correctly when it opens. that way, you do not have to automate excel, the huge overhead for excel when running multiple instances, etc...it's just a formatting issue for the writing to the file.
copy this html and name it a .xls extension to see it in action:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
</HEAD>
<BODY>
<TABLE BORDER="1">
<TR><TD BGCOLOR="#FFFFFF" colspan="2">Current FM / IDIS Error Messages as of 01/DD/YYYY (Automatically Created By Error Message Generator Program)</TD></TR><TR BGCOLOR="#FFFFFF"><TD>ERR_MSG_0 </TD><TD> NO ERROR Successful File Creation </TD></TR>
<TR><TD BGCOLOR="#CCCCCC">ERR_MSG_1 </TD><TD> ERROR 1 ** WARNING ** SYNDICATE AMOUNT EXISTS UNDER RENTAL HOUSING COMPLETION </TD></TR>
<TR><TD BGCOLOR="#FFFFFF"><B>ERR_MSG_2 </B></TD><TD BGCOLOR="#FF3333"> ERROR 2 `Setup Grantee` not found on C04PT-GRANTEE (System Setup>>Recipient>>UOG code/nbr is incorrect) </TD></TR>
<TR><TD BGCOLOR="#CCCCCC">ERR_MSG_3 </TD><TD> ERROR 3 Accomplishment Number is a required field
</TABLE>
</BODY>
</HTML>
Lowell
January 19, 2006 at 3:33 pm
Firstly, I will apologise for creating such a thread.
The reason is that after much playing around, all I did was opened the Excel spreadsheet, highlight the first row been the Column names and selected BOLD as the font saved the changes and.......when I execute the DTS Package, the BOLD font remains intact for the Column names.
While I was there I also autofit the Columns and that setting to remained intact each time I execute the DTS Package.
I have no idea how this is working, but it does work without any VBScript, XML or HTML for that matter.
January 19, 2006 at 5:18 pm
As long as you are exporting to the same file that should work.
January 20, 2006 at 7:58 am
Hmmm.. I tried renaming your example, Lowell and it didn't work using Word 2k3...
Ideas, comments or snide remarks?
January 20, 2006 at 8:07 am
copy and paste it from a raw text editor...word might reformat it to a microsoft format.
copying that as text, naming it test.xls and then opening it in excel and you'll see the cells i formatted maintain their formats with bold background etc.
Lowell
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply