middletree said:
I did a search in this forum, as well as at
www.asp.faq.com, and MSDN, and
would like to know how to use ASP to build a report and create an ..xls file
as a result.
Here's an ASP that I wrote that will help you. Basically, create your
report in HTML, then place a server.execute("makeexcel.asp") (or
whatever you call this program), just before your HTML in the report
file. What makes this good is that you can fine tune your report in
HTML, then add the server.execute and it will convert it to XLS.
Couple of notes: You set session("tablename") to whatever XLS filename
you want. There are also a number of settings you use, anything that
calls the DEF function is a session var you can set before executing
this file. In addition, session("rowstorepeat") is a value for the
number of rows in your HTML to repeat at the top of each page.
There are also various styles that I've created that you can use...
<%
response.Buffer = true
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition","filename=" &
session("tablename") &".xls"
function def(val)
if session(val)<>"" then
execute(val &"=" & session(val))
session(val)=""
end if
end function
'-------------default settings-----------------------------------
fitheight=9999
fitwidth=1
orientation="landscape"
lmargin=.25
rmargin=.25
tmargin=.25
bmargin=.75
'
def "fitheight"
def "fitwidth"
def "orientation"
def "lmargin"
def "rmargin"
def "tmargin"
def "bmargin"
%>
<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns
="urn:schemas-microsoft-com
ffice
ffice"
xmlns:x="urn:schemas-microsoft-com
ffice:excel"
xmlns="
http://www.w3.org/TR/REC-html40">
<style>
@page
{margin:<%=lmargin%>in <%=rmargin%>in <%=bmargin%>in <%=tmargin%>in;
mso-footer-data:"&LPrinted on &D &T&RPage &P of &N";
mso-header-margin:.25in;
mso-footer-margin:.25in;
mso-page-orientation:<%=orientation%>;
mso-horizontal-page-align:center;}
td {font-size:10px;white-space:normal;}
br {mso-data-placement:same-cell;}
..currency { mso-number-format:"_($* #,##0.00_)"}
..currencybold {mso-number-format:"_($* #,##0.00_)";font-weight:700;}
..number2dec {mso-number-format: Fixed;}
..number2decbold {mso-number-format: Fixed; font-weight:700;}
..text {mso-number-format:General; text-align:general;white-space:
normal }
..num2text {mso-style-parent:text; mso-number-format:"\@";white-space:
normal}
</style>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x
oNotDisplayGridlines/>
<x:CodeName>Sheet1</x:CodeName>
<x:FitToPage/>
<x
rint>
<x:ValidPrinterInfo/>
<x:Scale>10</x:Scale>
<x:FitHeight><%=fitheight%></x:FitHeight>
<x:FitWidth><%=fitwidth%></x:FitWidth>
<x:HorizontalResolution>600</x:HorizontalResolution>
<x:VerticalResolution>600</x:VerticalResolution>
</x
rint>
</x:WorksheetOptions>
<x:AutoFormatWidth/>
</x:ExcelWorksheet>
<x:AutoFormatWidth/>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
<%if session("rowstorepeat")>0 then%>
<x:ExcelName>
<x:Name>Print_Titles</x:Name>
<x:SheetIndex>1</x:SheetIndex>
<x:Formula>=Sheet1!$1:$<%=session("rowstorepeat")%></x:Formula>
</x:ExcelName>
<%end if%>
</xml>
