#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    7
    Rep Power
    0

    Creating An Excel Spreadsheet From Web Form Data


    I want 2 create a survey Web Form that people can fill in. I then want the data entered to be saved to an Excel spreadsheet located on a server.

    Can .ASP handle this kind of task? and if so does anyone know the code required to perform this function?

    The data entered will be RAW and the .ASP (or whatever required programming) will need 2 process and convert the data into the spreadsheet.

    Also, would it be possible to run this from a stand alone machine so that the web form, .ASP and spreadsheet are all contained on the machine and have no outside influences?

    I am new to using .ASP and server side processing so basic steps and terminology would be appreciated!!!

    Cheers
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    7
    Rep Power
    0
    I know no-one has yet replied to my post but I have been investigating myself since the post and I have a very simple question.

    If I am inputting into my code <form action="formhandler.asp"> does the document I am putting the code in have to be called formhandler.asp or does it have to be saved as an HTML file and a seperate .ASP file be created?

    Suppose what I am trying to say is do I need one file with an .ASP extension with all HTML and ASP coding within it or one for the HTML and one for the .ASP???

    Also. is there an easy way to test if the pages are working without posting to a website? I am using Windows XP Professional if that is any help!!!

    Cheers
  4. #3
  5. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    1,774
    Rep Power
    24
    You only need one page: formhandler.asp

    if you're really clever, you can put the form on the same page as the formhandler, and post the form to itself.

    Now, automating Excel. Here is an example using JScript, you could probably convert it to ASP (cos it seems to be the same syntax):

    Code:
    <SCRIPT LANGUAGE="JScript"> 
    function AutomateExcel()
    {
    
    	// Start Excel and get Application object.
    	var oXL = new ActiveXObject("Excel.Application");
           
    	oXL.Visible = true;
          
    	// Get a new workbook.
    	var oWB = oXL.Workbooks.Add();
    	var oSheet = oWB.ActiveSheet;
             
    	// Add table headers going cell by cell.
    	oSheet.Cells(1, 1).Value = "Scientist";
    	oSheet.Cells(1, 2).Value = "<% response.write(name) %>";
    	oSheet.Cells(2, 1).Value = "Month Beginning";
    	oSheet.Cells(2, 2).Value = "<% response.write(monthago + 1) %>";
    	oSheet.Range("A1", "A2").Font.Bold = true;
    
    	oSheet.Cells(4, 1).Value = "Date";
    	oSheet.Cells(4, 2).Value = "Start";
    	oSheet.Cells(4, 3).Value = "End";
    	oSheet.Cells(4, 5).Value = "Time Spent";
    	oSheet.Range("A4", "E4").Font.Bold = true;
    
    	<%
    	row = 5
    	while not rs2.eof
    		thisend = datediff("d",end_time, rs2("day_date"))
    		thisstart = datediff("d",start_time,rs2("day_date"))
    		if thisstart > -1 and thisend < 1 then 
    			if rs2("time_start") <> "" and rs2("time_end") <> "" then
    				daystart = FormatDateTime(hour(rs2("time_start")) & ":" & minute(rs2("time_start")),4)
    				dayend = FormatDateTime(hour(rs2("time_end")) & ":" & minute(rs2("time_end")),4)
    				mins = datediff("n",daystart,dayend)
    				mins = mins - minute(formatdatetime(rs2("lunch"),vbshorttime)) - hour(formatdatetime(rs2("lunch"),vbshorttime)) * 60
    			end if
    			hrs = Cint(mins / 60)
    			if hrs > mins / 60 then hrs = hrs - 1
    			mins = mins mod 60
    			if mins < 10 then mins = "0" & mins
    			response.write("oSheet.Cells(" & row & ", 1).Value = '" & rs2("day_date") & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 2).Value = '" & rs2("time_start") & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 3).Value = '" & rs2("time_end") & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 4).Value = '" & formatdatetime(rs2("lunch"),vbshorttime) & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 5).Value = '" & formatdatetime(hrs & ":" & mins,vbshorttime) & "'" & vbcrlf)
    			row = row + 1
    		end if
    		rs2.movenext
    	wend
    	row = row + 1
    
    	if not rs4.eof then
    	%>
    	oSheet.Cells(<%response.write(row) %>, 1).Value = "Date";
    	oSheet.Cells(<%response.write(row) %>, 2).Value = "Task Name";
    	oSheet.Cells(<%response.write(row) %>, 3).Value = "Customer";
    	oSheet.Cells(<%response.write(row) %>, 4).Value = "Status";
    	oSheet.Cells(<%response.write(row) %>, 5).Value = "Product";
    	oSheet.Cells(<%response.write(row) %>, 6).Value = "Project";
    	oSheet.Cells(<%response.write(row) %>, 7).Value = "Time Spent";
    	oSheet.Range("A<%response.write(row) %>", "G<%response.write(row) %>").Font.Bold = true;
    	<%
    	row = row + 1
    	while not rs4.eof
    		thisend = datediff("d",end_time, rs4("day_date"))
    		thisstart = datediff("d",start_time,rs4("day_date"))
    		if thisstart > -1 and thisend < 1 then 
    			response.write("oSheet.Cells(" & row & ", 1).Value = '" & rs4("day_date") & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 2).Value = '" & rs4("task_type") & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 3).Value = '" & rs4("customer") & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 4).Value = '" & rs4("status") & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 5).Value = '" & rs4("product") & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 6).Value = '" & rs4("project") & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 7).Value = '" & rs4("used") & "'" & vbcrlf)
    			row = row + 1
    		end if
    		rs4.movenext
    	wend
    	rs4.movefirst
    	end if
    	row = row + 1
    	%>
    	oSheet.Cells(<%response.write(row) %>, 1).Value = "Product";
    	oSheet.Cells(<%response.write(row) %>, 2).Value = "Time Spent";
    	oSheet.Range("A<%response.write(row) %>", "B<%response.write(row) %>").Font.Bold = true;
    	<%
    	row = row + 1
    	for x = 0 to ubound(analysAry) - 1
    		if not analysAry(x, 0) = "" then
    			hr = Cint(analysAry(x, 1) / 60)
    			if hr > analysAry(x, 1) / 60 then hr = hr - 1
    			min = analysAry(x, 1) mod 60
    			response.write("oSheet.Cells(" & row & ", 1).Value = '" & analysAry(x, 0) & "'" & vbcrlf)
    			response.write("oSheet.Cells(" & row & ", 2).Value = '" & hr & ":" & min & "'" & vbcrlf)
    			row = row + 1
    		end if
    	next
    	response.write("oRng = oSheet.Range(""A1"", ""H" & row & """);")
    	%>
    	oRng.EntireColumn.AutoFit();
             
    }
    </SCRIPT>
    Also, you can run code locally by setting up IIS on your own machine, and putting ASP pages in the inetpub/wwwroot directory.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    7
    Rep Power
    0
    Thanxz 4 the help. It does seem 2 be getting further than my previous attempts but still no Excel file is being generated. Which part of the code denotes the location of where the Excel file is saved to?

    Also, Internet Explorer says there is an error in the syntax of the code. Any ideas where this error may b located in the code given above?

    Cheers
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    7
    Rep Power
    0

    Exclamation


    I have done some more investigating on the Intranet capabilities of the site I am meant to be hosting the survey on and it now appears they do not support ASP. Can what I am attempting to achieve be completed using any of the following standards and if so where are the best places to find information out about this?

    PERL
    JSP
    Cold Fusion
    CGI

    Any help much appreciated as always as time is now against me :-)
  10. #6
  11. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    1,774
    Rep Power
    24
    The code is jscript, but should have transferred to ASP.

    This shows how Excel files can be created from PHP:

    http://www.stargeek.com/scripts.php?script=2&cat=sql

    the include bit at the start is linking to a file with the connection to a mysql database, you may have to look around for a few hints on changing this, though

    http://php.net

    can help a lot, great manual.

    Good luck!

IMN logo majestic logo threadwatch logo seochat tools logo