?
Solved

Extract DataGrid to Excel based on dates

Posted on 2011-10-09
14
Medium Priority
?
431 Views
Last Modified: 2012-05-12
Hello all

I would like to extract from my DataGridView(sauvegarde) all rows, including the header, base on the column "Date_of_Day".

So in my form, i have 2: DateTimePicker1 and DateTimePicker2.

So it will extract from the datagrid everything that' equal / and in between the 2 dates.

Hpw can i do that?

Thanks again for your help.


  If ((sauvegarde.Columns.Count = 0) Or (sauvegarde.Rows.Count = 0)) Then
            Exit Sub
        End If

        '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

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

        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 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

       


        wSheet.Columns.AutoFit()
        Dim strFileName As String = "C:\extract.xls"
        Dim blnFileOpen As Boolean = False
        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
        End Try

        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

       

        wBook.SaveAs(strFileName)
        excel.Workbooks.Open(strFileName)
        excel.Visible = True

Open in new window

0
Comment
Question by:Wilder1626
  • 7
  • 7
14 Comments
 
LVL 15

Expert Comment

by:x77
ID: 36939140
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()

Open 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:
     http://www.experts-exchange.com/Database/Oracle/Q_24850271.html?sfQueryTermInfo=1+10+30+excel+x77
    http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_26162431.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.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36939205
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.
0
 
LVL 15

Expert Comment

by:x77
ID: 36939248
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:x77
ID: 36939283
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
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36939532
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()

Open in new window


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

Expert Comment

by:x77
ID: 36939574
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.



 
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36939777
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

Open in new window

date-format.jpg
0
 
LVL 15

Expert Comment

by:x77
ID: 36944285
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

0
 
LVL 15

Expert Comment

by:x77
ID: 36944350
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).
   
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36945875
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

Open in new window


But now, it jam at :

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

Open 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:



0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36973571
Hello all

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

Thanks
0
 
LVL 15

Accepted Solution

by:
x77 earned 2000 total points
ID: 36974055
If you have Dgv.AllowUserToAddRows = True then you need avoid EmptyRow

   For Each Row As DataGridViewRow In sauvegarde.Rows
         if row.IsNewRow then continue for

Also you can need verify Types you can send to excel.
You can send number, dates, string.
For any other type you need convert it to string.
You can try :

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

or:

     dim v = Row.Cells(j).Value
     if v is nothing orelse v is dbnull.value then continue for
     excel.Cells(K, j) =  v

Note that null.Value is not valid for excel.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36974774
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

  

Open in new window

0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 37164122
Thanks for all the help. have a great week-end
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question