how to sum the cells in excel using asp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

how to sum the cells in excel using asp

Author

Topic

ajithevn
Starting Member

3 Posts

Posted - 2005-06-28 : 07:25:17

Hi

i need to sum the values in different cells of an excel sheet which im getting from data base and which is in a loop and the number of entries depends on the user.Please help me out

im adding the code please check it out:



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



<html>

<body>

<%

dim objConn
set objConn=server.CreateObject("ADODB.Connection")
objConn.open "provider=SQLOLEDB;data source=10.100.17.107;initial catalog=RUT;user id=test;password=test;"

iMonth = Request.QueryString ("month")
iyear = Request.QueryString ("year")
intpid = Request.QueryString ("project")
inteid = Request.QueryString ("eid")



dim fDate
DIM fmonth
Dim fi

if iMonth = "February" and iYear mod 4 <> 0 then 
iTo = 28 
elseif iMonth = "February" and iYear mod 4 = 0 then 
iTo = 29
end if

if (iMonth = "April" or iMonth = "June" or iMonth = "September" or iMonth = "November") then iTo = 30 end if

if (iMonth = "January" or iMonth = "March" or iMonth = "May" or iMonth = "July" or iMonth = "August" or iMonth = "October" or iMonth = "December") then iTo = 31 end if

dim rsobj1,rsobj2,rsobj5,rsobj9

sql1 = "select first_name from tblUserMaster where emp_id='"&inteid&"'" 
set rsobj1 = objConn.Execute (sql1)

sql2 = "select distinct activity_id from tblTimeSheet where month='"&iMonth&"' and year='"&iyear&"' and project_id='"&intpid&"' and emp_id='"&inteid&"' and bill_non = ''"
set rsobj2 = objConn.Execute (sql2)
%>


<b>Employee Name:<%=rsobj1("first_name")%></b> 


<TABLE BORDER=1>
<TR>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></td>
<TD ><%=imonth%></TD>
<TD ></td>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>

<TD ><%=imonth%>

</TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ><%=imonth%></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ><%=imonth%></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>
<TD ></TD>

</TR>

<TR bgcolor="#ffffff" style="FONT-FAMILY: fantasy; BACKGROUND-COLOR: powderblue">

<td ><b>Client/Assignment</b> </td>
<td ><b>ProjectName</b> </td>
<td ><b>Remarks</b></td> 
<td><b>Total Hrs</b></td>

<%for i = 1 to iTo



fmonth=imonth
fi =i

fDate = fi & "/" & fMonth & "/" & iYear




Response.Write "<td align=center><b>" & i & "</p>" & weekdayname(weekday(fdate)) & "</b></td>"


next%>
</TR>


<%'===============================================d efault activity============================%> 

<% do until rsobj2.EOF%>
<tr>
<%
dim intactid,rsobj3
intactid = rsobj2("activity_id")




sql3 = "select activity_name from tblActivityMaster where activity_id = '"&intactid&"'"
set rsobj3 = objConn.Execute (sql3)

sql4 = "select no_of_hrs,remarks,date from tblTimeSheet where month='"&iMonth&"' and year='"&iyear&"' and project_id='"&intpid&"' and emp_id='"&inteid&"' and activity_id='"&intactid&"'"
set rsobj4 = objConn.Execute (sql4)

%>

<td><%=rsobj3("activity_name")%></td>
<td></td>
<td><%=rsobj4("remarks")%></td>
<td></td>
<%do until rsobj4.eof%>

<%if (rsobj4("no_of_hrs") < 8 ) then%>
<td><%=rsobj4("no_of_hrs")%></td>// I need to add the values i get in this td and display in the td with red colour and the number of values may change according to the user
<%else%>
<td>8</td>
<%end if%> 



<% 
rsobj4.movenext
loop
%>



</tr>
<%
rsobj2.movenext
loop

%>

Thanks

ajith

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-28 : 09:42:59

You have to use loop to add the values to the variable
If you want to do this in query, use sum function in the query
Look in BOL for more information

Madhivanan

Failing to plan is Planning to fail

ajithevn
Starting Member

3 Posts

Posted - 2005-06-28 : 10:05:28

What is BOL? can u be bit more clear please

ajith

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-28 : 10:15:39

Books On Line - SQL Server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources