|
Generating Excel File Using SQL Server 2000
|
by Aravind Kumar
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
1111711/
2553
|
|
|
Introduction |
I dedicate this article to my brother's daughter Akshaya.
This article will explore the features of SQL Server 2000
for using Microsoft tools, such as MS-Excel and DOS. Here we extensively use
DOS Shell commands and ODBC database object connectivity for Excel. The main
objective of this article is to generate an excel file directly from MS SQL
Server 2000. This will be very useful when we have a huge amount of SQL data
output that needs to be transferred to the excel file. Usually Internet
applications will generate an excel file by
changing the Page’s Response-Content Type. This allows users to open it
with Internet Explorer and/or saving it in their Hard disk. This can work well
if we generate a fewer number of records. However, for a large amount of
records, it is better to generate the excel file in the web server and download
it later. The other advantage in this method is that we can also stream
multiple SQL outputs at a single time into various excel worksheets present in
that single downloadable excel file. In other words, simultaneous SQL Data
outputs can be generated to fill into different worksheets of the same excel
file.
|
Template File |
Before generating the output in an excel file, we first need
to create an excel file (template file). This template file should have the
column names of the result set to be generated through the SQL Query. This is
done by simply typing the Column names in the row of a excel sheet, below which
the data will be generated by SQL Server 2000 (it is nothing but a Header
column). If we want to generate multiple query sets through the same SQL
Query, we need to create multiple excel work sheets according to our result tables
inside the template file. Remember, Excel will have 3 sheets by default. We
can give the template formatting, such as color and background, for a better
look and feel for the output file. This template file will be copied using
simple DOS "Copy" command through the stored procedure.
|
DOS File Copy |
Microsoft SQL Server is a not only a powerful database, but
also a tool for utilizing other Microsoft utilities like DOS, Office suite and
other various software. We can utilize various DOS internal commands using SQL
Server for our needs. And one good command, which will be used here, is the
DOS File Copy command.
We can utilize DOS commands from SQL Server 2000 using the
built-in stored procedure "Xp_CmdShell" provided by SQL Server 2000. This
built-in stored procedure is placed by default in the master database. So, henceforth
it is assumed that we will be working in the master database while running this
particular command.
By default, only members (i.e. - sa) of the sysadmin fixed
server role can execute this extended stored procedure. You may, however,
grant other users permission to execute this stored procedure.
For Example, if we need to delete a file we can run the
following command in SQL.
Listing 1
EXEC MASTER..XP_CMDSHELL 'Del d:\sdf.xls'
In the above example we can see that the built-in stored
procedure XP_CMDSHELL will be executed by passing the desired dos command. Here
the dos command "Del" has been used to instruct the stored procedure
to delete the file in the path specified viz., 'd:\sdf.xls’;.
|
Using Excel Object |
This gives us the template file through DOS File Copy. Now
we need to connect to the Excel file, which we have just copied through the
stored procedure, for transferring the data generated by the SQL query from SQL
Server to the excel worksheet. Here we use Microsoft.Jet.OLEDB.4.0 as the database
engine for connecting to this excel file. We can connect to multiple sheets of
the same excel file by specifying the worksheet name in the select query string
in our stored procedure.
We can simulate the example by saying that the Database is
the Excel file and the table is the Sheet1 present inside the Excel file. So
by default, we can copy data from three data sources to three default Sheets
present in the excel file. If we can add more worksheets to the template file,
we can obviously get more data tables copied to the excel file.
|
Using OPENROWSET Command |
SQL OpenRowSet command is usually used to connect to the
linked server or remote server for fetching data. It includes all connection
information necessary to access remote data from an OLE DB data source. This
method is an alternative to accessing tables in a linked server and is a
one-time, ad hoc method of connecting and accessing remote data using OLE DB.
The OPENROWSET function can be referenced in the FROM clause
of a query as though it is a table name. The OPENROWSET function can also be
referenced as the target table of an INSERT, UPDATE or DELETE statement and is
subject to the capabilities of the OLE DB provider. Although the query may
return multiple result sets, OPENROWSET returns only the first one.
Listing 2
OPENROWSET ( 'provider_name' , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
, { [ catalog. ] [ schema. ] object | 'query' } )
So, if our need is to insert a data collection into an OLEDB data source (in this case, an Excel File), you have to specify the following:
·
OLEDB version of the SQL Server
·
Excel version and Excel FileName
·
Worksheet you are going to connect for data copying
The above will simply look like the following.
Listing 3
insert into OPENrowset( Provider, ExcelConnectionString,
'SELECT [Column Names] FROM [SheetN$]') 'select [Column Names] from TableName'
After the query execution, the select query mentioned after
OPENrowset parameters will be inserted into datasheet of the mentioned excel
file connected through the OPENrowset function.
|
Example |
We take an example here to generate Authors and Sales data
of pubs database into the first two sheets of the excel file. Here we specify
three parameters for the OPENROWSET Function.
·
OLEDB Engine provider as "Microsoft.Jet.OLEDB 4.0"
·
Excel Connectivity string as "Excel 8.0;
Database=FileName.xls"
·
Select query of the columns framed in the Excel File
Mentioning the OLEDB Provider and excel destination
filename
Listing 4
set @provider = 'Microsoft.Jet.OLEDB.4.0'
set @ExcelString = 'Excel 8.0;Database=' + @fn
Executing the OPENROWSET Command for copying the select
contents to Excel sheet
Listing 5
exec('insert into OPENrowset (''' + @provider +
''',''' + @ExcelString + ''',''SELECT FirstName, LastName, Phone, Address,
City, State, Zip FROM [Sheet1$]'') select au_fname as FirstName, au_lname as
LastName, phone, address, city, State, Zip from authors')
exec('insert into OPENrowset (''' + @provider +
''',''' + @ExcelString + ''',''SELECT StoreId, OrderNo, OrderDate, Quantity
FROM [Sheet2$]'') select stor_id as StoreId,Ord_Num as OrderNo,Ord_Date as
OrderDate,qty as Quantity from sales')
After the execution of the above statements, the results
obtained from the select statements mentioned after the each OPENROWSET
commands will be copied to the two sheets of the excel file respectively.
|
Guidelines for running the sample query |
Download
the sample and unzip it. Copy the Template.xls excel file to "D:\".
Copy and run the stored procedure from PUBS database as sa. While running the
stored procedure, we can specify a name in which the resultant data will be generated;
else it will automatically generate the data in a file called "D:\Test.xls."
|
Summary |
This article is concerned with reducing network traffic
while generating big excel files (with multiple excel sheets) through web
applications. It does this by utilizing SQL Server’s unique features. Generally
when we respond the excel data into the web browser, it will take more time to
generate huge data and it is hard to generate multiple sheets of data. I hope
the above will be a good solution for you.
Editor's Note: If you are considering third party tools, you may want to
consider
SoftArtisans OfficeWriter as another
alternative for keeping Microsoft Office off the server.
|
|
|
|
|
|