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 |
Partager