用Excel工作表中的使用范围填充.net组合框

Question

我试图用Excel工作簿中使用的范围填充Windows窗体上的组合框。我需要参考的Excel范围是D32:D62;然而，这个范围是动态的。它可能只包含5个值，也可能包含30个值，这取决于用户输入到我的应用程序中的内容。这个范围内的值将全部偏移到顶部，因此值之间不会有空白单元格。

      'populate ComboBoxProgram with relevant program names for loaded student
    Try
        Dim oRange As Excel.Range
        Dim oRangeArr As String
        oRange = DirectCast(StatVar.xlApp.Sheets("New Calculator Input").UsedRange("D32", "D62"), Excel.Range)
        oRangeArr = String.Empty
        'Build a string array delimited by commas
        For i As Integer = 1 To oRange.Rows.Count
            Dim oCell As Excel.Range = DirectCast(oRange.Rows(i), Excel.Range)
            oRangeArr &= DirectCast(oCell.Value.ToString, String) & ","
        Next
        oRangeArr = oRangeArr.Remove(oRangeArr.Length - 1, 1)
        ComboBoxProgram.Items.AddRange(oRangeArr.Split(","c))
        oRange = Nothing
    Catch exc As Exception
        MessageBox.Show("An error occured while retrieving the relevant programs from the budget. Please contact an administrator for assistance.")
    End Try

这将只填充我的组合框，如果所有单元格在我的范围有值。如何找到使用过的范围?

Answer 1

尝试一下这个稍微修改过的方法。它将检查整个工作簿中具有数据的单元格，而不填充空单元格的组合框。

Imports Microsoft.Office.Interop.Excel
...
Dim excel As Application = New Application
Dim w As Workbook = excel.Workbooks.Open("C:tempbook1.xlsx")
    For i As Integer = 1 To w.Sheets.Count
          Dim sheet As Worksheet = w.Sheets(i)
          Dim r As Range = sheet.UsedRange
          Dim array(,) As Object = r.Value(XlRangeValueDataType.xlRangeValueDefault)
          If array IsNot Nothing Then
              Dim bound0 As Integer = array.GetUpperBound(0)
              Dim bound1 As Integer = array.GetUpperBound(1)
              ' Loop over all elements.
              For j As Integer = 1 To bound0
                  For x As Integer = 1 To bound1
                      Dim s1 As String = array(j, x)
                         If s1 IsNot Nothing Then
                            If Not ComboBox1.Items.Contains(s1.ToString) Then
                                ComboBox1.Items.Add(s1.ToString)
                            End If
                         End If
                  Next
              Next
          End If
     Next
 w.Close()