VB.NET - 读取 excel 文件的全部内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7658976/
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
VB.NET - Reading ENTIRE content of an excel file
提问by FlyingM
I already know the basic procedure of how to read specific cells of an .xls file using VB.NET but I can't figure out how to automatically get all the data from such a file.
我已经知道如何使用 VB.NET 读取 .xls 文件的特定单元格的基本过程，但我无法弄清楚如何从这样的文件中自动获取所有数据。
Basically, I'm obtaining the data like this:
基本上，我正在获取这样的数据：
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
xlApp = New Excel.Application
wb = xlApp.Workbooks.Open("myfile.xls")
ws = wb.Worksheets("worksheet1")
For Each cell In ws.Range("A1", "C10")
Console.WriteLine(cell.value)
Next
In this case, I know that there will be content in the first 10 rows of the columns A, B and C but what would I have to do if I had a huge document whose size and content might even change from time to time? I know that there is also an attribute ws.rows.count but this always returns a big value like 60000 even if only a few rows are occupied.
在这种情况下，我知道 A、B 和 C 列的前 10 行会有内容，但是如果我有一个巨大的文档，其大小和内容甚至可能不时发生变化，我该怎么办？我知道还有一个属性 ws.rows.count 但这总是返回一个很大的值，比如 60000 即使只有几行被占用。
So basically, I'm looking for a simple way to loop through all the used rows of an Excel file with the possibility of accessing each cell in that row.
所以基本上，我正在寻找一种简单的方法来遍历 Excel 文件的所有已使用行，并可以访问该行中的每个单元格。
回答by brettdj
in VBA - as distinct from VB.NEt (so I may be off target here) you would either use
在 VBA 中 - 与 VB.NEt 不同（所以我可能在这里偏离目标）你要么使用
ActiveSheet.UsedRange
MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
(the first line as needed to "reset" the UsedRange, SpecialCells(xlCellTypeLastCell)will often give a larger range than is actually present), or more commonly
（根据需要“重置” UsedRange 的第一行，SpecialCells(xlCellTypeLastCell)通常会给出比实际存在的范围更大的范围），或更常见的是
or
或者
Dim rng1 As Range
Set rng1 = ActiveSheet.Cells.Find("*", [a1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then MsgBox rng1.Row
回答by JMax
Still in VBA, we often use the Endstatement for this kind of issue:
还是在VBA中，End对于这类问题，我们经常使用语句：
ws.Cells(Rows.Count, "A").End(xlUp).Row
Change the column name or number to fit your needs (or find it in the same way as the last row with End(xlLeft)
更改列名称或编号以满足您的需要（或以与最后一行相同的方式找到它 End(xlLeft)
回答by jdross
Here's an example that I have used in the past.
这是我过去使用过的一个例子。
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
xlWorkSheet = xlWorkBook.Worksheets("sheet1")
'display the cells value B2
MsgBox(xlWorkSheet.Cells(2, 2).value)
'edit the cell with new value
xlWorkSheet.Cells(2, 2) = "http://vb.net-informations.com"
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class
回答by MansoorShaikh
Dim aDataTemp = xl.Worksheets(sSheetName).UsedRange.value
Dim aDataTemp = xl.Worksheets(sSheetName).UsedRange.value
回答by ControlPower
It is easy to use Spire.XLS to read all available cells, for example:
使用 Spire.XLS 很容易读取所有可用的单元格，例如：
Dim workbook As New Workbook
workbook.LoadFromFile("your-excel-file.xls")
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim range As CellRange = worksheet.AllocatedRange
Console.WriteLine("LastRow Index: {0}, LastColumn Index:{1}", range.LastRow, range.LastColumn)
For Each cell As CellRange In range.Cells
If cell.IsBlank Then
Continue For
End If
Console.WriteLine("{0} = {1}", cell.RangeAddressLocal, cell.Value2)
Next
