How to export data to excel with checkListBox

Question

I have a windows form application with a check list box. So far I am able to bind my data to my checkListBox and have created a excel file using Microsoft.Office.Interop.Excel library. I have my code below. This part of the code work fine. However you can see that my code is linked with employeeCheckListBox which displayed employee's Nick Name to excel file if any of this of checkbox is checked. This code work fine if I only select one item from the checkListBox. But it doesn't work if I select 2 items from the checkListBox. I think the problem is at this line from my code string f_Name = dr.GetString(1), it does read the rest of the f_name from my database.

It show something like this in the excel.

What I really want is this

Here is my database table

I do know how to do this, help will be appreciated

Here is my code to create the Excel file

    private void exportToExcel() {
            object missing = Type.Missing;
            Excel.Application xlApp = new Excel.Application();

            xlApp.Visible = false;


             Excel.Workbook xlwb = xlApp.Workbooks.Add(missing);

      //first worksheet
         Excel.Worksheet xlEmployeeDetail = xlwb.ActiveSheet as Excel.Worksheet;

    //Cell name 
    xlEmployeeDetail.Cells[1, 1] = "Teacher Id";
    xlEmployeeDetail.Cells[1, 2] = "First Name";
    xlEmployeeDetail.Cells[1, 3] = "Last Name";
    xlEmployeeDetail.Cells[1, 4] = "Nick Name";

    //Read the data from the SQL database and store its according value to excel
    while (dr.Read()) {

          //f_Name is reading from column 5 from my db
        string f_Name = dr.GetString(1);
        string l_Name = dr.GetString(2);

        for (int i = 0; i < employeeCheckListBox.CheckedItems.Count; i++) {
            string s = (string) employeeCheckListBox.Items[i];

            xlEmployeeDetail.Cells[2 + i, 4] = s;
            xlEmployeeDetail.Cells[2 + i, 2] = f_Name;
        }
    }

    //Delete rows if there are any empty row
    var LastRow = xlEmployeeDetail.UsedRange.Rows.Count;
    LastRow = LastRow + xlEmployeeDetail.UsedRange.Row - 1;

    int c = 0;

    for (c = 1; c <= LastRow; c++) {
        if (xlApp.WorksheetFunction.CountA(xlEmployeeDetail.Rows[c]) == 0)
        (xlEmployeeDetail.Rows[c] as Microsoft.Office.Interop.Excel.Range).Delete();
    }
}
}

Shouldn't `xlEmployeeDetail.Cells[2 + i, 4]` be `xlEmployeeDetail.Cells[2 + numberOfRow, 4]`? Where `numberOfRow` is an `int` incremented every time you perform `dr.Read()` — Matteo Umili, Sep 23 '15 at 08:25
Why the inner loop over the checked items? Doesn't the reader load *only* the desired records? — Panagiotis Kanavos, Sep 23 '15 at 08:28

Panagiotis Kanavos · Accepted Answer · 2015-09-23T08:37:11.123

2

Instead of writing to a separate line for each database row, your inner loop overwrites the rows with the last data read.

To write your table's data, you only need to write the following code:

int i=1;
while (dr.Read()) {
    i++;
    //f_Name is reading from column 5 from my db
    string f_Name = dr.GetString(1);
    string l_Name = dr.GetString(2);

    xlEmployeeDetail.Cells[i, 4] = l_Name;
    xlEmployeeDetail.Cells[i, 2] = f_Name;
}

This assumes that the data reader returns only the records that should actually be exported. If not, the SQL statement used to load the data should be modified to load only the data to export

The way the original code was written, for each database row, the inner loop would write over the same rows and columns.

A better solution would be use a library like EPPlus to export data directly to an Excel file without using Interop or Excel at all. EPPlus has the LoadFromDataTable and LoadFromCollection convenience methods that allow you to export data from a DataTable or object collection directly to a sheet, eg:

using (ExcelPackage pck = new ExcelPackage())
{
    //Create the worksheet
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");

    //Load the datatable into the sheet, starting from cell A1. 
    //Print the column names on row 1
    ws.Cells["A1"].LoadFromDataTable(someTable, true);

    pck.SaveAs(new FileInfo(@"c:\PathTo\File.xlsx"))
}

edited Sep 23 '15 at 08:37

answered Sep 23 '15 at 08:27

Panagiotis Kanavos

120,703
13
188
236

Hello, thank you for your reply. I am wondering where should my for loop to check the check item be. ? It is because I don't want the user to export the data that they didn't check – RedRocket Sep 23 '15 at 08:31
What I am trying to say is where should I put the for loop that I had above? – RedRocket Sep 23 '15 at 08:35
Thanks, I will give this a try :) – RedRocket Sep 23 '15 at 08:38
Why do you need that at all? What is it supposed to do? If you want to load specific records, you should change your SQL query to return only those records. Or did you want to replace the last name with the checkbox value? – Panagiotis Kanavos Sep 23 '15 at 08:38
Hi Panagioties, I am just wondering, if I really want to use the loop to only export checked item from the checkListbox, can I still do it this way? – RedRocket Sep 23 '15 at 15:33
Do you mean, load everything in the DataTable then try to filter the rows by checking them against the checked items text values? Yes, but why do this? It's easier to change the SQL query and add eg `WHERE l_name in ('name1', 'name2')`. Anyway, you can filter a DataTable using LINQ or by setting the [RowFilter property](https://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter(v=vs.110).aspx) to the same conditions you would in the SQL query, eg `l_name in ('name1', 'name2')`, shown [in this SO question](http://stackoverflow.com/questions/13012585/how-i-can-filter-a-datatable) – Panagiotis Kanavos Sep 23 '15 at 15:49
Thank you for your respond. I am struggling with the query. This is my another question where I showed my actual table here and what query I have so far. Here is the link http://stackoverflow.com/questions/32743234/how-to-export-checked-item-from-checklistbox-to-excel – RedRocket Sep 23 '15 at 16:02

How to export data to excel with checkListBox

1 Answers1