Solved

Save  asp data to excel

Posted on 2001-07-27
21
536 Views
Last Modified: 2007-11-27
Hi experts,
can I save the info  from one of my asp page to an excel file( on my hard disk ) in certain cells( let me say cell D30)? how?
thanks
Suying
0
Comment
Question by:lyu97
  • 8
  • 6
  • 4
  • +1
21 Comments
 

Expert Comment

by:jsolo
Comment Utility
lyu97,

This code will take an entire table or query and place it into an Excel file.


<%response.ContentType="application/vnd.ms-excel"%>


<%
               accessdb="Unix_Hosts.mdb"
               myDSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
               myDSN = myDSN & "DATA SOURCE=" & server.mappath(accessDB) & ";"

               SQL = request.querystring("SQL")
               Set conn = Server.CreateObject("ADODB.Connection")
               conn.open myDSN
               set rs=server.createobject("adodb.recordset")
               rs.open SQL,conn,3,3
               totalfields=rs.fields.count

               rowstart="<tr>"
               rowend="</tr>"
               cellstart="<td>"
               cellend="</td>"
               
               response.write "<table border=1>" & rowstart
               For each item in rs.fields
                   response.write cellstart & "<b><font = 'Arial'>" & item.name & "</font></b>" & cellend
               Next
               If not rs.eof then
                   arrAllData=rs.getrows
                   totalrecs=rs.recordcount
               End If    
               

               rs.close
               set rs=nothing
               conn.close
               set conn=nothing
               response.write rowend
               If totalrecs=0 then
                   response.write rowstart & "<td colspan=" & totalfields & ">No data was returned" & cellend & rowend
               Else
                   For rowcount=0 to totalrecs-1
                       response.write rowstart
                       For colcount=0 to totalfields-1
                           response.write cellstart & arrAllData(colcount,rowcount) & cellend
                       Next
                       response.write rowend
                   Next
               End If
               response.write "</table>"
               

%>


Hope this helps!
0
 

Expert Comment

by:jsolo
Comment Utility
lyu97,

This code will take an entire table or query and place it into an Excel file.


<%response.ContentType="application/vnd.ms-excel"%>


<%
               accessdb="Unix_Hosts.mdb"
               myDSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
               myDSN = myDSN & "DATA SOURCE=" & server.mappath(accessDB) & ";"

               SQL = request.querystring("SQL")
               Set conn = Server.CreateObject("ADODB.Connection")
               conn.open myDSN
               set rs=server.createobject("adodb.recordset")
               rs.open SQL,conn,3,3
               totalfields=rs.fields.count

               rowstart="<tr>"
               rowend="</tr>"
               cellstart="<td>"
               cellend="</td>"
               
               response.write "<table border=1>" & rowstart
               For each item in rs.fields
                   response.write cellstart & "<b><font = 'Arial'>" & item.name & "</font></b>" & cellend
               Next
               If not rs.eof then
                   arrAllData=rs.getrows
                   totalrecs=rs.recordcount
               End If    
               

               rs.close
               set rs=nothing
               conn.close
               set conn=nothing
               response.write rowend
               If totalrecs=0 then
                   response.write rowstart & "<td colspan=" & totalfields & ">No data was returned" & cellend & rowend
               Else
                   For rowcount=0 to totalrecs-1
                       response.write rowstart
                       For colcount=0 to totalfields-1
                           response.write cellstart & arrAllData(colcount,rowcount) & cellend
                       Next
                       response.write rowend
                   Next
               End If
               response.write "</table>"
               

%>


Hope this helps!
0
 

Expert Comment

by:jsolo
Comment Utility
oops, sorry about that double post!

-jsolo
0
 

Author Comment

by:lyu97
Comment Utility
Hi jsolo,
I just want to grib some info from one asp page and save it to excel file. not the table or query something from database.let me say: there are three text box on asp page, when people click submit button, I want to save the data in that three text boxes to an excel file.
thanks
Suying
0
 
LVL 18

Expert Comment

by:mgfranz
Comment Utility
You want to save it to a specific .xls file?  If so, you can use the FSO to open and write to the file.

<%
fileExcel = "theFile.xls"
filePath = Server.mapPath("dirOfFile")
filename = filePath & "\" & fileExcel

Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set MyFile = fs.CreateTextFile(filename, 2, True)
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)
strLine = str
          MyFile.writeline strLine
       rs.MoveNext
       Loop

       'Clean up
       MyFile.Close
       Set MyFile=Nothing
       Set fs=Nothing
0
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
Dim Excel, oSheet, oRng,oBook

Set Excel = CreateObject("Excel.Application")
 
Excel.Workbooks.Add
Set oSheet = Excel.ActiveSheet
Set oBook = Excel.ActiveWorkbook

With oBook
   .Worksheets(1).Cells(1, 1) = "Hello World"
End With

Set oRng = oSheet.Range("A1", "Z1")
oRng.EntireColumn.AutoFit
Excel.ActiveWorkbook.RefreshAll
 
Excel.ActiveWorkbook.SaveAs "c:\test.xls", FileFormat:=xlNormal
Excel.Quit
0
 

Author Comment

by:lyu97
Comment Utility
Hi mgfranz,
what if the excel file is on the client machine?
actually the excel file is saved in Oracle database. the user can download it to their hard disk, do some change and save back to the database. I just want to put some data in certain cells before he/she save back the file.
the code you gave to me seems like save the info to excel file(I haven't try it, maybe I'm wrong), but I want to save the info to specific cells in that excel file(there are list of people name in that excel file, after I got the people's name from my asp page, I want to save that  name to excel file in his/her spot)
thanks
Suying
0
 
LVL 18

Expert Comment

by:mgfranz
Comment Utility
Look at Dave Greens code, it looks good.
0
 

Author Comment

by:lyu97
Comment Utility
Hi Dave Greene,
when I ran the code you give me, I got error:
Microsoft VBScript compilation error '800a0400'

Expected statement

/TestAR/Savesignature.asp, line 175

Excel.ActiveWorkbook.SaveAs "D:\Misc\test.xls", FileFormat:=xlNormal

(which is posted on = sign)
I'm new to use excel file in asp, could you tell me what to change from you code since I have an excel file aready.(what to do with:
Set Excel = CreateObject("Excel.Application")

Excel.Workbooks.Add
you gave )

Thank you so much.
Suying
0
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
May need to be in Dbl Quotes in ASP

Dim Excel, oSheet, oRng,oBook

Set Excel = CreateObject("Excel.Application")

Excel.Workbooks.Open "C:\your.xls"

Set oSheet = Excel.ActiveSheet
Set oBook = Excel.ActiveWorkbook

With oBook
  .Worksheets(1).Cells(1, 1) = "Hello World"
End With

Set oRng = oSheet.Range("A1", "Z1")
oRng.EntireColumn.AutoFit
Excel.ActiveWorkbook.RefreshAll

Excel.ActiveWorkbook.SaveAs "c:\Your.xls", "FileFormat:=xlNormal"
Excel.Quit


If this line gives you problems...
Excel.ActiveWorkbook.SaveAs "D:\your.xls", "FileFormat:=xlNormal"

Change TO

Excel.ActiveWorkbook.SaveAs "D:\your.xls"
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:lyu97
Comment Utility
Hi Dave Greene,
I just got on this web site(I've tried too many time)and pasted your code to my asp and just change the code:
With oBook
 .Worksheets(1).Cells(1, 1) = "Hello World"
End With
 to:
With oBook
 .Worksheets(3).Cells(B43) = "Hello World"
End With
and got the error:
Microsoft VBScript runtime error '800a03ec'

Unknown runtime error

/TestAR/Savesignature.asp, line 171
which is:
.Worksheets(3).Cells(B43) = "Hello World"

any clue?
thanks
suying
0
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
So you are trying to go to the third page and print some text?

Try this...
.Worksheets(3).Cells(2, 43) = "Hello World"


I believe you can also reference the worksheet name like so
.Worksheets("Sheet3").Cells(2, 43) = "Hello World"

0
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
Note: If your workbook doesn't have a sheet3 you will get an error...
0
 

Author Comment

by:lyu97
Comment Utility
Hi Dave Greene,
another thing want to ask is:why you use the Saveas in the line:Excel.ActiveWorkbook.SaveAs "D:\your.xls"
can I save back to the original file?
thanks
Suying
0
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
Yes... you can just use

Excel.SaveWorkspace "c:\test\test.xls"
0
 

Author Comment

by:lyu97
Comment Utility
Hi Dave Greene,
I tried the code several times(died in middle,so I shout down serveral time )and is kind of slow and I haven't get result yet. maybe I will give you the answer Monday(if it work I will close this question).
here I want to ask you another question(if possible I will post it on EE, so you can answer me from there, not in this question, I can't do this).
can I add a button in one of this Excel file sheet, after user finish change infomation,he/she can click this button to upload this Excel file to the server?
actually I have all the code in asp page to do this, but my boss doesn't like this to let the user first save this file to her/his disk and then click the browser in  my uploadform.htm file and click upload....he said I can create some Macros and call my asp page to upload file to the server and save it to database, I don't know how. if you think this is possible, just add a comment for this question, so I ask this question on EE on Monday.
(I think there is something wrong, my program still run about 6 min)
thanks.

Suying
0
 

Author Comment

by:lyu97
Comment Utility
Hi Dave Greene,
here is the code in asp page:
<%
Dim excel, oSheet, oRng,oBook
Set excel =server.CreateObject("Excel.Application")

excel.Workbooks.Open"C:\Book1.xls"
Set oSheet = excel.ActiveSheet
Set oBook =excel.ActiveWorkbook
 oBook.Worksheets(1).Cells(6, 1) = "Hello World"
 Set oRng = oSheet.Range("A1","Z1")
 oRng.EntireColumn.AutoFit
 excel.ActiveWorkbook.RefreshAll
excel.SaveWorkspace "C:\Book1.xls"
excel.Quit
Response.Write "thank you"
%>
 when I ran get the error:
error 'ASP 0113'
Script timed out...
I think I can't put the above code in asp because the excel file is on client's machine, not on the server.maybe I need write a function...
any idea.

thanks
suying

0
 
LVL 8

Accepted Solution

by:
Dave_Greene earned 30 total points
Comment Utility
<<I think I can't put the above code in asp because the excel file is on client's machine, not on the
server.maybe I need write a function...
any idea.>>

If the file is on the client machine, you will need to upload it to the server before you can perform processing against it.
0
 
LVL 18

Expert Comment

by:mgfranz
Comment Utility
Or run the script client-side... IE only and Excel must be loaded on the client.

<html>
<head>
<script language="vbscript">

Function WriteExcel() {

Dim excel, oSheet, oRng,oBook
Set excel =server.CreateObject("Excel.Application")

excel.Workbooks.Open"C:\Book1.xls"
Set oSheet = excel.ActiveSheet
Set oBook =excel.ActiveWorkbook
oBook.Worksheets(1).Cells(6, 1) = "Hello World"
Set oRng = oSheet.Range("A1","Z1")
oRng.EntireColumn.AutoFit
excel.ActiveWorkbook.RefreshAll
excel.SaveWorkspace "C:\Book1.xls"
excel.Quit
Response.Write "thank you"
}
</script>
<body onLoad="WriteExcel()">
...

UNTESTED
0
 

Author Comment

by:lyu97
Comment Utility
hi mfgranz,
if I run the script client-side, I can't use the code:
Set excel =server.CreateObject("Excel.Application")
what I should change with this line of code?
thanks
suying
0
 
LVL 18

Expert Comment

by:mgfranz
Comment Utility
Sorry... bad info, belay my last.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now