Spreadsheet Translator Symfony Bundle Laravel Package

Getting Started

Minimal Setup

1. Install Core Package:

   composer require samuelvi/spreadsheet-translator-symfony-bundle
   

2. Add Required Adapters (pick one provider, reader, and exporter):

   composer require samuelvi/spreadsheet-translator-provider-localfile
   composer require samuelvi/spreadsheet-translator-reader-matrix
   composer require samuelvi/spreadsheet-translator-exporter-xliff
   

3. Configure (config/packages/atico_spreadsheet_translator.yaml):

   atico_spreadsheet_translator:
       frontend:
           provider:
               name: 'local_file'
               source_resource: '%kernel.project_dir%/var/translations.xlsx'
           exporter:
               format: 'xliff'
               prefix: 'app_'
               domain: 'messages'
               destination_folder: '%kernel.project_dir%/translations'
           shared:
               default_locale: 'en'
               name_separator: '.'
               lazy_mode: true
   

4. First Use Case: Create a spreadsheet with columns: section, subsection, en_US, es_ES, etc. Run the command to generate translations:

   php bin/console atico:spreadsheet-translator:export
   

Implementation Patterns

Workflow Integration

1. Spreadsheet as Single Source of Truth:

   • Maintain translations in Google Sheets/Excel (shared or private).
   • Use google_drive or one_drive providers for cloud collaboration.
   • Example config for Google Drive (shared):

     provider:
         name: 'google_drive'
         source_resource: 'https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit'
     

2. Automated CI/CD Pipeline:

   • Trigger translation exports on git push to main branch.
   • Use Symfony’s CronBundle or a GitHub Action to run:

     php bin/console atico:spreadsheet-translator:export --env=prod
     

3. Multi-Domain Support:

   • Split translations into multiple sheets/tabs (e.g., auth, admin).
   • Override domain in config or use a custom command with --domain:

     php bin/console atico:spreadsheet-translator:export --domain=admin
     

4. Lazy Mode for Efficiency:

   • Enable lazy_mode: true to auto-generate hierarchical keys (e.g., contact.form.email).
   • Reduces manual key repetition in spreadsheets.

5. Locale-Specific Overrides:

   • Use default_locale to fall back to English if translations are missing.
   • Add locale-specific columns (e.g., es_MX, pt_BR) for regional variants.

Command-Line Usage

Symfony Integration

1. Service Autowiring: Inject the translator service into controllers/services:

   use Atico\Bundle\SpreadsheetTranslatorBundle\Service\SpreadsheetTranslator;
   
   public function __construct(private SpreadsheetTranslator $translator) {}
   

2. Event Listeners: Listen for kernel.terminate to auto-export translations on request:

   public function onKernelTerminate(RequestEvent $event): void {
       $this->translator->export();
   }
   

3. Twig Integration: Use Symfony’s built-in translation system with the generated files:

   {{ 'homepage.title'|trans }}
   

Gotchas and Tips

Common Pitfalls

1. Locale Format Mismatch:

   • Ensure locale columns match en_US, es_ES format (not en, es).
   • Fix: Use intl extension to validate locales:

     \IntlDateFormatter::create('en_US', IntlDateFormatter::NONE);
     

2. File Permissions:

   • Destination folder (destination_folder) must be writable by the web server.
   • Fix: Set permissions:

     chmod -R 775 %kernel.project_dir%/translations
     

3. Google/OneDrive Auth Issues:

   • For google_drive_auth/one_drive_auth, ensure OAuth credentials are configured in services.yaml:

     services:
         Atico\Bundle\SpreadsheetTranslatorBundle\Provider\GoogleDriveAuthProvider:
             arguments:
                 $clientId: '%env(GOOGLE_CLIENT_ID)%'
                 $clientSecret: '%env(GOOGLE_CLIENT_SECRET)%'
     

4. Empty or Malformed Spreadsheets:

   • Missing section/subsection columns will break key generation.
   • Fix: Use --dry-run to validate before export.

5. Lazy Mode Quirks:

   • Lazy mode assumes hierarchical relationships (e.g., contact.form → contact.form.email).
   • Fix: Manually define full keys if relationships are non-linear.

Debugging Tips

1. Enable Verbose Output:

   php bin/console atico:spreadsheet-translator:export -v
   

2. Check Logs:

   • Enable Symfony’s debug mode to log provider/reader/exporter errors:

     framework:
         profiler: { only_exceptions: false }
     

3. Validate Spreadsheet Structure:

   • Use the matrix reader’s debug mode (if available) or inspect the raw data:

     $reader = new \Atico\SpreadsheetTranslator\Reader\MatrixReader($spreadsheet);
     var_dump($reader->getData());
     

Extension Points

1. Custom Providers:

   • Extend \Atico\SpreadsheetTranslator\Provider\AbstractProvider to support AWS S3, Dropbox, etc.
   • Example:

     class S3Provider extends AbstractProvider {
         public function getSpreadsheet(): \PhpOffice\PhpSpreadsheet\Spreadsheet {
             $client = new \Aws\S3\S3Client([...]);
             $file = $client->getObject([...]);
             return $this->reader->loadFromStream($file['Body']);
         }
     }
     

2. Custom Exporters:

   • Implement \Atico\SpreadsheetTranslator\Exporter\ExporterInterface for JSON, CSV, or database exports.
   • Example JSON exporter:

     class JsonExporter implements ExporterInterface {
         public function export(array $data, string $locale, string $domain): string {
             return json_encode($data, JSON_PRETTY_PRINT);
         }
     }
     

3. Pre/Post-Export Hooks:

   • Use Symfony’s event system to modify data before export:

     # config/services.yaml
     services:
         App\EventListener\TranslationExportListener:
             tags:
                 - { name: kernel.event_listener, event: atico.spreadsheet_translator.export, method: onExport }
     

Performance Tips

1. Cache Exports:

   • Cache generated translation files for 1 hour to avoid re-exporting on every request:

     $cache = new \Symfony\Component\Cache\SimpleFileCache($this->cacheDir);
     if (!$cache->get('translations_exported_at')) {
         $this->translator->export();
         $cache->set('translations_exported_at', true, 3600);
     }
     

2. Batch Processing:

   • For large spreadsheets, process sheets in chunks:

     $spreadsheet = $provider->getSpreadsheet();
     foreach ($spreadsheet->getSheetNames() as $sheetName) {
         $worksheet = $spreadsheet->getSheetByName($sheetName);
         $this->processWorksheet($worksheet);
     }
     

3. Disable Lazy Mode for Large Projects:

   • Lazy mode can slow down key generation for spreadsheets with >1000 rows.
   • Fix: Set lazy_mode: false and manually define keys.