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