Sign in to follow this  
Followers 0
Rishav

Compare EXcels using ADODB

4 posts in this topic

#1 ·  Posted (edited)

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).

#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. :/

#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 by Rishav

Share this post


Link to post
Share on other sites



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.

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

shucks. :D

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? :D

Share this post


Link to post
Share on other sites

shucks. :D

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? :D

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0