从excel导入到列表框 [英] importing from excel to listbox

问题描述

我尝试将列从excel导入到我的列表框中时以vb.net形式出现中断错误



问题是在导入所有内容后列成功地列到了列表框。出现中断错误并关闭程序。这是中断错误



在System.Windows.Forms.dll中发生了'System.ArgumentNullException'类型的未处理异常

/>
附加信息：价值不能为空））



这是我的代码

 私有  Sub  Button5_Click（发件人作为 对象，e  As  EventArgs）句柄 Button5 .Click 
  Dim  oExcel  As   Object  = CreateObject（  Excel.Application）
  Dim  oBook 作为 对象 = oExcel.Workbooks.Open（  C：\ Users \User \Desktop\1.xlsx）
  Dim  oSheet 作为 对象 = oBook.Worksheets（ 1 ）
  Dim  i  As  整数 
  Dim  cell  As  字符串 
 对于 i =  0  要 AscW（ListBox1.Items.Count.ToString（）（i = i +  1 ））
 
 cell =   E& Convert.ToString（i +  1 ）
 cell = oSheet.Range（cell）.Value 
 ListBox1.Items.Add（cell）
 
 下一步 
 oExcel.Quit（）
 结束 < span class =code-keyword> Sub  

[来自CHill60的编辑 - 来自进一步查询的解决方案的OP代码]

引用：

非常感谢你现在的工作



如果您允许我向您询问有关相关事项的小问题



将所有列值导入列表框

i不要它导入excel文件中列的最后一行



这里是我的代码

 私有  Sub  Button5_Click（发件人 As  对象，e  As  EventArgs）句柄 Button5.Click 
  Dim  oExcel 作为 对象 = CreateObject（  Excel.Application）
  Dim  oBook 正如 对象 = oExcel.Workbooks.Open（  C：\ Users \User\Desktop\1.xlsx）
  Dim  oSheet 作为 对象 = oBook.Worksheets（ 1 ）
 < span class =code-keyword> Dim  i 作为 整数 
 < span class =code-keyword> Dim  cell  As  字符串 
 对于 i =  0  要 AscW（ListBox1.Items.Count.ToString（）（i = i +  1 ） ） -   1  
 
 cell =   E& Convert.ToString（i +  1 ）
 
 cell = oSheet.Range（cell）.Value 
 如果 cell =   然后 
 退出 对于 
  Else  
 ListBox1.Items.Add（cell）
  End  如果 
 下一步 
 oExcel.Quit（）
 结束  Sub  

解决方案

如果在

 cell = oSheet.Range（cell）.Value 

你会看到 cell 的值是什么

您需要确保在尝试之前确实在该单元格中有一些数据要将其添加到ListBox，例如

 如果 不 cell   没什么 然后 ListBox1.Items.Add（cell）

[跟进查询后编辑]

我现在已正确查看代码，您似乎只加载来自电子表格如果ListBox中已有信息 - 请参阅 For 语句。

据我所知，你想要最后一行的电子表格除之外的所有数据。



因为您已经为excel应用程序使用了 CreateObject ，所以您无权访问使用（例如）interop的任何数量的属性。



我会从工作表中读取所有内容并在您按下一个空白单元格时退出（就像您已经在代码中一样）。请注意，从表格的末尾开始运行存在危险...但是您不应该在GUI中有那么多数据，所以我不会检查 i 大于最大行号。



这样的东西......

而真
 
 cell = E& Convert.ToString（i）
i + = 1 
 
 cell = oSheet.Range（cell）.Value 
 If Not cell is Nothing Then 
 ListBox1.Items.Add （单元格）
其他
退出时
结束如果
 
结束时
 

一次你拥有所有数据然后只删除最后一个条目

 ListBox1.Items.RemoveAt（ListBox1.Items.Count  -   1 ）

NB - 如果您要使用Sorted ListBox，请确保切换排序

 ListBox1.Sorted =  False     

并仅在之后重新开启排序/ i>你已经填写并删除了最后一项



如果你想探索用VB.Net操纵Excel的其他方法，CodeProject上有一些很棒的文章/ BLOCKQUOTE>

I'm having an break error while trying to import column from excel to my listbox in vb.net form

the problem is after importing all the items inside the column successfuly to the listbox . A break error shown up and close the program. here is the break error

An unhandled exception of type 'System.ArgumentNullException' occurred
in System.Windows.Forms.dll
Additional information: Value cannot be null))

and here is my code

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
Dim oExcel As Object = CreateObject("Excel.Application")
Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\User\Desktop\1.xlsx")
Dim oSheet As Object = oBook.Worksheets(1)
Dim i As Integer
Dim cell As String
For i = 0 To AscW(ListBox1.Items.Count.ToString()(i = i + 1))

        cell = "E" & Convert.ToString(i + 1)
        cell = oSheet.Range(cell).Value
        ListBox1.Items.Add(cell)

    Next
    oExcel.Quit()
End Sub

[EDIT from CHill60 - OP Code from solution with further query]

Quote:

Thank you very much it's work now

If you allow me to ask you a little question about something related

when importing all column values into listbox
i dont want it to import the last row of the column in the excel file

here is my code

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
    Dim oExcel As Object = CreateObject("Excel.Application")
    Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\User\Desktop\1.xlsx")
    Dim oSheet As Object = oBook.Worksheets(1)
    Dim i As Integer
    Dim cell As String
    For i = 0 To AscW(ListBox1.Items.Count.ToString()(i = i + 1)) - 1

        cell = "E" & Convert.ToString(i + 1)

        cell = oSheet.Range(cell).Value
        If cell = "" Then
            Exit For
        Else
            ListBox1.Items.Add(cell)
        End If
    Next
    oExcel.Quit()
End Sub

解决方案

If you put a breakpoint on

cell = oSheet.Range(cell).Value

you will see that the value of cell is Nothing
You need to make sure you actually have some data in that cell before attempting to add it to the ListBox e.g.

If Not cell Is Nothing Then ListBox1.Items.Add(cell)

[EDIT after follow up query]
I've looked at the code properly now and you appear to only load data from the spreadsheet if there is already information in the ListBox - see that For statement.
As I understand it you want all of the data from the spreadsheet except for the last row.

Because you have used CreateObject for the excel application you don't have access to as many properties as you would by using (for example) interop.

I would just read everything from the sheet and exit when you hit a blank cell (as you have already in your code). Note that there is a "danger" of running off the end of the sheet ... but you shouldn't have that much data in a GUI so I'm not checking that i is greater than the maximum row number.

Something like this ...

While True

    cell = "E" & Convert.ToString(i)
    i += 1

    cell = oSheet.Range(cell).Value
    If Not cell Is Nothing Then
        ListBox1.Items.Add(cell)
    Else
        Exit While
    End If

End While

Once you have all of the data then just remove the last entry

ListBox1.Items.RemoveAt(ListBox1.Items.Count - 1)

NB - if you are going to use a Sorted ListBox then make sure you switch of the sorting

ListBox1.Sorted = False"

and switch sort back on only after you have filled it and removed the last item

There are some great articles on CodeProject if you want to explore other ways of manipulating Excel with VB.Net

这篇关于从excel导入到列表框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！