' * Le code permet d'exporter le contenu d'une datagrdiview vers un fichier excel
'**
'UTILISATION
' Dim ex As New ExportXls.now
' ex.Export(datagv)
' ex = Nothing
'FIN UTILISATION
Imports
Microsoft.VisualBasic Imports
System Imports
System.Collections.Generic Imports
System.ComponentModel Imports
System.Data Imports
System.Text Imports
System.Windows.Forms Imports
System.Reflection Public
Class now Private oXL As Microsoft.Office.Interop.Excel.Application Private oWB As Microsoft.Office.Interop.Excel._Workbook Private oSheet As Microsoft.Office.Interop.Excel._Worksheet Private oRng As Microsoft.Office.Interop.Excel.Range Private M As Object = System.Reflection.Missing.Value#
Region "Enregistrer le fichier Excel sous"
Public Sub SaveAs( ByVal sNameFichier As String ) Try oWB.SaveAs(sNameFichier, M, M, M, M, M, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, M, M, M, M, M)
Catch e As ExceptionMessageBox.Show(e.Message)
End Try
End Sub #
End Region
Public Function Export( ByRef dtgv As DataGridView) Dim z As Int32 = 1 Dim f As New Formf.Width = 200
f.Height = 50
f.FormBorderStyle = FormBorderStyle.None
Dim pgb As New ProgressBarpgb.Parent = f
pgb.Dock = DockStyle.Fill
pgb.Style = ProgressBarStyle.Marquee
f.Show()
pgb.Value = pgb.Value + z
Dim savefd1 As New SaveFileDialog With savefd1.AddExtension =
True .DefaultExt =
".xlsx" .CheckPathExists =
True .DereferenceLinks =
True .Filter =
"Excell 2007(*.xlsx)|*.xlsx" .OverwritePrompt =
True .ShowHelp =
True .SupportMultiDottedExtensions =
True .Title =
"Save as Excell 2007 file" .ValidateNames =
True
End With
Try
'Start Excel and get Application object. oXL =
New Microsoft.Office.Interop.Excel.Application()oXL.Visible =
False pgb.Value = pgb.Value + z
'Get a new workbook. oWB =
CType (oXL.Workbooks.Add(System.Reflection.Missing.Value), Microsoft.Office.Interop.Excel._Workbook)oSheet =
CType (oWB.ActiveSheet, Microsoft.Office.Interop.Excel._Worksheet)pgb.Value = pgb.Value + z
' Copier les noms des colonnes
Dim i As Integer = 0 For Each ch As DataGridViewColumn In dtgv.ColumnsoRng = oSheet.Range(Convert.ToChar(65 + i).ToString() &
"1" , Missing.Value)oSheet.Cells(1, i + 1) = ch.Name.Trim()
oRng.Interior.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic
oRng.Font.Bold =
True oRng.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value)
oRng.EntireColumn.AutoFit()
pgb.Value = pgb.Value + z
i += 1
Next ch ' Copier toutes les cellules du datagridview
' j=2 on commence à la 2eme ligne dans le fichier excel
Dim j As Integer = 2 For Each uneLigne As DataGridViewRow In dtgv.Rowspgb.Value = pgb.Value + z
i = 1
' si i=1 alors 65-1+1 donne 65<=>A et ainsi on aura la lettre de la colonne puis on juxtapose le numero de la ligne
For Each uneColonne As DataGridViewColumn In dtgv.ColumnsoRng = oSheet.Range(Convert.ToChar(65 + i - 1).ToString() & j.ToString(), Missing.Value)
oSheet.Cells(j, i) = uneLigne.Cells(uneColonne.Name).Value.ToString().Trim()
oRng.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value)
oRng.EntireColumn.AutoFit()
i += 1
Application.DoEvents()
Next uneColonneoSheet.Columns.AutoFit()
j += 1
Application.DoEvents()
Next uneLigne Catch
End Try f.Dispose()
If savefd1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Try SaveAs(savefd1.FileName)
Catch MessageBox.Show(
"erreur" ) End Try
End If oXL.Quit()
Return True
End FunctionEnd
Class il y a 10 sortes de gens: ceux qui comprennent le binaire, et les autres