Validation of excel sheet

[haseebhm]

My clients upload excel sheets to the software which need to be validates. One of the validation steps is to check the sheet for duplicate records. The following code demonstrates the method I use to make this possible.

 

For i = 0 To xlDtSet.Tables(0).Rows.Count - 1
           xlCommand2 = New System.Data.OleDb.OleDbDataAdapter("select [COL1], [COL2] from [sheet1$] WHERE [COL1]=" & xlDtSet.Tables(0).Rows(i).Item("COL1"), xlConnection)
           xlCommand2.Fill(xlDtSet2)
           If xlDtSet2.Tables(0).Rows.Count > 1 Then
               'LOG ERROR
               MsgBox("DUPLICATE")
           End If
           xlDtSet2.Clear()
Next

 

But this makes the validation process very very slow.Please suggest me other(faster) ways to do the same job. Thanks in advance.

 

I am using VB .NET 2008

[PlausiblyDamp]

Are you only checking for duplicates in Col1? If so could you not group by Col1 and do a count of matching rows?

[filip]

Hi,

 

easiest and fastest way to work with Excel in VB is by using some 3rd party component. You can try using GemBox.Spreadsheet - VB component for Excel. The component is free for commercial use with limit of 150 rows.

 

Filip

GemBox Software - Easy and fast read/write Excel files for .NET