CODEWITHSUNDEEP
c#excellistbox
I'm just a coder for fun
I'm just a coder for fun

Reputation: 151

Reading an excel sheet, and adding to listbox once read. C#

I am looking to read an excel file and post it to a listBox(employeeBox) with a name (Which is on the first column of the excel sheet always). Then I'm looking to delete that name once I have selected the name in the list box. My FIRST and MAIN problem is that it WON'T load the names in the listBox. When I'm debugging it, it doesn't even look like it is loading the excel to read. I have put the code in buttons and it loads fine. Can anyone assist? This is my first post. So if there are any recommendations on how to ask a question, please let me know. P.S. The code is set just to get the value of the cells.. Not the names yet.

private void Form3_Load(object sender, EventArgs e)
{
    Excel.Application xlexcel = new Excel.Application();
    xlexcel.Visible = true;
    string fileName = "C:\\MyExcel.xlsx";
    Excel.Workbook xlWorkBook = xlexcel.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[1];

    Excel.Range usedRange = xlWorkSheet.UsedRange;
        string data = "";
        Excel.Range curRange;
        List<string> itemsToAdd = new List<string>();

        try
        {
            foreach (Excel.Range row in usedRange.Rows)
            {
                curRange = (Excel.Range)row.Cells[1, 1];
                data = curRange.Cells.Value.ToString();
                itemsToAdd.Add(data);
                xlWorkBook.Close();
                xlexcel.Quit();
            }
            employeeBox.DataSource = itemsToAdd;
        }
        catch(Exception)
        {
            Console.WriteLine();
        }
}

private void employeeBox_SelectedIndexChanged(object sender, EventArgs e)
{
    employeeBox.Click += employeeBox_Click;
}

private void employeeBox_Click(object sender, EventArgs e)
{
    throw new NotImplementedException();
}

After making the appropriate corrections, it was giving me a runtimeexception.. This prompted me to use a try catch.. Which took away the error, but it still didn't add. It showed the MessageBox but didn't add to the listbox. Now.. I experimented and found out that it adds when you close the excel, but it only adds one thing.

enter image description here enter image description here

Upvotes: 2

Views: 2195

Answers (1)

JohnG
JohnG

Reputation: 9469

This should work to loop thru each row with data and get the value from column 1.

Range usedRange = xlWorkSheet.UsedRange;
string data = "";
Excel.Range curRange;
List<string> itemsToAdd = new List<string>();
foreach (Excel.Range row in usedRange.Rows)
{
  curRange = (Excel.Range)row.Cells[1, 1];
  if (curRange.Cells.Value2 != null)
  {
    data = curRange.Cells.Value2.ToString();
    itemsToAdd.Add(data);
  }
}
listBox1.DataSource = itemsToAdd;
xlWorkBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);  
xlexcel.Quit();

UPDATE To ignore blank rows - when usedRange returns... it may include blank rows if the data is not in contiguous rows.

Hope this helps!

Upvotes: 2

Related Questions