Click here to Skip to main content
15,940,246 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
            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
                        .cells(excelRowIndex, j + 1) = row.Cells(j).Value
                    End If
                excelRowIndex += 1
                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
                    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 = ExcelSheet.Range("a1", "V1")
            End With

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

    End Sub

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

What I have tried:

Searching forum and web browsers
Maciej Los 28-May-17 17:03pm    
It's nothing hard to implement. Use your logic!
caf20012 28-May-17 17:08pm    
Any guidance? I'm fairly new to so would like any support
caf20012 29-May-17 4:58am    
I've been suggested to use the following code from ; i'm struggling to convert from c# 2005 to 2013. Can anyone help!?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900