# Cross-Platform Testing Guide

**Last Updated:** 2025-11-20

This guide provides instructions for testing Excel templates generated by the Ordio Template Generator across different platforms: Microsoft Excel, Google Sheets, and LibreOffice Calc.

## Overview

All templates generated by the Ordio Template Generator should be tested in:
1. **Microsoft Excel** (Windows/Mac) - Primary target platform
2. **Google Sheets** - Web-based alternative
3. **LibreOffice Calc** - Open-source alternative

## Testing Checklist

### Pre-Testing Setup

- [ ] Generate template using `php v2/systems/excel-template-generator/scripts/template-cli.php generate template.json output.xlsx`
- [ ] Verify template passes all quality gates: `php v2/systems/excel-template-generator/scripts/template-quality-gates.php template.json`
- [ ] Ensure template has valid schema: `php v2/systems/excel-template-generator/scripts/template-validator.php template.json output.xlsx`

### Microsoft Excel Testing

**Test Environment:**
- Excel 2016 or later (Windows/Mac)
- Excel Online (optional)

**Test Items:**

1. **File Opening**
   - [ ] File opens without errors
   - [ ] All sheets are visible and accessible
   - [ ] No corruption warnings

2. **Formulas**
   - [ ] All formulas calculate correctly
   - [ ] German function names work (SUMME, WENN, etc.)
   - [ ] Error handling works (WENNFEHLER)
   - [ ] No #NAME? or #REF! errors

3. **Data Validation**
   - [ ] Dropdown lists work
   - [ ] Date range validations work
   - [ ] Custom validation rules work
   - [ ] Error messages display correctly (in German)

4. **Conditional Formatting**
   - [ ] Conditional formatting rules apply correctly
   - [ ] Colors match Ordio branding
   - [ ] Formatting updates when data changes

5. **Styling & Branding**
   - [ ] Fonts display correctly (Inter, Gilroy-Bold)
   - [ ] Colors match Ordio brand colors
   - [ ] Cell styles (presets) apply correctly
   - [ ] Borders and fills display correctly

6. **Charts** (if applicable)
   - [ ] Charts render correctly
   - [ ] Chart titles display
   - [ ] Data labels work
   - [ ] Colors match brand palette

7. **Performance**
   - [ ] File opens quickly (< 3 seconds for typical templates)
   - [ ] Formulas recalculate quickly
   - [ ] No lag when scrolling/editing

**Common Excel Issues:**

- **Font Issues:** If Inter/Gilroy fonts are missing, Excel will substitute. Consider embedding fonts or using system fonts.
- **Formula Locale:** Ensure formulas use German locale (SUMME not SUM, WENN not IF).
- **Date Formats:** Verify dates display as DD.MM.YYYY format.

### Google Sheets Testing

**Test Environment:**
- Google Sheets (web-based)
- Google Sheets mobile app (optional)

**Test Items:**

1. **File Upload**
   - [ ] File uploads successfully
   - [ ] All sheets are preserved
   - [ ] No data loss during conversion

2. **Formulas**
   - [ ] Formulas convert correctly (may auto-translate to English)
   - [ ] Calculations are accurate
   - [ ] Error handling works

3. **Data Validation**
   - [ ] Dropdown lists work
   - [ ] Validation rules apply
   - [ ] Error messages display

4. **Conditional Formatting**
   - [ ] Conditional formatting rules apply
   - [ ] Colors display correctly
   - [ ] Formatting updates dynamically

5. **Styling**
   - [ ] Fonts display (may substitute if not available)
   - [ ] Colors match brand colors
   - [ ] Cell styles apply correctly

6. **Charts** (if applicable)
   - [ ] Charts render correctly
   - [ ] Chart data links correctly
   - [ ] Colors display correctly

**Common Google Sheets Issues:**

- **Formula Translation:** Google Sheets may auto-translate German formulas to English. Test that calculations still work.
- **Font Substitution:** Google Sheets will substitute fonts if Inter/Gilroy are not available.
- **Conditional Formatting:** Some advanced conditional formatting may not be fully supported.

### LibreOffice Calc Testing

**Test Environment:**
- LibreOffice Calc 7.0 or later
- OpenOffice Calc (optional, for compatibility)

**Test Items:**

1. **File Opening**
   - [ ] File opens without errors
   - [ ] All sheets are accessible
   - [ ] No compatibility warnings

2. **Formulas**
   - [ ] Formulas calculate correctly
   - [ ] German function names work
   - [ ] Error handling works

3. **Data Validation**
   - [ ] Dropdown lists work
   - [ ] Validation rules apply
   - [ ] Error messages display

4. **Conditional Formatting**
   - [ ] Conditional formatting rules apply
   - [ ] Colors display correctly
   - [ ] Formatting updates dynamically

5. **Styling**
   - [ ] Fonts display (may substitute)
   - [ ] Colors match brand colors
   - [ ] Cell styles apply correctly

6. **Charts** (if applicable)
   - [ ] Charts render correctly
   - [ ] Chart data links correctly
   - [ ] Colors display correctly

**Common LibreOffice Issues:**

- **Font Substitution:** LibreOffice will substitute fonts if Inter/Gilroy are not available.
- **Conditional Formatting:** Some advanced conditional formatting may have limited support.
- **Chart Rendering:** Chart appearance may differ slightly from Excel.

## Automated Testing Script

A script is available to help automate cross-platform testing:

```bash
php v2/scripts/test-cross-platform.php --template=template.json --output=output.xlsx
```

**Note:** This script requires manual verification in each platform, but it can:
- Generate test reports
- Check for known compatibility issues
- Validate formulas and structure

## Testing Workflow

### Step 1: Generate Template

```bash
php v2/systems/excel-template-generator/scripts/template-cli.php generate template.json output.xlsx
```

### Step 2: Run Quality Gates

```bash
php v2/systems/excel-template-generator/scripts/template-quality-gates.php template.json --strict
```

### Step 3: Test in Excel

1. Open `output.xlsx` in Microsoft Excel
2. Go through Excel testing checklist above
3. Document any issues

### Step 4: Test in Google Sheets

1. Upload `output.xlsx` to Google Drive
2. Open with Google Sheets
3. Go through Google Sheets testing checklist above
4. Document any issues

### Step 5: Test in LibreOffice

1. Open `output.xlsx` in LibreOffice Calc
2. Go through LibreOffice testing checklist above
3. Document any issues

### Step 6: Document Results

Create a test report documenting:
- Platform tested
- Issues found
- Workarounds or fixes applied
- Overall compatibility rating

## Known Compatibility Issues

### Formula Locale

**Issue:** German formulas (SUMME, WENN) may not work in English Excel installations.

**Solution:** 
- Use German locale Excel for testing
- Document that templates require German locale Excel
- Consider providing English formula versions for international users

### Font Availability

**Issue:** Inter and Gilroy-Bold fonts may not be available on all systems.

**Solution:**
- Use system font fallbacks
- Consider embedding fonts (increases file size)
- Document font requirements

### Conditional Formatting

**Issue:** Some advanced conditional formatting may not be fully supported in Google Sheets/LibreOffice.

**Solution:**
- Test conditional formatting in all platforms
- Use simpler formatting rules when possible
- Document platform-specific limitations

### Chart Compatibility

**Issue:** Charts may render differently across platforms.

**Solution:**
- Test charts in all platforms
- Use standard chart types when possible
- Document visual differences

## Best Practices

1. **Test Early:** Test templates during development, not just at the end
2. **Test Often:** Re-test after making changes
3. **Document Issues:** Keep a log of compatibility issues and solutions
4. **Use Quality Gates:** Run quality gates before manual testing
5. **Automate When Possible:** Use automated testing scripts to catch common issues

## Test Report Template

```markdown
# Template Test Report: [Template Name]

**Date:** YYYY-MM-DD
**Template File:** template.json
**Generated File:** output.xlsx

## Excel Testing
- Status: ✓ Pass / ✗ Fail
- Issues: [List any issues]
- Notes: [Additional notes]

## Google Sheets Testing
- Status: ✓ Pass / ✗ Fail
- Issues: [List any issues]
- Notes: [Additional notes]

## LibreOffice Testing
- Status: ✓ Pass / ✗ Fail
- Issues: [List any issues]
- Notes: [Additional notes]

## Overall Compatibility
- Rating: Excellent / Good / Fair / Poor
- Recommendations: [Any recommendations]
```

## References

- **Excel Compatibility:** [Microsoft Excel File Formats](https://support.microsoft.com/en-us/office/excel-file-formats)
- **Google Sheets Compatibility:** [Google Sheets File Formats](https://support.google.com/docs/answer/49103)
- **LibreOffice Compatibility:** [LibreOffice File Formats](https://www.libreoffice.org/discover/file-formats/)