Question

我想使用C＃将Excel中的一列加载到可选择的下拉菜单中。我可以访问文件，并且可以在C＃中加载文件，但不确定如何实现我想要的内容。有什么建议吗？（我正在使用Visual Studio 2008）

Answer 1

您可以使用OleDb托管数据提供程序以ADO.NET的方式读取Excel电子表格，就像使用数据库一样。

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";

Answer 2

您可以像这样实施PIA解决方案（假设第一个工作表中“A”列有5个项目）：

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);
}

如果您在运行时不知道下拉菜单中项目的确切数量，则需要搜索范围以找到结束位置；请看这个示例这里。

此示例使用Office 2007 PIAs，如果您使用较旧版本，则语法应该非常接近，但可能会略有不同。

Answer 3

据我所知，你只有几个选择：

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.

友情链接