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:\Users\Administrator\Documents\ 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

So, what exactly are you calling a Table? A database, a text file (Maybe delimited in some way), a report of some nature, a excel spreedsheet or the like. Any advise you get on how this works is going to be dependent on your desired output. — Hursey, Jan 24 '21 at 20:33
@Hursey , I want an output in the format of the table above. The output should be in graphic form so that one can even print. I ACCEPT any ideas but i had Excel spreadsheet as my first option — Sheikh Kelvin, Jan 25 '21 at 11:15
Right, obviously you've not done much if any research into this as there are literally thousands of examples inter is to choose how you're going to interact with Excel. Start with how you're going to work with Interop is common, but can be hard work if not done right, or you could use OpenXML, or one of the many pre-built tools/libaraies available (Check nugets and VS Marketplace) — Hursey, Jan 25 '21 at 19:51
You can use comboboxes selected items to generate a datatable, then [export the data table to Excel](https://stackoverflow.com/questions/8207869/how-to-export-datatable-to-excel) everty time you need. Let me know if you need further assistance. — Xingyu Zhao, Jan 27 '21 at 06:52
@XingyuZhao do you mind if we get in touch? And help me out. I have the design, the concept but a little bit stuck at te implementation. Kindly reach me via +254720540425 — Sheikh Kelvin, Jan 28 '21 at 10:08

score 0 · Answer 1 · answered Jan 29 '21 at 07:43

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:

my friend ,the code didnt work. I got several errors and even after changing the codes to get rid of the errors i still didnt get the excel file updated. I would have posted the whole response here but my characters are limited. Let me just google for answers — Sheikh Kelvin, Feb 04 '21 at 06:47

POPULATION OF TABLE FROM WINDOWS FORM

1 Answers1