Excel to JSON Validation with Vitest

🚀 QuickStart

python3 scripts/extract_uids.py # Extract UIDs from Excel
python3 scripts/update_json_with_uids.py
python3 scripts/generate_tests.py

python3 scripts/extract_uids.py && python3 scripts/update_json_with_uids.py && python3 scripts/generate_tests.py

pnpm test-generated
pnpm serve-report

For both Testing and Test File Generation:

pnpm install

To Generate the test files (Python required):

pip install -r requirements.txt
pnpm vitest

Werify Panda and Openpyxl are installed:

python3 -c "import pandas, openpyxl; print('Python dependencies are installed!')"

## add this for testing verfication
pnpm vitest --version

To run the tests (no need to install python dependencies)

pnpm vitest
pnpm vitest tests/generated_tests.spec.js

📌 Overview

This project automates the validation of Excel data against a JSON dataset by generating Vitest test cases dynamically.
It ensures that:

• ✅ All Excel UIDs exist in the JSON
• ✅ Totals from Excel match JSON values within a tolerance range
• ✅ Invalid or missing totals are flagged as errors

---

🛠 Features

• 🔄 Dynamic Test Generation – Reads Excel & JSON, then creates Vitest test cases.
• 🎯 Customizable Tolerances – Compare values within 1%, 5%, or custom thresholds.
• ✨ Safe Name Formatting – Removes special characters from test names to avoid syntax issues.
• 🛠 Automated Validation – Run Vitest to verify all data matches correctly.

---

🚀 Installation

1️⃣ Install Python Dependencies

Ensure you have pandas and openpyxl installed:

pip install pandas openpyxl

2️⃣ Install Vitest (if not installed)

pnpm:

pnpm add -D vitest

---

📂 Project Structure

📂 src/                   # Source code
│── 📜 config.py          # Configurations (paths, settings)
│── 📜 file_handler.py    # Handles reading/writing Excel & JSON
│── 📜 test_generator.py  # Generates Vitest test files
│── 📜 utils.py           # Utility functions (like name normalization)
📂 tests/                 # Testing and generated test files
│── 📜 generated_tests.spec.js  # Generated test file (dynamic)
📂 data/                  # Store raw data
│── 📄 LOVE.xlsx          # Source Excel file
│── 📄 formatted_section_uid.json  # JSON reference file
📂 scripts/               # Helper scripts
│── 📜 generate_tests.py  # Run test file generation manually
📜 .gitignore             # Ignore unnecessary files (node_modules, logs)
📜 README.md              # Documentation
📜 package.json           # Vitest setup (if applicable)
📜 requirements.txt       # Python dependencies
📜 pre-commit             # Git Hook (optional, auto-run tests)

---

📂 Usage

Run the Script

python3 scripts/generate_tests.py

This will generate generated_tests.spec.js in the tests/ directory.

---

🧪 Running Tests

Once the test file is generated, run Vitest:

pnpm vitest

or

npx vitest

---

Debugging

python3 --version
which python3
echo $VIRTUAL_ENV
source venv/bin/activate 

echo $VIRTUAL_ENV
which python3

pip freeze
pip install -r requirements.txt
python3 -c "import sys; print(sys.path)"

restart the terminal

python3 -c "import src.config; print('✅ Config imported successfully!')"
python3 scripts/generate_tests.py

---

⚡ Automate with Git Hooks (Optional)

To automatically generate and run tests before committing code, set up a Git pre-commit hook.

1️⃣ Open the Git hooks folder:

nano .git/hooks/pre-commit

2️⃣ Add the following lines:

#!/bin/sh
python3 scripts/generate_tests.py
pnpm vitest

3️⃣ Save and exit (CTRL+X, Y, Enter).

Now, every time you commit, tests will be automatically generated and run! 🚀

---

📜 License

MIT License - Free to use and modify.

Tree commnad without node and python

tree -I 'node_modules|__pycache__' -L 2