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

Improve this question
12
  • Where are you adding the names to the listbox? Commented Sep 30, 2016 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. Commented Sep 30, 2016 at 16:04
  • Any advice? @Magnetron Commented Sep 30, 2016 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... Commented Sep 30, 2016 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. Commented Sep 30, 2016 at 17:19

1 Answer 1

Reset to default
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!

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Compiles fine.. Doesn't give the message box. @JohnG
@I'm just a coder for fun ... i updated the code to include putting the items in the ListBox... I do not see in your original code where you are doing this.
BRAVO!! This worked when it ignored the blank rows. Thank you!
If I could give you 1000 likes, I would!
@I'm just a coder for fun.. in your updated code above... you are closing the spreadsheet in the loop... Don't close excel until you have read all the data... move the close and quit excel out of the foreach loop!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.