Click here to Skip to main content
Licence 
First Posted 10 Oct 2002
Views 150,567
Bookmarked 57 times

Creating a formatted MS-Excel sheet using ASP

By | 10 Oct 2002 | Article
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

About the Author

Srinivas Chintakindi

Web Developer

India India

Member

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

Sign Up to vote   Poor Excellent
Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

 
You must Sign In to use this message board. (secure sign-in)
 
Search this forum  
 FAQ
    Noise  Layout  Per page   
  Refresh
GeneralAdding Font toolbar in Content Management Tool PinmemberMember 413312318:01 1 Jun '08  
QuestionHow to Add a Styles to the Table by Using External Style Sheet ? PinmemberMember 41039925:44 29 May '08  
Generalcorrect formatting from dotnet to Excel PinsussSugandh Jain23:24 21 Sep '05  
Hi,
I am taking some data from database, putting it in datagrid and generating an excel in response.
The problem is some column entries are picked up from Text datatype in the database and they have bullets, hyphens in between, So when the excel is generated these characters get corrupted.
I want these characters to be the same in excel as in the database.
 

The method I am writing now is :
 
public static void Convert(DataSet dataSet, HttpResponse response)
{
response.Clear();
response.Charset = "";
response.ContentEncoding = Encoding.ASCII;
response.Buffer = true;
string currentDate = String.Format("{0:dd_MM_yyyy}",DateTime.Now);
// response.AddHeader("Content-Disposition", "attachment;filename=" + "AssessSummary"+currentDate+".xls") ;
response.ContentType = "application/vnd.ms-excel";

//EnableViewState = false;

StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
DataGrid dataGrid = new DataGrid();
dataGrid.HeaderStyle.Font.Bold = true;
dataGrid.DataSource = dataSet.Tables[0];
dataGrid.DataBind();
// tell the datagrid to render itself to our htmltextwriter
dataGrid.RenderControl(htmlWriter);
response.Write(stringWriter.ToString());
response.End();
}
 
Please suggest the solution.
 
Help will be highly appreciated.
 
Regards,
Sugandh
GeneralProblem in opening excel doc on linux PinsussSuneet kumar2:44 15 Apr '04  
QuestionHow can I set column style? (For example I need DateTime column) PinsussAnonymous3:14 28 Jan '04  
GeneralUsing Excell features in my web application PinsussWaleedshafik19:24 27 Nov '03  
GeneralSmall Correction Pinmemberiragersh4:53 29 Sep '03  
GeneralLove it but... PinmemberObiwan21:04 22 Jun '03  
GeneralRe: Love it but... Pinmemberkimjc019:25 15 Mar '04  
Questionhow to integrate excel sheets with i/explorer Pinsussvijaymala1:28 29 Apr '03  
Generalsave this in server PinmemberSPS0:42 26 Apr '03  
Generalcsv to graph PinmemberZoltan6:02 27 Jan '03  
GeneralExcellent technique! PinmemberRocketJeff11:27 17 Dec '02  
QuestionHowbout adding sheets ? PinmemberFrank Driesens23:22 14 Oct '02  
AnswerRe: Howbout adding sheets ? Pinmemberknaicker0:23 16 Jul '04  
GeneralRe: Howbout adding sheets ? PinsussManjunath Basrur2:12 14 Apr '05  
GeneralRe: Howbout adding sheets ? PinmemberMember 41039926:20 29 May '08  
GeneralRe: Howbout adding sheets ? Pinmemberfedetano2:39 27 Feb '09  
GeneralJust what I needed!!! PinmemberLuca Leonardo Scorcia0:34 12 Oct '02  
GeneralPretty cool indeed Pinmember__Stephane Rodriguez__21:29 10 Oct '02  
GeneralRe: Pretty cool indeed PinmemberSrinivas Chintakindi23:43 10 Oct '02  
GeneralRe: Pretty cool indeed PinmemberDavid Wulff9:51 11 Oct '02  
GeneralRe: Pretty cool indeed PinmemberSrinivas Chintakindi19:08 11 Oct '02  

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web02 | 2.5.120528.1 | Last Updated 11 Oct 2002
Article Copyright 2002 by Srinivas Chintakindi
Everything else Copyright © CodeProject, 1999-2012
Terms of Use
Layout: fixed | fluid