如何使用VB.net获取excel文件的确切行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12280007/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get the exact number of rows of an excel file with VB.net?
提问by miranda
I just started working with VB.net, in my application I will need to have the exact number of used rows in an excel file I had a look at this post Reading line count using vb.netI tried all the answers ,but I've never got the exact number of rows, I've also tried other codes in some websites but also nothing!! Can anybody help me plz
我刚开始使用 VB.net，在我的应用程序中，我需要在 excel 文件中有确切的已用行数我看过这篇文章 使用 vb.net 读取行数我尝试了所有答案，但我我从来没有得到确切的行数，我也在一些网站上尝试过其他代码，但也没有！！有人可以帮我吗
Hello in actually I work with SQL SERVER 2008 I tried this code
你好，实际上我使用 SQL SERVER 2008 我试过这段代码
Imports System.Diagnostics.Process
Imports System.IO
Imports System.Data.DataTable
Imports Microsoft.Office.Tools
Imports Excel
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim selectedFile As String = String.Empty
OpenFileDialog1.ShowDialog()
selectedFile = OpenFileDialog1.FileName
If (selectedFile IsNot Nothing) Then
ListBox1.Items.Add(selectedFile)
End If
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim fullPath As String
Dim fileResult As String
Dim numRow As Integer
fullPath = Path.GetFullPath(ListBox1.SelectedItem)
fileResult = Path.GetFileName(fullPath)
Dim file_count As Integer = File.ReadAllLines(fullPath).Length
MsgBox(file_count)
but again the count is not correct, and I really do not know why!!
但是再次计数不正确，我真的不知道为什么！！
回答by smh
I wanted the used rows for a routine importing an excel worksheet into sql 2008 using Microsoft.Office.Interop.Excel and vb.net 2010 and used just:
我想要使用 Microsoft.Office.Interop.Excel 和 vb.net 2010 将 excel 工作表导入 sql 2008 的例程所使用的行，并且仅使用：
usedRowsCount = xlWorksheet.UsedRange.Rows.Count
usedRowsCount = xlWorksheet.UsedRange.Rows.Count
回答by miranda
Hello finally I got the solution:
你好，我终于得到了解决方案：
Imports Microsoft.Office.Interop.Excel
Imports System.Data.DataTable
Imports Microsoft.Office.Interop
private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim fullPath As String
Dim fileResult As String
Dim numRow As Integer
fullPath = Path.GetFullPath(ListBox1.SelectedItem)
fileResult = Path.GetFileName(fullPath)
Dim obook As Excel.Workbook
Dim oapp As Excel.Application
oapp = New Excel.Application
obook = oapp.Workbooks.Open(fullPath)
numRow = 3
While (obook.ActiveSheet.Cells(numRow, 1).Value IsNot Nothing)
numRow = numRow + 1
End While
MsgBox(numRow)
End Sub
and you have to add the folowing references:
并且您必须添加以下引用：
Microsoft Excel 12.0 Library
Microsoft Office 12.0 Library
Microsoft Office Tools v9.0
Microsoft visual Basic for application extensibility
Hope it helps :)
希望能帮助到你 ：）
回答by namus
Get the datas of excel file into datatable and count the rows.
将excel文件的数据放入数据表中并统计行数。
Public Class Form1
Private Sub getexcelfile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim excelfile As New OpenFileDialog()
excelfile.ShowDialog()
If (excelfile.ShowDialog() = DialogResult.Cancel) Then
Return
Else
Dim file As String = excelfile.FileName
Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + ";Extended Properties=Excel 8.0;"
Dim con As New OleDb.OleDbConnection(str)
con.Open()
Dim ds As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", con)
Dim dt As New DataTable()
ds.Fill(dt)
Dim rowcount As Integer
rowcount = dt.Rows.Count()
End If
End Sub
End Class
回答by namus
Public Class Form1
Private Sub getexcelfile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim excelfile As New OpenFileDialog()
excelfile.ShowDialog()
If (excelfile.ShowDialog() = DialogResult.Cancel) Then
Return
Else
Dim file As String = excelfile.FileName
Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + ";Extended Properties=Excel 8.0;"
Dim con As New OleDb.OleDbConnection(str)
con.Open()
Dim ds As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", con)
Dim dt As New DataTable()
ds.Fill(dt)
Dim rowcount As Integer
rowcount = dt.Rows.Count()
End If
End Sub
End Class
