1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
| Private Sub btn_Traitement_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Traitement.Click
'Déclaration des variables
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim feuil1, feuil2, ftmp As Excel.Worksheet
Dim strQuery, strQueryLigne, strQueryPays As String
'Requete
Dim strSociete, strNom, strDate As String
'Societe selectionnée
Dim connexion, connexionPays, connexionLigne As SqlConnection
Dim command, commandLigne, commandPays As SqlCommand
Dim reader, readerLigne, readerPays As SqlDataReader
Dim row As Object() = Nothing
Dim connectstring As String
Dim strChemin As String
Dim i, intColis, intVolume, intPoids, intValeur As Integer
'Vérification Société Mise en place chaine de connexion
strSociete = lst_Societe.Text
Select Case strSociete
Case "OLC"
connectstring =
"Server=serveur2002;Database=OLC;Trusted_Connection=True"
Case "OMP"
strSociete = "OMP4"
connectstring =
"Server=serveur2002;Database=omp4;Trusted_Connection=True"
Case "MPC"
connectstring =
"Server=serveur2002;Database=mpc;Trusted_Connection=True"
Case Else
MsgBox("Erreur dans le choix de la société")
lst_Societe.Focus()
Exit Sub
End Select
'Etablissement connexion
connexion = New SqlConnection(connectstring)
Try
connexion.Open()
Catch ex As System.Exception
MsgBox("Erreur de Connexion à la Base de Données")
Exit Sub
End Try
'Création Application excel et renommage de feuilles
appExcel = CreateObject("Excel.application")
appExcel.Visible = True
wkb = appExcel.Workbooks.Add
'insertion de deux feuil et suppression des feuilles existantes
feuil1 = wkb.Worksheets.Add
feuil1.Name = "ENTETE"
feuil2 = wkb.Worksheets.Add
feuil2.Name = "DETAIL"
feuil2.Move(, feuil1)
feuil1.Activate()
ftmp = wkb.Sheets("feuil1")
ftmp.Delete()
ftmp = Nothing
'Requete de selection des enregistrement en fonction de date ou de BL
If rdbtn_Date.Checked Then
strDate = txt_Date.Text.Substring(6, 4) & "-"
strDate &= txt_Date.Text.Substring(3, 2) & "-"
strDate &= txt_Date.Text.Substring(0, 2) & " 00:00:00"
'strQuery = "SELECT * FROM F_DOCENTETE E, F_COMPTET C WHERE
E.DO_Tiers = C.CT_Num AND E.DO_TYPE =3 AND
E.DO_DATE=CONVERT(DATETIME, '" & Me.txt_Date.Text & "', 102)"
strQuery = "SELECT * , F_DOCENTETE.DO_Type AS Expr1,
F_DOCENTETE.DO_Date AS Expr2 FROM F_DOCENTETE INNER JOIN
F_COMPTET ON F_DOCENTETE.DO_Tiers = F_COMPTET.CT_Num WHERE
(F_DOCENTETE.DO_Type = 1) AND (F_DOCENTETE.DO_Date =
CONVERT(DATETIME, '" & strDate & "', 102))"
Else
If txt_BL1.Text = txt_BL2.Text Or txt_BL2.Text = "" Then
strQuery = "SELECT * FROM F_DOCENTETE E ,F_COMPTET C WHERE
E.DO_Tiers = C.CT_Num AND E.DO_Piece='" & txt_BL1.Text &
"';"
Else
strQuery = "SELECT * FROM F_DOCENTETE E ,F_COMPTET C WHERE
E.DO_Tiers = C.CT_Num AND DO_Piece>='" & txt_BL1.Text & "'
AND DO_Piece<='" & txt_BL2.Text & "'"
End If
End If
command = New SqlCommand(strQuery, connexion)
reader = command.ExecuteReader()
'Variable de ligne
i = 2
'Boucle de remplissage
While reader.Read
'Traitement des données
end while |
