beginning tutorial for exporting data from ASP to Excel

23 Mar 2005 3:04 PM

middletree

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.

I've seen lots of tutorials that are for developers who know some basics
that I don't know. Assuming I know ASP, and can use Excel the normal way,
can anyone point me to a tutorial or set of instructions that will get me up
and running?

23 Mar 2005 3:22 PM

Curt_C [MVP]

this will get you started

--
Curt Christianson
Site & Scripts: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com

Show quoteHide quote

"middletree" <middlet***@htomail.com> wrote in message
news:e9Xlfm7LFHA.1176@TK2MSFTNGP12.phx.gbl...
>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.
>
> I've seen lots of tutorials that are for developers who know some basics
> that I don't know. Assuming I know ASP, and can use Excel the normal way,
> can anyone point me to a tutorial or set of instructions that will get me
> up
> and running?
>
>

23 Mar 2005 3:22 PM

Curt_C [MVP]

Ooops...
http://www.darkfalz.com/1085

sorry about that

--
Curt Christianson
Site & Scripts: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com

Show quoteHide quote

"middletree" <middlet***@htomail.com> wrote in message
news:e9Xlfm7LFHA.1176@TK2MSFTNGP12.phx.gbl...
>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.
>
> I've seen lots of tutorials that are for developers who know some basics
> that I don't know. Assuming I know ASP, and can use Excel the normal way,
> can anyone point me to a tutorial or set of instructions that will get me
> up
> and running?
>
>

23 Mar 2005 4:47 PM

middletree

Perfect!

Thanks!

Show quoteHide quote

"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:%23Xl$Mv7LFHA.3812@TK2MSFTNGP10.phx.gbl...
> Ooops...
> http://www.darkfalz.com/1085
>
> sorry about that
>
> --
> Curt Christianson
> Site & Scripts: http://www.Darkfalz.com
> Blog: http://blog.Darkfalz.com
>
>
> "middletree" <middlet***@htomail.com> wrote in message
> news:e9Xlfm7LFHA.1176@TK2MSFTNGP12.phx.gbl...
> >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.
> >
> > I've seen lots of tutorials that are for developers who know some basics
> > that I don't know. Assuming I know ASP, and can use Excel the normal
way,
> > can anyone point me to a tutorial or set of instructions that will get
me
> > up
> > and running?
> >
> >
>
>

29 Mar 2005 3:24 PM

larrybud2002

middletree wrote:

> 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:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office: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:DoNotDisplayGridlines/>
     <x:CodeName>Sheet1</x:CodeName>
     <x:FitToPage/>
     <x:Print>
      <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:Print>
    </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>