Html To Spreadsheet Bundle Laravel Package

Getting Started

Minimal Setup

1. Install the Bundle

   composer require davidannebicque/html-to-spreadsheet-bundle
   

   Add to config/bundles.php (Symfony auto-discovers it, but explicit inclusion is recommended for clarity).

2. First Use Case: Basic HTML-to-XLSX Create a Twig template (templates/export/simple_table.html.twig) with annotated HTML:

   <table data-xls-file="report.xlsx">
       <thead>
           <tr>
               <th data-xls-style="header">Name</th>
               <th data-xls-style="header">Value</th>
           </tr>
       </thead>
       <tbody>
           <tr>
               <td>Item 1</td>
               <td data-xls-style="money">100.50</td>
           </tr>
       </tbody>
   </table>
   

3. Render in Controller

   use Dannebicque\HtmlToSpreadsheetBundle\Renderer\SpreadsheetRenderer;
   
   class ExportController extends AbstractController
   {
       public function export(SpreadsheetRenderer $renderer): Response
       {
           return $renderer->renderToResponse('export/simple_table.html.twig');
       }
   }
   

   Access /export to download report.xlsx.

Implementation Patterns

1. Twig + HTML as DSL

• Declarative Styling: Use data-xls-* attributes for styles, formulas, and metadata:

  <td data-xls-style="percent2" data-xls-value="0.75">75%</td>
  <td data-xls-formula="=SUM(A1:A10)">Total</td>
  

• Dynamic Data: Pass variables to Twig and reference them in data-xls-*:

  <th data-xls-style="{{ styleClass }}">Dynamic Header</th>
  

2. Multi-Format Export

• Single Renderer for All Formats:

  $renderer->renderToResponse('template.html.twig', 'report.ods'); // ODS
  $renderer->renderToResponse('template.html.twig', 'report.csv'); // CSV
  

• Auto-Detect Format: Omit extension to default to .xlsx.

3. Reusable Components

• Shared Styles: Define styles in a Twig macro or base template:

  {# templates/_styles.html.twig #}
  <style data-xls-style="success">
      background-color: #d4edda;
      font-weight: bold;
  </style>
  

• Include in Any Table:

  {% include '_styles.html.twig' %}
  <table data-xls-file="report.xlsx">
      <!-- ... -->
  </table>
  

4. Controller Integration

• Trait for DRY Code:

  use Dannebicque\HtmlToSpreadsheetBundle\Controller\SpreadsheetTrait;
  
  class ExportController extends AbstractController
  {
      use SpreadsheetTrait;
  
      public function export(): Response
      {
          return $this->renderSpreadsheet('template.html.twig', 'report.xlsx');
      }
  }
  

• Dependency Injection: Inject SpreadsheetRenderer for custom logic:

  public function customExport(SpreadsheetRenderer $renderer, User $user): Response
  {
      return $renderer->renderToResponse(
          'user_report.html.twig',
          'user_' ~ $user->id ~ '.xlsx',
          ['user' => $user]
      );
  }
  

5. Advanced Features

• Freeze Panes:

  <table data-xls-freeze-panes="A2">
      <!-- ... -->
  </table>
  

• Column Widths:

  <th data-xls-width="30">Narrow Column</th>
  

• Images:

  <td data-xls-image="path/to/logo.png" data-xls-image-width="100"></td>
  

6. Event Listeners

• Post-Render Hooks: Extend functionality via events:

  // config/services.yaml
  Dannebicque\HtmlToSpreadsheetBundle\Event\SpreadsheetEvents::POST_RENDER:
      tag: kernel.event_listener
      class: App\EventListener\SpreadsheetListener
  

  // src/EventListener/SpreadsheetListener.php
  public function onPostRender(PostRenderEvent $event): void
  {
      $spreadsheet = $event->getSpreadsheet();
      $sheet = $spreadsheet->getActiveSheet();
      $sheet->setSelectedCell('A1'); // Auto-select cell after render
  }
  

Gotchas and Tips

Pitfalls

1. Attribute Conflicts:

   • Avoid naming collisions with data-* attributes used by other libraries (e.g., data-xls-* vs. data-toggle).
   • Fix: Use a namespace prefix (e.g., data-app-xls-*) or wrap tables in a container:

     <div data-xls-namespace="app">
         <table data-xls-file="report.xlsx">
             <!-- Attributes become data-app-xls-* -->
         </table>
     </div>
     

2. Style Inheritance:

   • Styles defined in <style> tags apply globally. Override them with inline data-xls-style:

     <style data-xls-style="default">...</style>
     <td data-xls-style="override">Overrides default</td>
     

3. CSV Quirks:

   • CSV exports escape commas and quotes automatically, but complex data (e.g., embedded commas) may require manual handling:

     <td data-xls-csv-delimiter="|">Value, with, commas</td>
     

4. Performance with Large Data:

   • PhpSpreadsheet loads the entire spreadsheet into memory. For >10,000 rows:
     • Tip: Use chunked exports or consider PhpSpreadsheet’s setReadDataOnly(true).
     • Workaround: Pre-generate sheets and merge them:

       $spreadsheet = $renderer->render('template.html.twig');
       $sheet = $spreadsheet->getActiveSheet();
       // Manually add more rows via PhpSpreadsheet API
       

5. French Presets:

   • The bundle includes French-specific formats (e.g., float2 for 1 000,50 instead of 1,000.50).
   • Gotcha: Overriding these may break localization. Use sparingly or extend:

     $renderer->addStyle('custom_float', ['numberFormat' => '#,##0.00']);
     

Debugging Tips

1. Inspect Rendered HTML:

   • Validate data-xls-* attributes before rendering. Use Twig’s dump():

     {{ dump(table|slice(0, 1)) }} {# Inspect first row #}
     

2. PhpSpreadsheet Errors:

   • Enable PhpSpreadsheet’s logging for low-level issues:

     // config/packages/dannebicque_html_to_spreadsheet.yaml
     dannebicque_html_to_spreadsheet:
         php_spreadsheet:
             log_level: 200 {# Log DEBUG messages #}
     

3. File Download Issues:

   • Ensure the response has the correct headers. Use Symfony’s StreamedResponse for large files:

     use Symfony\Component\HttpFoundation\StreamedResponse;
     
     public function largeExport(): StreamedResponse
     {
         return new StreamedResponse(
             function () use ($renderer) {
                 $response = $renderer->renderToResponse('large_table.html.twig', 'large.xlsx');
                 $response->send();
             },
             200,
             ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']
         );
     }
     

Extension Points

1. Custom Styles:

   • Add global styles via the renderer:

     $renderer->addStyle('custom_bold', ['font' => ['bold' => true]]);
     

   • Use in Twig:

     <td data-xls-style="custom_bold">Bold Text</td>
     

2. Pre/Post-Processing:

   • Extend the SpreadsheetRenderer class to modify the spreadsheet object:

     class CustomRenderer extends SpreadsheetRenderer
     {
         protected function postProcess(Spreadsheet $spreadsheet): void
         {
             $sheet = $spreadsheet->getActiveSheet();
             $sheet->getStyle('A1')->getFont()->setSize(14);
         }
     }
     

     Register as a service:

     # config/services.yaml