Writing data from multiple list box into excel columns in c#

Question

I have a windows form application in c# where there are list of data in multiple listbox (around 56 in each listbox). here is an example:

1. listbox1 contains data of name of students
2. listbox2 contains data of address of students
3. listbox3 contains percentage of students.

the list goes on with the student data. Now i need to print the data in excel in a button click event. the data should go in an ordered way .

I tried using the CSV helper but it only helped me for 1 set of list box i need to sent data from multiple list box.

var sw = new StreamWriter(@"output.csv");
var csvWriter = new CsvHelper.CsvWriter(sw);

foreach (var Name in lbx.Items)
{
    csvWriter.WriteField(Name);
    csvWriter.NextRecord();
}
foreach (var Address in ptr.Items)
{
    csvWriter.WriteField(Address);
}
sw.Flush();

However, this doesn't solve the problem. Any help regarding the solution to this problem would be helpful. Any other method to solve the problem will be great.

Answer 1

It's difficult for me to determine the full details of your configuration, but if it's as simple as two ListBox controls for which you're writing values, you could easily do something like the following:

var sw = new StreamWriter(@"output.csv");
var csvWriter = new CsvHelper.CsvWriter(sw);
int lbxCount = lbx.Items.Count;
int ptrCount = ptr.Items.Count;
for (int i = 0; i < Math.Max(lbx.Items.Count, ptr.Items.Count); i++)
{
    object lbxValue = lbxCount > i ? lbx.Items[i] : String.Empty;
    object ptrValue = ptrCount > i ? ptr.Items[i] : String.Empty;
    csvWriter.WriteField(lbxValue);
    csvWriter.WriteField(ptrValue);
    csvWriter.NextRecord();
}
sw.Flush();
sw.Close();

This determines the maximum number of records to write (Math.Max(lbx.Items.Count, ptr.Items.Count) based on the greatest ListBox.Items.Count.

By altering your loops to be a single loop, you can now use the WriteField method correctly. If the ListBox lbx does not contain a value for a certain record, it uses a String.Empty as the value for that record. Likewise it does the same for ListBox ptr, in the event that there are more items in lbx.

If you have many ListBox controls on a single form and wish to each of them, you could determine the maximum items overall by iterating through all ListBox controls and getting their respective Item.Count values as follows:

var sw = new StreamWriter(@"output.csv");
var csvWriter = new CsvHelper.CsvWriter(sw);
int maxRecords = 0;
List<ListBox> listBoxes = new List<ListBox>();

// Add all ListBox controls to list
// Determine maximum number of items (in all ListBox controls)
foreach (Control control in Controls)
{
    if (control.GetType() == typeof(ListBox))
    {
        ListBox currentListBox = (ListBox)control;
        listBoxes.Add(currentListBox);
        if (currentListBox.Items.Count > maxRecords)
            maxRecords = currentListBox.Items.Count;
    }
}

// Write fields for each ListBox
for (int i = 0; i < maxRecords; i++)
{
    foreach (ListBox currentListBox in listBoxes)
    {
        if (currentListBox.Items.Count > i)
            csvWriter.WriteField(currentListBox.Items[i]);
        else
            csvWriter.WriteField(String.Empty);
    }
    csvWriter.NextRecord();
}
sw.Flush();
sw.Close();