有没有更好的方法来检查Excel电子表格中的数据？ [英] Is there a better way to check data in excel spreadsheet?

问题描述

想检查电子表格第1行以验证数据是否正确。它是每列的标题。这似乎是读过它应该的单元格。



我尝试了什么：

want to check the spread sheet 1st row to verify the data is correct. It's the header for each column. This seems to read past the cell it should.

What I have tried:

private Sub SomeSubName(and so on)

 	Dim fp As String = SelectFile()
   	Dim ExcelData As DataTable
        Dim correctColumns = True

            ExcelData = PopulateDataTable(fp)
       
              
            If Not IsDBNull(ExcelData) Then
                Try
                    'loop over each column name and verify data is correct

                      For Each columnName In ExcelData.Columns
                   
                        Select Case columnName.ToString().ToLower()
                            Case "lastname"
                                correctColumns = True
                            Case "firstname"
                                correctColumns = True
                            Case "mi"
                                correctColumns = True
                            Case "suffix"
                                correctColumns = True
                            Case "street"
                                correctColumns = True
                            Case "city"
                                correctColumns = True
                            Case "state"
                                correctColumns = True
                            Case "zip"
                                correctColumns = True
                            Case "phone"
                                correctColumns = True
                            Case "fax"
                                correctColumns = True
                        End Select
                    Next

推荐答案

您可以使用以下

dim LastColumn作为long = 0

LastColumn = Sheet1.Cells.Range（A1）。End（xlToRight）.Column



，j = 1到LastColumn

MsgBox（Sheet1.Cells（j，i）.Value）

.....

....

...

next



此方法有助于以下场景

如果您有数据直到Z列，之后你删除了3列数据

但是ExcelData.Columns仍然会循环到Z，其中它应该只循环到W。

You could use the below
dim LastColumn as long=0
LastColumn=Sheet1.Cells.Range("A1").End(xlToRight).Column

for j=1 to LastColumn
MsgBox(Sheet1.Cells(j, i).Value)
.....
....
...
next

This method would help in the below scenario
If you have data till Column Z and after that you have deleted 3 columns data
But ExcelData.Columns would still loop till Z, wherein it should loop only upto W.

这篇关于有没有更好的方法来检查Excel电子表格中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！