Solved

C#.Net - Populate combobox with an Excel range

Posted on 2011-03-15
5
2,210 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
Thanks @webtubbs for all your help.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now