Loading excel column data using C# in a dropdown menu.

Question

I want to load a column in excel into a selectable dropdown menu using c#. I have access to the file, and can load the file in C#, but am not sure how to implement what I want. Suggestions? (I'm using Visual Studio 2008)

Answer 1

As far as I know you only have a couple of options:

• Primary Interop Assemblies (PIA) that let you read and write from the Excel object model.
• Building a Visual Studio Tools for Office (VSTO) solution, which effectively lets you write 'code behind' for your Excel spreadsheet. Depending on what you are trying to achieve this can make sense if you are actually doing a lot of work within excel, and your current application is really just creating an extension to the spreadsheet UI.

Answer 2

You could implement a PIA solution something like this (assuming 5 items in column "A" of the first worksheet):

using Excel = Microsoft.Office.Interop.Excel;

...

worksheet = workbook.Worksheets[1] as Excel.Worksheet;

Excel.Range range;
range = worksheet.get_Range("A1", "A5") as Excel.Range;
foreach (Excel.Range cell in range.Cells)
{
    myComboBox.Items.Add(cell.Value2 as string);
}

If you don't know the exact number if items in the dropdown at runtime, you will need to search the range to find the end; check out this sample here.

This sample uses the Office 2007 PIAs, if you are using an older version the syntax should be very close but might vary a bit.

Answer 3

You can use the OleDb Managed Data Provider to read an Excel spreadsheet using ADO.NET just like you would with a database.

using System.Data.OleDb;

DataTable dt = new DataTable();
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Book1.xls;Extended Properties='Excel 8.0;HDR=NO'";
using (OleDbConnection conn = new OleDbConnection(connString))
{
    conn.Open();                
    //Where [F1] = column one, [F2] = column two etc, etc.
    OleDbCommand selectCommand = new OleDbCommand("select [F1] AS [id] from [Sheet1$]",conn);
    OleDbDataAdapter adapter = new OleDbDataAdapter();
    adapter.SelectCommand = selectCommand;
    adapter.Fill(dt);
}

listBox1.DataSource = dt;
listBox1.DisplayMember = "id";