Solved

C#.Net - Populate combobox with an Excel range

Posted on 2011-03-15
5
2,281 Views
Last Modified: 2013-12-17
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
Comment
Question by:gcastong
  • 3
  • 2
5 Comments
 
LVL 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 250 total points
ID: 35143849
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
 

Author Comment

by:gcastong
ID: 35144299
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
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 total points
ID: 35144394
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
 

Author Comment

by:gcastong
ID: 35148321
@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
 

Author Closing Comment

by:gcastong
ID: 35149069
Thanks @webtubbs for all your help.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question