vba 循环读取excel数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13676259/
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
looping read excel data
提问by Pauline
I need to loop the read data from excel to vb.net and when I reach the last row/column "!@#$%^&*()" the excel data will stop read. How can I do that?
我需要将读取的数据从 excel 循环到 vb.net，当我到达最后一行/列“！@#$%^&*()”时，excel 数据将停止读取。我怎样才能做到这一点？
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim xRange As Excel.Range
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub cmdGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGenerate.Click
'Dim row As String
Dim empty_cell_ctr As Integer = 0 '5
Dim end_of_xlsheet As Boolean = False
Dim sRow As Integer 'start row
Dim col_end As Integer 'col W
'
'loading excel(open and read)
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open("c:\sample.xls")
xlWorkSheet = xlWorkBook.Worksheets("Timesheet")
xlApp.Visible = True
While Not end_of_xlsheet
If sRow = "'!@#$%^&*()_+" Then
xRange = xRange.Cells(sRow, col_end)
end_of_xlsheet = False 'end of sheet
Continue While
End If
sRow += 1
End While
MessageBox.Show(sRow)
End Sub
End Class
回答by JMK
You seem to be overthinking this, you can get all of the data in the spreadsheet by accessing the UsedRangeproperty, and then load this into a 2D array variable by accessing the Value2property of this object like so:
您似乎想得太多了，您可以通过访问UsedRange属性获取电子表格中的所有数据，然后通过访问此对象的Value2属性将其加载到二维数组变量中，如下所示：
Dim application = New Excel.Application()
Dim workbook As Excel.Workbook = application.Workbooks.Open("C:\aaa\bbb.xlsx")
Dim worksheet As Excel.Worksheet = workbook.Sheets(1)
Dim usedRange = worksheet.UsedRange
Dim usedRangeAs2DArray As Object(,) = usedRange.Value2
workbook.Save()
workbook.Close()
application.Quit()
Marshal.ReleaseComObject(application)
回答by bonCodigo
You may use LIKEoperator :)
您可以使用LIKE运算符:)
'Not sure why you are trying to check Row number for set of characters...
Excel.Range range = sheet.UsedRange;
Int rows_count = range.Rows.Count;
For (int sRow = 1; sRow <= rows_count; sRow++)
If (sheet.Cells[sRow, col_end].value Like "*'!@#$%^&*") Then
'-- input the data to where ever you want. It is best to store it into an array first..
xRange = sheet.Cells[i, col_end].value;
Break;
Else
sRow++;
End If
Please take a look the following reference for better understanding your options.
请查看以下参考资料以更好地了解您的选择。
