Datagridview to excel - vertical export

See more:

VB

DataGridView

Hi All,

Currently i have a datagridview with multiple columns and various headers in which data populates from either combobox's, textbox's etc.. underneath the header. When clicking the 'button' export the data is showing horizontally in excel and i want it to show vertical with the headers in column 'A' and answers beside them in column 'B'. Below is the code exporting the data;

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        DataGridView1.RowTemplate.Height = 120
        DataGridView1.AllowUserToAddRows = False
        DataGridView1.DataSource = table

        table.Rows.Add(Form4.TextBox1.Text, Form4.TextBox2.Text, Form4.TextBox3.Text, Form4.TextBox4.Text, Form4.TextBox5.Text, Form4.TextBox6.Text, Form4.TextBox7.Text, Form4.DateTimePicker2.Text, Form4.Gender.ToString, Form4.RichTextBox1.Text, Form4.RichTextBox2.Text, ComboBox1.Text, ProcedureType.ToString, RichTextBox1.Text, FractureType.ToString, ComboBox2.Text, ComboBox3.Text, ComboBox4.Text, Debridement.ToString, ComboBox5.Text, Fixation.ToString, ComboBox6.Text)

        'Save to excel with headers
        Dim ExcelApp As Object, ExcelBook As Object
        Dim ExcelSheet As Object
        Dim j As Integer
        Dim PicFile As String = IO.Path.Combine(Application.StartupPath, "Image.jpg")
        Dim Proceed As Boolean = False
        Form4.PictureBox1.Image.Save(PicFile, System.Drawing.Imaging.ImageFormat.Jpeg)



        'create object of exce
        ExcelApp = CreateObject("Excel.Application")
        ExcelBook = ExcelApp.WorkBooks.Add
        ExcelSheet = ExcelBook.WorkSheets(1)

        With ExcelSheet
            For Each column As DataGridViewColumn In DataGridView1.Columns
                .cells(1, column.Index + 1) = column.HeaderText
            Next
            Dim excelRowIndex = 2
            Dim picX = 50
            Dim picY = 50
            For Each row As DataGridViewRow In DataGridView1.Rows
                For j = 0 To DataGridView1.Columns.Count - 1
                    If (j = 0) Then
                        .Shapes.AddPicture(PicFile, Microsoft.Office.Core.MsoTriState.msoFalse,
                                           Microsoft.Office.Core.MsoTriState.msoCTrue, picX, picY, 200, 100)
                        picX += 10
                        picY += 10
                    Else
                        .cells(excelRowIndex, j + 1) = row.Cells(j).Value
                    End If
                Next
                excelRowIndex += 1
            Next
                For i = 1 To Me.DataGridView1.RowCount
                    .cells(i + 1, 1) = Me.DataGridView1.Rows(i - 1).Cells("Surgeon").Value
                    For j = 1 To DataGridView1.Columns.Count - 1
                        .cells(i + 1, j + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
                    Next
                    Dim formatRange As Excel.Range
                    formatRange = ExcelSheet.Range("a1")
                    formatRange.EntireRow.Font.Bold = True
                    formatRange = ExcelSheet.Range("A1", "V1")
                    formatRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue)
                    formatRange.BorderAround(Excel.XlLineStyle.xlContinuous)
                    formatRange = ExcelSheet.Range("a1", "V1")
                    formatRange.EntireRow.BorderAround()
                Next
            End With

            ExcelApp.Visible = True
            '
            ExcelSheet = Nothing
            ExcelBook = Nothing
            ExcelApp = Nothing

            Application.Exit()
            End
    End Sub

Any suggestions how i can change this format? so it shows vertically?

What I have tried:

Searching forum and web browsers