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
|
Sub exportexcel(ByVal datagridview1 As DataGridView, ByVal page As String)
Dim processus() As Process = Process.GetProcessesByName("Excel")
For i = 0 To UBound(processus)
'MessageBox.Show(processus(i).Id)
Next
'MessageBox.Show(processus(Process.GetProcesses.Length))
'processus = Process.GetProcessesByName("Excel")
App = New xls.Application
With App
Dim objBook As xls.Workbook
Dim objBooks As xls.Workbooks
Dim objSheets As xls.Sheets
Dim objSheet As xls._Worksheet
Dim rangea As xls.Range
' Create a new instance of Excel and start a new workbook.
'App = New xls.Application()
objBooks = App.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
objSheet = objSheets(1)
Try
'entête des colonnes
For Col_cpt = 0 To datagridview1.ColumnCount - 1
.ActiveSheet.cells(1, Col_cpt + 1).value = datagridview1.Columns(Col_cpt).HeaderText
Next
'permet de générer l'export
For Lig_cpt = 0 To datagridview1.Rows.Count - 1
For Col_cpt = 0 To datagridview1.ColumnCount - 1
If IsNumeric(datagridview1.Item(Col_cpt, Lig_cpt).Value) Then
.ActiveSheet.cells(Lig_cpt + 2, Col_cpt + 1).value = CDbl(datagridview1.Item(Col_cpt, Lig_cpt).Value)
Else
.ActiveSheet.cells(Lig_cpt + 2, Col_cpt + 1).value = datagridview1.Item(Col_cpt, Lig_cpt).Value
End If
Next
Next
'permet de faire un trie sur les colonnes
If page = "VenteAnnee" Then
'rangea = objBook.ActiveSheet.cells("D5").select
.ActiveSheet.cells(1, 9).select '--> permet de sélectionner une cellule
.ActiveSheet.columns("A:G").select 'permet de selectionner les colonnes
objSheet.Range("A1").CurrentRegion.Select() ' selectionne la region active
'on stock une plage de cellule
rangea = objSheet.Range("A:G")
'On réalise le sous-total mettre entre accolade la liste de colonne
'rangea.Subtotal(1, xls.XlConsolidationFunction.xlSum, TotalList:={5, 6, 7}, True, False, True)
End If
'MessageBox.Show(System.DateTime.Now.ToString())
objBook.SaveAs("C:\Users\mmachart\Downloads\classeur" + System.DateTime.Now.ToString("yyyyMMddHHmmss"))
'App.Visible = True
Catch ex As Exception
MessageBox.Show("Error excel" & Chr(10) & Err.Description)
objBook.Close()
.Quit()
Finally
objBook.Close()
App.Quit()
App = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
End Try
End With
'je stocke dans un nouveau tableau les process
Dim processusdiff() As Process = Process.GetProcessesByName("Excel")
'me permet de vérifier si les process sont bien repris.
'For ii = 0 To UBound(processusdiff)
' MessageBox.Show(processusdiff(ii).Id)
'Next
For ii = 0 To UBound(processusdiff)
For iii = 0 To UBound(processus)
' MessageBox.Show(processusdiff(ii).Id & processus(iii).Id)
If processusdiff(ii).Id.ToString = processus(iii).Id.ToString Then Exit For
If processusdiff(ii).Id.ToString <> processus(iii).Id.ToString And iii = UBound(processus).ToString Then
MessageBox.Show(processusdiff(ii).Id.ToString)
processusdiff(ii).Kill()
End If
Next
Next |
Partager