2

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

I'm just a coder for fun
  • 151
  • 3
  • 19
  • Where are you adding the names to the listbox? – Magnetron Sep 30 '16 at 15:58
  • @Magnetron I haven't gotten to that yet.. It is supposed to just read the value of the cells right now but it is not even doing that. – I'm just a coder for fun Sep 30 '16 at 16:04
  • Any advice? @Magnetron – I'm just a coder for fun Sep 30 '16 at 16:53
  • @I'm just a coder for fun... In your foreach loop... where are the ranges defined? Its you that define those ranges... example if you want to get the text from cell A1,A1... you would use... Excel.Range range1 = xlWorkSheet.get_Range("A1", "A1"); then you can set.. string value = range1.Text.ToString(); I am just guessing here... – JohnG Sep 30 '16 at 17:07
  • @JohnG .. I'm getting it from ".Cells".. It should be reading ALL the cells in the excel sheet that has data in it. I did try what you were saying at it is still showing a blank listBox. – I'm just a coder for fun Sep 30 '16 at 17:19
  • @I'm just a coder for fun Ok if the workbook has predefined ranges then it will loop thru those ranges. If it doesn't... it will go thru all the cells. However if you are trying to get the value from a particular cell... why go thru ALL the cells. Do you not know what cell the data you want is in? Also if the cell has no value your loop will crash when it gets to a cell that is empty. I am confused as to why you would want to go thru every cell in the sheet. – JohnG Sep 30 '16 at 17:25
  • @JohnG Well, at first it was to test if it would actually load the excel sheet and read something. If you are saying it crashes when it reaches an empty cell, then yes, I should change where it goes through them all. So in the foreach loop, get_Range("A1", "A1")) would still crash because eventually it'll find an empty cell again.. Correct? – I'm just a coder for fun Sep 30 '16 at 17:29
  • @I'm just a coder for fun I do not see where you are adding the string to the listBox... I would make sure you have something there before you add it to the listBox. – JohnG Sep 30 '16 at 17:29
  • @I'm just a coder for fun Your code to open the sheet looks good. But the foreach loop in its present form will give some null values because you are going thru ALL the cells. Again... do you NOT know which cells the data you want is in? – JohnG Sep 30 '16 at 17:32
  • @JohnG I do.. First Column only.. Row count could be infinite though. – I'm just a coder for fun Sep 30 '16 at 17:34
  • @I'm just a coder for fun Check out this link it may help -- C# - How do I iterate all the rows in Excel._Worksheet? (http://stackoverflow.com/questions/7727738/c-sharp-how-do-i-iterate-all-the-rows-in-excel-worksheet) – JohnG Sep 30 '16 at 17:52
  • @JohnG I'll look it over. Thanks. – I'm just a coder for fun Sep 30 '16 at 18:05

1 Answers1

2

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!

JohnG
  • 9,259
  • 2
  • 20
  • 29