• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2745
  • Last Modified:

C#.Net - Populate combobox with an Excel range

Hi ~

I've created a windows form combobox and want to populate it with a range of data cells from Excel.  Basically, I'm trying to open an Excel file in read-only mode and read the data.  It would've been ideal if I could read the range without opening the file, but I don't think that's possible.  Anyway, I thought I was doing it right, but I'm getting an error from this code.  Could you provide some insight?  

Thank you so much in advance.
public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();            
        }        

        private void Form1_Load(object sender, System.EventArgs e)
        {
            buildcboData();
        }

        private void buildcboData()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            string strWB = "excelfile.xls";
            string strWBPath = "c:/temp/";


            /* WorkBooks.open(string Filename, object UpdateLinks, object ReadOnly, 
             * object Format, object Password, object WriteResPassword, object ReadOnlyRecommend, 
             * object Origin, object Delimiter, object Editable, object Notify, object Converter, 
             * object AddToMru, object Local, object CorruptLoad )
             */
            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open(strWBPath + strWB, 0, true, 5, "", "",
                true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            Excel.Range xlRange = xlWorkSheet.get_Range("B2", "B42");

            foreach (object item in xlRange)
            {
                comboBox1.Items.Add(item);
            }
            
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }

Open in new window

0
gcastong
Asked:
gcastong
  • 3
  • 2
2 Solutions
 
Wayne Taylor (webtubbs)Commented:
You need to get the item's Value....

            foreach (Excel.Range item in xlRange.Cells)
            {
                comboBox1.Items.Add(item.Value);
            }

Open in new window


Wayne
0
 
gcastongAuthor Commented:
Thank you @webtubbs.  That did the trick.  Here's another question for you.

I want to change the range to A2:C42.  I think I need some data in the other columns.  I'd like to put the extra values into a string array.  So, how would I populate the combobox1 with data from just column B?  I tried the following code, but I'm getting errors.  Is there another way of doing this?
foreach (DataRow row in xlRange.Rows)
            {
                int i = 0;
                foreach (var item in row.ItemArray)
                {
                    i++;
                    if (i == 2 )
                    {
                        comboBox1.Items.Add(item);
                    }
                }  
            }

Open in new window

0
 
Wayne Taylor (webtubbs)Commented:
Try this....

Excel.Range xlRange = xlWorkSheet.get_Range("A2", "C42");
for (int iRow = 1; iRow <= xlRange.Rows.Count; iRow++) {
	comboBox1.Items.Add(xlRange.Cells(iRow, 2).Value);
}

Open in new window


You can access the other columns by accessing the different column in the Cells() property.

Wayne
0
 
gcastongAuthor Commented:
@webtubbs: I had to modify your code a bit and it works great.  Thanks for the guidance.  Here's what I did:
Excel.Range xlRange = xlWorkSheet.get_Range("A2", "C42");
for (int iRow = 1; iRow <= xlRange.Rows.Count; iRow++) {
  Excel.Range xlCell = (Excel.Range)xlRange.Cells[iRow, 2];
  comboBox1.Items.Add(xlCell.Value2);
} 

Open in new window


Now this might be a newbie question since I'm learning C#, but how do I create a public string array?  I tried creating a public string array but the compiler gave me errors.  What I'm trying to achieve is when I click a button, based on the SelectedItem from the combobox1, I'd like to get the associated row string value displayed.
0
 
gcastongAuthor Commented:
Thanks @webtubbs for all your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now