Use ASP to generate Excel Data

One of the most widely used ASP is to generate database-driven Web reports. Generally, this is to view reports in an HTML table in the browser, but it is not enough for some users. These users want to download the report data and modify it with their own applications, including spreadsheet software (Microsoft Excel) or local database (Microsoft Access ). I will use ASP to demonstrate a variety of techniques for outputting data to a Microsoft Excel readable format. By using one or more of these techniques, you can place an "output to Excel" option on your web page to satisfy those users.

Test Environment

I use the following environment to create and test the samples provided in this article:

Windows NT 4/SP5 and IIS 4

Windows 95

Internet Explorer 4.x and 5

Interdev 6

Homesite 4.0

Access 97

Excel 97

This may be the fastest way to convert an html table into Microsoft Excel format. The ContentType attribute indicates the format of browser data to be formatted. Here, we want Microsoft Excel. When the browser sees that the attribute value is Excel, it prompts the user to save or open the file. If you choose to open a file, you can start Excel and view data in it. To make it work correctly, you must set ContentType before writing anything to the Response object. An example of this syntax is as follows:

Line 1: <% @ LANGUAGE = "VBSCRIPT" %> 〉

Line 2: Response. ContentType = "application/msexcel"

Line 3: %> 〉

Click here to get more information about the ContentType attribute of ASP Response object.

I found a problem when trying to use Internet Explorer 4.x. During the test, as mentioned in the Microsoft article Q185978. The content of this knowledge base article can be summarized as follows:

"If Internet Explorer is connected to a Web server resource that dynamically generates Word, Excel, or other active documents, Internet Explorer sends two GET requests to this resource. The second GET usually does not have session status information, temporary cookies, or evidentiary information that has been specified for the customer. This error may affect any activity document application hosted on the local server (EXE) in the Internet Explorer window. It occurs most frequently in ISAPI, ASP, or CGI applications. They verify the HTTP "Content Type" header file to identify the installed application ".

Therefore, if you try to use session variables or cookies and use IE4, this error may occur. It has been confirmed that this problem has been solved in ie5.

A value File separated by commas (,) is the second option for outputting web pages to an Excel readable format. This format is more flexible than the ContentType attribute. Compared with other methods, CSV has two advantages: First, you do not need any software on the client or server to create it. Second, the file is usually smaller than an Excel file.

The CSV format is defined as follows: comma-separated columns, and carriage return separates rows. A comma is used as a separator, which also raises a problem with the fields containing the comma (such as $1,234). This will cause an additional column in the row to be created. This problem can also be easily corrected by sealing the domain at each end with a comma.

The CreateCSVFile () function is used in the provided example to learn how to create a CSV file.

Line 1: strFile = GenFileName ()

Line 2: Set fs = CreateObject ("Scripting. FileSystemObject ")

Line 3: Set a = fs. CreateTextFile (server. MapPath (".") & "& strFile & Line 4:". csv ", True)

Line 5: If Not oRS. EOF Then

Line 6: strtext = chr (34) & "Year" & chr (34 )&","

Line 7: strtext = strtext & chr (34) & "Region" & chr (34 )&","

Line 8: strtext = strtext & chr (34) & "Sales" & chr (34 )&","

Line 9: a. WriteLine (strtext)

Line 10: Do Until oRS. EOF

Line 11: For I = 0 To oRS. fields. Count-1

Line 12: strtext = chr (34) & oRS. fields (I) & chr (34) Line 13 :&","

Line 14: a. Write (strtext)

Line 15: Next

Line 16: a. Writeline ()

Line 17: oRS. MoveNext

Line 18: Loop

Line 19: End If

Line 20: a. Close

Line 21: Set fs = Nothing

Line 22: Response. Write ("Click <a href =" & strFile & ". csv> Here </A> 〉

Line 23: to get CSV file ")

The first line calls the GenFileName () function to create a unique file name. The GenFileName () function will be discussed later.

Lines 2nd to 4th use the FileSystemObject object and the CreateTextFile function to write text files. In this example, the written file is in the same path as the source file. In actual work, you may want to create a separate path to store these files.

Line 5-9 generates the title of the first line. Because the reports are usually the same, I fixed the code of the column names, although it is also possible to read the database column names and use them. Note how I include commas in each domain. Use the WriteLine function to send them together with a carriage return to a file.

Rows from 10th to 18 are cyclically in the record set. Each field is marked with quotation marks followed by a comma. The Write function then sends each domain to a file. WriteLine: Press enter to end each row.

In the last few lines, close the file, release the object, and place a link on the page to retrieve it.

When you click the generated link, you will be prompted to save or open it. If you choose to open the file, open it in Excel (assuming that the file has been installed on the computer ). If you select save, save the text file to the storage device and enter it in each application.

The last method I have discussed is to create an Excel (.xls) file in Microsoft Excel. To use these objects, you must install Excel on the Web server. With these controls, you can have more control over formatting (such as font and color) and allow you to perform all the operations that can be performed in a real Excel application. Be sure to monitor the performance of your server, because Excel may become a very large object and impact the performance, depending on how you use it and how busy the server is.

I found that the fastest and easiest way to get more information on an Excel object is to use the Visual Basic Object Browser to watch the object and use context-sensitive help to get more details. When using this Object Browser: Start Visual Basic, create a project file, and add a reference to the Microsoft Excel Object Library. Under the View menu, you can select an Object Browser and specify an Excel library to View all available objects. Press F1 to get context-sensitive help for the current title.

After passing the CreateXlsFile () function in the provided sample, you can see how to create an Excel file. The basic steps are basically the same as creating a CSV file, but the created worksheet is an Excel worksheet.

Line 1: Dim xlWorkSheet

Line 2: Dim xlApplication

Line 3: Set xlApplication = Server. CreateObject ("Excel. Application ")

Line 4: xApplication. Visible = False

Line 5: xlApplication. Workbooks. Add

Line 6: Set xlWorksheet = xlApplication. Worksheets (1)

Line 7: xlWorksheet. Cells (1, 1). Value = "Year"

Line 8: xlWorksheet. Cells (1, 1). Interior. ColorIndex = 5

Line 9: xlWorksheet. Cells (1, 2). Value = "Region"

Line 10: xlWorksheet. Cells (1, 2). Interior. ColorIndex = 5

Line 11: xlWorksheet. Cells (1, 3). Value = "Sales"

Line 12: xlWorksheet. Cells (1, 3). Interior. ColorIndex = 5

Line 13: iRow = 2

Line 14: If Not oRS. EOF Then

Line 15: Do Until oRS. EOF

Line 16: For I = 0 To oRS. fields. Count-1

Line 17: xlWorksheet. Cells (iRow, I + 1). Value = oRS. fields (I)

Line 18: xlWorkSheet. Cells (iRow, I + 1). Interior. ColorIndex = 4

Line 19: Next

Line 20: iRow = iRow + 1

Line 21: oRS. MoveNext

Line 22: Loop

Line 23: End If

Line 24: strFile = GenFileName ()

Line 25: xlWorksheet. SaveAs Server. MapPath (".") & "& strFile &". xls"

Line 26: xlApplication. Quit 'close the Workbook

Line 27: Set xlWorksheet = Nothing

Line 28: Set xlApplication = Nothing

Line 29: Response. Write ("Click <A HRef =" & strFile & ". xls> Here </A> 〉

To Line 30: get XLS file ")

Rows 1st and 2nd determine the dimension of the Excel object used.

Row 3: Create an Excel file. Similarly, in order to work properly, the web server must also have an Excel file.

In row 3, set the Excel visibility to false so that there is no interface.

Rows 5th and 6th Add a workbook to accommodate the worksheet, and then set the current worksheet to the first Worksheet (this is created by Excel by default ). You can also use the Add function of the Worksheet object to Add a new Worksheet, which allows multiple worksheets in your Excel file.

Row 8-12 creates the title of the worksheet. In this example, we set the value of each unit to a proper title and set the internal color to blue. You can also use the Range object to modify multiple units at the same time.

Row 13-23 provides a loop for loading all data from the record set. Because the first line contains the title, I start the data in the second row of the workbook. In the For loop, each column is loaded into the row, and the internal color is set to green. An External Loop loops every row in the record set.

Line 3: Call the GenFileName () function to create a unique file name with the same function as CSV.

Row 3 stores the workbook. You can store tables in multiple formats specified in Excel.

The following three rows are used to organize objects. As a good ASP programmer, you must organize all objects.

Finally, I put the link to the Excel file on the page for download.

I created a sample to demonstrate every technique discussed above. To install the sample, you only need to copy all the files to the server and start the application with main.html. Excel is required on the server to use the "Native Excel" option. The sample uses an Access database (without a DSN link) to store sales data.

You can select one year or region for sales report. The final option is how you want to return data. You can see the following screen image:

<P align = "center">
The following table describes all the files provided in the sample.

File Name Description

DSN-SQL.asp contains a non-DSN link string

Adovbs. inc contains ADO Constants

TestDB. mdb contains An Access97 database for sales data. The sales table contains three fields: year -- Text, region -- Text, sales-amt -- numeric.

Main.html this file creates a frame for the screen and loads the initial Page

Welcome.html

Request.html contains a form to collect user choices for report creation.

The guts of the runquery. asp application. This document establishes an SQL statement to determine how the client requests the data to be returned, executes the SQL statement, and returns data according to the request.

Most of the Code is quite easy to understand. But I still want to discuss some functions in the runquery. asp file. I have demonstrated how to create CSV and Excel files.

The GenHTML () function creates an HTML table for the requested data. This function is used for both HTML and ContentType requests. For ContentType requests, note that Response. ContentType = "application/msexcel" is one of the first lines to be executed.

The GenFileName () function uses the system date to create the first part of the file name. This file name will be unique, so you can avoid a lot of trouble when trying to store the file. The extension (CSV or XLS) is used to store files. This allows the same function to generate two types of files.

The BuildSQL () function uses form variables to create an SQL statement that matches the user's request and returns it to the caller.

The Recordset is opened at the top of the script because it is independent of the selected display type. Recordset processes the SQL statement generated by the BuildSQL () function call and uses a non-DSN link to Access 97.

The <BODY> tag contains only two "if .... Then "statement to determine the display method required by the user. If declarations are distributed to the function that generates the correct return type, which is based on the user's "ReturnAS" selection. Then, the link and record set object are cleared.

Note: This sample does not involve the maintenance of the files created on the web server. I recommend that you delete these files after expiration. I do not advocate transferring the burden of removing these files to customers (via links on the page) because they are easy to forget these things.

Conclusion

This article demonstrates three ways to output data to an Excel readable format. I believe these three methods are available based on different user needs. If you want to output data to Excel quickly but not beautifully, you can use ContentType. If you want a file in a limited format that can be loaded into many different applications, the CSV format is suitable for you. If you prefer to include full formats, icons, or special Excel functions, creating a full Excel worksheet is a suitable method. Hopefully, these methods can help other programmers meet the customer's requirements or at least help you choose the right approach.