vb.net - POPULATION OF TABLE FROM WINDOWS FORM

Question

I am done with designing the windows form::

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ComboBox2.Visible = False
        Label2.Visible = False
        ComboBox3.Visible = False
        Label3.Visible = False
        ComboBox4.Visible = False
        Label4.Visible = False
        ComboBox5.Visible = False
        Label5.Visible = False
        DateTimePicker1.Visible = False
End Sub

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

        If (ComboBox1.Text = "BY-ELECTION") Then
            ComboBox2.Visible = True
            Label2.Visible = True
            DateTimePicker1.Visible = False
        ElseIf (ComboBox1.Text = "GENERAL ELECTION") Then
            ComboBox2.Visible = False
            Label2.Visible = False
            ComboBox3.Visible = False
            Label3.Visible = False
            ComboBox4.Visible = False
            Label4.Visible = False
            ComboBox5.Visible = False
            Label5.Visible = False
            DateTimePicker1.Visible = True
        End If

    End Sub

Private Sub ComboBox4_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox4.SelectedIndexChanged

        If ComboBox4.SelectedValue = "" And ComboBox2.Text = "MP" Then
            DateTimePicker1.Visible = True
        End If

        If (ComboBox4.Text = "MVITA") And ComboBox2.Text = "MCA" Then
            Label5.Visible = True
            ComboBox5.Visible = True
            ComboBox5.Items.Clear()
            ComboBox5.Items.Add("MAJENGO")
            ComboBox5.Items.Add("MAKADARA")
            ComboBox5.Items.Add("SHIMANZI")
            ComboBox5.Items.Add("TONONOKA")
            ComboBox5.Items.Add("TUDOR")
            DateTimePicker1.Visible = False
        End If

    End Sub

Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox3.SelectedIndexChanged
       
If (ComboBox3.Text = "MOMBASA") And ComboBox2.Text = "MP" Or (ComboBox3.Text = "MOMBASA") And ComboBox2.Text = "MCA" Then
            Label4.Visible = True
            ComboBox4.Visible = True
            ComboBox4.Items.Clear()
            ComboBox4.Items.Add("CHANGAMWE")
            ComboBox4.Items.Add("JOMVU")
            ComboBox4.Items.Add("KISAUNI")
            ComboBox4.Items.Add("LIKONI")
            ComboBox4.Items.Add("MVITA")
            ComboBox4.Items.Add("NYALI")
End If
        
End Sub

Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged
        
If ComboBox2.Text = "GOVERNORSHIP" Or ComboBox2.Text = "SENATOR" Then
            Label3.Visible = True
            ComboBox3.Visible = True
            Label5.Visible = False
            ComboBox5.Visible = False
            DateTimePicker1.Visible = True
            ComboBox4.Visible = False
            Label4.Visible = False
ElseIf ComboBox2.Text = "MP" Or ComboBox2.Text = "MCA" Then
            Label3.Visible = True
            ComboBox3.Visible = True
            DateTimePicker1.Visible = False
            Label6.Visible = False
            ComboBox4.Visible = False
            Label4.Visible = False
            ComboBox5.Visible = False
            Label5.Visible = False
End If

End Sub

Private Sub ComboBox5_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox5.SelectedIndexChanged
        Label6.Visible = True
        DateTimePicker1.Visible = True
End Sub

and i am stuck at the command button.

My windows form has 5 combobox with items assigned to them but in this illustration i am going to use one item for example:

• Combobox2 has item MCA
• Combobox3(labeled COUNTY) has item MOMBASA
• Combobox4(labeled CONSTITUENCY) has item MVITA
• Combobox5(labeled WARD) has item SHIMANZI

I want if i select item MCA in combobox2 and click the button GENERATE a table:

will be created and saved in the path C:UsersAdministratorDocuments and populated in the following format

• cell A2 = item selected in combobox3
• cell B2= item selected in combobox4
• cell C2=item selected in combobox5
• cell H2 to be populated with random input be based on gender(male/female) and letters(ABCD only) in that order. ie cell H2= MALE,D

question from:https://stackoverflow.com/questions/65871530/population-of-table-from-windows-form

Answer 1

You can refer to the following code to generate DataTable.

    Dim dt As DataTable = New DataTable
    dt.Columns.Add("COUNTY")
    dt.Columns.Add("CONSTITUENCY")
    dt.Columns.Add("WARD")
    dt.Rows.Add(ComboBox3.SelectedItem.ToString, ComboBox4.SelectedItem.ToString, ComboBox5.SelectedItem.ToString)

Then use the following code to export the data table to Excel:

    Dim excelFilePath As String = "excel path"
    Dim excelApp = New Excel.Application()
    excelApp.Workbooks.Add()
    Dim workSheet As Excel._Worksheet = excelApp.ActiveSheet

    For i = 0 To dt.Columns.Count - 1
        workSheet.Cells(1, i + 1) = dt.Columns(i).ColumnName
    Next
    For i = 0 To dt.Rows.Count - 1

        For j = 0 To dt.Columns.Count - 1
            workSheet.Cells(i + 2, j + 1) = dt.Rows(i)(j)
        Next
    Next

    If Not String.IsNullOrEmpty(excelFilePath) Then
        Try
            workSheet.SaveAs(excelFilePath)
            excelApp.Quit()
            MessageBox.Show("Excel file saved!")
        Catch ex As Exception
            Throw New Exception("ExportToExcel: Excel file could not be saved! Check filepath." & vbLf & ex.Message)
        End Try
    End If

Result of the test looks like: