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

Hi hemma123,

I have created a sample that full fill your requirement. So you need to modify the code as per your requirement.

For this i have used ClosedXml library to export DataTable to Excel. For downloading the dll refer below link.

Download ClosedXML Library

and i have made use of Northwind Database Customers table you can get the database from below article.

Install the Northwind and Pubs Sample Databases in SQL Server Express

Namespaces

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Windows.Forms;
using ClosedXML.Excel;

C#

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    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["ContactName"]);
        }
        con.Close();
    }

    private void Form1_Load(object sender, System.EventArgs e)
    {
        BindCheckedListBox(checkedListBox1, "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) ROWNO,CustomerID,ContactName FROM Customers) t WHERE ROWNO BETWEEN 1 AND 10");
        BindCheckedListBox(checkedListBox2, "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) ROWNO,CustomerID,ContactName FROM Customers) t WHERE ROWNO BETWEEN 11 AND 20");
        BindCheckedListBox(checkedListBox3, "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) ROWNO,CustomerID,ContactName FROM Customers) t WHERE ROWNO BETWEEN 21 AND 30");
        BindCheckedListBox(checkedListBox4, "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) ROWNO,CustomerID,ContactName FROM Customers) t WHERE ROWNO BETWEEN 31 AND 40");
    }

    private void button1_Click(object sender, System.EventArgs e)
    {
        int[] maxCount = { checkedListBox1.CheckedItems.Count, checkedListBox2.CheckedItems.Count, checkedListBox3.CheckedItems.Count, checkedListBox4.CheckedItems.Count };
        int rows = maxCount.Max();
        List<columns> list = new List<columns>();
        for (int i = 0; i < rows; i++)
        {
            string column1 = "", column2 = "", column3 = "", column4 = "";
            if (i < checkedListBox1.CheckedItems.Count)
            {
                column1 = checkedListBox1.CheckedItems[i] == null ? "" : checkedListBox1.CheckedItems[i].ToString();
            }
            if (i < checkedListBox2.CheckedItems.Count)
            {
                column2 = checkedListBox2.CheckedItems[i] == null ? "" : checkedListBox2.CheckedItems[i].ToString();
            }
            if (i < checkedListBox3.CheckedItems.Count)
            {
                column3 = checkedListBox3.CheckedItems[i] == null ? "" : checkedListBox3.CheckedItems[i].ToString();
            }
            if (i < checkedListBox4.CheckedItems.Count)
            {
                column4 = checkedListBox4.CheckedItems[i] == null ? "" : checkedListBox4.CheckedItems[i].ToString();
            }

            columns col = new columns { Column1 = column1, Column2 = column2, Column3 = column3, Column4 = column4 };
            list.Add(col);
        }
        DataTable dt = new DataTable("Employees");
        dt.Columns.Add("Column1", typeof(string));
        dt.Columns.Add("Column2", typeof(string));
        dt.Columns.Add("Column3", typeof(string));
        dt.Columns.Add("Column4", typeof(string));

        for (int i = 0; i < list.Count; i++)
        {
            dt.Rows.Add(list[i].Column1, list[i].Column2, list[i].Column3, list[i].Column4);
        }
        XLWorkbook wb = new XLWorkbook();
        wb.Worksheets.Add(dt, dt.TableName);
        wb.SaveAs(@"C:\Users\dharmendra\Desktop\" + dt.TableName + ".xlsx");
    }

    public class columns
    {
        public string Column1 { get; set; }
        public string Column2 { get; set; }
        public string Column3 { get; set; }
        public string Column4 { get; set; }
    }
}

Screenshot

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