C# Gembox Spreadsheet to populate comboBox

Question

I am working on a program that uses comboboxes that are populated from a csv file, I initially just used streamreader to populate the combobox, but I was instructed to use gembox spreadsheet to populate it instead.

Here is the streamreader code that I used:

        private static List<ItemLine> Readfile(string fileName, string defaultValueDescription)
    {
        string path = $".\\Backend Files\\{fileName}";
        var items = new List<ItemLine>();
        int row = 0;
        //read the file
        StreamReader reader = new StreamReader(path);
        while (reader.EndOfStream == false)
        {
            string lines = reader.ReadLine();
            row++;
            if (row == 1)
            {
                items.Add(new ItemLine { Description = defaultValueDescription, Quantity = 0 });
                continue;
            }
            string[] itemFields = lines.Split(",");
            ItemLine IL = new ItemLine();
            if (itemFields.Length == 2)
            {
                IL.Description = itemFields[0];
                IL.Quantity = 0;
                if (int.TryParse(itemFields[1], out int qty))
                {
                    IL.Quantity = qty;
                }
            }
            else if (itemFields.Length >= 3)
            {
                IL.Plan = itemFields[0];
                IL.Description = itemFields[1];
                IL.Quantity = 0;
                if (int.TryParse(itemFields[2], out int qty))
                {
                    IL.Quantity = qty;
                }
            }
            ///Add Items to List
            items.Add(IL);
        }
        reader.Close();
        return items;

    }

Here is an example of what I used gembox spreadsheet for in another part of my program which I use to update the csv files with, I think the code I am trying to figure out would look something like:

 public void updateFile(string filename, int range, ItemLine selectedItem, decimal outValue)
    {
        var fullPath = $".\\Backend Files\\{filename}";
        SpreadsheetInfo.SetLicense("FREE -LIMITED-KEY");
        var workbook = ExcelFile.Load(fullPath, new CsvLoadOptions(CsvType.CommaDelimited));
        var worksheet = workbook.Worksheets[0];
        for (int i = 1; i <= range; i++)
        {
            var plan = worksheet.Rows[i].Cells[0].Value;
            var desc = worksheet.Rows[i].Cells[1].Value;
            var csvDescription = plan + " - " + desc;
            var platedescription = plan + " - ";
            if (selectedItem.ComboDescription == csvDescription)
            {
                worksheet.Rows[i].Cells[2].Value = outValue;
            }
            if (selectedItem.plateDescription == platedescription)
            {
                worksheet.Rows[i].Cells[1].Value = outValue;
            }
        }
        workbook.Save(fullPath, new CsvSaveOptions(CsvType.CommaDelimited));
    }

score 1 · Accepted Answer · answered Dec 21 '22 at 05:41

Try this:

private static List<ItemLine> ReadFile(string fileName, string defaultValueDescription)
{
    string path = $".\\Backend Files\\{filename}";
    var workbook = ExcelFile.Load(path, new CsvLoadOptions(CsvType.CommaDelimited));
    var worksheet = workbook.Worksheets[0];

    var items = new List<ItemLine>();
    items.Add(new ItemLine { Description = defaultValueDescription, Quantity = 0 });

    foreach (var row in worksheet.Rows)
    {
        var cells = row.AllocatedCells;

        var il = new ItemLine();
        items.Add(il);

        if (cells.Count == 2)
        {
            il.Description = cells[0].Value.ToString();
            il.Quantity = cells[1].ValueType == CellValueType.Int ? cells[1].IntValue : 0;
        }
        else if (cells.Count >= 3)
        {
            il.Plan = cells[0].Value.ToString();
            il.Description = cells[1].Value.ToString();
            il.Quantity = cells[2].ValueType == CellValueType.Int ? cells[2].IntValue : 0;
        }
    }

    return items;
}

Also, I'm not sure about the logic around the defaultValueDescription, it seems that in your StreamReader approach you just skip the first line in CSV because of it.

If that is the intended behavior, then add the .Skip(1) extension method from System.Linq, like this:

foreach (var row in worksheet.Rows.Skip(1))
{
    // ...
}

C# Gembox Spreadsheet to populate comboBox

1 Answers1