March 26, 2007 at 12:12 am
Can you please help me make a design decision? The requirements are that I have to regularly produce Excel spreadsheets that contain transactional performance information. Examples of this information would be:
1.) A count of transaction type X, Y and Z
2.) Of each of the above transaction types, how many had a response time under 2 seconds?
The Excel spreadsheets have to be laid out in a certain, unpredictable fashion (i.e. for transaction type X, the response time under 2 seconds should be shown in cell A10, the response time for transaction type Y should appear in cell D12, etc.).
And yes it has to be Excel.
It seems I have limited options. I either design an Excel spreadsheet with ActiveX Marcos that go directly to the data source and retrieve the data I’m interested in, or write a stored procedure that produces a csv spreadsheet which can then be imported in to Excel.
We’re using SQL Server 2000 and Excel 2000. I have access to development tools such as Delphi, but never found the Excel programming in
Dan
March 26, 2007 at 1:13 am
Hi Dan,
I would probably go down the root of DTSing out the information to Excel (http://support.microsoft.com/kb/319951) however your reformatting sounds slightly more fun to do, probably the easiest thing to do would be to automate excel from WSH and run this as the second step of the job after the DTSExec. http://cwashington.netreach.net/depo/view.asp?Index=142 seems to be an okish example script covering some level of automation of excel.
- James
--
James Moore
Red Gate Software Ltd
March 26, 2007 at 2:27 am
Hi James,
thanks for the response. Yeah, the real constraint is Excel. It is just not a very good tool for database reporting (hence Crystal Reports and Reporting Services I guess).
I'll play about with the formatting and let you know.
Any other offers suggestions received.
Dan
March 26, 2007 at 6:41 am
an FYI that you already know most likely: programatically automating excel is slow, and it is notoriously difficult to close the last excel object...so if you run task manager, it is often common to see dozens of instances of excel running because a program did not close excel properly. I've done it in vb6 and vb.NET.
That being said, sometimes you have to do it...I recently had a requirement that a sheet had to be made, but only certain cells were editable, the rest were to remain locked.
typically, I prefer to make a csv if possible, if formating is required, like bold, background colors, fonts, etc., I make an html doc and rename it to .xls and let excel automatically import it (that works great). locked cells can only be done via excel automation. I prefer to make a new sheet every time, rather than open a template, because the template can disappear after deployment.
in a loop, creating a dozen semi-complicated worksheets of the same type, but different data takes a couple of minutes; keep that in mind.
Lowell
March 26, 2007 at 5:07 pm
Thanks for the feedback.
The html to Excel sounds like a good idea. Presumably you created the html file dynamically and populated the data as you reqruied them (?). What did you use to create the html doc?
Still trying to get the final spec agreed (i.e. how configurable does it have to be after the implementation, competency of the users, that sort of thing).
I think this could turn into a nightmare.
Dan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply