Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Excel follow-up

Reply
Thread Tools

Excel follow-up

 
 
middletree
Guest
Posts: n/a
 
      03-25-2005
I read through Curt's sample code for using ASP to create a spreadsheet
(.xls). I think I understand the basics, but am still running into problems.

I'm doing this on an Intranet app, so I saved a page to an HTML file, and
placed it at http://www.middletree.net/sort.htm for viewing to answer this
question. I'd like to have a clickable button that would take the info you
see in that table, and generate a spreadsheet. However, I want it to not
include that menu, and also to not include the hidden div (click the Options
checkbox to see what I mean)

As it is now, when I use some code built on Curt's example, it gives me
everything on that page.

Can anyone guide me on this? Not sure how to go about it.


 
Reply With Quote
 
 
 
 
McKirahan
Guest
Posts: n/a
 
      03-25-2005
"middletree" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I read through Curt's sample code for using ASP to create a spreadsheet
> (.xls). I think I understand the basics, but am still running into

problems.
>
> I'm doing this on an Intranet app, so I saved a page to an HTML file, and
> placed it at http://www.middletree.net/sort.htm for viewing to answer this
> question. I'd like to have a clickable button that would take the info

you
> see in that table, and generate a spreadsheet. However, I want it to not
> include that menu, and also to not include the hidden div (click the

Options
> checkbox to see what I mean)
>
> As it is now, when I use some code built on Curt's example, it gives me
> everything on that page.
>
> Can anyone guide me on this? Not sure how to go about it.



From what do you build the table -- a database?

Why not generate the Excel file at the same time as you build the table?



 
Reply With Quote
 
 
 
 
middletree
Guest
Posts: n/a
 
      03-25-2005
"McKirahan" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
>
> From what do you build the table -- a database?


Yes.


> Why not generate the Excel file at the same time as you build the table?


That's what I am asking how to do. I want the user to be able to return a
set of data like you saw, based upon several criteria (which you can see
when you check the Options checkbox), and have a button or link where they
can have a spreadsheet of the same data they see in that web page they just
generated.


 
Reply With Quote
 
McKirahan
Guest
Posts: n/a
 
      03-25-2005
"middletree" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "McKirahan" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
> >
> > From what do you build the table -- a database?

>
> Yes.
>
>
> > Why not generate the Excel file at the same time as you build the table?

>
> That's what I am asking how to do. I want the user to be able to return a
> set of data like you saw, based upon several criteria (which you can see
> when you check the Options checkbox), and have a button or link where they
> can have a spreadsheet of the same data they see in that web page they

just
> generated.
>


Can your Excel file be consistent with the initial display of the page?

Or does it have to reflect the sort options selected on the Web page?

Which of the following (pseudo-code) do you want?

1) Excel = static Web page

read database table
build Web page table
build Excel file
loop
save Excel file
display Web page

User clicks a button to:
download Excel file

2) Excel = dynamic Web page

read database table
build Web page table
loop
display Web page

User clicks a button to:
build Excel file
save Excel file
download Excel file


 
Reply With Quote
 
middletree
Guest
Posts: n/a
 
      03-25-2005
User gets the data displayed, after setting the criteria for the search, as
shown on my web sample page. There's a button or link that user clicks to
get the same data into an xls file.

Curt's code allowed me to do this already, but it put everything on the
calling page into the spreasdsheet, including stuff I didn't want in there,
like the menu and the form elements.

I guess I could just do a pop-up of a page containing only the parts I want,
and then generate the excel file from there.


"McKirahan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "middletree" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > "McKirahan" <(E-Mail Removed)> wrote in message
> > news(E-Mail Removed)...
> > >
> > > From what do you build the table -- a database?

> >
> > Yes.
> >
> >
> > > Why not generate the Excel file at the same time as you build the

table?
> >
> > That's what I am asking how to do. I want the user to be able to return

a
> > set of data like you saw, based upon several criteria (which you can see
> > when you check the Options checkbox), and have a button or link where

they
> > can have a spreadsheet of the same data they see in that web page they

> just
> > generated.
> >

>
> Can your Excel file be consistent with the initial display of the page?
>
> Or does it have to reflect the sort options selected on the Web page?
>
> Which of the following (pseudo-code) do you want?
>
> 1) Excel = static Web page
>
> read database table
> build Web page table
> build Excel file
> loop
> save Excel file
> display Web page
>
> User clicks a button to:
> download Excel file
>
> 2) Excel = dynamic Web page
>
> read database table
> build Web page table
> loop
> display Web page
>
> User clicks a button to:
> build Excel file
> save Excel file
> download Excel file
>
>



 
Reply With Quote
 
McKirahan
Guest
Posts: n/a
 
      03-25-2005
"middletree" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I read through Curt's sample code for using ASP to create a spreadsheet
> (.xls). I think I understand the basics, but am still running into

problems.
>
> I'm doing this on an Intranet app, so I saved a page to an HTML file, and
> placed it at http://www.middletree.net/sort.htm for viewing to answer this
> question. I'd like to have a clickable button that would take the info

you
> see in that table, and generate a spreadsheet. However, I want it to not
> include that menu, and also to not include the hidden div (click the

Options
> checkbox to see what I mean)
>
> As it is now, when I use some code built on Curt's example, it gives me
> everything on that page.
>
> Can anyone guide me on this? Not sure how to go about it.


I finally tracked down your original post and Curt's response.

I got the result you apparently want by cutting-and pasting
the following from the script at http://www.darkfalz.com/1085:

<%@ Language=VBScript %>
<% Response.Expires = -1
Response.ExpiresAbsolute = Now()-1
Response.ContentType = "application/vnd.ms-excel"
Response.Buffer = True
Response.Clear
Response.AddHeader "Content-Disposition", "filename=Sortable.xls"
%>

into a new ASP page then appending your table below it.

Finally, I created an empty Excel file called "Sortable.xls".

Is that what you really want?


 
Reply With Quote
 
middletree
Guest
Posts: n/a
 
      03-25-2005
Yes. I am working on it right now. I think I misunderstood hwo it works, and
now I have a better handle on it. Let's see what I can come up with . . .


"McKirahan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Is that what you really want?
>
>



 
Reply With Quote
 
McKirahan
Guest
Posts: n/a
 
      03-25-2005
"middletree" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> Yes. I am working on it right now. I think I misunderstood hwo it works,

and
> now I have a better handle on it. Let's see what I can come up with . . .
>
>
> "McKirahan" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> > Is that what you really want?



What about this:

1) surround your table with:

<span id="sortabled"> and </span>


2) add this code at the top of the page:

<form action="Sortabler.asp" method="post" name="formXL">
<input type="button" value="Excel" onclick="sortabler()">
<textarea name="Xcel" cols="1" rows="1" style="display:none"></textarea>
</form>

3) Add this code in the Head section

<script type="text/javascript">
function sortabler() {
var what = document.getElementById("sortabled").innerHTML;
var code = [
'<' + '%@ Language="VBScript" %>',
'<' + '% Response.Expires = -1',
' Response.ExpiresAbsolute = Now()-1',
' Response.ContentType = "application/vnd.ms-excel"',
' Response.Buffer = True',
' Response.Clear',
' Response.AddHeader "Content-Disposition",
"filename=Sortable.xls"',
'%>',];
var page = code.join("\n");
var xcel = page + what;
document.formXL.Xcel.value = xcel;
document.formXL.submit();
}
</script>


4) create a new ASP page called "sortabled.asp":

<%@ Language='VBScript' %>
<% Option Explicit
'*
Const cASP = "Sortablex.asp"
'*
Dim strASP
strASP = Server.MapPath(cASP)
Dim strXLS
strXLS = Request.Form("Xcel")
'Response.Write Len(strXLS)
'*
Dim objFSO
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strASP) Then
objFSO.DeleteFile(strASP)
End If
Dim objOTF
Set objOTF = objFSO.OpenTextFile(strASP,2,True)
objOTF.Write(strXLS)
Set objOTF = Nothing
Set objFSO = Nothing
'*
Response.Redirect(cASP)
%>


Then when you click on the "Excel" button
on the page with your table, it:
1) extracts the HTML between the span tags,
2) puts it in a hidden textarea prefaced with the AddHeader code,
3) submits the textarea in the form to the server,
4) which writes out a new ASP file
5) and then opens it in Excel!

It works for me! Try it and let me know.


 
Reply With Quote
 
McKirahan
Guest
Posts: n/a
 
      03-25-2005
"McKirahan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "middletree" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
> > Yes. I am working on it right now. I think I misunderstood hwo it works,

> and
> > now I have a better handle on it. Let's see what I can come up with . .

..
> >
> >
> > "McKirahan" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > >
> > > Is that what you really want?


[snip]

Here's a cleaner variation of my last post:

1) Add this to the HEAD section of your page

<script type="text/javascript">
function sortable2() {
document.formXL.Xcel.value =
document.getElementById("sortable1").innerHTML;
document.formXL.submit();
}
</script>


2) Add this in the BODY section of your page

<form action="Sortable2.asp" method="post" name="formXL">
<input type="button" value="Excel" onclick="sortable2()">
<textarea name="Xcel" cols="1" rows="1" style="display:none"></textarea>
</form>


3) Enclose your table with these tags:

<span id="sortable1">

</span>


4) Create the following ASP page:

<%@ Language='VBScript' %>
<% Option Explicit
'*
Const cASP = "Sortable3.asp"
Const cXLS = "Sortable3.xls"
'*
Dim strASP
strASP = Server.MapPath(cASP)
Dim arrVBS(7)
arrVBS(0) = "<%@ Language=`VBScript` %" & ">"
arrVBS(1) = "<% Response.Expires = -1"
arrVBS(2) = " Response.ExpiresAbsolute = Now()-1"
arrVBS(3) = " Response.ContentType = `application/vnd.ms-excel`"
arrVBS(4) = " Response.Buffer = True"
arrVBS(5) = " Response.Clear"
arrVBS(6) = " Response.AddHeader `Content-Disposition`,
`filename=" & cXLS & "`"
arrVBS(7) = "%" & ">"
Dim strVBS
strVBS = Join(arrVBS,vbCrLf)
strVBS = Replace(strVBS,"`",Chr(34))
Dim strXLS
strXLS = Request.Form("Xcel")
'Response.Write Len(strXLS)
'*
Dim objFSO
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strASP) Then
objFSO.DeleteFile(strASP)
End If
Dim objOTF
Set objOTF = objFSO.OpenTextFile(strASP,2,True)
objOTF.Write(strVBS & strXLS)
Set objOTF = Nothing
Set objFSO = Nothing
'*
Response.Redirect(cASP)
%>


Basically, I moved the AddHeader code to the ASP page.


 
Reply With Quote
 
McKirahan
Guest
Posts: n/a
 
      03-26-2005
"McKirahan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "McKirahan" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > "middletree" <(E-Mail Removed)> wrote in message
> > news:#(E-Mail Removed)...
> > > Yes. I am working on it right now. I think I misunderstood hwo it

works,
> > and
> > > now I have a better handle on it. Let's see what I can come up with .

..
> .
> > >
> > >
> > > "McKirahan" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > >
> > > > Is that what you really want?


[snip]

Here's an even cleaner variation that revises my last post.

No <script> in the HEAD section needed:

1+2) Add this in the BODY section of your page

<form action="Sortable2.asp" method="post"
onsubmit="this.XL.value = document.getElementById('sortable1').innerHTML">
<input type="submit" value="Excel">
<textarea name="XL" cols="1" rows="1" style="display:none"></textarea>
</form>


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to return an excel file or excel data from ASP.NET Anonieko ASP .Net 2 02-12-2008 07:03 PM
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
using Microsoft Excel image for Export to Excel button =?Utf-8?B?U3JpZGhhcg==?= ASP .Net 0 12-09-2005 08:58 PM
exporting an excel file from database; making changes to excel file and updating the database by importing it back Luis Esteban Valencia ASP .Net 1 01-12-2005 12:28 AM
Excel _WorkBook vs. Excel.Workbook =?Utf-8?B?SmltIEhlYXZleQ==?= ASP .Net 1 09-29-2004 03:48 PM



Advertisments