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