Visual Basic.NET - Llenar Excel según la variable repetitiva en la columna del DTGV

Dim ColonaPaciente  As Integer

Dim ColonaFecha  As Integer

Dim ColonaValor  As Integer

Dim PrimeraLinea  As Integer

Dim CatidadLinea As Integer = 0

Dim ColonaValorXLS  As Integer

' Supongo tu imagen mostra la Rows(0) con las palabras Fecha, Paciente, NombreMedicion, …

For C As Integer = 0 To DataGridView1.Columns.Count-1

If DataGridView1.Item(C, 0).Value = "Fecha Recepcion Paciente" Then ColonaFecha = C

If DataGridView1.Item(C, 0).Value = "Paciente" Then ColonaPaciente = C

If DataGridView1.Item(C, 0).Value = "Valor" Then ColonaValor = C

Next C

For R As Integer = 0 To DataGridView1.Rows.Count-1

If DataGridView1.Item(ColonaPaciente, R).Value = «Alice» Then

If CantidadLinea = 0 Then

PrimeraLinea = R

End If

CantidadLiena +=1

End If

' … … …  … 

        XLSAPP = New Excel.Application

        XLSAPP.Visible = True

        XLSAPP.Workbooks.Open("ElArchivaXLS.xls")

        Dim ColXLS As Integer  ‘ Colona XLS, la primera es numero 1

        Dim LinXLS As Integer = 1  ‘ Linea XLS, la primera es numero 1

Dim Fecha As String

       	 For ColXLS = 1 To 31

            		Fecha = XLSAPP.Cells(LinXLS, ColXLS).Text

            		If Fecha = DataGridView1.Item(ColonaFecha, PrimeraLina).Value Then

                		ColonaValorXLS = ColXLS

                		Exit For

End If

Next

Dim LineaDGV As Integer = PrimeraLinea

For LinXLS = 2 To CantidadLiena – 1

	XLSAPP.Cells(LinXLS, ColonaValorXLS).Text =  DataGridView1.Item(ColonaValor, LineaDGV).Value

LineaDGV +=1

Next LinXLS

Imports System.Data

Imports System.Data.OleDb

Imports Microsoft.Office.Interop

Public Class Form1

    Dim cadena As New OleDbConnection

    Dim m_Excel As Excel.Application

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Try

            cadena.ConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0; Data Source=C:Desktop\Pruebavisu.accdb"

            cadena.Open()

            Mostrar()

            LLENAR()

            MsgBox("Conectado con la Base de Datos", vbCritical, "Aviso")

        Catch ex As Exception

            MsgBox("No se conceto con la Base de Datos", vbCritical, "Aviso")

        End Try

    End Sub

    Private Sub Mostrar()

        Dim oda As New OleDbDataAdapter

        Dim ods As New DataSet

        Dim consulta As String

        consulta = "Select *From Pacientes"

        oda = New OleDbDataAdapter(consulta, cadena)

        ods.Tables.Add("pacientes")

        oda.Fill(ods.Tables("Pacientes"))

        DataGridView1.DataSource = ods.Tables("Pacientes")

    End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim oda As New OleDbDataAdapter("SELECT * FROM Cavidades " &

        " Where FechaRecepcion BETWEEN #" & DateTimePicker1.Value.ToString("MM/dd/yyyy") &

        "# AND #" & DateTimePicker2.Value.ToString("MM/dd/yyyy") & "# AND NombreMedicion = '" & ComboBox1.Text & "' ORDER BY FechaRecepcion DESC, Column1 DESC", cadena)

        Dim ods As New DataSet

        oda.Fill(ods)

        DataGridView1.DataSource = ods.Tables(0)

    End Sub

 Private Sub LLENAR()

        Dim Da As New OleDbDataAdapter("SELECT distinct NombreMedicion From Pacientes", cadena)

        Dim DT As New DataTable

        Da.Fill(DT)

        ComboBox1.DataSource = DT

        ComboBox1.DisplayMember = "NombreMedicion"

        'ComboBox1.ValueMember = "ID"

    End Sub

 Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button3.Click

        Dim ColumnaPaciente As Integer

        Dim ColumnaFecha As Integer

        Dim ColumnaValor As Integer

        Dim PrimeraLinea As Integer

        Dim CantidadLinea As Integer = 0

        Dim ColumnaValorXLS As Integer

        For C As Integer = 0 To DataGridView1.Columns.Count - 1

            If DataGridView1.Item(C, 0).Value = "FechaRecepcion" Then ColumnaFecha = C

            If DataGridView1.Item(C, 0).Value = "Pacientes" Then ColumnaPaciente = C

            If DataGridView1.Item(C, 0).Value = "VALOR" Then ColumnaValor = C

        Next C

        For R As Integer = 0 To DataGridView1.Rows.Count - 1

            If DataGridView1.Item(ColumnaPaciente, R).Value = "Alice" Then

                If CantidadLinea = 0 Then

                    PrimeraLinea = R

                End If

                CantidadLinea += 1

            End If

        Next R

        m_Excel = New Excel.Application

        m_Excel.Visible = True

        m_Excel.Workbooks.Open("C:Desktop\Formato.xlsx")

        Dim ColXLS As Integer

        Dim LinXLS As Integer = 1

        Dim Fecha As String

            For ColXLS = 1 To 31

            Fecha = m_Excel.Cells(LinXLS, ColXLS).Text

            If Fecha = DataGridView1.Item(ColumnaFecha, PrimeraLinea).Value Then

                ColumnaValorXLS = ColXLS

                Exit For

            End If

        Next

            Dim LineaDGV As Integer = PrimeraLinea

        For LinXLS = 2 To CantidadLinea - 1

            m_Excel.Cells(LinXLS, ColumnaValorXLS).Text = DataGridView1.Item(ColumnaValorXLS, LineaDGV).Value

            LineaDGV += 1

        Next LinXLS

       End Sub

End Class

For C As Integer = 0 To DataGridView1.Columns.Count - 1

            'If DataGridView1.Item(C, 0).Value.ToString = "FechaRecepcion" Then ColumnaFecha = C

            'If DataGridView1.Item(C, 0).Value.ToString = "Pacientes" Then ColumnaPaciente = C

            'If DataGridView1.Item(C, 0).Value.ToString = "VALOR" Then ColumnaValor = C

            If DataGridView1.Columns(C).HeaderText = "FechaRecepcion" Then ColumnaFecha = C

            If DataGridView1.Columns(C).HeaderText = "Paciente" Then ColumnaPaciente = C

            If DataGridView1.Columns(C).HeaderText = "VALOR" Then ColumnaValor = C

 DataGridView1.Sort(DataGridView1.Columns(ColumnaPaciente), System.ComponentModel.ListSortDirection.Ascending)

        Next C

Imports System.Data

Imports System.Data.OleDb

Imports Microsoft.Office.Interop

Public Class Form1

    Dim cadena As New OleDbConnection

    Dim m_Excel As Excel.Application

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Try

            cadena.ConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0; Data Source=C:\Desktop\Pruebavisu.accdb"

            cadena.Open()

            Mostrar()

            LLENAR()

            MsgBox("Conectado con la Base de Datos", vbCritical, "Aviso")

        Catch ex As Exception

            MsgBox("No se conceto con la Base de Datos", vbCritical, "Aviso")

        End Try

    End Sub

    Private Sub Mostrar()

        Dim oda As New OleDbDataAdapter

        Dim ods As New DataSet

        Dim consulta As String

        consulta = "Select *From Pacientes"

        oda = New OleDbDataAdapter(consulta, cadena)

        ods.Tables.Add("Pacientes")

        oda.Fill(ods.Tables("Pacientes"))

        DataGridView1.DataSource = ods.Tables("Pacientes")

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim oda As New OleDbDataAdapter("SELECT * FROM Pacientes " &

        " Where FechaRecepcion BETWEEN #" & DateTimePicker1.Value.ToString("MM/dd/yyyy") &

        "# AND #" & DateTimePicker2.Value.ToString("MM/dd/yyyy") & "# AND NombreMedicion = '" & ComboBox1.Text & "' ORDER BY Paciente ASC, FechaRecepcion ASC, Column1 ASC", cadena)

        Dim ods As New DataSet

        oda.Fill(ods)

        DataGridView1.DataSource = ods.Tables(0)

    End Sub

    Private Sub LLENAR()

        Dim Da As New OleDbDataAdapter("SELECT distinct NombreMedicion From Pacientes", cadena)

        Dim DT As New DataTable

        Da.Fill(DT)

        ComboBox1.DataSource = DT

        ComboBox1.DisplayMember = "NombreMedicion"

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim ColumnaPaciente As Integer

        Dim ColumnaFecha As Integer

        Dim ColumnaValor As Integer

        Dim PrimeraLinea As Integer

        Dim CantidadLinea As Integer = 0

        Dim ColumnaValorXLS As Integer

        For C As Integer = 0 To DataGridView1.Columns.Count - 1

            If DataGridView1.Columns(C).HeaderText = "FechaRecepcion" Then ColumnaFecha = C

            If DataGridView1.Columns(C).HeaderText = "Paciente" Then ColumnaPaciente = C

            If DataGridView1.Columns(C).HeaderText = "VALOR" Then ColumnaValor = C

            DataGridView1.Sort(DataGridView1.Columns(ColumnaPaciente), System.ComponentModel.ListSortDirection.Ascending)

        Next C

        For R As Integer = 0 To DataGridView1.Rows.Count - 1

            If DataGridView1.Item(ColumnaPaciente, R).Value = "Alice" Then

                If CantidadLinea = 0 Then

                    PrimeraLinea = R

                End If

                CantidadLinea += 1

            End If

        Next R

        m_Excel = New Excel.Application

        m_Excel.Visible = True

        m_Excel.Workbooks.Open("\Desktop\Formato.xlsx")

        Dim ColXLS As Integer

        Dim LinXLS As Integer = 1

        Dim Fecha As String

        For ColXLS = 1 To 31

            Fecha = m_Excel.Cells(LinXLS, ColXLS).Text

            If Fecha = DataGridView1.Item(ColumnaFecha, PrimeraLinea).Value Then

                ColumnaValorXLS = ColXLS

                Exit For

            End If

        Next

        Dim LineaDGV As Integer = PrimeraLinea

        For LinXLS = 2 To CantidadLinea - 1

            m_Excel.Cells(LinXLS, ColumnaValorXLS).Text = DataGridView1.Item(ColumnaValorXLS, LineaDGV).Value

            LineaDGV += 1

        Next LinXLS

    End Sub

End Class

        ' Cambiar fecha americana ==> fecha euopena  (como en XLS)

        Dim Mese As String

        Dim Dia As String

        Dim Ano As String

        Dim Sep As Integer

        For R As Integer = 0 To DataGridView1.Rows.Count - 1

            Fecha = DataGridView1.Item(ColumnaFecha, R).Value

            Sep = Fecha.IndexOf("/")

            Mese = Fecha.Substring(0, Sep)

            Dia = Fecha.Substring(Sep + 1, Fecha.LastIndexOf("/") - Sep - 1)

            Ano = Fecha.Substring(Fecha.LastIndexOf("/"))

            DataGridView1.Item(ColumnaFecha, R).Value = Dia & "/" & String.Format("{0:00}", CType(Mese, Integer)) & "/" & Ano

        Next R

Public Class Form1

    Dim cadena As New System.Data.OleDb.OleDbConnection

    Dim m_Excel As New Microsoft.Office.Interop.Excel.Application

    Dim FechaPrimera As Date

    Dim FechaUltima As Date

...

m_Excel.Cells.Select()

        m_Excel.Selection.ClearContents()

        m_Excel.Cells(1, 1).Select()

        m_Excel.Cells(1, 1) = "Date"

        m_Excel.Rows("1:1").Select()

        m_Excel.Selection.NumberFormat = "@"

For ColXLS = 2 To 200

                    Fecha = m_Excel.Cells(1, ColXLS).Text

                    If Fecha = DataGridView1.Item(ColumnaFecha, LineaFechaXLS).Value Then

                        ColumnaValorXLS = ColXLS   ' Error viene mas tarde si no encontrar Fecha aqui, ColumnaValorXLS  es malo

                        Exit For

                    End If

            Next

While PreparFecha(FechaDate, 1) <= PreparFecha(FechaUltima, 1)  ' escribir las fechas a la primera linea XLS

            m_Excel.Cells(1, ColXLS) = FechaDate

            ColXLS += 1

            FechaDate = AgregarUnDia(FechaDate)   ' Agregar 1 dia a FechaDate en el formato DD/MM/YYYY y do el resultado en el mismo formato

            'FechaDate = CType(FechaDate, DateTime).AddDays(1).ToString("dd/MM/yyyy")  ' No conviene para que es relacionado con los parametros regionales

        End While

Private Sub LeerDBOrigenYCargarDBAccess()

        Dim cadenaSQL As New SqlConnection

        Dim MiComandoSQL As New SqlCommand

        Dim ComandoSQL As String

        Dim cadenaAccess As New OleDbConnection

        Dim MiComandoAccess As New OleDbCommand

        Dim ComandoAccess As String

        Dim MiDatos As SqlDataReader

        Dim FraseDeConexionSQL As String = "Data Source=" & NombreServidor & "; AttachDbFilename=" & NombreBDCompleto & ";Integrated Security=True"

        Dim FraseDeConexionAccess As String = "Provider=Microsoft.ACE.OLEDB.16.0; Data Source=" & "Pruebavisu.accdb"

        cadenaSQL.ConnectionString = FraseDeConnexionSQL

        cadenaSQL.Open()

        cadenaAccess.ConnectionString = FraseDeConnexionAccess

        cadenaAccess.Open()

        MiComandoSQL = cadenaSQL.CreateCommand()

        MiComandoSQL.CommandType = CommandType.Text

        MiComandoSQL.CommandText = "SELECT ID, FechaRecepcion, Paciente, Column1, Valor, y todo te gusta ... FROM Pacientes  WHERE ... si necesario ..."

        MiComandoAccess = cadenaAccess.CreateCommand()

        MiComandoAccess.CommandType = CommandType.Text

        MiDatos = MiComandoSQL.ExecuteReader()

        Do While MiDatos.Read

            MiComandoAccess.CommandText = "INSERT INTO ID, FechaRecepcion, Paciente, Column1, Valor VALUE (" & MiDatos.Item(0) & "," &  MiDatos.Item(1) & ", ... y todo la cointinuacion ... )"

            MiComandoAccess.ExecuteNonQuery()

        Loop

        MiDatos.Close()

        cadenaSQL.Dispose()

        MiComandoSQL.Dispose()

        cadenaAccess.Dispose()

        MiComandoAccess.Dispose()

    End Sub

                '       If Fecha = DataGridView1.Item(ColumnaFecha, LineaFechaXLS).Value Then        ''' ERROR !!!

                If Fecha = DataGridView1.Item(ColumnaFecha, LineaDGV).Value Then     ' Buena versión