Excel Tools

A command-line tool for comparing and merging Excel files (.xls, .xlsx).

Features

Compare Excel Files

Compare two Excel files and identify differences:

• Sheet comparison: Detects missing sheets in either file
• Structure analysis: Reports differences in row and column counts
• Cell-by-cell comparison: Identifies exact cell value differences with location
• CI/CD integration: Returns exit codes (0 = no differences, 1 = differences found)

Merge Excel Files

Combine two Excel files into a single output file:

• Sheet merging: Intelligently merges sheets that exist in both files
• Row appending: Appends data rows from the second file to the first file
• Header handling: Automatically skips duplicate header rows (configurable)
• Sheet preservation: Copies sheets that exist only in one file
• Format support: Works with both legacy .xls and modern .xlsx formats
• Output format: Output format matches the output file extension

Installation

pip install excel-util-tools

Usage

Compare Two Excel Files

Compare two Excel files to find differences:

excel-util-tools --compare file1.xlsx file2.xlsx

Or using the subcommand:

excel-util-tools compare file1.xlsx file2.xlsx

What it compares:

• Sheet names (reports missing sheets)
• Sheet dimensions (row and column counts)
• Every cell value in common sheets

Exit Codes:

• 0: No differences found - files are identical
• 1: Differences detected - files differ
• 2: Error occurred (file not found, invalid format, etc.)

Example Output:

Comparing:
  FILE1  : file1.xlsx
  FILE2  : file2.xlsx

Structure mismatch in 'Sheet1' (FILE1: 10x5, FILE2: 12x5)
Cell mismatch [Sheet1] Row 5, Col 3 | FILE1='100' vs FILE2='200'
Sheet missing in FILE2: Summary

Differences detected.

Quiet Mode:

excel-util-tools compare file1.xlsx file2.xlsx --quiet

Suppresses all output and only returns exit codes (useful for scripts).

Merge Two Excel Files

Merge two Excel files into a single output file:

excel-util-tools --merge file1.xlsx file2.xlsx output.xlsx

Or using the subcommand:

excel-util-tools merge file1.xlsx file2.xlsx output.xlsx

How merging works:

1. Common Sheets: For sheets that exist in both files:

   • All rows from the first file are copied
   • Data rows from the second file are appended (header row is skipped by default)
   • Column alignment matches the first file's structure

2. Unique Sheets: Sheets that exist only in one file are copied entirely to the output

3. Header Row: By default, the header row from the second file is skipped to avoid duplication. Use --no-skip-header to include it.

Example Scenarios:

# Merge two data files, skipping duplicate header
excel-util-tools merge data1.xlsx data2.xlsx merged.xlsx

# Merge files including header from second file
excel-util-tools merge data1.xlsx data2.xlsx merged.xlsx --no-skip-header

# Merge .xls files
excel-util-tools merge file1.xls file2.xls output.xls

# Quiet mode (suppress progress messages)
excel-util-tools merge file1.xlsx file2.xlsx output.xlsx --quiet

Merge Output:

Merging sheet: Sheet1
Adding sheet 'Summary' from file2

Merged file saved to: output.xlsx

Command-Line Reference

Compare Command

excel-util-tools compare FILE1 FILE2 [OPTIONS]

Arguments:
  FILE1                 First Excel file to compare (.xls or .xlsx)
  FILE2                 Second Excel file to compare (.xls or .xlsx)

Options:
  --quiet, -q           Suppress output, only return exit code
  --help, -h            Show help message

Legacy format:
  excel-util-tools --compare FILE1 FILE2

Merge Command

excel-util-tools merge FILE1 FILE2 OUTPUT [OPTIONS]

Arguments:
  FILE1                 First Excel file (.xls or .xlsx)
  FILE2                 Second Excel file (.xls or .xlsx)
  OUTPUT                Output file path (.xls or .xlsx)

Options:
  --no-skip-header      Include header row from second file (default: skip)
  --quiet, -q           Suppress progress messages
  --help, -h            Show help message

Legacy format:
  excel-util-tools --merge FILE1 FILE2 OUTPUT

Python API

Use Excel Tools programmatically in your Python code:

from excel_util_tools import compare_excel_files, merge_excel_files

# Compare files
differences = compare_excel_files("file1.xlsx", "file2.xlsx", verbose=True)
if differences:
    print("Files are different")
else:
    print("Files are identical")

# Merge files
merge_excel_files(
    "file1.xlsx", 
    "file2.xlsx", 
    "output.xlsx",
    skip_header_row=True,  # Skip header row from second file
    verbose=True           # Show progress messages
)

API Reference

compare_excel_files(file1, file2, verbose=True)

Compare two Excel files and return whether differences were found.

Parameters:

• file1 (str or Path): Path to first Excel file
• file2 (str or Path): Path to second Excel file
• verbose (bool): If True, print differences to stdout

Returns:

• bool: True if differences found, False if files are identical

Raises:

• ImportError: If required libraries are not installed
• ValueError: If file format is not supported
• FileNotFoundError: If file does not exist

merge_excel_files(file1, file2, output_file, skip_header_row=True, verbose=True)

Merge two Excel files into a single output file.

Parameters:

• file1 (str or Path): Path to first Excel file
• file2 (str or Path): Path to second Excel file
• output_file (str or Path): Path to output file
• skip_header_row (bool): If True, skip header row from second file (default: True)
• verbose (bool): If True, print progress messages (default: True)

Returns:

• str: Path to the output file

Raises:

• ImportError: If required libraries are not installed
• ValueError: If file format is not supported
• FileNotFoundError: If input file does not exist

Supported Formats

• .xlsx (Excel 2007+): Requires openpyxl
• .xls (Excel 97-2003): Requires xlrd (reading) and xlwt (writing)

All dependencies are automatically installed when you install excel-util-tools.

Use Cases

Data Validation

Compare exported data files to ensure consistency:

excel-util-tools compare production_data.xlsx staging_data.xlsx

Data Consolidation

Merge multiple data files into a single file:

excel-util-tools merge january.xlsx february.xlsx combined.xlsx

CI/CD Integration

Use in automated workflows to validate data:

excel-util-tools compare expected.xlsx actual.xlsx --quiet
if [ $? -eq 1 ]; then
    echo "Data validation failed!"
    exit 1
fi

Batch Processing

Process multiple file pairs:

for file in data*.xlsx; do
    excel-util-tools compare "${file}" "backup/${file}"
done

Requirements

• Python 3.7 or higher
• openpyxl (for .xlsx files)
• xlrd (for .xls files)
• xlwt (for .xls output)

License

MIT License - see LICENSE file for details