?
Solved

C#.Net - Populate combobox with an Excel range

Posted on 2011-03-15
5
Medium Priority
?
2,452 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 1000 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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

752 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