如何在使用ASP创建的电子表格中移动单元格? [英] How do I move cells in a spreadsheet created with ASP?

问题描述

我已经在ASP中编写了一个使用
的页面
Response.ContentType =应用程序/vnd.ms-excel"

我的问题是我可以从数据库表中填充工作表，并且可以在25列中填充5000多个行，但是完成循环后，我需要移动到工作表中的其他单元格.

因此，在填充到Y列和所有行之后，我想将光标移动到单元格AC并开始另一个循环，以填充单元格AC2中的行，直到本例中的表中记录的数量为5000.

什么是正确的Response.Write语法以移动到单元格，以便其活动填充我的下一个循环?

对此我将不胜感激，因为我一直在尝试各种不同的方法，但是却一无所获:)

非常感谢
Ray

I have writen a page in ASP that uses

Response.ContentType = "application/vnd.ms-excel"

My problem is I can get it to populate the sheet from my database table and it fills in over 5000 rows fine across 25 columns, however after finishing the loop i need to move to a different cell in the sheet.

So after filling in upto column Y and all the rows I want to move the cursor to cells AC and start another loop to fill in the rows from cell AC2 down to the amount of records in my table in this case 5000.

What is the correct Response.Write syntax to move to the cell so its active to fill in my next loop?

Any help would be very appreciated on this as I have been trying various different things and getting absolutely nowhere :)

Many Thanks
Ray

推荐答案

很难给出完整的答案而没有看到一些代码.

您是否尝试过ActiveCell.Offset(0,4)?

Hard to give a full answer without seeing some of the code.

Have you tried ActiveCell.Offset(0,4)?

嗨Dalek Dave

感谢您的回答，是的，我应该包括一些代码...

这是代码:因此，正如前面所提到的，当我完成所有列和行的填充后，我想保留3列为空白，并从第2行开始向下填充3列，这是在单元格中自动填充方程式.我尝试将其添加到内部循环中，但是当它到达记录集的末尾时它会炸毁，所以我需要知道如何添加相关代码以从我确定活动单元应该在的位置再次开始在单元中写入.

<！-#include virtual ="/Connections/Asda.asp"->
<％

Response.ContentType =应用程序/vnd.ms-excel"
昏暗Confirm_data
昏暗Confirm_data_cmd
昏暗Confirm_data_numRows
设置Confirm_data_cmd = Server.CreateObject("ADODB.Command")
Confirm_data_cmd.ActiveConnection = MM_Asda_STRING
Confirm_data_cmd.CommandText ="SELECT dbo.Tbl_Down_Fleet.Inp_ID，Site_Name，dbo.Tbl_Down_Fleet.Day_No，dbo.Tbl_Down_Fleet.Inp_Date，dbo.Tbl_Down_Fleet.Reach_Total，dbo.Tbl_et_F_Fet_Fle_Fet_Fle_Fet_Fle_Fet_Fet_Fle_Fet_Fet_Fet_Fleet_Tal_Tle_Fleet dbo.Tbl_Down_Fleet.ECB_Total，dbo.Tbl_Down_Fleet.BDU_Total，dbo.Tbl_Down_Fleet.PPPT_Total，dbo.Tbl_Down_Fleet.PPPTBC_Total，dbo.Tbl_Down_Fleet.Dollop_Total，dbo.Tbl_Down_Fleet.Kombi_Total，dbo.Tbl_Down_Fleet.Beast_Total，dbo.Tbl_Down_Fleet.Day_No，DBO. Tbl_Down_Fleet.Inp_Date，dbo.Tbl_Down_Fleet.Reach，dbo.Tbl_Down_Fleet.LLOP，dbo.Tbl_Down_Fleet.PPT，dbo.Tbl_Down_Fleet.CB，dbo.Tbl_Down_Fleet. PPPTBC，dbo.Tbl_Down_Fleet.Dollop，dbo.Tbl_Down_Fleet.Kombi，dbo.Tbl_Down_Fleet.Beast FROM dbo.Tbl_Down_Fleet，dbo.Tbl_Primary_Fleet WHERE Fleet_ID = Priflt_ID SC ORDER网站 Confirm_data_cmd.Prepared = true
Confirm_data_cmd.Parameters.Append Confirm_data_cmd.CreateParameter("param1"，5，1，-1，Confirm_data__MMColParam)''adDouble
设置RS = Confirm_data_cmd.Execute

％>
< TABLE BORDER = 1>
< TR>
<％
''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
''％遍历字段名称并打印出字段名称
''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
j = 2''行计数器
对于i = 0到15-1
％>
< TD>< B>％= RS(i).名称％</B</TD>
<％下一个％>
< TD>< B><％Response.Write("Total Fleet")％</B</TD>

<％
''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
''％通过剩余的标题再次循环
''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
对于i = 15到RS.Fields.Count -1
％>
< TD>< B>％= RS(i).名称％</B</TD>
<％下一个％>
< TD> B<％Response.Write("Sum")％</B</TD>
< TD>< B><％Response.Write("Reach")％</B</TD>
< TD>< B><％Response.Write("LLOP")％</B</TD>
< TD>< B><％Response.Write("PPT")％</B</TD>
< TD> B<％Response.Write("CB")％</B</TD>
< TD>< B><％Response.Write("ECB")％</B</TD>
< TD> B<％Response.Write("BDU")％</B</TD>
< TD>< B><％Response.Write("PPPT")％</B</TD>
< TD>< B><％Response.Write("PPPTBC")％</B</TD>
< TD>< B><％Response.Write("Dollop")％</B</TD>
< TD>< B><％Response.Write("Kombi")％</B</TD>
< TD>< B><％Response.Write("Beast")％</B</TD>
< TD>< B><％Response.Write("dfleet")％</B</TD>


</TR>
<％
''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
''％遍历行，创建第一组Fleet Totals
''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
不使用RS.EOF时要做的事
％>
< TR>
<％对于i = 0到15-1％>
< TD VALIGN = TOP><％= RS(i)％</TD>

<％下一个％>
< TD VALIGN = TOP><％Response.Write("= SUM(E"&(j)&:O"&(j)&;)")％</TD;

<％
''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
''％遍历行，创建第二组Fleet Down Totals
''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
％>

<％对于i = 15到RS.Fields.Count -1％>
< TD VALIGN = TOP><％= RS(i)％</TD>

<％下一个％>
< TD VALIGN = TOP><％Response.Write("= SUM(S"&(j)&:AC"&(j)&;)")％</TD;
< TD VALIGN = TOP><％Response.Write("=(E"&(j)&-S"&(j)&)/E"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(F"&(j)&-T"&(j)&)/F"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(G"&(j)&-U"&(j)&)/G"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(H"&(j)&-V"&(j)&)/H"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(I"&(j)&-W"&(j)&)/I"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(J"&(j)&-X"&(j)&;)/J"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(K"&(j)&-Y"&(j)&)/K"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(L"&(j)&-Z"&(j)&)/L"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(M"&(j)&-AA"&(j)&)/M"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(N"&(j)&-AB"&(j)&)/N"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(O"&(j)&-AC"&(j)&)/O"&(j))％> ;</TD>
< TD VALIGN = TOP><％Response.Write("=(P"&(j)&-AD"&(j)&)/P"&(j))％> ;</TD>



</TR>
<％
RS.MoveNext
j = j + 1
循环


''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
''％确保关闭结果集和连接对象
''%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
RS.Close

％>

Hi Dalek Dave

Thank you for answering and yes I should included some code...

This is the code: So as mentioned when its finished running through filling in all the columns and rows I want to then leave 3 columns blank and start filling 3 columns from row 2 down, this is to auto fill the equations in the cells. i tried adding it to the inner loop but it bombs out when it hits the end of the recordset so i need to know how to add the relevant code to start writting in the cells again from where I decide the active cell should be.

<!--#include virtual="/Connections/Asda.asp" -->
<%

Response.ContentType = "application/vnd.ms-excel"
Dim Confirm_data
Dim Confirm_data_cmd
Dim Confirm_data_numRows
Set Confirm_data_cmd = Server.CreateObject ("ADODB.Command")
Confirm_data_cmd.ActiveConnection = MM_Asda_STRING
Confirm_data_cmd.CommandText = "SELECT dbo.Tbl_Down_Fleet.Inp_ID,Site_Name,dbo.Tbl_Down_Fleet.Day_No,dbo.Tbl_Down_Fleet.Inp_Date, dbo.Tbl_Down_Fleet.Reach_Total, dbo.Tbl_Down_Fleet.LLOP_Total, dbo.Tbl_Down_Fleet.PPT_Total, dbo.Tbl_Down_Fleet.CB_Total, dbo.Tbl_Down_Fleet.ECB_Total, dbo.Tbl_Down_Fleet.BDU_Total, dbo.Tbl_Down_Fleet.PPPT_Total, dbo.Tbl_Down_Fleet.PPPTBC_Total, dbo.Tbl_Down_Fleet.Dollop_Total, dbo.Tbl_Down_Fleet.Kombi_Total, dbo.Tbl_Down_Fleet.Beast_Total,dbo.Tbl_Down_Fleet.Day_No,dbo.Tbl_Down_Fleet.Inp_Date, dbo.Tbl_Down_Fleet.Reach, dbo.Tbl_Down_Fleet.LLOP, dbo.Tbl_Down_Fleet.PPT, dbo.Tbl_Down_Fleet.CB, dbo.Tbl_Down_Fleet.ECB, dbo.Tbl_Down_Fleet.BDU, dbo.Tbl_Down_Fleet.PPPT, dbo.Tbl_Down_Fleet.PPPTBC, dbo.Tbl_Down_Fleet.Dollop, dbo.Tbl_Down_Fleet.Kombi, dbo.Tbl_Down_Fleet.Beast FROM dbo.Tbl_Down_Fleet, dbo.Tbl_Primary_Fleet WHERE Fleet_ID =Priflt_ID ORDER BY Site_Name ASC"
Confirm_data_cmd.Prepared = true
Confirm_data_cmd.Parameters.Append Confirm_data_cmd.CreateParameter("param1", 5, 1, -1, Confirm_data__MMColParam) '' adDouble
Set RS = Confirm_data_cmd.Execute

%>
<TABLE BORDER=1>
<TR>
<%
'' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'' % Loop through Fields Names and print out the Field Names
'' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
j = 2 ''row counter
For i = 0 to 15 - 1
%>
<TD><B><% = RS(i).Name %></B></TD>
<% Next %>
<TD><B><% Response.Write("Total Fleet")%></B></TD>

<%
'' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'' % Loop again through remaining Headers
'' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
For i = 15 to RS.Fields.Count -1
%>
<TD><B><% = RS(i).Name %></B></TD>
<% Next %>
<TD><B><% Response.Write("Sum")%></B></TD>
<TD><B><% Response.Write("Reach")%></B></TD>
<TD><B><% Response.Write("LLOP")%></B></TD>
<TD><B><% Response.Write("PPT")%></B></TD>
<TD><B><% Response.Write("CB")%></B></TD>
<TD><B><% Response.Write("ECB")%></B></TD>
<TD><B><% Response.Write("BDU")%></B></TD>
<TD><B><% Response.Write("PPPT")%></B></TD>
<TD><B><% Response.Write("PPPTBC")%></B></TD>
<TD><B><% Response.Write("Dollop")%></B></TD>
<TD><B><% Response.Write("Kombi")%></B></TD>
<TD><B><% Response.Write("Beast")%></B></TD>
<TD><B><% Response.Write("dfleet")%></B></TD>


</TR>
<%
'' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'' % Loop through rows, create first set of Fleet Totals
'' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Do While Not RS.EOF
%>
<TR>
<% For i = 0 to 15 - 1 %>
<TD VALIGN=TOP><% = RS(i) %></TD>

<% Next %>
<TD VALIGN=TOP><% Response.Write("=SUM(E"&(j)&":O"&(j)&")") %></TD>

<%
'' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'' % Loop through rows, create second set of Fleet Down Totals
'' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%>

<% For i = 15 to RS.Fields.Count -1 %>
<TD VALIGN=TOP><% = RS(i) %></TD>

<% Next %>
<TD VALIGN=TOP><% Response.Write("=SUM(S"&(j)&":AC"&(j)&")") %></TD>
<TD VALIGN=TOP><% Response.Write("=(E"&(j)&"-S"&(j)&")/E"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(F"&(j)&"-T"&(j)&")/F"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(G"&(j)&"-U"&(j)&")/G"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(H"&(j)&"-V"&(j)&")/H"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(I"&(j)&"-W"&(j)&")/I"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(J"&(j)&"-X"&(j)&")/J"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(K"&(j)&"-Y"&(j)&")/K"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(L"&(j)&"-Z"&(j)&")/L"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(M"&(j)&"-AA"&(j)&")/M"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(N"&(j)&"-AB"&(j)&")/N"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(O"&(j)&"-AC"&(j)&")/O"&(j)) %></TD>
<TD VALIGN=TOP><% Response.Write("=(P"&(j)&"-AD"&(j)&")/P"&(j)) %></TD>



</TR>
<%
RS.MoveNext
j = j + 1
Loop


'' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'' % Make sure to close the Result Set and the Connection object
'' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
RS.Close

%>

这篇关于如何在使用ASP创建的电子表格中移动单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！