Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Comparing two Excel spread sheets

by Win (Novice)
on Apr 21, 2006 at 08:32 UTC ( #544814=perlquestion: print w/replies, xml ) Need Help??

Win has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

Is there anyway of comparing two spread sheets short of going through each cell and comparing each cell? I am really looking for a VBA command that I can activate using Perl. Not really sure if it is a Perl question though.

Replies are listed 'Best First'.
Re: Comparing two Excel spread sheets
by marto (Archbishop) on Apr 21, 2006 at 08:42 UTC

    Thanks (again) for not using Super Search before posting a question.
    compare two Excel spreadsheets is returned by a search for 'compare excel'. Also A few times in the past I have pointed out to you guides for converting VBA code to Perl, calling (MS Office based) macros from Perl and using Win32::OLE to drive MS Office applications. Please start using the advice you are given, and the Super Search feature of this site.

Re: Comparing two Excel spread sheets
by rinceWind (Monsignor) on Apr 21, 2006 at 08:41 UTC

    I'll give you a Perl answer though :).

    Use a combination of the modules Spreadsheet::ParseExcel and Data::Dumper. Parse both spreadsheets, serialise the results with Data::Dumper and diff the results (though you could use Array::Compare as an alternative).


    Oh Lord, won’t you burn me a Knoppix CD ?
    My friends all rate Windows, I must disagree.
    Your powers of persuasion will set them all free,
    So oh Lord, won’t you burn me a Knoppix CD ?
    (Missquoting Janis Joplin)

    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Comparing two Excel spread sheets
by planetscape (Chancellor) on Apr 21, 2006 at 11:52 UTC

    Here's what I use to (semi-)intelligently export all worksheets in a workbook to CSV files. In VBA:

    Sub BulkCSVExport() ' Export all worksheets to .CSV files. ' Surround text (not numbers!) with double quotes. ' Handle text that contains double quotes, as in: 18" X 11" X 11 +" Dim DestFile As String Dim FileNum As Integer Dim ColumnCount As Integer Dim RowCount As Integer Dim st As Integer Dim strTemp As String Dim wbk As Workbook Dim sht As Worksheet Dim intSheetCount As Integer Application.ScreenUpdating = False Set wbk = Application.ActiveWorkbook For intSheetCount = 1 To wbk.Sheets.Count Set sht = wbk.Sheets.Item(intSheetCount) sht.Activate DestFile = wbk.Name & "." & sht.Name & ".CSV" FileNum = FreeFile() On Error Resume Next Open DestFile For Output As #FileNum If Err <> 0 Then MsgBox "Cannot open filename " & DestFile End End If On Error GoTo 0 ActiveSheet.UsedRange.Select For RowCount = 1 To Selection.Rows.Count For ColumnCount = 1 To Selection.Columns.Count If IsNumeric(Selection.Cells(RowCount, ColumnCount +)) Then Print #FileNum, Selection.Cells(RowCount, _ ColumnCount).Value; Else ' strTemp = Selection.Cells(RowCount, ColumnCoun +t).Text ' Fix double quotes, lest they confuse the .CS +V format If InStr(strTemp, CStr(Chr$(34))) <> 0 Then st% = 1 While st% <= Len(strTemp) And InStr(st%, st +rTemp, CStr(Chr$(34))) > 0 st% = InStr(st%, strTemp, CStr(Chr$(34)) +) strTemp = Left$(strTemp, st%) & CStr(Chr +$(34)) & Mid$(strTemp, st% + 1) st% = st% + 2 Wend End If ' Print #FileNum, CStr(Chr$(34)) & strTemp & CSt +r(Chr$(34)); End If If ColumnCount = Selection.Columns.Count Then Print #FileNum, Else Print #FileNum, ","; End If Next ColumnCount Next RowCount Close #FileNum Next Application.ScreenUpdating = True End Sub

    Modifications, calling from Perl, and diffing the output are all left as exercises for the reader.

Re: Comparing two Excel spread sheets
by simon.proctor (Vicar) on Apr 21, 2006 at 09:01 UTC
    Well if you only have two sheets, one in each. You could just save each out as csv's and do a diff. Any diffs and they aren't the same.

    But that iimplies that you want to do a like for like comparison on data and data position.
    A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://544814]
Approved by marto
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2020-08-04 00:14 GMT
Find Nodes?
    Voting Booth?
    Which rocket would you take to Mars?

    Results (31 votes). Check out past polls.