Excel follow-up

[middletree]

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.

 

[McKirahan]

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?

 

[middletree]

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.

 

[McKirahan]

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

 

[middletree]

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]

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?

 

[middletree]

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]

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 . . .

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.

 

[McKirahan]

[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.

 

[McKirahan]

..

[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>

 

[middletree]

Well, I wasn't able to get to this until this morning. I hope you're still
reading this.

I appreciate the effort. I think your idea is beter than what I was going to
do. However it isn't quite working. It generates a spreadsheet, but it's
empty. I've tried putting the code in various places throughout the page,
but it still ends up with the same result.

On the seconds page, which is named sortabel2.asp, was I supposed to build
some HTML table code there?

 

[middletree]

OK, I added some code on that second page; just basic HTML code for a simple
table. It still generated an empty spreadsheet.

 

[middletree]

Ok, it works now. The problem was that the form element on one page had a
different name than the one on the page that requested it. It's coming in
now, although with bad colors. But I'll figure that out.

thanks