Can ASP Dump a Web Page's Content to an Excel spread sheet?

mapper
mapper used Ask the Experts™
on
I have a Web page that the owner wants to dump the contents to an Excel file/spreadsheet - can ASP do that?

If so, how?

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi Mapper,

If this is a single, one-time task, the easiest way is to view the page in your browser, select all, select copy, and then paste to a new Excel worksheet.

If this is not what you want, please provide more details on what exactly you want to do.

HTH.

Best Regards,
Jim
Mark FranzProject Manager

Commented:
If th epage is in a table format, then probably... ar eyou creating the .asp out of dB data?  If so, then it's even easier...

Commented:
Hi skipper68,

If the web page contains a HTML table that you want to import into Excel, then this can be easily done  using the Excel Web query wizard.

For details see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office06012000.asp

HTH.

Best Regards,
Jim
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Commented:
Sorry Mapper, I confused your name with another user, skipper68.
are u looking for creating an excel from ASP and writing the data to teh excel fie thus created

Commented:
Dear Mapper,

Do you want to make an excel file? or what ? thing are confusing will you please make it clear?

Regards,
Shirjeel.

you can dump contents to a txt file or csv/tab delimited file using the scripting object.

I'm assuming you only want to save a table of data or the like in which case the filesystem object should do it no probs. if you are looking for a bit more control over formatting and look and feel, softartisians do a great add in called excelwriter, where you can command pretty much all excel functionality from asp.
Set the content type to:  
Response.contenttype = "text/csv"

On most windows machines this will cause Excel to open the page.  What excel does with it depends on the contents of your page.

You'll need include the:
response.buffer=true

at the beginning of the page

Cheers,
Dave

eg:

response.buffer=true
Response.contenttype = "text/csv"
[get recordset i presume]
response.write "<table>"
do while not rs.eof
  response.write "<tr><td>" & rs.fields("fieldname") _
                 & "</td></tr>"
  rs.movenext
loop
response.write "</table>"



Commented:
Hi chisholmd,

>>>
response.buffer=true
Response.contenttype = "text/csv"
<<<

This did NOT cause IE6 SP1 to open the page (File_list.asp) in Excel on my machine.  If fact, it prompted a security warning, asking me if I wanted to open the page or save the page.  When I chose open, it open the page in my ASP editor, not Excel.  I would imagine this is because the file extension is "asp".

Best Regards,
Jim
Mark FranzProject Manager

Commented:
mapper?  Comments?
Hey..

This isnt really an answer to your prob, but if it was me, Id create a dynamic csv file write with file system objects inside the web page and let the client open that instead of tryin to jack the webpage into excel..

Just an idea..

SC

Author

Commented:
I want to use an ASP page to write the contents of form data to an Excel spread sheet - can ASP do this?

Is that clearer?

Thanks,

Commented:
Is the Excel file located on the user's machine, or on the web server?
Mark FranzProject Manager

Commented:
Yes, her eis what I use to create a .xls file, it's pulling data from a dB but the principal is the same;

<%
'Create a random Filename based on the SessionID

fileExcel = theSessionID&".xls"
filePath = Server.mapPath("temp")
filename = filePath & "\" & fileExcel

Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set MyFile = fs.CreateTextFile(filename, 2, True)
strLineTitle = "" 'Initialize the variable for storing the fieldnames
strLineTitle = "Name" &chr(9) &_
"FirstName" &chr(9) &_
"Middle" &chr(9) &_
"LastName" &chr(9) &_
"Suffix" &chr(9) &_
"Notes"
     strLineTitle = strLineTitle & vbCrlf
         'Write this string into the file
          MyFile.writeline strLineTitle

         'Retrieve the values from the database and write into the Excel file
          Do while Not rs.EOF
str = rs.Fields("Name")&chr(9) &_
rs.Fields("FirstName")&chr(9) &_
rs.Fields("Middle")&chr(9) &_
rs.Fields("LastName")&chr(9) &_
rs.Fields("Suffix")&chr(9) &_
rs.Fields("Notes")&chr(9)
          strLine = str
          MyFile.writeline strLine
       rs.MoveNext
       Loop

       'Clean up
       MyFile.Close
       Set MyFile=Nothing
       Set fs=Nothing


' Now open the newly created .xls file
Response.CacheControl = "no-store"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
Response.ContentType = "application/vnd.ms-excel"
Response.Clear
Response.Redirect "temp/"&theSessionID&".xls"

%>
Sorry mapper in my haste I forgot to mention this line

Response.AddHeader "Content-Disposition", "attachment; filename=nDegreeResults.csv"

so:

Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment; filename=somefile.csv"

This should cause the browser to properily load Excel or more specifically whatever application the user has CSV associated with.  You could experiement with nameing it .xls and see how Excel handles it being a csv.

Sry for the confusion,
Dave

btw: I looked at my project where I used this and I had commented out the "response.buffer=true" I don't remember why. :) You could try it both ways and compare results

Dave

Commented:
mapper,
Has your question been answered?

Best Regards,
Jim
I have a similar question, i have to write an html page where the client inputs data into a form (textboxes/drop down menu) and the results are stored on a excel file.
any comments ?

Thanx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial