Exporter une table access vers un fichier excel
ahmani24
Messages postés
39
Date d'inscription
mardi 13 avril 2004
Statut
Membre
Dernière intervention
14 novembre 2006
-
2 juin 2006 à 19:28
jfmaze Messages postés 13 Date d'inscription mercredi 21 janvier 2004 Statut Membre Dernière intervention 16 juin 2006 - 16 juin 2006 à 22:13
jfmaze Messages postés 13 Date d'inscription mercredi 21 janvier 2004 Statut Membre Dernière intervention 16 juin 2006 - 16 juin 2006 à 22:13
1 réponse
jfmaze
Messages postés
13
Date d'inscription
mercredi 21 janvier 2004
Statut
Membre
Dernière intervention
16 juin 2006
16 juin 2006 à 22:13
16 juin 2006 à 22:13
L'important c'est la première ligne
après on génère un tableau standard html qui sera reconnu par excel:
--------------------------
<%response.ContentType="application/vnd.ms-excel"%>
<html>
----
--Date début--,
--Heure-début-,
---Date fin---,
---Heure-fin--,
---Date CJ----,
---Structure-,
-----Intervenant-----,
----Orig. appel-----,
----Environnement----,
-------Application------,
-------Job Pf Serveur-----,
------------Région-----------,
------------Caisses----------,
--------------------------Evènement------------------------,
---Pertinence---,
-----------------------Justification-----------------------,
<%
valdatd=request(\"date_deb\")
valheud=request(\"HeureDeb\")
valdatf=request(\"date_fin\")
valheuf=request(\"HeureFin\")
valstru=request(\"Structure\")
valcri1=request(\"Critere1\")
valcri2=request(\"Critere2\")
valcri3=request(\"Critere3\")
valcri4=request(\"Critere4\")
%>
<!--#include virtual=\"/asp/Astreintes/CopyRequete.txt\" -->
----
<%=rs("DateDebut")%>,
<%=rs("HeureDebut")%>,
<%=rs("DateFin")%>,
<%=rs("HeureFin")%>,
<%=rs("DateCJ")%>,
<%=rs("Structure")%>,
<%=rs("Intervenant")%>,
<%=rs("OrigineAppel")%>,
<%=rs("Environnement")%>,
<%=rs("Application")%>,
<%=rs("Element")%>,
<%=rs("Region")%>,
<%=rs("Caisse")%>,
<%=rs("Description")%>,
<%=rs("Pertinence")%>,
<%=rs("Justification")%>,
<%rs.movenext
loop
end if
rs.close
set rs=nothing
conn.close
set conn=nothing%>
</html>
============
la requete est standard:
<%
'Crée un objet connexion.
Set conn = Server.CreateObject("ADODB.Connection")
'Ouvre une connexion à l'aide de la chaîne de connexion OLE DB.
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&NomDeLaBaseAstreinte
valstructure=request("Structure")
if valcri2 "" then valcri2 valcri1 end if if valcri3 "" then valcri3 valcri1 end if if valcri4 "" then valcri4 valcri1 end if
' recherche du nombre de rapports correspondant à la journée
' sur sql le format date est mm/jj/aaaaif valdatd "" then valdatd "01/01/2004" end if
valAA=year(valdatd)
valMM=month(valdatd)
valJJ=day(valdatd)if valheud "" then valheud "00:00" end if
valJourd=valMM&"/"&valJJ&"/"&valAA&" "&valheudif valdatf "" then valdatf dateadd("d","+1",date()) end if
valAA=year(valdatf)
valMM=month(valdatf)
valJJ=day(valdatf)if valheuf "" then valheuf "00:00" end if
valJourf=valMM&"/"&valJJ&"/"&valAA&" "&valheuf
if request("EtOu") = "OR" then
sql="select * from RapportsAstreinte where (DateDebut+heuredebut) between #"&valJourd&"# and #"&valJourf&"# and "
if len(request("Structure")) > 0 then
sql=sql&"Structure = '"&valstructure&"' and "
end if
sql=sql&"(Description like '%"&valcri1&"%' or Description like '%"&valcri2&"%' or Description like '%"&valcri3&"%' or Description like '%"&valcri4&"%' "
sql=sql&"or Structure like '%"&valcri1&"%' or Structure like '%"&valcri2&"%' or Structure like '%"&valcri3&"%' or Structure like '%"&valcri4&"%' "
sql=sql&"or Intervenant like '%"&valcri1&"%' or Intervenant like '%"&valcri2&"%' or Intervenant like '%"&valcri3&"%' or Intervenant like '%"&valcri4&"%' "
sql=sql&"or OrigineAppel like '%"&valcri1&"%' or OrigineAppel like '%"&valcri2&"%' or OrigineAppel like '%"&valcri3&"%' or OrigineAppel like '%"&valcri4&"%' "
sql=sql&"or Region like '%"&valcri1&"%' or Region like '%"&valcri2&"%' or Region like '%"&valcri3&"%' or Region like '%"&valcri4&"%' "
sql=sql&"or Caisse like '%"&valcri1&"%' or Caisse like '%"&valcri2&"%' or Caisse like '%"&valcri3&"%' or Caisse like '%"&valcri4&"%' "
sql=sql&"or Application like '%"&valcri1&"%' or Application like '%"&valcri2&"%' or Application like '%"&valcri3&"%' or Application like '%"&valcri4&"%' "
sql=sql&"or Element like '%"&valcri1&"%' or Element like '%"&valcri2&"%' or Element like '%"&valcri3&"%' or Element like '%"&valcri4&"%' "
sql=sql&"or Environnement like '%"&valcri1&"%' or Environnement like '%"&valcri2&"%' or Environnement like '%"&valcri3&"%' or Environnement like '%"&valcri4&"%' "
sql=sql&"or Justification like '%"&valcri1&"%' or Justification like '%"&valcri2&"%' or Justification like '%"&valcri3&"%' or Justification like '%"&valcri4&"%' ) order by DateDebut, HeureDebut"
'response.write(sql)
else
sql="select * from RapportsAstreinte where (DateDebut+heuredebut) between #"&valJourd&"# and #"&valJourf&"# and "
if len(request("Structure")) > 0 then
sql=sql&"Structure = '"&valstructure&"' and "
end if
sql=sql&"(Description like '%"&valcri1&"%' or Structure like '%"&valcri1&"%' or Intervenant like '%"&valcri1&"%' or OrigineAppel like '%"&valcri1&"%' "
sql=sql&"or Region like '%"&valcri1&"%' or Caisse like '%"&valcri1&"%' or Application like '%"&valcri1&"%' or Element like '%"&valcri1&"%' or Environnement like '%"&valcri1&"%' or Justification like '%"&valcri1&"%' ) "
sql=sql&"and (Description like '%"&valcri2&"%' or Structure like '%"&valcri2&"%' or Intervenant like '%"&valcri2&"%' or OrigineAppel like '%"&valcri2&"%' "
sql=sql&"or Region like '%"&valcri2&"%' or Caisse like '%"&valcri2&"%' or Application like '%"&valcri2&"%' or Element like '%"&valcri2&"%' or Environnement like '%"&valcri2&"%' or Justification like '%"&valcri2&"%' ) "
sql=sql&"and (Description like '%"&valcri3&"%' or Structure like '%"&valcri3&"%' or Intervenant like '%"&valcri3&"%' or OrigineAppel like '%"&valcri3&"%' "
sql=sql&"or Region like '%"&valcri3&"%' or Caisse like '%"&valcri3&"%' or Application like '%"&valcri3&"%' or Element like '%"&valcri3&"%' or Environnement like '%"&valcri3&"%' or Justification like '%"&valcri3&"%' ) "
sql=sql&"and (Description like '%"&valcri4&"%' or Structure like '%"&valcri4&"%' or Intervenant like '%"&valcri4&"%' or OrigineAppel like '%"&valcri4&"%' "
sql=sql&"or Region like '%"&valcri4&"%' or Caisse like '%"&valcri4&"%' or Application like '%"&valcri4&"%' or Element like '%"&valcri4&"%' or Environnement like '%"&valcri4&"%' or Justification like '%"&valcri4&"%' ) order by DateDebut, HeureDebut"
end if
set rs=conn.execute(sql)
if (not rs.BOF and not rs.EOF) then
rs.movefirst
do while not rs.EOF%>
après on génère un tableau standard html qui sera reconnu par excel:
--------------------------
<%response.ContentType="application/vnd.ms-excel"%>
<html>
----
--Date début--,
--Heure-début-,
---Date fin---,
---Heure-fin--,
---Date CJ----,
---Structure-,
-----Intervenant-----,
----Orig. appel-----,
----Environnement----,
-------Application------,
-------Job Pf Serveur-----,
------------Région-----------,
------------Caisses----------,
--------------------------Evènement------------------------,
---Pertinence---,
-----------------------Justification-----------------------,
<%
valdatd=request(\"date_deb\")
valheud=request(\"HeureDeb\")
valdatf=request(\"date_fin\")
valheuf=request(\"HeureFin\")
valstru=request(\"Structure\")
valcri1=request(\"Critere1\")
valcri2=request(\"Critere2\")
valcri3=request(\"Critere3\")
valcri4=request(\"Critere4\")
%>
<!--#include virtual=\"/asp/Astreintes/CopyRequete.txt\" -->
----
<%=rs("DateDebut")%>,
<%=rs("HeureDebut")%>,
<%=rs("DateFin")%>,
<%=rs("HeureFin")%>,
<%=rs("DateCJ")%>,
<%=rs("Structure")%>,
<%=rs("Intervenant")%>,
<%=rs("OrigineAppel")%>,
<%=rs("Environnement")%>,
<%=rs("Application")%>,
<%=rs("Element")%>,
<%=rs("Region")%>,
<%=rs("Caisse")%>,
<%=rs("Description")%>,
<%=rs("Pertinence")%>,
<%=rs("Justification")%>,
<%rs.movenext
loop
end if
rs.close
set rs=nothing
conn.close
set conn=nothing%>
</html>
============
la requete est standard:
<%
'Crée un objet connexion.
Set conn = Server.CreateObject("ADODB.Connection")
'Ouvre une connexion à l'aide de la chaîne de connexion OLE DB.
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&NomDeLaBaseAstreinte
valstructure=request("Structure")
if valcri2 "" then valcri2 valcri1 end if if valcri3 "" then valcri3 valcri1 end if if valcri4 "" then valcri4 valcri1 end if
' recherche du nombre de rapports correspondant à la journée
' sur sql le format date est mm/jj/aaaaif valdatd "" then valdatd "01/01/2004" end if
valAA=year(valdatd)
valMM=month(valdatd)
valJJ=day(valdatd)if valheud "" then valheud "00:00" end if
valJourd=valMM&"/"&valJJ&"/"&valAA&" "&valheudif valdatf "" then valdatf dateadd("d","+1",date()) end if
valAA=year(valdatf)
valMM=month(valdatf)
valJJ=day(valdatf)if valheuf "" then valheuf "00:00" end if
valJourf=valMM&"/"&valJJ&"/"&valAA&" "&valheuf
if request("EtOu") = "OR" then
sql="select * from RapportsAstreinte where (DateDebut+heuredebut) between #"&valJourd&"# and #"&valJourf&"# and "
if len(request("Structure")) > 0 then
sql=sql&"Structure = '"&valstructure&"' and "
end if
sql=sql&"(Description like '%"&valcri1&"%' or Description like '%"&valcri2&"%' or Description like '%"&valcri3&"%' or Description like '%"&valcri4&"%' "
sql=sql&"or Structure like '%"&valcri1&"%' or Structure like '%"&valcri2&"%' or Structure like '%"&valcri3&"%' or Structure like '%"&valcri4&"%' "
sql=sql&"or Intervenant like '%"&valcri1&"%' or Intervenant like '%"&valcri2&"%' or Intervenant like '%"&valcri3&"%' or Intervenant like '%"&valcri4&"%' "
sql=sql&"or OrigineAppel like '%"&valcri1&"%' or OrigineAppel like '%"&valcri2&"%' or OrigineAppel like '%"&valcri3&"%' or OrigineAppel like '%"&valcri4&"%' "
sql=sql&"or Region like '%"&valcri1&"%' or Region like '%"&valcri2&"%' or Region like '%"&valcri3&"%' or Region like '%"&valcri4&"%' "
sql=sql&"or Caisse like '%"&valcri1&"%' or Caisse like '%"&valcri2&"%' or Caisse like '%"&valcri3&"%' or Caisse like '%"&valcri4&"%' "
sql=sql&"or Application like '%"&valcri1&"%' or Application like '%"&valcri2&"%' or Application like '%"&valcri3&"%' or Application like '%"&valcri4&"%' "
sql=sql&"or Element like '%"&valcri1&"%' or Element like '%"&valcri2&"%' or Element like '%"&valcri3&"%' or Element like '%"&valcri4&"%' "
sql=sql&"or Environnement like '%"&valcri1&"%' or Environnement like '%"&valcri2&"%' or Environnement like '%"&valcri3&"%' or Environnement like '%"&valcri4&"%' "
sql=sql&"or Justification like '%"&valcri1&"%' or Justification like '%"&valcri2&"%' or Justification like '%"&valcri3&"%' or Justification like '%"&valcri4&"%' ) order by DateDebut, HeureDebut"
'response.write(sql)
else
sql="select * from RapportsAstreinte where (DateDebut+heuredebut) between #"&valJourd&"# and #"&valJourf&"# and "
if len(request("Structure")) > 0 then
sql=sql&"Structure = '"&valstructure&"' and "
end if
sql=sql&"(Description like '%"&valcri1&"%' or Structure like '%"&valcri1&"%' or Intervenant like '%"&valcri1&"%' or OrigineAppel like '%"&valcri1&"%' "
sql=sql&"or Region like '%"&valcri1&"%' or Caisse like '%"&valcri1&"%' or Application like '%"&valcri1&"%' or Element like '%"&valcri1&"%' or Environnement like '%"&valcri1&"%' or Justification like '%"&valcri1&"%' ) "
sql=sql&"and (Description like '%"&valcri2&"%' or Structure like '%"&valcri2&"%' or Intervenant like '%"&valcri2&"%' or OrigineAppel like '%"&valcri2&"%' "
sql=sql&"or Region like '%"&valcri2&"%' or Caisse like '%"&valcri2&"%' or Application like '%"&valcri2&"%' or Element like '%"&valcri2&"%' or Environnement like '%"&valcri2&"%' or Justification like '%"&valcri2&"%' ) "
sql=sql&"and (Description like '%"&valcri3&"%' or Structure like '%"&valcri3&"%' or Intervenant like '%"&valcri3&"%' or OrigineAppel like '%"&valcri3&"%' "
sql=sql&"or Region like '%"&valcri3&"%' or Caisse like '%"&valcri3&"%' or Application like '%"&valcri3&"%' or Element like '%"&valcri3&"%' or Environnement like '%"&valcri3&"%' or Justification like '%"&valcri3&"%' ) "
sql=sql&"and (Description like '%"&valcri4&"%' or Structure like '%"&valcri4&"%' or Intervenant like '%"&valcri4&"%' or OrigineAppel like '%"&valcri4&"%' "
sql=sql&"or Region like '%"&valcri4&"%' or Caisse like '%"&valcri4&"%' or Application like '%"&valcri4&"%' or Element like '%"&valcri4&"%' or Environnement like '%"&valcri4&"%' or Justification like '%"&valcri4&"%' ) order by DateDebut, HeureDebut"
end if
set rs=conn.execute(sql)
if (not rs.BOF and not rs.EOF) then
rs.movefirst
do while not rs.EOF%>
