Exporter vers Excel entre 2 dates [Résolu]

Messages postés
11
Date d'inscription
vendredi 22 mars 2019
Statut
Membre
Dernière intervention
22 mai 2019
-
Bonjour,

J'ai un dgv remplit a partir d'une database et j'aimerais exporter ce dgv vers Excel uniquement quand la colonne 0 est comprise entre 2 datetimepicker

J'arrive a exporter mais avec la condition j'ai l'erreur : 'Operator '>=' is not defined for type 'Integer' and type 'Date'.'

Voici ce que j'ai deja fait pour l'instant
Private Sub BtnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click

        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        DataGridView3.Columns(0).DefaultCellStyle.Format = "MMddyyyy"

        Dim MyTable As New DataTable
        Dim CNX As New OleDbDataAdapter("select * from tblToolRequisition", cn)
        CNX.FillSchema(MyTable, SchemaType.Source)
        CNX.Fill(MyTable)

        For i = 0 To DataGridView3.RowCount - 2
            For Each R As DataRow In MyTable.Rows
                If Not R.Item("RequiredBy") Is DBNull.Value Then
                    If DataGridView3.Item(0, i).Value >= dtpStart.Value.Date And DataGridView3.Item(0, i).Value <= dtpEnd.Value.Date Then
                        For j = 0 To DataGridView3.ColumnCount - 1

                            xlWorkSheet.Cells(i + 1, j + 1) = DataGridView3(j, i).Value.ToString()

                                xlWorkSheet.SaveAs("C:\ITDEV\Excel export\ToolRequisition\ToolRequisition" & Format(Today, "MMddyyyy") & ".xlsx")
                                xlWorkBook.Close()
                                xlApp.Quit()

                                ReleaseObject(xlApp)
                                ReleaseObject(xlWorkBook)
                                ReleaseObject(xlWorkSheet)

                                Process.Start("C:\ITDEV\Excel export\ToolRequisition\ToolRequisition" & Format(Today, "MMddyyyy") & ".xlsx")

                           Next
                    End If
                End If
            Next
        Next
    End Sub


Merci d'avance pour vos reponses
Afficher la suite 

2 réponses

Messages postés
13650
Date d'inscription
mardi 11 mars 2003
Statut
Contributeur
Dernière intervention
25 août 2019
298
0
Merci
Bonjour

pour tes prochains posts, merci de lire et d'appliquer ce qui est décrit là https://codes-sources.commentcamarche.net/faq/11288-les-balises-de-code
Tu n'as pas forcé la coloration en basic et c'est pas franchement lisible.

Le message d'erreur est clair tu essaye de comparer un date avec un entier et .Net ne sais pas faire.
Je suppose que c'est à cette ligne
DataGridView3.Item(0, i).Value >= dtpStart.Value.Date
ce qui veut dire que l'item 0,i est un entier et pas une date
Commenter la réponse de Whismeril
Messages postés
11
Date d'inscription
vendredi 22 mars 2019
Statut
Membre
Dernière intervention
22 mai 2019
0
Merci
Merci pour ta reponse
Au final j'ai trouvé une solution en creant un dgv filtré

Private Sub BtnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click

        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer
        Dim k As Integer

        DGV.Visible = False

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        DGV.Columns(0).DefaultCellStyle.Format = "MM/dd/yyyy"

        Dim MyTable As New DataTable
        Dim DtSet As New DataSet
        Dim cmd As New OleDbDataAdapter("select * from tblToolRequisition where [RequiredBy] between #" & dtpStart.Value.Date & "# and #" & dtpEnd.Value.Date & "#", cn)
        cmd.Fill(MyTable)
        DtSet.Tables.Add(MyTable)

        DtSet.Tables(0).TableName = "tblToolRequisition"
        DGV.DataSource = DtSet.Tables("tblToolRequisition")

        For k = 1 To DGV.ColumnCount
            xlWorkSheet.Cells(1, k) = DGV.Columns(k - 1).HeaderText
        Next

        For i = 0 To DGV.RowCount - 2
            For j = 0 To DGV.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = DGV(j, i).Value.ToString()
            Next
        Next

        If My.Computer.FileSystem.DirectoryExists("C:\ITDEV\Excel export\ToolRequisition") Then
        Else
            My.Computer.FileSystem.CreateDirectory("C:\ITDEV\Excel export\ToolRequisition")
        End If

        If My.Computer.FileSystem.FileExists("C:\ITDEV\Excel export\ToolRequisition\ToolRequisition" & Format(Today, "MMddyyyy") & ".xlsx") Then

            My.Computer.FileSystem.DeleteFile("C:\ITDEV\Excel export\ToolRequisition\ToolRequisition" & Format(Today, "MMddyyyy") & ".xlsx")

            xlWorkSheet.SaveAs("C:\ITDEV\Excel export\ToolRequisition\ToolRequisition" & Format(Today, "MMddyyyy") & ".xlsx")
            xlWorkBook.Close()
            xlApp.Quit()

            ReleaseObject(xlApp)
            ReleaseObject(xlWorkBook)
            ReleaseObject(xlWorkSheet)

            Process.Start("C:\ITDEV\Excel export\ToolRequisition\ToolRequisition" & Format(Today, "MMddyyyy") & ".xlsx")

        Else
            xlWorkSheet.SaveAs("C:\ITDEV\Excel export\ToolRequisition\ToolRequisition" & Format(Today, "MMddyyyy") & ".xlsx")
            xlWorkBook.Close()
            xlApp.Quit()

            ReleaseObject(xlApp)
            ReleaseObject(xlWorkBook)
            ReleaseObject(xlWorkSheet)

            Process.Start("C:\ITDEV\Excel export\ToolRequisition\ToolRequisition" & Format(Today, "MMddyyyy") & ".xlsx")

        End If
    End Sub
Commenter la réponse de paradize99