Do you write web applications for intranet applications? Have your users ever asked for the ability to export a certain report to excel? Do they complain about formulas not being there?
I do a lot of web applications that deal with HR stuff on the intranet (I can assume my users have Internet Explorer and Excel 2002 on their machines). With HR there are formulas for almost everything and when they get a spreadsheet of useful information without formulas, it suddently isn't so useful anymore (takes them too long to go and put the formulas in).
Thanks to Microsoft's Office getting on the HTML bandwangon so soon (Office 2000 and above), I can "export" data from a web application to excel with the formulas. To the user, it is an export. Really it isn't, it is only an HTML page with certain XML tags in it that Excel will interprete for the user into formuals, font formats, etc. If you put the proper headers on the page, Internet Explorer will tell Excel to open the HTML page and do its magic. So if you have a spread sheet that needs to be exported with formulas, the best place to start is in Excel. Create the spreadsheet you want your application to create and then Save As Web Page. Open this file (html) and check out the source. Now all you need to do is write the code to generate that file and tell IE it is for Excel and you are all set. Here is a little summary of the pieces involved:
Pieces of an excel spreadsheet with formulas
- HTML
head - with proper namespaces imported <html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
- Declare your
style <style id="STI_5961_Styles">
<!--
table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
.xlGeneral
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
...
</style>
-->
- Close the
head section and define the table with its cols </head>
<body>
<div id="STI_5961" align=center x:publishsource="Excel">
<table x:str border=0 cellpadding=0 cellspacing=0 width=2020 style='border-collapse:
collapse;table-layout:fixed;width:1518pt'>
<col width=147 style='mso-width-source:userset;mso-width-alt:5376;width:110pt'>
<col width=55 style='mso-width-source:userset;mso-width-alt:2011;width:41pt'>
...
- Create your header
tr <tr class=xlGeneralBold height=51 style='mso-height-source:userset;height:38.25pt'>
<td height=51 class=xlGeneralBold width=147 style='height:38.25pt;width:110pt'>Associate
Name</td>
<td class=xlGeneralBold width=55 style='border-left:none;width:41pt'>Rating</td>
...
</tr>
- Loop through the data and output the table data building a
tr for each record <tr height=17 style='height:12.75pt'>
<td height=17 class=xlGeneral style='height:12.75pt'>NOVOSELIC, CHRIS</td>
<td class=xlGeneral align=right x:num>2</td>
...
</tr>
- In the correct area add the SUM function code
...
<td class=xlSumDollar x:num="146751.2408" x:fmla="=SUM(A1:A6)"><span style='mso-spacerun:
yes'> </span>$<span style='mso-spacerun:yes'> </span>146,751.24 </td>
...
- Finish off the
body of the page </table>
</div>
</body>
</html>
- Set the content type to tell Internet Explorer to use Excel for this file
Response.ContentType = "application/vnd.ms-excel";
- Optional - add the header to tell Internet Explorer to treat the file as an attachment (should prompt user to save or open)
Response.AddHeader("Content-Disposition", "attachment; filename=SPQ.xls;");
No playing around with the Excel object (on the server?!?) ... let Excel do the work for you on the client..