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