Basic Excel Spreadsheet Generation (ASP/ASP.NET)
The 'correct' way to generate Excel spreadsheets is through the use of Office Web Components. However, due to the licensing (see MSKB Article) you cannot use them on a public internet site (as a client license for Office is needed for each user retrieving the document).
However, this can be overcome due to the fact that Excel recognises HTML code. You can therefore output HTML that can be opened in Excel:
First of all, set the content type and response header to make the browser handle it as an Excel spreadsheet.
ASP VBScript:
<%@Language="VBScript"%> <% Response.ContentType="application/vnd.ms-excel" Response.AddHeader "content-disposition", "attachment; filename=ExcelFileName" %>
ASP JScript:
<%@Language="JScript"%>
<%
Response.ContentType="application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=ExcelFileName");
%>
ASP.NET VB
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load (Sender As Object, E As EventArgs)
Response.ContentType="application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment; filename=ExcelFileName")
End Sub
</script>
ASP.NET C#
<%@ Page Language="C#" %>
<script runat="server">
void Page_Load(Object Sender, EventArgs E)
{
Response.ContentType="application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=ExcelFileName");
}
</script>
Start the html document and set the summary details (i.e. the information you see when you view the document properties: File - Properties in Excel). xmlns:o and xmlns:x are required for the summary information to be recognised by Excel. represents a line break (only valid in o:Description)
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"> <head> <title>Title</title> <!--[if gte mso 9]><xml> <o:DocumentProperties> <o:Subject>Subject</o:Subject> <o:Author>Author</o:Author> <o:Keywords>Keyword1, Keyword 2</o:Keywords> <o:Description>Comment Line 1 Comment Line 2</o:Description> <o:Category>Category</o:Category> <o:Manager>Manager Name</o:Manager> <o:Company>Company Name</o:Company> </o:DocumentProperties> </xml><![endif]-->
You can also set the name of the worksheet (this code is also required if you want to see the grid lines in the sheet) and also protect the contents by setting x:ProtectContents to True (i.e. you will have to unprotect the sheet (Tools - Protection - Unprotect Sheet) before you can edit):
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Worksheet Name</x:Name>
<x:WorksheetOptions>
<x:ProtectContents>False</x:ProtectContents>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->
Next add the CSS (this is important as you want line breaks to be part of the same cell, otherwise the text after the line break will go on a new row):
<style>
<!--
br {mso-data-placement:same-cell;}
-->
</style>
Close the head tag and then output the table (static, or done dynamically using repeater (i.e. loop through recordset object in ASP, or bind to DataTable in ASP.NET)):
</head> <body> <table border="1"> <tr><th>Head 1</th><th>Head 2</th></tr> <tr><td>Data 1</td><td>Data 2</td></tr> <tr><td>Data 3<br>Should be<br>In same cell</td><td>Data 4</td></tr> </table> </body> </html>
Comments
Better than using a commercial component (although it will not work with old versions of Excel) and quite easy to do.