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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
| Private Sub FicheSuivimenu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FicheSuivimenu.Click
'Déclaration des variables
Dim appExcel As New Excel.Application 'Application Excel
Dim wbExcel As Excel.Workbook 'Classeur Excel
Dim wsExcel As Excel.Worksheet 'Feuille Excel
Try
'Ouverture de l'application
appExcel = CreateObject("Excel.Application")
'Ouverture d'un fichier Excel
wbExcel = appExcel.Workbooks.Open(Application.StartupPath & "\fiche_intervention.xls")
'wsExcel correspond à la première feuille du fichier
wsExcel = wbExcel.Worksheets(1)
wbExcel = appExcel.ActiveWorkbook
'Récupération de la feuille par défaut
wsExcel = wbExcel.ActiveSheet
Dim MyDataReader As MySqlDataReader
Dim MyConString As String = "SERVER=localhost;" + _
"DATABASE=prj;" + "UID=root;" + "PASSWORD=;"
Dim idclt, x As Integer
Dim con As New MySqlConnection(MyConString) '
con.Open()
Dim MyCommand As New MySqlCommand()
MyCommand.Connection = con
For Each myShp As Excel.Shape In wsExcel.Shapes
If myShp.Name = "clt" Then
myShp.TextFrame.Characters.Text = frmGestionClt.DTGclient.CurrentRow.Cells(0).Value
myShp.TextFrame.Characters.Font.Size = 10
myShp.TextFrame.Characters.Font.Name = "Arial"
ElseIf myShp.Name = "int" Then
myShp.TextFrame.Characters.Text = frmGestionClt.DTGIntervention.CurrentRow.Cells(0).Value
myShp.TextFrame.Characters.Font.Size = 10
myShp.TextFrame.Characters.Font.Name = "Arial"
End If
Next
wsExcel.Cells(7, 1).value = frmGestionClt.DTGclient.CurrentRow.Cells(1).Value
'récupérer id_client
MyCommand.CommandText = "select Id_client from client where Numero_de_client=" & "'" & frmGestionClt.client & "'"
MyDataReader = MyCommand.ExecuteReader()
x = MyDataReader.Read()
idclt = MyDataReader.GetInt32(0)
MyDataReader.Close()
Dim lig As Integer
' Charger la datagridview
MyCommand.CommandText = "select `Numero_du_poste`, `Type de nuisible`, `Batiment`, `Niveau`, `Nom des locaux`, Id_poste from poste where Id_client=" & "'" & idclt & "' order by `Batiment`, `Niveau`, `Numero_du_poste`"
' z = MyCommand.ExecuteNonQuery
'MsgBox(z)
MyDataReader = MyCommand.ExecuteReader()
Dim od As Integer
od = 0
Dim savniv, savbat As String
savniv = ""
savbat = ""
lig = 15
While MyDataReader.Read()
If MyDataReader.GetString(2) <> "<aucun>" And savbat <> MyDataReader.GetString(2) And savniv <> MyDataReader.GetString(3) Then
savbat = MyDataReader.GetString(2)
savniv = MyDataReader.GetString(3)
wsExcel.Range("A" & lig & ":BE" & lig).Merge(False)
wsExcel.Range("A" & lig & ":BE" & lig).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
wsExcel.Cells(lig, 1).value = "Batiment: " & savbat
lig = lig + 1
wsExcel.Range("A" & lig & ":BE" & lig).Merge(False)
wsExcel.Cells(lig, 1).value = " Niveau : " & savniv
wsExcel.Range("A" & lig & ":BE" & lig).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
lig = lig + 1
ElseIf MyDataReader.GetString(2) <> "<aucun>" And savbat <> MyDataReader.GetString(2) And savniv = MyDataReader.GetString(3) Then
savniv = MyDataReader.GetString(3)
wsExcel.Range("A" & lig & ":BE" & lig).Merge(False)
wsExcel.Cells(lig, 1).value = "Batiment: " & MyDataReader.GetString(2)
wsExcel.Range("A" & lig & ":BE" & lig).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
lig = lig + 1
wsExcel.Range("A" & lig & ":BE" & lig).Merge(False)
wsExcel.Cells(lig, 1).value = " Niveau : " & MyDataReader.GetString(3)
wsExcel.Range("A" & lig & ":BE" & lig).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
lig = lig + 1
ElseIf MyDataReader.GetString(3) <> "<aucun>" And savniv <> MyDataReader.GetString(3) Then
wsExcel.Range("A" & lig & ":BE" & lig).Merge(False)
wsExcel.Cells(lig, 1).value = " Niveau : " & MyDataReader.GetString(3)
wsExcel.Range("A" & lig & ":BE" & lig).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
lig = lig + 1
End If
wsExcel.Cells(lig, 19).value = MyDataReader.GetString(4)
wsExcel.Cells(lig, 20).value = MyDataReader.GetString(0)
wsExcel.Cells(lig, 21).value = MyDataReader.GetString(1)
For Each cellule In wsExcel.Range("A" & lig & ":BE" & lig)
cellule.Borders.Weight = Excel.XlBorderWeight.xlThin
Next
If lig = 35 Then
End If
lig = lig + 1
End While
MyDataReader.Close()
con.Close()
appExcel.Visible = True
wsExcel.PrintOut(1, 4, 1, True, , , , )
Dim p As String
p = Environment.GetFolderPath(System.Environment.SpecialFolder.Personal).ToString & "\Rapport\"
MsgBox(p)
wsExcel.SaveAs("Fiche de suivi du client ", , , , , , , , , p)
wbExcel.Close()
appExcel.Quit()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub |
améliorer un code
Répondre avec citation
Partager