Sujet Précédent Sujet Suivant

Comment exporter une datagridview sur un fichier Excel existant ?

norddist Messages postés 6 Date d'inscription mercredi 17 mars 2010 Statut Membre Dernière intervention 1 septembre 2010 - 1 sept. 2010 à 17:44
norddist Messages postés 6 Date d'inscription mercredi 17 mars 2010 Statut Membre Dernière intervention 1 septembre 2010 - 1 sept. 2010 à 18:17
Bonjour à tous,

Je m'adresse à vous sur ce site car toute mes recherches sur le net n'ont rien données (sites Français et Anglais).
Il y a bien sur le net pleins de tuto pour faire cela, mais l'exportation se fait toujours sur un nouveau fichier Excel existant.

Moi je voudrais exporter la datagridview sur un fichier Excel que j'ai déja créee.
voici le code que j'utilise et que j'aimerais modifier pour qu'il fasse l'exportation sur le fichier Excel (bon_de_commande.xlsx);
chemin du fichier (bon_de_commande.xlsx): C:\Users\compaq\Desktop\projet_inventaire1

j'utilise Visual basic 2008 edition express,
et Excel 2007.

d'avance un grand merci. 


'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
    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
A voir également:

2 réponses

Réponse 1 / 2
norddist Messages postés 6 Date d'inscription mercredi 17 mars 2010 Statut Membre Dernière intervention 1 septembre 2010
1 sept. 2010 à 17:51
correction au début du message et à la fin du code pour la compréhension du code. 


'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
0
Réponse 2 / 2
norddist Messages postés 6 Date d'inscription mercredi 17 mars 2010 Statut Membre Dernière intervention 1 septembre 2010
1 sept. 2010 à 18:17
encore une dernière correction (ne sachant pas comment corriger son message sur le forum).
je voulais dir

"Il y a bien sur le net pleins de tuto pour faire cela, mais l'exportation se fait toujours sur un nouveau fichier Excel"

merci pour votre aide.
0
Rejoignez-nous