Export CheckedListBox selected items based Data from Database to Excel using C# and VB.Net in Windows Forms Application

Hi hemma123,

I have created sample as per your requirement. Refer the below code.

C#

private void BindCheckedListBox(CheckedListBox checkedListBox, string query)
{
    string cns = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
    SqlConnection con = new SqlConnection(cns);
    SqlCommand cmd = new SqlCommand(query);
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    con.Open();
    SqlDataReader dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        checkedListBox.Items.Add(dr[0]);
    }
    con.Close();
}

private void Form1_Load(object sender, System.EventArgs e)
{
    BindCheckedListBox(checkedListBox1, "SELECT DISTINCT Column1 FROM TableA");
    BindCheckedListBox(checkedListBox2, "SELECT DISTINCT Column2 FROM TableA");
    BindCheckedListBox(checkedListBox3, "SELECT DISTINCT Column3 FROM TableA");
}

private void button1_Click(object sender, System.EventArgs e)
{
    string col1 = "";
    string col2 = "";
    string col3 = "";
    string query = "SELECT Column1,Column2,SUM(Column4) 'SUM(Column4)',(SUM(CONVERT(INT,REPLACE(Column5,'%','')))) 'SUM(Column5)' FROM TableA WHERE 1 = 1";
    foreach (string item in checkedListBox1.CheckedItems)
    {
        col1 += "'" + item + "',";
    }
    if ((!string.IsNullOrEmpty(col1)))
    {
        col1 = col1.Remove(col1.Length - 1);
        query += " AND Column1 IN (" + col1 + ")";
    }

    foreach (string item in checkedListBox2.CheckedItems)
    {
        col2 += "'" + item + "',";
    }
    if ((!string.IsNullOrEmpty(col2)))
    {
        col2 = col2.Remove(col2.Length - 1);
        query += " AND Column2 IN (" + col2 + ")";
    }

    foreach (string item in checkedListBox3.CheckedItems)
    {
        col3 += "'" + item + "',";
    }
    if ((!string.IsNullOrEmpty(col3)))
    {
        col3 = col3.Remove(col3.Length - 1);
        query += " AND Column3 IN (" + col3 + ")";
    }
    query += " GROUP BY Column1,Column2";
    string cns = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
    SqlConnection con = new SqlConnection(cns);
    SqlCommand cmd = new SqlCommand(query);
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    DataTable dtExcel = new DataTable("TableA");
    dtExcel.Columns.Add("Column1");
    dtExcel.Columns.Add("Column2");
    dtExcel.Columns.Add("SUM(Column4)");
    dtExcel.Columns.Add("SUM(Column5)");
    foreach (DataRow dr in dt.Rows)
    {
        dtExcel.Rows.Add(dr[0], dr[1], dr[2], dr[3] + "%");
    }
    XLWorkbook wb = new XLWorkbook();
    wb.Worksheets.Add(dtExcel, dtExcel.TableName);
    wb.SaveAs(@"C:\Users\dharmendra\Desktop\" + dtExcel.TableName + ".xlsx");
}

VB.Net

Public Class Form1
    Private Sub BindCheckedListBox(checkedListBox As CheckedListBox, query As String)
        Dim cns As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
        Dim con As New SqlConnection(cns)
        Dim cmd As New SqlCommand(query)
        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        con.Open()
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        While dr.Read()
            checkedListBox.Items.Add(dr(0))
        End While
        con.Close()
    End Sub

    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles MyBase.Load
        BindCheckedListBox(checkedListBox1, "SELECT DISTINCT Column1 FROM TableA")
        BindCheckedListBox(checkedListBox2, "SELECT DISTINCT Column2 FROM TableA")
        BindCheckedListBox(checkedListBox3, "SELECT DISTINCT Column3 FROM TableA")
    End Sub

    Private Sub button1_Click(sender As Object, e As System.EventArgs) Handles button1.Click
        Dim col1 As String = ""
        Dim col2 As String = ""
        Dim col3 As String = ""
        Dim query As String = "SELECT Column1,Column2,SUM(Column4) 'SUM(Column4)',(SUM(CONVERT(INT,REPLACE(Column5,'%','')))) 'SUM(Column5)' FROM TableA WHERE 1 = 1"

        For Each item As String In checkedListBox1.CheckedItems
            col1 += "'" + item + "',"
        Next
        If (Not String.IsNullOrEmpty(col1)) Then
            col1 = col1.Remove(col1.Length - 1)
            query += " AND Column1 IN (" + col1 + ")"
        End If

        For Each item As String In checkedListBox2.CheckedItems
            col2 += "'" + item + "',"
        Next
        If (Not String.IsNullOrEmpty(col2)) Then
            col2 = col2.Remove(col2.Length - 1)
            query += " AND Column2 IN (" + col2 + ")"
        End If

        For Each item As String In checkedListBox3.CheckedItems
            col3 += "'" + item + "',"
        Next
        If (Not String.IsNullOrEmpty(col3)) Then
            col3 = col3.Remove(col3.Length - 1)
            query += " AND Column3 IN (" + col3 + ")"
        End If
        query += " GROUP BY Column1,Column2"
        Dim cns As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
        Dim con As New SqlConnection(cns)
        Dim cmd As New SqlCommand(query)
        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        con.Open()
        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()
        da.Fill(dt)
        Dim dtExcel As New DataTable("TableA")
        dtExcel.Columns.Add("Column1")
        dtExcel.Columns.Add("Column2")
        dtExcel.Columns.Add("SUM(Column4)")
        dtExcel.Columns.Add("SUM(Column5)")
        For Each dr As DataRow In dt.Rows
            dtExcel.Rows.Add(dr(0), dr(1), dr(2), dr(3).ToString() + "%")
        Next
        Dim wb As New XLWorkbook()
        wb.Worksheets.Add(dtExcel, dtExcel.TableName)
        wb.SaveAs("C:\Users\dharmendra\Desktop\" + dtExcel.TableName + ".xlsx")
    End Sub
End Class

Screenshot