Solved

C#.Net - Populate combobox with an Excel range

Posted on 2011-03-15
5
2,318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 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