none
Vb.net - Datagridview vertical headers row's / columns RRS feed

  • Question

  • The following code isn't working when exporting the datagridview data when trying to make it vertical with headers along the left side along with text beside each one. Once this is flipped the user would click on button1 to export to excel.

    Imports System.Data.DataTable
    Imports System.IO
    Imports Microsoft.Office.Interop
    Public Class Form1
    Dim table As New DataTable(0)
    Public checkBoxList As List(Of CheckBox)
    Private ds As DataSet = Nothing
    Private dt As DataTable = Nothing
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ds = New DataSet()
        dt = New DataTable()
        ds.Tables.Add("Table")
        Dim my_DataView As DataView = ds.Tables(0).DefaultView
        DataGridView1.DataSource = my_DataView
        table.Columns.Add("Forename", Type.GetType("System.String"))
        table.Columns.Add("Surname", Type.GetType("System.String"))
        table.Columns.Add("Food", Type.GetType("System.String"))
        checkBoxList = New List(Of CheckBox) From {CheckBox1, CheckBox2, CheckBox3, CheckBox4}
    
    
    End Sub
    
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
        Dim currentDataSet As DataSet = FlipDataSet(ds) ' Flip the DataSet
        Dim values As String = "" &
        String.Join(" & ", checkBoxList _
        .Where(Function(cb) cb.Checked).Select(Function(cb) cb.Text))
    
        ' use values for placing into your DataGridView
        CheckBox1.Text = values
        CheckBox2.Text = values
        CheckBox3.Text = values
        CheckBox4.Text = values
    
    
        table.Rows.Add(TextBox1.Text, TextBox2.Text, values.ToString)
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        DataGridView1.RowTemplate.Height = 100
        DataGridView1.AllowUserToAddRows = False
        DataGridView1.DataSource = table
    
        'Save to excel with headers
        Dim ExcelApp As Object, ExcelBook As Object
        Dim ExcelSheet As Object
        Dim i As Integer
        Dim j As Integer
    
        'create object of excel
        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
            For i = 1 To Me.DataGridView1.RowCount
                .cells(i + 1, 1) = Me.DataGridView1.Rows(i - 1).Cells("Forename").Value
                For j = 1 To DataGridView1.Columns.Count - 1
                    .cells(i + 1, j + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
                Next
            Next
    
        End With
    
        ExcelApp.Visible = True
        '
        ExcelSheet = Nothing
        ExcelBook = Nothing
        ExcelApp = Nothing
    End Sub
    
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
    
    End Sub
    Public Function FlipDataSet(ByVal my_DataSet As DataSet) As DataSet
        Dim ds As New DataSet()
    
        For Each dt As DataTable In my_DataSet.Tables
            Dim table As New DataTable()
    
            For i As Integer = 0 To dt.Rows.Count
                table.Columns.Add(Convert.ToString(i))
            Next
            Dim r As DataRow
            For k As Integer = 0 To dt.Columns.Count - 1
                r = table.NewRow()
                r(0) = dt.Columns(k).ToString()
                For j As Integer = 1 To dt.Rows.Count
                    r(j) = dt.Rows(j - 1)(k)
                Next
                table.Rows.Add(r)
            Next
    
            ds.Tables.Add(table)
        Next
    
        Return ds
    End Function
    
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim currentDataSet As DataSet = FlipDataSet(ds) ' Flip the DataSet
        Dim currentDataView As DataView = currentDataSet.Tables(0).DefaultView
        DataGridView1.DataSource = currentDataView
    
        Button2.Enabled = False
    
    End Sub
    End Class

    When clicking button2 it should flip the data with the following;

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles 
        Button2.Click
        Dim currentDataSet As DataSet = FlipDataSet(ds) ' Flip the DataSet
        Dim currentDataView As DataView = currentDataSet.Tables(0).DefaultView
        DataGridView1.DataSource = currentDataView
    
        Button2.Enabled = False
    
        End Sub
        End Class

    I've tried debugging but it i can't seem to find anything wrong? It will allow me to insert data in the textbox's whilst selecting checkbox's and when clicking button 1 to export it works fine, but it doesn't flip the data.

    Please can anyone suggest how to fix this as i have a presentation on the 8th June and this data needs to automatically be flipped

    Sourcecode: My Project

    Sunday, June 4, 2017 6:12 PM

Answers

  •  Is there some reason behind flipping the whole table so that the columns are rows and the rows are columns in the excel file?  It seems like it would make more sense to just insert the column names into the first row of the excel table and then just add the rows as they are.

     If you really need to do all the extra work to flip everything,  then maybe this will help.  I tried to duplicate what it appears you are trying to do and going by how you said you wanted it to work.  So,  this is just an example but,  it does flip the table as it is added to the excel sheet.

     You can give it a try if you like.

    Imports Microsoft.Office.Interop
    Imports System.Runtime.InteropServices
    
    Public Class Form1
        Private ds As DataSet = Nothing
        Private dt As DataTable = Nothing
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DataGridView1.AllowUserToAddRows = False
    
            dt = New DataTable("MyTable")
            dt.Columns.Add("Forename", Type.GetType("System.String"))
            dt.Columns.Add("Surname", Type.GetType("System.String"))
            dt.Columns.Add("Food", Type.GetType("System.String"))
    
            ds = New DataSet
            ds.Tables.Add(dt)
    
            Dim my_DataView As DataView = ds.Tables("MyTable").DefaultView
            DataGridView1.DataSource = my_DataView
        End Sub
    
        Private Sub Button_AddRowData_Click(sender As Object, e As EventArgs) Handles Button_AddRowData.Click
            Dim foods As String = String.Join(" & ", CheckedListBox1.CheckedItems.Cast(Of String))
            dt.Rows.Add(New Object() {TextBox_Forename.Text, TextBox_Surname.Text, foods})
        End Sub
    
        Private Sub Button_FlipAndSave_Click(sender As Object, e As EventArgs) Handles Button_FlipAndSave.Click
            FlipAndSave(ds.Tables("MyTable"))
        End Sub
    
        Private Sub FlipAndSave(table As DataTable)
            Dim ExcelApp As New Excel.Application
            Dim WrkBk As Excel.Workbook = ExcelApp.Workbooks.Add()
            Dim WrkSht As Excel.Worksheet = CType(WrkBk.Worksheets(1), Excel.Worksheet)
    
            With WrkSht
                For ci As Integer = 0 To table.Columns.Count - 1
                    .Cells(ci + 1, 1) = table.Columns(ci).ColumnName
                Next
                For ri As Integer = 0 To table.Rows.Count - 1
                    For ci As Integer = 0 To table.Columns.Count - 1
                        .Cells(ci + 1, ri + 2) = table.Rows(ri).Item(ci).ToString
                    Next
                Next
            End With
    
            ExcelApp.Visible = True
    
            'use this lines if you want to automatically save the WorkBook
            'WrkBk.SaveAs("C:\Some Folder\My Workbook.xlsx") '(.xls) if you have an old version of Excel
    
            'ExcelApp.Quit() 'use this line if you want to close the Excel Application
    
            ReleaseObject(ExcelApp)
            ReleaseObject(WrkBk)
            ReleaseObject(WrkSht)
        End Sub
    
        Private Sub ReleaseObject(obj As Object)
            Marshal.ReleaseComObject(obj)
            obj = Nothing
        End Sub
    End Class
    
     

     Here is what my test Form with a few rows added looks like...

     

     Here is what the excel sheet looks like when you click the "Flip-N-Save" button.  Notice the data is flipped.


    If you say it can`t be done then i`ll try it

    • Marked as answer by caf20012 Monday, June 5, 2017 4:38 PM
    Monday, June 5, 2017 12:33 AM

All replies

  •  Is there some reason behind flipping the whole table so that the columns are rows and the rows are columns in the excel file?  It seems like it would make more sense to just insert the column names into the first row of the excel table and then just add the rows as they are.

     If you really need to do all the extra work to flip everything,  then maybe this will help.  I tried to duplicate what it appears you are trying to do and going by how you said you wanted it to work.  So,  this is just an example but,  it does flip the table as it is added to the excel sheet.

     You can give it a try if you like.

    Imports Microsoft.Office.Interop
    Imports System.Runtime.InteropServices
    
    Public Class Form1
        Private ds As DataSet = Nothing
        Private dt As DataTable = Nothing
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DataGridView1.AllowUserToAddRows = False
    
            dt = New DataTable("MyTable")
            dt.Columns.Add("Forename", Type.GetType("System.String"))
            dt.Columns.Add("Surname", Type.GetType("System.String"))
            dt.Columns.Add("Food", Type.GetType("System.String"))
    
            ds = New DataSet
            ds.Tables.Add(dt)
    
            Dim my_DataView As DataView = ds.Tables("MyTable").DefaultView
            DataGridView1.DataSource = my_DataView
        End Sub
    
        Private Sub Button_AddRowData_Click(sender As Object, e As EventArgs) Handles Button_AddRowData.Click
            Dim foods As String = String.Join(" & ", CheckedListBox1.CheckedItems.Cast(Of String))
            dt.Rows.Add(New Object() {TextBox_Forename.Text, TextBox_Surname.Text, foods})
        End Sub
    
        Private Sub Button_FlipAndSave_Click(sender As Object, e As EventArgs) Handles Button_FlipAndSave.Click
            FlipAndSave(ds.Tables("MyTable"))
        End Sub
    
        Private Sub FlipAndSave(table As DataTable)
            Dim ExcelApp As New Excel.Application
            Dim WrkBk As Excel.Workbook = ExcelApp.Workbooks.Add()
            Dim WrkSht As Excel.Worksheet = CType(WrkBk.Worksheets(1), Excel.Worksheet)
    
            With WrkSht
                For ci As Integer = 0 To table.Columns.Count - 1
                    .Cells(ci + 1, 1) = table.Columns(ci).ColumnName
                Next
                For ri As Integer = 0 To table.Rows.Count - 1
                    For ci As Integer = 0 To table.Columns.Count - 1
                        .Cells(ci + 1, ri + 2) = table.Rows(ri).Item(ci).ToString
                    Next
                Next
            End With
    
            ExcelApp.Visible = True
    
            'use this lines if you want to automatically save the WorkBook
            'WrkBk.SaveAs("C:\Some Folder\My Workbook.xlsx") '(.xls) if you have an old version of Excel
    
            'ExcelApp.Quit() 'use this line if you want to close the Excel Application
    
            ReleaseObject(ExcelApp)
            ReleaseObject(WrkBk)
            ReleaseObject(WrkSht)
        End Sub
    
        Private Sub ReleaseObject(obj As Object)
            Marshal.ReleaseComObject(obj)
            obj = Nothing
        End Sub
    End Class
    
     

     Here is what my test Form with a few rows added looks like...

     

     Here is what the excel sheet looks like when you click the "Flip-N-Save" button.  Notice the data is flipped.


    If you say it can`t be done then i`ll try it

    • Marked as answer by caf20012 Monday, June 5, 2017 4:38 PM
    Monday, June 5, 2017 12:33 AM
  • Hi @Ironrazers,

    Thank you for the response! I will try this tonight once i have access to my application and if it works i will be highly grateful as i've been having this problem for somedays now . I Unfortunately have to flip the whole data as it is copied from Excel Format to an external application so it looks presentable. 

    Separately, i do like some of the words you have used in your report =)

    Monday, June 5, 2017 10:02 AM
  • Hi @Ironrazers,

    Thank you for the response! ......  Separately, i do like some of the words you have used in your report =)


     You`re welcome.   Haha,  yes i like to keep things on the fun side.  8)

    If you say it can`t be done then i`ll try it

    Monday, June 5, 2017 4:54 PM