Excel follow-up

Discussion in 'ASP General' started by middletree, Mar 25, 2005.

  1. middletree

    middletree Guest

    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.
     
    middletree, Mar 25, 2005
    #1
    1. Advertising

  2. middletree

    McKirahan Guest

    "middletree" <> wrote in message
    news:...
    > 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?
     
    McKirahan, Mar 25, 2005
    #2
    1. Advertising

  3. middletree

    middletree Guest

    "McKirahan" <> wrote in message
    news:eek:...
    >
    > 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.
     
    middletree, Mar 25, 2005
    #3
  4. middletree

    McKirahan Guest

    "middletree" <> wrote in message
    news:...
    > "McKirahan" <> wrote in message
    > news:eek:...
    > >
    > > 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
     
    McKirahan, Mar 25, 2005
    #4
  5. middletree

    middletree Guest

    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" <> wrote in message
    news:...
    > "middletree" <> wrote in message
    > news:...
    > > "McKirahan" <> wrote in message
    > > news:eek:...
    > > >
    > > > 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
    >
    >
     
    middletree, Mar 25, 2005
    #5
  6. middletree

    McKirahan Guest

    "middletree" <> wrote in message
    news:...
    > 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?
     
    McKirahan, Mar 25, 2005
    #6
  7. middletree

    middletree Guest

    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" <> wrote in message
    news:...
    >
    > Is that what you really want?
    >
    >
     
    middletree, Mar 25, 2005
    #7
  8. middletree

    McKirahan Guest

    "middletree" <> wrote in message
    news:#...
    > 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" <> wrote in message
    > news:...
    > >
    > > 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.
     
    McKirahan, Mar 25, 2005
    #8
  9. middletree

    McKirahan Guest

    "McKirahan" <> wrote in message
    news:...
    > "middletree" <> wrote in message
    > news:#...
    > > 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" <> wrote in message
    > > news:...
    > > >
    > > > 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.
     
    McKirahan, Mar 25, 2005
    #9
  10. middletree

    McKirahan Guest

    "McKirahan" <> wrote in message
    news:...
    > "McKirahan" <> wrote in message
    > news:...
    > > "middletree" <> wrote in message
    > > news:#...
    > > > 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" <> wrote in message
    > > > news:...
    > > > >
    > > > > 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>
     
    McKirahan, Mar 26, 2005
    #10
  11. middletree

    middletree Guest

    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, Mar 28, 2005
    #11
  12. middletree

    middletree Guest

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


    "middletree" <> wrote in message
    news:...
    > 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, Mar 28, 2005
    #12
  13. middletree

    middletree Guest

    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


    "middletree" <> wrote in message
    news:...
    > OK, I added some code on that second page; just basic HTML code for a

    simple
    > table. It still generated an empty spreadsheet.
    >
    >
    > "middletree" <> wrote in message
    > news:...
    > > 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, Mar 28, 2005
    #13
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Martin
    Replies:
    0
    Views:
    1,245
    Martin
    Aug 29, 2003
  2. Davef

    Follow up to javascript question.

    Davef, Aug 22, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    654
    Davef
    Aug 23, 2003
  3. Jason MacKenzie

    Dynamic Control Follow Up

    Jason MacKenzie, Nov 26, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    293
    Jason MacKenzie
    Nov 26, 2004
  4. Charles Thomas

    Images from JAR files - follow up

    Charles Thomas, Jul 24, 2003, in forum: Java
    Replies:
    0
    Views:
    348
    Charles Thomas
    Jul 24, 2003
  5. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,525
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
Loading...

Share This Page