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
|
'============================= code pour l'exportation ===================
Private Declare Function ShellEx Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As Integer, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Integer) As Integer
'ByVal fileExtension As String, ByVal filePath As String)
Private Sub Exporter_vers_excel_Click(ByVal Table_inventairDatagridView As DataGridView, ByVal bon_de_commande As String, ByVal xlsx As String)
' Choose the path, name, and extension for the Excel file
Dim myFile As String = "C:\Users\compaq\Desktop\projet_inventaire1" & "\" & "bon_de_commande"
' & "xlsx"
' Open the file and write the headers
Dim fs As New IO.StreamWriter(myFile, False)
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
' Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>")
' Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" <ss:Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" " & _
"ss:WrapText=""1""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C0C0"" ss:Pattern=""Solid""/>")
fs.WriteLine(" </ss:Style>")
' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")
' Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
fs.WriteLine(" <ss:Table>")
For i As Integer = 0 To Table_inventairDatagridView.Columns.Count - 2
fs.WriteLine(String.Format(" <ss:Column ss:Width=""{0}""/>", _
Table_inventairDatagridView.Columns.Item(i).Width))
Next
fs.WriteLine(" <ss:Row>")
For i As Integer = 0 To Table_inventairDatagridView.Columns.Count - 2
fs.WriteLine(String.Format(" <ss:Cell ss:StyleID=""1"">" & _
"<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _
Table_inventairDatagridView.Columns.Item(i).HeaderText))
Next
fs.WriteLine(" </ss:Row>")
' Check for an empty row at the end due to Adding allowed on the DataGridView
Dim subtractBy As Integer, cellText As String
If Table_inventairDatagridView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
' Write contents for each cell
For i As Integer = 0 To Table_inventairDatagridView.RowCount - subtractBy
fs.WriteLine(String.Format(" <ss:Row ss:Height=""{0}"">", _
Table_inventairDatagridView.Rows(i).Height))
For intCol As Integer = 0 To Table_inventairDatagridView.Columns.Count - 2
cellText = Table_inventairDatagridView.Item(intCol, i).Value
' Check for null cell and change it to empty to avoid error
If cellText = vbNullString Then cellText = ""
fs.WriteLine(String.Format(" <ss:Cell ss:StyleID=""2"">" & _
"<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _
cellText.ToString))
Next
fs.WriteLine(" </ss:Row>")
Next
' Close up the document
fs.WriteLine(" </ss:Table>")
fs.WriteLine("</ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()
' Open the file in Microsoft Excel
' SW_SHOWDEFAULT = 10
ShellEx(Me.Handle, "Open", myFile, "", "", 10)
End Sub
'pour l'exportation final de la gdv (j'ai crée ce 2ème bouton pour faire le transfer final vers le fichier Excel)
Private Sub finalExportation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles finalExportation.Click
' Call the export sub
Exporter_vers_excel_Click(Table_inventairDataGridView, "exportedData", ".xlsx")
',_
'My.Computer.FileSystem.SpecialDirectories.Desktop)
End Sub |
