Da SQL Server a formato excel (xls)

**orcim** · 08-03-2005, 16:27

Ciao. Con questo script scrivo un file excel sul server con i dati provenienti da un dbase SQL Server:

codice:

sub listaClienti(objConn,byRef listaClientiOutput)
	sql = "SELECT * FROM STATO_AVANZAMENTO_LAVORI WHERE ID = "& strVar &" ORDER BY ID"
	set rs = server.createObject("ADODB.Recordset")
	rs.open sql, objConn, 1, 3
	if not rs.eof then
		listaClientiOutput = rs.getrows() 
	end if
	rs.close
	set rs = nothing
end sub

Il problema è che invece di prendere tutti i records che appartengono a strVar mi estrae solo il primo... dove sbaglio?
grazie

**michele.santoro** · 08-03-2005, 16:46

io uso questo per *.mdb e funziona perfettamente vedi se puoi adattarlo:

codice:

<html>
<title>CodeAve.com(Create Excel on Server)</title>
<body bgcolor="#FFFFFF">
<%
' Name of the access db being queried
accessdb="state_info" 

' Connection string to the access db
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)

' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Query the states table from the state_info db
sql = "select state,statename,capital,year,order from states " 

' Execute the sql
rs.Open sql, cn

' Move to the first record
rs.MoveFirst

' Name for the ouput document 
file_being_created= "states.xls"

' create a file system object
set fso = createobject("scripting.filesystemobject")

' create the text file  - true will overwrite any previous files
' Writes the db output to a .xls file in the same directory 
Set act = fso.CreateTextFile(server.mappath(file_being_created), true)

' All non repetitive html on top goes here
act.WriteLine("<html><body>")
act.WriteLine("<table border=""1"">")
act.WriteLine("<tr>")
act.WriteLine("<th nowrap>State</th>")
act.WriteLine("<th nowrap>Abbreviaton</th>")
act.WriteLine("<th nowrap>Capital</th>")
act.WriteLine("<th nowrap>Year Entered</th>")
act.WriteLine("<th nowrap>Entrance Number</th>")
act.WriteLine("</tr>")

' For net loop to create seven word documents from the record set
' change this to "do while not rs.eof" to output all the records
' and the corresponsding next should be changed to loop also

for documents= 1 to 7

Act.WriteLine("<tr>")
act.WriteLine("<td align=""right"">" & rs("statename") & "</td>" )
act.WriteLine("<td align=""right"">" & rs("state") & "</td>" )
act.WriteLine("<td align=""right"">" & rs("capital") & "</td>")
act.WriteLine("<td align=""right"">"& rs("year") & "</td>")
act.WriteLine("<td align=""right"">"& rs("order") & "</td>")
act.WriteLine("</tr>")

' move to the next record
rs.movenext

' return to the top of the for - next loop
' change this to "loop" to output all the records
' and the corresponsding for statement above should be changed also
next

' All non repetitive html on top goes here
act.WriteLine("</table></body></html>")


' close the object (excel)
act.close


' Writes a link to the newly created excel in the browser
response.write "States (.xls) has been created on  " & now() & "
"

%>
</body>
</html>

Il Database è così composto (per facilitarti le cose): database (state_info) atabella (states)
state =Testo
statename=Testo
capital=Testo
year=Numerico
order=Numerico

**orcim** · 08-03-2005, 16:49

grazie, ma devo utilizzare la sub che ho postato prima... ho anche provato così ma restituisce errore sul record:

codice:

sub listaClienti(objConn,byRef listaClientiOutput)
	sql = "SELECT * FROM STATO_AVANZAMENTO_LAVORI WHERE ID = "& strVar &" ORDER BY ID"
	set rs = server.createObject("ADODB.Recordset")
	rs.open sql, objConn, 1, 3
	if rs.eof then
response.write "ko"
else
While Not rs.eof
		listaClientiOutput = rs.getrows() 
rs.MoveNext
wend
	end if
	rs.close
	set rs = nothing
end sub

**orcim** · 08-03-2005, 17:00

ho risolto, non vi dico cos'era se no mi prendete a pernacchie...

**michele.santoro** · 08-03-2005, 18:29

Facci sapere, facci sapere!!!!

Discussione: Da SQL Server a formato excel (xls)

Strumenti discussione

Ricerca discussione

Visualizza

Da SQL Server a formato excel (xls)

Permessi di invio