Hi All,
To read from the second sheet of an excel file, do the following
Create a "Sample.xls" file. In sheet2, select the range of cells which you wish to populate with data, and then do the following
1. From the menu bar, select Insert-> Name-> Define Name
For eg I have named it as data. then input the data into these columns and rows, then to retrieve data from sheet2 of Sample.xls, please find the code below:
-
<%@ Language=VBScript %>
-
<HTML>
-
<HEAD>
-
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
-
</HEAD>
-
<BODY>
-
<%
-
-
dim adoCon,strCon
-
Set adoCon = Server.CreateObject("ADODB.Connection")
-
strcon ="DBQ=" & Server.MapPath("sample.xls") & ";" & _
-
"DRIVER={Microsoft Excel Driver (*.xls)};"
-
-
adoCon.Open strCon
-
%>
-
<%
-
'on error resume next
-
dim rsCustomQuery
-
dim rsColumnSchema
-
dim rsTableSChema
-
dim strSQl
-
dim i
-
Const adSchemaTables = 20
-
const adSchemaColumns = 4
-
%>
-
<html>
-
<head>
-
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
-
<%
-
set rsColumnSchema = adocon.OpenSchema(adSchemaColumns)
-
set rsTableSChema = adocon.OpenSchema(adSchemaTables)
-
-
'Intialise the ADO recordset object
-
Set rsCustomQuery = Server.CreateObject("ADODB.Recordset")
-
'Initialise the SQL variable with an SQL statement to get the configuration details from the database
-
strSQL = "select * from data;"
-
'Query the database
-
rsCustomQuery.Open strSQL, strCon, 1, 3
-
%>
-
<p align="center">Your sql statement as executed is: <b><%=strSQL%></b></p>
-
<div>
-
<table border="1" cellspacing="0">
-
<tr>
-
<% For i = 0 to rsCustomQuery.Fields.Count - 1 %>
-
<TD><B><% = rsCustomQuery(i).Name %></B></TD>
-
<% Next %>
-
</tr>
-
<%
-
Do While Not rsCustomQuery.EOF
-
%>
-
<tr>
-
<% For i = 0 to rsCustomQuery.Fields.Count - 1 %>
-
<TD VALIGN=TOP><% = rsCustomQuery(i) %></TD>
-
<% Next %>
-
</tr>
-
<%
-
rsCustomQuery.MoveNext
-
Loop
-
%>
-
</table>
-
</div>
-
<%=request.Form("sel") %>
-
-
</BODY>
-
</HTML>
-
Please let me know if anybody has tried to insert data into the Second sheet of the excel file. Could you help me with the same?
I need to extract data from Sql server and populate a particular column in the excel file's sheet2.
If any body has already come up with a solution, in ASP, please share it with me.
Thanks n Regards
Milan
3
