Click here to Skip to main content
12,512,548 members (35,596 online)
Click here to Skip to main content
Add your own
alternative version

Stats

169.1K views
1.8K downloads
58 bookmarked
Posted

Creating a formatted MS-Excel sheet using ASP

, 10 Oct 2002
Rate this:
Please Sign up or sign in to vote.
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.

<%
' 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.

<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

Share

About the Author

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

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralAdding Font toolbar in Content Management Tool Pin
Member 41331231-Jun-08 18:01
memberMember 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
memberMember 410399229-May-08 5:44 
Generalcorrect formatting from dotnet to Excel Pin
Sugandh Jain21-Sep-05 23:24
sussSugandh 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
sussAnonymous28-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
memberiragersh29-Sep-03 4:53 
GeneralLove it but... Pin
Obiwan22-Jun-03 21:04
memberObiwan22-Jun-03 21:04 
GeneralRe: Love it but... Pin
kimjc0115-Mar-04 9:25
memberkimjc0115-Mar-04 9:25 
Questionhow to integrate excel sheets with i/explorer Pin
vijaymala29-Apr-03 1:28
sussvijaymala29-Apr-03 1:28 
Generalsave this in server Pin
SPS26-Apr-03 0:42
memberSPS26-Apr-03 0:42 
Generalcsv to graph Pin
Zoltan27-Jan-03 6:02
memberZoltan27-Jan-03 6:02 
GeneralExcellent technique! Pin
RocketJeff17-Dec-02 11:27
memberRocketJeff17-Dec-02 11:27 
QuestionHowbout adding sheets ? Pin
Frank Driesens14-Oct-02 23:22
memberFrank Driesens14-Oct-02 23:22 
AnswerRe: Howbout adding sheets ? Pin
knaicker16-Jul-04 0:23
memberknaicker16-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
memberMember 410399229-May-08 6:20 
GeneralRe: Howbout adding sheets ? Pin
fedetano27-Feb-09 2:39
memberfedetano27-Feb-09 2:39 
GeneralJust what I needed!!! Pin
Luca Leonardo Scorcia12-Oct-02 0:34
memberLuca Leonardo Scorcia12-Oct-02 0:34 
GeneralPretty cool indeed Pin
__Stephane Rodriguez__10-Oct-02 21:29
member__Stephane Rodriguez__10-Oct-02 21:29 
GeneralRe: Pretty cool indeed Pin
Srinivas Chintakindi10-Oct-02 23:43
memberSrinivas Chintakindi10-Oct-02 23:43 
GeneralRe: Pretty cool indeed Pin
David Wulff11-Oct-02 9:51
memberDavid Wulff11-Oct-02 9:51 
GeneralRe: Pretty cool indeed Pin
Srinivas Chintakindi11-Oct-02 19:08
memberSrinivas Chintakindi11-Oct-02 19:08 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160929.1 | Last Updated 11 Oct 2002
Article Copyright 2002 by Srinivas Chintakindi
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid