Excel File Comparision

[rajkumar24]

How to compare two excel files when there is a difference in color for the content of files.

[zfisherdrums]

Some help to aid in your journey:

In Excel:

Record a macro while changing color of a cell/range

Observe the code written in the macro.

In AutoIt:

Create an instance of Excel:

Use the code you observed in Excel to determine the color of a cell/range

Dim $oXl = ObjGet( "", "Excel.Application" )

Func _ExcelGetRangeColor( $oExcel, $sAddress )
    ; beware: sending only rc coordinates assumes the Activesheet context.
    ; To ensure that the correct sheet is assumed, pass in the sheet name:
    ;
    ;    example: $sAddress = "Sheet1!A1"
    
    Return $oExcel.Application.Range($sAddress). ; <---- finish this line with the property you observed in Excel
EndFunc

Dim $result = _ExcelGetRangeColor( $oXl, "Sheet1!A1" ) 
ConsoleWrite( $result & @CRLF)

Successfully completing this should show you how to leverage the Excel DOM ( and generated macro code ) in AutoIT.

[rajkumar24]

Thanx zfisherdrums,

I need some more help.I want to compare two excel files.I want the difference when there is a difference in color in cell.

Example:-In one excel the cell is filled with Red and in another excel the cell is filled with Green.So there is a difference in color.So how can i compare.

[rajkumar24]

Hi...Please find the 2 attachment.

In the two excel file,colors for the cells are different.So how can i compare and how can i get the difference report.

Is this possible in AUTO IT.

File1:-

Cell color

1234 red

245 green

3534 yellow

File2:-

Cell color

1234 yellow

245 red

3534 green

[Manjish]

pixelgetcolor() would be your best option here.. Open the excel file in visible mode and check the color of cells

[99ojo]

pixelgetcolor() would be your best option here.. Open the excel file in visible mode and check the color of cells

Hi,

not elegant:

expandcollapse popup

#include <excel.au3>

Global $aArrayColor [1] [1]
Global $bArrayColor [1] [1]

$excel1 = @UserProfileDir & "\Eigene Dateien\mappe1.xls" ;change to your excelfiles 
$excel2 = @UserProfileDir & "\Eigene Dateien\mappe2.xls" ;change to your excelfiles

$oExcel1 = _ExcelBookOpen ($excel1, 0) ;open 1st ExcelBook
$aArray = _ExcelReadSheetToArray ($oExcel1) ;reading Excelsheet to Array to get dimensions
ReDim $aArrayColor [$aArray [0] [0] + 1] [$aArray [0] [1] + 1] ;redim 1st array for colorstoring

;Get color for 1st
For $i = 1 To $aArray [0] [0]
    For $j = 1 To $aArray [0] [1]
        $aArrayColor [$i] [$j] = _ExcelGetRangeColor ($oExcel1, $i, $j)
    Next
Next
_ExcelBookClose ($oExcel1, 0)

$oExcel1 = _ExcelBookOpen ($excel2, 0) ;open 2nd excelbook
$aArray = _ExcelReadSheetToArray ($oExcel1)
ReDim $bArrayColor [$aArray [0] [0] + 1] [$aArray [0] [1] + 1] ;redim 2nd array for colorstoring

;Get color for 2nd
For $i = 1 To $aArray [0] [0]
    For $j = 1 To $aArray [0] [1]
        $bArrayColor [$i] [$j] = _ExcelGetRangeColor ($oExcel1, $i, $j)
    Next
Next
_ExcelBookClose ($oExcel1, 0)

;Compare the two color Arrays
For $i = 0 To UBound ($aArrayColor) - 1
    For $j = 0 To UBound ($aArrayColor, 2) - 1
        If $aArrayColor [$i] [$j] <> $bArrayColor [$i] [$j] Then
            MsgBox (0,"", "Row " &$i & " "& "Column " & $j & " different color!")
        EndIf
    Next
Next

Func _ExcelGetRangeColor( $oExcel, $sRow, $sColumn )
    ; beware: sending only rc coordinates assumes the Activesheet context.
    ; To ensure that the correct sheet is assumed, pass in the sheet name:
    ;
    ;    example: $sAddress = "Sheet1!A1"
    
    Return $oExcel.Activesheet.Cells($sRow, $sColumn).Interior.ColorIndex 
EndFunc

;-))

Stefan

[rajkumar24]

Thanks Stefan and Manjish......

To Stefan:-

What I need to define for the user defined functions like

_ExcelBookOpen

_ExcelReadSheetToArray

_ExcelBookClose

[Manjish]

hi these functions are already included in excel.au3 udf file.. its included in v 3.3.0.0.. If u r using lesser version pls download this file by searching in the forums

Edited August 21, 2009 by Manjish