AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=904&pId=-1
Generating Excel File Using SQL Server 2000
page
by Aravind Kumar
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 1398719/ 2230

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.



©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-05-23 11:38:59 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search