Extract DataGrid to Excel based on dates

1 - You don´t says where is your data. Oracle Db , Serialized file, SqlsServer ?
     You create a Dataset - You don´t need dataset.
     You can create Dt Table, then Fill it and work with it.

    Sample to fill a data Table with OleDb.

Dim cmd As New OleDbCommand("SELECT * from YourTable Where YourDateField between ? and ?", CnDb)
    Dim pp = cmd.Parameters
    pp.AddWithValue(Nothing, DatePicker1.value) 
    pp.AddWithValue(Nothing, DatePicker2.Value)

    Dim Dt As New DataTable, Da As New OleDbDataAdapter(cmd)
    Da.Fill(Dt)
    Da.Dispose()
    cmd.Dispose()

Select allOpen in new window

CnDb - Your AdoDb connection to Data Base.

2 - For Fast Process, build as two dimension array and fill it with Dt columns (first row), then Dt Values.
     Dim Datos(Dt.Columns -1, Dt.Rows) as object
     some samples:
     https://www.experts-exchange.com/questions/24850271/Oracle-To-Excel.html?sfQueryTermInfo=1+10+30+excel+x77
    https://www.experts-exchange.com/questions/26162431/Fast-export-to-excel.html?sfQueryTermInfo=1+10+30+excel+fast+x77 

3 - To use AutoFit, you need set each excel column to a big size to avoid wrap column on load.

Hello x77

My Gridd is auto populated from a Microsoft Access dBase.

So know, i just need to be able to extract based on the dates in column 2.

If is equal or in between my 2 datepicker, then it will extract in Excel.

For now, with the code i have, everything extract.

You can filter your Dgv DataSource, then export Dgv Data.

By sample, asume you are populating Dgv with BindingSource Bs, Then Set Bs.Filter.
For a DataView DataSource, you can set Dv.RowFilter  

     Bs.Filter = "YourDateCol >= #" &  DtPicker1.Value.ToString ("MM/dd/yyyy") &  _
                "# and YourDateCol <= #" &  DtPicker2.Value.ToString ("MM/dd/yyyy")  & "#"

Note I use Month, Day,Year format with "#" as I use System.Globalization.CultureInfo.InvariantCulture format.

You can use your current method to export to Excel, adding a  condition on your code:


       For each Row as DataGridviewRow in sauvegarde.Rows
--> Add Condition here:
           If bFilter then
              Dim DateValue = Row.Cells(2).value
              if DateValue is DbNull.Value orelse DateValue is nothing then continue for
              Dim v = DirectCast(DateValue,Date)
              If  v < DtPicker1.Value orelse v > DtPicker2.Value then Continue For.  
           end if

Hello,

I tried to use your code like this:

   Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each Row As DataGridViewRow In sauvegarde.Rows

            Dim DateValue = Row.Cells(1).Value
            If DateValue Is DBNull.Value OrElse DateValue Is Nothing Then Continue For
            Dim v = DirectCast(DateValue, Date)
            If v >= DateTimePicker1.Value OrElse v <= DateTimePicker2.Value Then Continue For


            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(1, colIndex) = dc.ColumnName
            Next

            For Each dr In dt.Rows
                rowIndex = rowIndex + 1
                colIndex = 0
                For Each dc In dt.Columns
                    colIndex = colIndex + 1
                    excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)


                Next
            Next
        Next



        wSheet.Columns.AutoFit()

Select allOpen in new window

But now, there is no error, the excel file open, but with no data in.

Your are using :

   If v >= DateTimePicker1.Value OrElse v <= DateTimePicker2.Value Then Continue For

  I Suspect you want rows between  and DateTimePicker2

  then you can use:
   
     If Not ( v >= DateTimePicker1.Value AndAlso v <= DateTimePicker2.Value) Then Continue For

Note that  "Continue for"  is for no selected rows.

Boolean reduction :

      If v < DateTimePicker1.Value  OrElse v > DateTimePicker2.Value  Then Continue For

I Know most people don´t understand Boolean transformations.

Then use      Not ( Expression  )    as it is easy to understand.



 

Wow,

Now, everything transfer to excel, even the ones that does not respect the date picker.

I'm adding how the date show in my grid.

  'Creating dataset to export
        Dim dset As New DataSet
        'add table to dataset
        dset.Tables.Add()
        'add column to that table
        For i As Integer = 0 To sauvegarde.ColumnCount - 1
            dset.Tables(0).Columns.Add(sauvegarde.Columns(i).HeaderText)
        Next
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To sauvegarde.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To sauvegarde.Columns.Count - 1
                dr1(j) = sauvegarde.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next

        Dim excel As New Excel.Application
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim excelRng As Microsoft.Office.Interop.Excel.Range
        Dim excelRng1 As Microsoft.Office.Interop.Excel.Range
        Dim excelRng2 As Microsoft.Office.Interop.Excel.Range


        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        'Header format
        excelRng1 = wSheet.Range("A1:U1")
        excelRng1.Font.Bold = True
        excelRng1.Interior.Color = 3
        excelRng1.Font.Color = Color.White

        'FreezPane
        excelRng2 = wSheet.Range("D2")
        'excelRng2.Application.ActiveWindow.FreezePanes = True


        excelRng = wSheet.Range("A:Z")
        excelRng.HorizontalAlignment = HorizontalAlignment.Center

        Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each Row As DataGridViewRow In sauvegarde.Rows

            Dim DateValue = Row.Cells(1).Value
            If DateValue Is DBNull.Value OrElse DateValue Is Nothing Then Continue For
            Dim v = DirectCast(DateValue, Date)
            ' If v < Format(DateTimePicker1, "yyyy-mm-dd") OrElse v > Format(DateTimePicker2, "yyyy-mm-dd") Then Continue For
            If v < DateTimePicker1.Value OrElse v > DateTimePicker2.Value Then Continue For


            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(1, colIndex) = dc.ColumnName
            Next

            For Each dr In dt.Rows
                rowIndex = rowIndex + 1
                colIndex = 0
                For Each dc In dt.Columns
                    colIndex = colIndex + 1
                    excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)


                Next
            Next

        Next

Select allOpen in new window

date-format.jpg

Yo do a unnecesary copy from Dgv to a new DataTable.
I sugest you can do export from you Dgv.

But you maintains you Copy To Table

Then " For Each Row As DataGridViewRow In sauvegarde.Rows" don´t Apply

          For Each dr In dt.Rows          
             Dim DateValue = dr(1)
             If DateValue Is DBNull.Value OrElse DateValue Is Nothing Then Continue For
             Dim v = DirectCast(DateValue, Date)
             If v < DateTimePicker1.Value OrElse v > DateTimePicker2.Value Then Continue For
         
              rowIndex = rowIndex + 1
              colIndex = 0
              For Each dc In dt.Columns
                  colIndex = colIndex + 1
                  excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
              Next
          Next


By sample, to export to excel From Dgv:

        Dim K=1 'First Excel Row to add
        For Each Row As DataGridViewRow In sauvegarde.Rows
            Dim DateValue = row.Cells(1).value
            If DateValue Is DBNull.Value OrElse DateValue Is Nothing Then Continue For
            Dim v = DirectCast(DateValue, Date)
            If v < DateTimePicker1.Value OrElse v > DateTimePicker2.Value Then Continue For

            For j As Integer = 0 To sauvegarde.Columns.Count - 1
                excel.Cells(k, j) = Row.Cells(j).Value
            Next
            K +=1 'Next Excel Row
        Next

Note about performance on DataTables.

You can get value from a DataRow and Datacolumn with:

          DataRow(ColumnIndex)
          DataRow(ColumnName)
          DataRow(DataColumn)

The 3ª option is fastest, first an secon option call the 3ª option.
Second option is slowest as it uses a Dictionary to get DataColumn and call  DataRow(DataColumn).
   

Hello x77

I have switch now to:

Dim excel As New Excel.Application
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim excelRng As Microsoft.Office.Interop.Excel.Range
        Dim excelRng1 As Microsoft.Office.Interop.Excel.Range
        Dim excelRng2 As Microsoft.Office.Interop.Excel.Range


        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        'Header format
        excelRng1 = wSheet.Range("A1:U1")
        excelRng1.Font.Bold = True
        excelRng1.Interior.Color = 3
        excelRng1.Font.Color = Color.White

        'FreezPane
        excelRng2 = wSheet.Range("D2")
        'excelRng2.Application.ActiveWindow.FreezePanes = True


        excelRng = wSheet.Range("A:Z")
        excelRng.HorizontalAlignment = HorizontalAlignment.Center

        Dim dt As System.Data.DataTable = dset.Tables(0)

        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0





        Dim K = 1 'First Excel Row to add
        For Each Row As DataGridViewRow In sauvegarde.Rows
            Dim DateValue = Row.Cells(1).Value
            If DateValue Is DBNull.Value OrElse DateValue Is Nothing Then Continue For
            Dim v = DirectCast(DateValue, Date)
            If v < DateTimePicker1.Value OrElse v > DateTimePicker2.Value Then Continue For

            For j As Integer = 0 To sauvegarde.Columns.Count - 1
                excel.Cells(K, j) = Row.Cells(j).Value
            Next
            K += 1 'Next Excel Row
        Next

Select allOpen in new window

But now, it jam at :

excel.Cells(K, j) = Row.Cells(j).Value

Select allOpen in new window

Exception from HRESULT: 0x800A03EC  COM Exeption was unhandled.

System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2146827284
  Message=Exception from HRESULT: 0x800A03EC
  Source=""
  StackTrace:
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.Range.set__Default(Object RowIndex, Object ColumnIndex, Object Param)
       at Le_vignoble.Form3.btnButton2_Click(Object sender, EventArgs e)
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at Le_vignoble.My.MyApplication.Main(String[] Args)
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at System.Activator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

Hello all

Coming back on this topic if somebody can help me on this.

Thanks

Hello x77

I have tried like you said but still block at the same place: excel.Cells(K, j) = Row.Cells(j).FormattedValue

 Dim dt As System.Data.DataTable = dset.Tables(0)

        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0





        Dim K = 1 'First Excel Row to add
        For Each Row As DataGridViewRow In sauvegarde.Rows
            If Row.IsNewRow Then Continue For
            Dim DateValue = Row.Cells(1).Value
            If DateValue Is DBNull.Value OrElse DateValue Is Nothing Then Continue For
            Dim v = DirectCast(DateValue, Date)
            If v < DateTimePicker1.Value OrElse v > DateTimePicker2.Value Then Continue For

            For j As Integer = 0 To sauvegarde.Columns.Count - 1
                excel.Cells(K, j) = Row.Cells(j).FormattedValue
            Next
            K += 1 'Next Excel Row
        Next

  

Select allOpen in new window

Thanks for all the help. have a great week-end