(untagged)

Creating a formatted MS-Excel sheet using ASP

Srinivas Chintakindi

0.00/5 (No votes)

10 Oct 2002

An article on how to create a formatted MS-Excel sheet using ASP

Download source - 1 Kb

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