?
Solved

Manipulating Excel Spread Sheets through ASP

Posted on 2010-01-08
5
Medium Priority
?
400 Views
Last Modified: 2012-05-08
Hi all,

I have this code I will insert to this question, it generates me an excel spread sheet and in that spread sheet I need to be able to insert a sum total for columns E & H at the bottom for the total amount of money exchanged and hide column I.

Could someone please help me modify this code to accommodate these needs.
0
Comment
Question by:smyers051972
  • 3
5 Comments
 
LVL 12

Expert Comment

by:sdwalker
ID: 26210728
I don't see the code.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 26210847
For some reason the site wont allow me to attach the code, even though i paste it in, and click ATTACH, the code isnt there.  This started happening when the site was changed.

<%
'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
Connection.ConnectionTimeout = 0
Connection.CommandTimeout = 0
Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL, Connection
Response.ContentType = "application/vnd.ms-excel"

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records located.<br><br>")
Response.Write(Time)
Response.Write("<br><br>")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof  
Response.write "<tr>" & "</TD> "
Response.write "<TD ALIGN=left><B>" & showCell(Recordset("docgamingdate")) & "</B></TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("player_id")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("firstname")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("lastname")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("ForexAmount")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("units")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("Rate")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("USDamount")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("username")) & "</TD> "
Response.write "</tr>"
Recordset.MoveNext  
Loop
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
Session.Timeout=1
%>
0
 
LVL 12

Accepted Solution

by:
R_Harrison earned 2000 total points
ID: 26275157
Code below should sum columns E & H and hide colume I.

<%
'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
Connection.ConnectionTimeout = 0
Connection.CommandTimeout = 0 
Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL, Connection
Response.ContentType = "application/vnd.ms-excel"

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records located.<br><br>")
Response.Write(Time)
Response.Write("<br><br>")
Else
'if there are records then loop through the fields
Counter=0
Do While NOT Recordset.Eof  
Counter=Counter+1
Response.write "<tr>" & "</TD> "
Response.write "<TD ALIGN=left><B>" & showCell(Recordset("docgamingdate")) & "</B></TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("player_id")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("firstname")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("lastname")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("ForexAmount")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("units")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("Rate")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("USDamount")) & "</TD> "
Response.write "<TD width=0 ALIGN=left>" & showCell(Recordset("username")) & "</TD> "
Response.write "</tr>"
Recordset.MoveNext   
Loop
response.write "<tr>"
response.write "<TD ALIGN=left></td>"
response.write "<TD ALIGN=left></td>"
response.write "<TD ALIGN=left></td>"
response.write "<TD ALIGN=left></td>"
response.write "<TD ALIGN=left>=SUM(E1:E" & Counter & ")</td>"
response.write "<TD ALIGN=left></td>"
response.write "<TD ALIGN=left></td>"
response.write "<TD ALIGN=left>=SUM(H1:H" & Counter & ")</td>"
response.write "<TD width=0 ALIGN=left></td>"
response.write "</tr>"



End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
Session.Timeout=1
%>

Open in new window

0
 
LVL 1

Author Comment

by:smyers051972
ID: 26284034
thanks im going to test this out today or tomorrow and report back asap

Thank you again!
0
 
LVL 1

Author Comment

by:smyers051972
ID: 26293405
Looks like it worked well. Thanks for your help!
I got another one coming though will open a new one for that though!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

864 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