Capt. Horatio T.P. Webb
MIS 4372 Creating Simple Excel files on the server with ASP
Parks -- Spring 2013

Version 1 -- Last Updated 9:00 PM 4/19/2013

One often occuring need is to provide data to users in Microsoft Excel format. There are some simple ways to do this that will often work:

  1. a simple *.asp program to make an Excel spreadsheet

    The code below:

    You can run the program here. Here is the code (you can cut and paste this):

    You will note that the code just make an HTML table and writes it out using standard HTM <table>, <tr> and <td> tags. Note that Excel will recognize "some" of the tag attributes used like:

    Further, you can insert an Excel formula into the cells provided you have the correct cell columns (A,B,C,...) and row numbers (1,2,3,...). See for example in the last cell of each row of data, the Excel is directed to compute the row sum:

    response.write "=sum(b"+cstr(r+2)+":k"+cstr(r+2)+")"

    places the

    This provides Excel with a sum function that sums columns br+2 : kr+2. This is because the data table rows start in Excel column B and go through column K AND the data starts on the 3rd table row. A similar Excel sum function is used on the lower right hand cell to get the sum of all 100 data cells. Here is the output as displayed in Excel:

  2. From a database to Excel

    We can also get data from a database and display the results in EXCEL like this:

    This example rolls out the full trial balance from our glmaster table into an HTML table. You will still get the warning from Excel in a message box. You can run the program here. The output is displayed in Excel like this:

  3. Creating Excel using VB.NET

    This is essentially the same process used for *.asp. We only change the syntax to VB.NET (it also works for C#). It is a simple table rollout. We are letting VB.NET do the work. You can run the *.aspx program here. Here is the code:

    Here is the output displayed in Excel:

  4. Using a *.csv" file instead of HTML to get data from a database to Excel

    The *.csv" file format is an ASCII text format that uses commas to separate the cells of a table and the carriage return (ASCII character 13) and line feed (ASCII character 10) to define the end of rows. Using the same asp logic to roll out a recordset from our glmaster database table. You can run the program here. The code is:

    Notes:

    Here is the output:

    In general, the purpose of creating Excel for the user on the server is to get the "data" to Excel -- not perform analyis, draw graphs or anything else. While the analytic senarios that utilize Execl are vast, Excel is a desktop tool. It cannot retrieve data from the server or store data on the server. So, making the server-side middle-ware code that interactes with the database to produce "data sets for Excel" is a common task you will encounter.

    On the client-side you will be asked to provide:

    1. selection options (which columns, which tables -- i.e., the "SELECT" information)
    2. data formating options (precision, scale, length -- i.e., integer, decimal and string formatting)
    3. be sure to ask your user about providing:
      • extraction dates and source information as part of the data download:
        • when was the data retrieved
        • where did the data come from (database(s), location, authorization)
      • file/database creation (i.e., additionally creating a permanent data store for future use)
      • distribution (providing ways to share the same data with other users)
      • security (access to the datasets you create and encryption)
      • code retention (where do you keep the code you write? can it be reused? by whom?)

    CAUTION: Browsers other than IE will NOT automatically fire up Excel. When you execeute server-side code that produces an *.xls, *.xlsx or *.cvs file, they prompt the user about what to do with the file.

    The simple relationship between any SQL SELECTs and HTML tables makes this process easy.