Click here to Skip to main content
15,881,709 members
Search within:


Articles / Programming Languages / ASP
 
Article

Creating a formatted MS-Excel sheet using ASP

Rate me:
Please Sign up or sign in to vote.
4.29/5 (18 votes)
10 Oct 2002 192K   1.9K   58   24
An article on how to create a formatted MS-Excel sheet using ASP

Introduction

Frequently, all the data driven web sites need to show reports in tabular forms on their web pages. The data may be static or dynamic. Static data can be shown in tabular form using <table>, <tr>, <td> and every web developer knows that, even though the dynamic data can also be shown in a tabular form using their own business logic mixed with HTML content.

Some times the Webmaster/Administrator need to save the reports in soft copy as a text/doc/Excel file. One way is to select and copy the entire table from the browser and paste it in a file. By this way we can save the content but all the formatting will be gone.

Create and save Excel files dynamically

Here is a way to create Excel files dynamically. I used static content in the table to create Excel, but you can write your own business logic to make dynamic tables and dynamic Excel sheets. The resultant Excel file has the same format as the HTML table, which is to be displayed on the browser.

VBScript
<%
' Tells the browser to use Excel to open the file
Response.ContentType = "application/vnd.ms-excel"

' Your data can come from ANYWHERE! Since that's not the
' point of this sample, I'm just hand writing a table
' below, but it could easily be generated from a database
' as illustrated in some of our other samples.

' Everything that follows is plain HTML... what you see
' in Excel is based on Excel's interpretation of it.
' It picks up the formulas and emulates the formatting
' pretty well...
%>

The MS Excel document.

HTML
<TABLE borderColor=#808080 border=1>
  <THEAD>        
    <TR>
      <TH BGCOLOR="BLUE"><B><FONT color=white 
         face=Arial size=2>Against</FONT></B></TD>
      <TH BGCOLOR="BLUE"><B><FONT color=white 
         face=Arial size=2>Match</FONT></B></TD>
      <TH BGCOLOR="BLUE"><B><FONT color=white 
         face=Arial size=2>Won</FONT></B></TD>
      <TH BGCOLOR="BLUE"><B><FONT color=white 
         face=Arial size=2>Lost</FONT></B></TD>
      <TH BGCOLOR="BLUE"><B><FONT color=white 
         face=Arial size=2>Tied</FONT></B></TD>
      <TH BGCOLOR="BLUE"><B><FONT color=white 
         face=Arial size=2>No Result</FONT></B></TD>
    </TR>
  </THEAD>
  <TBODY>    
     <TR>
       <TD><B><FONT face=Arial color=#000080 
          size=2>Australia</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
          size=2>67</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
          size=2>25</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
          size=2>39</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
          size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
          size=2>3</FONT></B></TD>
     </TR>
     <TR>
       <TD><B><FONT face=Arial color=#000080 
         size=2>Bangladesh</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>8</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>8</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
     </TR>
     <TR>
       <TD><B><FONT face=Arial color=#000080 
         size=2>East Africa</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>1</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>1</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
     </TR>
     <TR>
       <TD><B><FONT face=Arial color=#000080 
         size=2>England</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>46</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>21</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>23</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>2</FONT></B></TD>
     </TR>
     <TR>
       <TD><B><FONT face=Arial color=#000080 
         size=2>Kenya</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>10</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>8</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>2</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
     </TR>
     <TR>
       <TD><B><FONT face=Arial color=#000080 
         size=2>New Zealand</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>61</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>31</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>27</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>3</FONT></B></TD>
     </TR>   
     <TR>
       <TD><B><FONT face=Arial color=#000080 
         size=2>Pakistan</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>85</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>29</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>52</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>4</FONT></B></TD>
     </TR> 
     <TR>
       <TD><B><FONT face=Arial color=#000080 
         size=2>South Africa</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>42</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>14</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>27</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>1</FONT></B></TD>
     </TR> 
     <TR>
       <TD><B><FONT face=Arial color=#000080 
         size=2>Sri Lanka</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>73</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>39</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>29</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>5</FONT></B></TD>
     </TR> 
     <TR>
       <TD><B><FONT face=Arial color=#000080 
         size=2>U.A.E.</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>1</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>1</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
     </TR> 
     <TR>
       <TD><B><FONT face=Arial color=#000080 
           size=2>West Indies</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>69</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>25</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>42</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>1</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>1</FONT></B></TD>
     </TR> 
     <TR>
       <TD><B><FONT face=Arial color=#000080 
         size=2>Zimbabwe</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>42</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>32</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>8</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>2</FONT></B></TD>
       <TD><B><FONT face=Arial color=#800000 
         size=2>0</FONT></B></TD>
     </TR>
     <TR>
       <TD><B><FONT face=Arial color=#000000 
         size=2>Total</FONT></B></TD>
       <TD><B><FONT face=Arial color=#000000 
         size=2>=SUM(B2:B13)</FONT></B></TD>
       <TD><B><FONT face=Arial color=#000000 
         size=2>=SUM(C2:C13)</FONT></B></TD>
       <TD><B><FONT face=Arial color=#000000 
         size=2>=SUM(D2:D13)</FONT></B></TD>
       <TD><B><FONT face=Arial color=#000000 
         size=2>=SUM(E2:E13)</FONT></B></TD>
       <TD><B><FONT face=Arial color=#000000 
         size=2>=SUM(F2:F13)</FONT></B></TD>
     </TR>
  </TBODY>
</TABLE>

Pretty cool huh?

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Srinivas Chintakindi
Web Developer
India India
Hi All, I am having around 6+ years of Software Development Experience completely in Microsoft Technologies. With the initial background of ASP,VB I have strengthened my skillset with DotNet and SharePoint Technologies. I do have passion for work in latest technologies

Comments and Discussions

 
First Prev Next
GeneralAdding Font toolbar in Content Management Tool Pin
Member 41331231-Jun-08 18:01
Member 41331231-Jun-08 18:01 
QuestionHow to Add a Styles to the Table by Using External Style Sheet ? Pin
Member 410399229-May-08 5:44
Member 410399229-May-08 5:44 
Generalcorrect formatting from dotnet to Excel Pin
Sugandh Jain21-Sep-05 23:24
Sugandh Jain21-Sep-05 23:24 
GeneralProblem in opening excel doc on linux Pin
Suneet kumar15-Apr-04 2:44
sussSuneet kumar15-Apr-04 2:44 
QuestionHow can I set column style? (For example I need DateTime column) Pin
Anonymous28-Jan-04 3:14
Anonymous28-Jan-04 3:14 
GeneralUsing Excell features in my web application Pin
Waleedshafik27-Nov-03 19:24
sussWaleedshafik27-Nov-03 19:24 
GeneralSmall Correction Pin
iragersh29-Sep-03 4:53
iragersh29-Sep-03 4:53 
It is truly fantastic. I loaded your file on Excel 2000 and Excel 97 and they rendered differently. 97 had a better rendition of the heading. 2000 doesn't like that you have <th> </td> header pairs. 2000 imports fine when I make the pairs <th> </th>.
GeneralLove it but... Pin
Obiwan22-Jun-03 21:04
Obiwan22-Jun-03 21:04 
GeneralRe: Love it but... Pin
kimjc0115-Mar-04 9:25
kimjc0115-Mar-04 9:25 
Questionhow to integrate excel sheets with i/explorer Pin
vijaymala29-Apr-03 1:28
vijaymala29-Apr-03 1:28 
Generalsave this in server Pin
Sarvesvara (BVKS) Dasa26-Apr-03 0:42
Sarvesvara (BVKS) Dasa26-Apr-03 0:42 
Generalcsv to graph Pin
Zoltan27-Jan-03 6:02
Zoltan27-Jan-03 6:02 
GeneralExcellent technique! Pin
RocketJeff17-Dec-02 11:27
RocketJeff17-Dec-02 11:27 
QuestionHowbout adding sheets ? Pin
Frank Driesens14-Oct-02 23:22
Frank Driesens14-Oct-02 23:22 
AnswerRe: Howbout adding sheets ? Pin
knaicker16-Jul-04 0:23
knaicker16-Jul-04 0:23 
GeneralRe: Howbout adding sheets ? Pin
Manjunath Basrur14-Apr-05 2:12
sussManjunath Basrur14-Apr-05 2:12 
GeneralRe: Howbout adding sheets ? Pin
Member 410399229-May-08 6:20
Member 410399229-May-08 6:20 
GeneralRe: Howbout adding sheets ? Pin
fedetano27-Feb-09 2:39
fedetano27-Feb-09 2:39 
GeneralJust what I needed!!! Pin
Luca Leonardo Scorcia12-Oct-02 0:34
professionalLuca Leonardo Scorcia12-Oct-02 0:34 
GeneralPretty cool indeed Pin
Stephane Rodriguez.10-Oct-02 21:29
Stephane Rodriguez.10-Oct-02 21:29 
GeneralRe: Pretty cool indeed Pin
Srinivas Chintakindi10-Oct-02 23:43
Srinivas Chintakindi10-Oct-02 23:43 
GeneralRe: Pretty cool indeed Pin
David Wulff11-Oct-02 9:51
David Wulff11-Oct-02 9:51 
GeneralRe: Pretty cool indeed Pin
Srinivas Chintakindi11-Oct-02 19:08
Srinivas Chintakindi11-Oct-02 19:08 
Last Visit: 31-Dec-99 18:00     Last Update: 19-Apr-24 9:04Refresh1

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 10 Oct 2002

Article Copyright 2002 by Srinivas Chintakindi
Everything else Copyright © CodeProject, 1999-2024

Web02 2.8:2024-04-02:1