Compare EXcels using ADODB

[Rishav]

Hi all

On searching through the forums i found an example by PTRex which seems intriguing. He used ADO to query the excel row numbers.

i decided to try to use his code for the comparison, but am not really going anywhere.

I need to compare the contents of the two excels.

Also, i need to compare the color contents of the two excels.

Here is the simple code for comparing two excels (using color content code from the awesome 99ojo).

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>

Dim $timerarray[11][2]


$File1 = FileOpenDialog("Select Excel", "C:\Documents and Settings\rishavs\My Documents\My Dropbox\Workspace\Automata\Automata Scripts\Output Files", "xls (*.xls)")
$File2 = FileOpenDialog("Select Excel", "C:\Documents and Settings\rishavs\My Documents\My Dropbox\Workspace\Automata\Automata Scripts\Reference Files", "xls (*.xls)")

$totaltime = TimerInit()
$timer = TimerInit()

; Open the excel
$oExcel1 = _ExcelBookOpen($File1, 0)

$timerdiff = Round(TimerDiff($timer)/1000,1)
$timerarray[0][0] = "Open Excel 1"
$timerarray[0][1] = $timerdiff

$timer = TimerInit()
; Read excel into array
$ExcelArray1 = _ExcelReadSheetToArray($oExcel1)

$timerdiff = Round(TimerDiff($timer)/1000,1)
$timerarray[1][0] = "Excel to Array 1"
$timerarray[1][1] = $timerdiff


; Create new array with proper size. Dimensions are same as the array created from the excel
Dim $ArrayColor1[$ExcelArray1[0][0] + 1][$ExcelArray1[0][1] + 1]

$timer = TimerInit()

;Get color for 1st
For $p = 1 To $ExcelArray1[0][0]
    For $q = 1 To $ExcelArray1[0][1]
        $ArrayColor1[$p][$q] = $oExcel1.Activesheet.Cells($p, $q).Interior.ColorIndex
    Next
Next

$timerdiff = Round(TimerDiff($timer)/1000,1)
$timerarray[2][0] = "Excel color to to Array 1"
$timerarray[2][1] = $timerdiff

$timer = TimerInit()

_ExcelBookClose($oExcel1, 0)

$timerdiff = Round(TimerDiff($timer)/1000,1)
$timerarray[3][0] = "Excel close 1"
$timerarray[3][1] = $timerdiff

$timer = TimerInit()
; Open 2nd excelbook
$oExcel2 = _ExcelBookOpen($File2, 0)

$timerdiff = Round(TimerDiff($timer)/1000,1)
$timerarray[4][0] = "Open Excel 2"
$timerarray[4][1] = $timerdiff


$timer = TimerInit()
; Read excel into array
$ExcelArray2 = _ExcelReadSheetToArray($oExcel2)

$timerdiff = Round(TimerDiff($timer)/1000,1)
$timerarray[5][0] = "Excel to Array 2"
$timerarray[5][1] = $timerdiff

; Create new array with proper size. Dimensions are same as the array created from the excel
Dim $ArrayColor2[$ExcelArray2[0][0] + 1][$ExcelArray2[0][1] + 1]

$timer = TimerInit()
;Get color for 2nd
For $p = 1 To $ExcelArray2[0][0]
    For $q = 1 To $ExcelArray2[0][1]
        $ArrayColor2[$p][$q] = $oExcel2.Activesheet.Cells($p, $q).Interior.ColorIndex
    Next
Next

$timerdiff = Round(TimerDiff($timer)/1000,1)
$timerarray[6][0] = "Excel color to to Array 2"
$timerarray[6][1] = $timerdiff

$timer = TimerInit()
_ExcelBookClose($oExcel2, 0)

$timerdiff = Round(TimerDiff($timer)/1000,1)
$timerarray[7][0] = "Excel close 2"
$timerarray[7][1] = $timerdiff

#cs ----------------------------------------------------------------------------
    Compare the arrays made up of the excel data
#ce ----------------------------------------------------------------------------

; 1st compare the excel array size
If $ExcelArray1[0][0] = $ExcelArray2[0][0] And $ExcelArray1[0][1] = $ExcelArray2[0][1] Then

$timer = TimerInit()

    ; 2nd compare excel arrays content
    $ExcelContent_comp_result = "Pass"
    For $p = 0 To $ExcelArray1[0][0]
        For $q = 0 To $ExcelArray1[0][1]
            If $ExcelArray1[$p][$q] <> $ExcelArray2[$p][$q] Then
                $ExcelContent_comp_result = "Fail"
                MsgBox(0, "Excel Compare", "Excel array compare fail")
            EndIf
        Next
    Next

    $timerdiff = Round(TimerDiff($timer)/1000,1)
$timerarray[8][0] = "Compare excel array contents"
$timerarray[8][1] = $timerdiff

$timer = TimerInit()
    If $ExcelContent_comp_result <> "Fail" Then

        ; 3rd compare the color info
        $ExcelColor_comp_result = "Pass"
        For $p = 0 To UBound($ArrayColor1) - 1
            For $q = 0 To UBound($ArrayColor1, 2) - 1
                If $ArrayColor1[$p][$q] <> $ArrayColor2[$p][$q] Then
                    $ExcelColor_comp_result = "Fail"
                    MsgBox(0,"Excel Compare", "Excel color compare" & $ExcelColor_comp_result)
                EndIf
            Next
        Next
    EndIf

    $timerdiff = Round(TimerDiff($timer)/1000,1)
$timerarray[9][0] = "Excel color array compare"
$timerarray[9][1] = $timerdiff

$totaltimediff = Round(TimerDiff($totaltime)/1000,1)
$timerarray[10][0] = "total time"
$timerarray[10][1] = $totaltimediff
Else

    MsgBox(0, "Excel Compare", "Excel array dimension compare fail")
EndIf

MsgBox(0, "Excel Compare", $ExcelContent_comp_result)
_ArrayDisplay($timerarray)

And here is my bastardization of PT's code. :/

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>

Dim $timerarray[11][2]


$File1 = FileOpenDialog("Select Excel", "", "xls (*.xls)")
$File2 = FileOpenDialog("Select Excel", "", "xls (*.xls)")

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001
Global $s_Filename1 = FileGetShortName($File1)
Global $s_Filename2 = FileGetShortName($File2)

Global $s_Tablename1 = "[Sheet1$]"
Dim $s_Tablename2 = "[Sheet1$]"

; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

; Source XLS data
$objConnection1 = ObjCreate("ADODB.Connection")
$objRecordSet1 = ObjCreate("ADODB.Recordset")
$objConnection1.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & $s_Filename1 & ";" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";")

$objConnection2 = ObjCreate("ADODB.Connection")
$objRecordSet2 = ObjCreate("ADODB.Recordset")
$objConnection2.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & $s_Filename2 & ";" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";")

;~ $objRecordSet1.Open("Select Count(*) FROM" & $s_Tablename1 & "Order by 1 Asc", $objConnection1, $adOpenStatic, $adLockOptimistic, $adCmdText)
;~ $objRecordSet2.Open("Select Count(*) FROM" & $s_Tablename2 & "Order by 1 Asc", $objConnection2, $adOpenStatic, $adLockOptimistic, $adCmdText)

$objConnection3 = ObjCreate("ADODB.Connection")
$objRecordSet3 = ObjCreate("ADODB.Recordset")
$objRecordSet3.Open("select * from " & $s_Tablename1 & " except select * from " & $s_Tablename2 & "Order by 1 Asc", $objConnection3, $adOpenStatic, $adLockOptimistic, $adCmdText)
;~ $objRecordSet3.Open("select * from " & $s_Tablename1 & " minus select * from " & $s_Tablename2 & "Order by 1 Asc", $objConnection3, $adOpenStatic, $adLockOptimistic, $adCmdText)
Do
    ConsoleWrite($objRecordSet1.Fields(0).value + 1 & @CR) ; + 1 because it is 0 based
    ConsoleWrite($objRecordSet2.Fields(0).value + 1 & @CR) ; + 1 because it is 0 based
    $objRecordSet1.MoveNext()
    $objRecordSet2.MoveNext()
    ConsoleWrite($objRecordSet3.Fields(0).value & @CR)
    $objRecordSet3.MoveNext()
Until $objRecordSet1.EOF() or $objRecordSet2.EOF()

$objConnection1.Close
$objConnection1 = ""
$objRecordSet1 = ""

$objConnection2.Close
$objConnection2 = ""
$objRecordSet2= ""

$objConnection3.Close
$objConnection3 = ""
$objRecordSet3= ""

Func MyErrFunc()
    $HexNumber = Hex($oMyError.number, 8)
    MsgBox(0, "COM Test", "We intercepted a COM Error !" & @CRLF & @CRLF & _
            "err.description is: " & @TAB & $oMyError.description & @CRLF & _
            "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
            "err.number is: " & @TAB & $HexNumber & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.source is: " & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
    SetError(1) ; to check for after this function returns
EndFunc   ;==>MyErrFunc

Edited September 10, 2009 by Rishav

[PsaltyDS]

I don't think the objects created by ADO have the same methods and properties. For example, there is a collection of "Sheets" in and Excel object, but "Tables" in the ADO. Further, a "Range" in Excel has completely different methods and properties than a "RecordSet" in ADO. There isn't any such thing as an ".Interior.ColorIndex" property in an ADO object.

[Rishav]

shucks.

so, is there any way at all to fasten up the color index compare for excels?

can we get the entire worksheet color index in one step instead of doing it cell by cell?

a radically different approach? anything?

[hydroxide]

shucks.

so, is there any way at all to fasten up the color index compare for excels?

can we get the entire worksheet color index in one step instead of doing it cell by cell?

a radically different approach? anything?

Yea. but you'll have to look through Excel docs though. I don't remember much either.

I think you can use a Selection (and make all your cells part of that selection) and probably set the color index.