Excel Comparison Tool

A web application for column-wise comparison of two Excel files. Upload source, target, and config files — the tool highlights every cell mismatch in a downloadable text result.

Features

• Column-based comparison using a .properties config file
• Supports .xlsx and .xls formats
• Handles STRING, NUMERIC, and BOOLEAN cell types
• Float comparison with epsilon tolerance (1e-10)
• Skips sheets without header rows
• Docker Compose for one-command full-stack deployment
• 23 automated tests (JUnit 5 + Mockito)

Technologies

• Backend: Java 17, Jakarta Servlets 4.0, Apache POI 5.3, Log4j 2
• Frontend: Vanilla JavaScript, Vite 6
• Infrastructure: Docker Compose, Tomcat 9, nginx:alpine
• Testing: JUnit 5, Mockito 5.12

Architecture

Browser ──▶ nginx (:80) ── proxy ──▶ Tomcat (:8080)
                │                        │
                ├── static assets        ├── POST /Compare
                │   (dist/)              ├── GET  /health
                │                        └── Apache POI
                └── /templates/

See docs/architecture.md for the full system diagram and component breakdown.

Prerequisites

• Docker & Docker Compose (recommended — no manual setup)
• Alternative: JDK 17+, Maven 3.9+, Node.js 18+

Quick Start

Docker (recommended)

# Build and start everything
docker compose -f docker/docker-compose.yml up --build

# Backend health:  http://localhost:8080/health → OK
# Frontend UI:     http://localhost

The frontend includes sample files in frontend/public/samples/ for quick testing.

Local Development

Backend (requires JDK 17+, Maven 3.9+, Tomcat 9):

cd backend
mvn clean package

Deploy target/CompareUI.war to Tomcat's webapps/ROOT.war (delete the existing ROOT directory first). Start Tomcat — the API will be available at http://localhost:8080.

Frontend (requires Node.js 18+):

cd frontend
npm install
npm run dev

Opens at http://localhost:5173 with hot-reload. API requests to /Compare and /health are proxied to the Tomcat backend at http://localhost:8080.

Usage

1. Open http://localhost in a browser
2. Upload Source File (.xlsx/.xls), Target File (.xlsx/.xls), and Property File (.properties)
3. Click COMPARE FILES
4. A result_file.txt is downloaded with every mismatch

Property File Format

sourceColumns=Name,Address,Age
targetColumns=Name,Address,Age

Column names must match the header row in each file exactly (case-sensitive).

Sample Files

Pre-built samples are at frontend/public/samples/:

• ExcelSourceFile.xlsx — source workbook
• ExcelTargetFile.xlsx — target workbook (4 intentional mismatches)
• Excel.properties — config mapping Name,Address,Age,CreditLimit

API

Endpoint	Method	Description
/Compare	POST	Upload 3 files (multipart), returns text result
/health	GET	Returns OK (used by Docker healthcheck)

Testing

cd backend
mvn test

23 tests across 4 test classes:

• CompareTest (7) — servlet requests: missing parts, success, errors
• ComparisonConfigTest (5) — property file parsing
• ComparisonServiceTest (6) — comparison logic, edge cases
• WorkbookUtilsTest (5) — cell value extraction

Code Highlights

• Modular architecture — Separated into Comparer interface, ComparisonService, and ComparisonOrchestrator for clean dependency injection and testability
• Robust error handling — Sanitized HTTP responses with no stack trace leakage; comprehensive Log4j2 logging
• Precision comparison — Epsilon-based float comparison (Math.abs(a - b) < 1e-10) handles floating-point drift; supports STRING, NUMERIC, and BOOLEAN cell types
• File lifecycle management — Workbooks are opened, read, and closed in a controlled finally block to prevent resource leaks
• Test coverage — 23 automated tests across servlets, config parsing, comparison logic, and utility functions

Project Structure

excel-compare/
├── backend/
│   ├── src/main/java/compare/
│   │   ├── Compare.java                  # Servlet — HTTP entry point
│   │   ├── HealthServlet.java            # GET /health
│   │   ├── Comparer.java                 # Interface for testability
│   │   ├── ComparisonService.java        # Core comparison logic
│   │   ├── ComparisonOrchestrator.java   # Workflow + lifecycle
│   │   └── WorkbookUtils.java            # Cell value helpers
│   └── src/test/java/compare/
│       ├── CompareTest.java
│       ├── ComparisonConfigTest.java
│       ├── ComparisonServiceTest.java
│       └── WorkbookUtilsTest.java
├── frontend/
│   ├── src/                              # Vite source
│   │   ├── index.html
│   │   ├── script.js
│   │   └── style.css
│   ├── public/                           # Copied as-is to dist
│   │   ├── samples/
│   │   ├── templates/                    # Instructions + demo page
│   │   ├── images/
│   │   └── style.css
│   └── vite.config.js
├── docker/
│   ├── docker-compose.yml
│   ├── Dockerfile.backend
│   └── nginx.conf
└── docs/
    ├── architecture.md
    ├── workflow.md
    └── images/
        └── ui-screenshot.png

Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/my-change)
3. Make changes and run tests: cd backend && mvn test
4. If changing the frontend: cd frontend && npm run build
5. Commit and open a Pull Request

License

Copyright © 2024 Akshay Tadakod. All Rights Reserved.