The need to produce Excel reports from SQL Server is very common.
Here, Robyn Page and Phil Factor present practical techniques for creating and manipulating Excel spreadsheets from SQL Server, using linked servers and T-SQL.
The pièce de résistance is a stored procedure that uses OLE Automation to allow you full control over the formatting of your Excel report, and the ability to include sums, ranges, pivot tables and so on.
As always, the workbench is structured so that it can be pasted into Query Analyser and SSMS, and the individual examples executed – you can download the from the “Code Download” link at the bottom of the article, load it up and start experimenting!We start by showing you how to create an Excel Spreadsheet from SQL Server in TSQL(Transact SQL), create a worksheet, attach to it as a linked server, write to it, read from it, update it as if it was an ordinary SQL Server Database table, and then synchronise the data in the worksheet with SQL Server.We also illustrate the use of ) to create and populate an Excel spreadsheet from Transact SQL.If you need more control over the Excel Spreadsheet that you are creating, we then show you how to do it via OLE automation.This will enable you to do anything you can do via keystrokes, and allow you to generate full Excel reports with pivot tables and Graphs. If you have your security wide open, it is not just you who would be able to write out data as a spreadsheet.
Using this technique, you should be able to populate the data, or place data in particular calls or ranges. An intruder would be able to do it with that list of passwords or credit-card numbers.In a production system, this sort of operation needs to be properly ring-fenced.We tend to create a job queue and have a special user, with the appropriate permissions, on the Task Scheduler, to do anything that involves OLE automation or .Security precautions can get quite complex, but they are outside the scope of the article.Some of what we illustrate can be done using DTS or SSIS.Unfortunately, these are outside the scope of this article.