Compare Excel Sheets with Perl Scripting: Quick Guide

The versatility of Perl scripting extends to various data manipulation tasks, including comparing and analyzing Excel sheets. If you find yourself needing to compare data between different Excel files or within the same file, Perl can offer an efficient solution. In this guide, we'll walk through how to use Perl to compare Excel sheets, ensuring you can quickly identify differences, updates, or similarities without manual checks.

Why Perl for Comparing Excel Sheets?

• Ease of Use: Perl’s straightforward syntax makes it an excellent choice for scripting tasks, especially for those who may not be professional programmers but need to perform complex data operations.
• Powerful Regular Expressions: Perl excels at pattern matching, which is beneficial for identifying specific data entries or changes within spreadsheets.
• Extensibility: With Perl, you can easily add libraries or modules to extend its functionality, like handling Excel files more naturally with modules such as Spreadsheet::Read or Excel::Writer::XLSX.

Setting Up Perl for Excel Manipulation

Before we dive into the script, you’ll need to ensure Perl and necessary modules are installed on your system:

• Install Perl, if not already present. It’s available for all major platforms.
• Install the Spreadsheet::ParseExcel module for reading Excel files. Use the CPAN module installer with the command:

cpan Spreadsheet::ParseExcel

• For writing or modifying Excel files, you might also want to install Excel::Writer::XLSX:

cpan Excel::Writer::XLSX

Basic Example of Comparing Two Excel Sheets

Let’s begin with a simple script to compare two sheets from different Excel files for any differences in data:

#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::ParseExcel;

my $parser = Spreadsheet::ParseExcel->new();
my $book1 = $parser->parse('file1.xlsx') or die "Parser can't open file1.xlsx: $!";
my $book2 = $parser->parse('file2.xlsx') or die "Parser can't open file2.xlsx: $!";

my $sheet1 = $book1->worksheet(0);
my $sheet2 = $book2->worksheet(0);

for my $row (0 .. $sheet1->row_range()) {
    for my $col (0 .. $sheet1->col_range()) {
        my $cell1 = $sheet1->get_cell($row, $col);
        my $cell2 = $sheet2->get_cell($row, $col);

        if ($cell1 && $cell2 && $cell1->value() ne $cell2->value()) {
            print "Difference found at row: $row, column: $col\n";
            print "file1.xlsx: " . $cell1->value() . "\n";
            print "file2.xlsx: " . $cell2->value() . "\n";
            print "------------------------\n";
        }
    }
}

🔑 Note: This script assumes both sheets have identical structures, i.e., the same number of rows and columns. If your Excel files have variable sheet sizes, adjustments would be needed.

Advanced Comparison Techniques

Comparing sheets isn’t just about finding differences in cell values. Here are some advanced techniques you might need:

Comparing Ranges

Instead of comparing entire sheets, you might want to focus on specific ranges:

my $range = $sheet1->get_cell_range(5, 10, 20, 25); # (start_row, start_col, end_row, end_col)

📝 Note: Comparing cell ranges can be useful for isolating sections of your Excel sheets for comparison, like comparing only data tables or specific sections.

Ignoring Certain Columns or Rows

You might want to skip certain columns or rows for comparison:

for my $row (0 .. $sheet1->row_range()) {
    next if $row == 0; # Skip headers
    for my $col (0 .. $sheet1->col_range()) {
        next if $col == 2; # Skip column C
        # Your comparison logic here
    }
}

Handling Different Sheet Structures

If the sheets have different structures, you’ll need to adapt the script to handle these discrepancies:

• Map cell positions based on content rather than coordinates.
• Use a hash to store cell values with keys reflecting their relative positions.

use strict;
use warnings;
use Spreadsheet::ParseExcel;

my $parser = Spreadsheet::ParseExcel->new();
my $book1 = $parser->parse('file1.xlsx') or die "Parser can't open file1.xlsx: $!";
my $book2 = $parser->parse('file2.xlsx') or die "Parser can't open file2.xlsx: $!";

my $sheet1 = $book1->worksheet(0);
my $sheet2 = $book2->worksheet(0);

my %data1; my %data2;

# Map cell values to hash keys
for my $row (0 .. $sheet1->row_range()) {
    for my $col (0 .. $sheet1->col_range()) {
        my $cell = $sheet1->get_cell($row, $col);
        $data1{"$row,$col"} = $cell->value() if $cell;
    }
}
for my $row (0 .. $sheet2->row_range()) {
    for my $col (0 .. $sheet2->col_range()) {
        my $cell = $sheet2->get_cell($row, $col);
        $data2{"$row,$col"} = $cell->value() if $cell;
    }
}

# Compare data
for my $key (keys %data1) {
    if (exists $data2{$key}) {
        if ($data1{$key} ne $data2{$key}) {
            print "Difference at cell: $key\n";
            print "file1: " . $data1{$key} . "\n";
            print "file2: " . $data2{$key} . "\n";
        }
    } else {
        print "Cell $key exists only in file1\n";
    }
}
for my $key (keys %data2) {
    if (!exists $data1{$key}) {
        print "Cell $key exists only in file2\n";
    }
}

🧩 Note: This approach allows for comparison even when sheets have different structures, but remember, it might not catch every structural nuance.

In conclusion, Perl scripting offers a robust platform for Excel sheet comparison. By leveraging Perl’s capabilities in data handling and the right modules, you can automate the comparison process, making it easier to detect discrepancies or updates across multiple spreadsheets. This not only saves time but also reduces the likelihood of human error, providing a reliable solution for data analysts, accountants, and anyone who regularly deals with Excel data.

Here’s how this guide has explored the power of Perl for Excel sheet comparison:

• Preparation: You’ve learned how to set up Perl with necessary modules for handling Excel files.
• Basic Comparison: A fundamental script was provided for comparing two sheets.
• Advanced Techniques: We covered methods for comparing specific ranges, handling sheets with different structures, and ignoring parts of the data.

This approach not only enhances productivity but also provides a scalable solution to manage and analyze large datasets.

How do I install Perl?

+

To install Perl, visit Perl’s official download page and choose the version suitable for your operating system. Most Unix-like systems come with Perl pre-installed, but you might need to update it.

Can I compare Excel sheets without Perl modules?

+

While Perl modules provide an easier interface, you could potentially use Perl’s system commands to read and compare text-based CSV exports of Excel sheets. However, this method would lack the formatting, formula preservation, and full data integrity that specialized modules offer.

What if the sheets have headers or footers?

+

Modify the script to skip the rows containing headers or footers when performing the comparison. You might need to identify these rows manually or by their content.