VB.NET-读取Excel文件的整个内容 [英] VB.NET - Reading ENTIRE content of an excel file

问题描述

我已经知道如何使用VB.NET读取.xls文件的特定单元格的基本过程，但是我不知道如何自动从此类文件中获取所有数据.

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.

基本上，我正在获取像这样的数据:

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

在这种情况下，我知道A，B和C列的前10行中将有内容，但是如果我有一个庞大的文档，其大小和内容甚至可能随时间变化，那该怎么办?时间?我知道也有一个属性ws.rows.count，但是即使只占用了几行，它总是返回一个大的值，如60000.

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.

因此，基本上，我正在寻找一种简单的方法来遍历Excel文件的所有已使用行，并可能访问该行中的每个单元格.

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.

推荐答案

在VBA中-与VB.NEt不同(因此我可能不在此处)，您可以使用

in VBA - as distinct from VB.NEt (so I may be off target here) you would either use

ActiveSheet.UsedRange
MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

(重置" UsedRange所需的第一行， SpecialCells(xlCellTypeLastCell)通常会提供比实际范围更大的范围)，或更常见的是

(the first line as needed to "reset" the UsedRange, SpecialCells(xlCellTypeLastCell) will often give a larger range than is actually present), or more commonly

或

Dim rng1 As Range
Set rng1 = ActiveSheet.Cells.Find("*", [a1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then MsgBox rng1.Row

这篇关于VB.NET-读取Excel文件的整个内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！