从 VB.NET 在 Excel 中查找最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35547856/
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
Finding Last Row in Excel from VB.NET
提问by Peter Renwick
I am trying to learn how to communicate with an Excel spreadsheet from within VB.Net and have found some topics from Siddharth Rout that have been most helpful. However when I try to find the last row used I get the following error message:
我正在尝试学习如何在 VB.Net 中与 Excel 电子表格进行通信，并从 Siddharth Rout 中找到了一些最有帮助的主题。但是，当我尝试查找使用的最后一行时，我收到以下错误消息：
An unhandled exception of type 'SystemMissingMemberException' occurred in Microsoft.VisualBasic.dll Additional information: Public member 'xlapp' on type 'Range' not found
Microsoft.VisualBasic.dll 中发生类型为“SystemMissingMemberException”的未处理异常附加信息：未找到类型“Range”上的公共成员“xlapp”
The error occurs when I run the line beginning lRow =
当我运行以 lRow = 开头的行时发生错误
Help greatly appreciated
非常感谢帮助
My code includes
我的代码包括
Imports System.Data.OleDb
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop.Excel
'~~> Define your Excel Objects
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
' Find the LAST row with data in Column A
lRow = xlWorkSheet.Range("A" & xlWorkSheet.Rows.Count).xlapp.xlUp.Row
MsgBox("The last row which has data in Col A of Sheet1 is " & lRow
回答by Ranjith
Try to use this below code,by which you can get the number of rows used
尝试使用下面的代码，通过它您可以获得使用的行数
lRow=ActiveSheet.UsedRange.Rows.Count
回答by Karen Payne
Try the following, one function gets last used row for a column, one for last used row for a sheet.
尝试以下操作，一个函数获取列的最后使用的行，一个获取工作表的最后使用的行。
Example
例子
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx")
Dim sheetName As String = "Sheet1"
Dim lastRow As Integer = 0
lastRow = UseRowsdByColumn(fileName, sheetName, "A")
MessageBox.Show(lastRow.ToString)
End Sub
End Class
Code Module
代码模块
Option Strict On
Option Infer Off
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module ExcelCode
''' <summary>
''' Get last used row in sheetname
''' </summary>
''' <param name="FileName">path and filename to excel file to work with</param>
''' <param name="SheetName">Worksheet name to get information</param>
''' <returns>-1 if issues else lasted used row</returns>
''' <remarks></remarks>
Public Function UsedRows(ByVal FileName As String, ByVal SheetName As String) As Integer
Dim RowsUsed As Integer = -1
If IO.File.Exists(FileName) Then
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open(FileName)
xlApp.Visible = False
xlWorkSheets = xlWorkBook.Sheets
For x As Integer = 1 To xlWorkSheets.Count
xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
If xlWorkSheet.Name = SheetName Then
Dim xlCells As Excel.Range = Nothing
xlCells = xlWorkSheet.Cells
Dim thisRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
RowsUsed = thisRange.Row
Marshal.FinalReleaseComObject(thisRange)
thisRange = Nothing
Marshal.FinalReleaseComObject(xlCells)
xlCells = Nothing
Exit For
End If
Marshal.FinalReleaseComObject(xlWorkSheet)
xlWorkSheet = Nothing
Next
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
ReleaseComObject(xlWorkSheets)
ReleaseComObject(xlWorkSheet)
ReleaseComObject(xlWorkBook)
ReleaseComObject(xlWorkBooks)
ReleaseComObject(xlApp)
Else
Throw New Exception("'" & FileName & "' not found.")
End If
Return RowsUsed
End Function
''' <summary>
''' Get last used row for a single column
''' </summary>
''' <param name="FileName"></param>
''' <param name="SheetName"></param>
''' <param name="Column"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function UseRowsdByColumn(ByVal FileName As String, ByVal SheetName As String, ByVal Column As String) As Integer
Dim LastRowCount As Integer = 1
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open(FileName)
xlApp.Visible = False
xlWorkSheets = xlWorkBook.Sheets
For x As Integer = 1 To xlWorkSheets.Count
xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
If xlWorkSheet.Name = SheetName Then
Dim xlCells As Excel.Range = xlWorkSheet.Cells()
Dim xlTempRange1 As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
Dim xlTempRange2 As Excel.Range = xlWorkSheet.Rows
Dim xlTempRange3 As Excel.Range = xlWorkSheet.Range(Column.ToUpper & xlTempRange2.Count)
Dim xlTempRange4 As Excel.Range = xlTempRange3.End(Excel.XlDirection.xlUp)
LastRowCount = xlTempRange4.Row
Marshal.FinalReleaseComObject(xlTempRange4)
xlTempRange4 = Nothing
Marshal.FinalReleaseComObject(xlTempRange3)
xlTempRange3 = Nothing
Marshal.FinalReleaseComObject(xlTempRange2)
xlTempRange2 = Nothing
Marshal.FinalReleaseComObject(xlTempRange1)
xlTempRange1 = Nothing
Marshal.FinalReleaseComObject(xlCells)
xlCells = Nothing
End If
Marshal.FinalReleaseComObject(xlWorkSheet)
xlWorkSheet = Nothing
Next
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
ReleaseComObject(xlWorkSheets)
ReleaseComObject(xlWorkSheet)
ReleaseComObject(xlWorkBook)
ReleaseComObject(xlWorkBooks)
ReleaseComObject(xlApp)
Return LastRowCount
End Function
Public Sub ReleaseComObject(ByVal obj As Object)
Try
If obj IsNot Nothing Then
Marshal.ReleaseComObject(obj)
obj = Nothing
End If
Catch ex As Exception
obj = Nothing
End Try
End Sub
End Module
