Excelbundle Laravel Package

Getting Started

Minimal Setup

1. Installation Add the bundle via Composer in your Laravel project (Symfony-compatible):

   composer require agence-gw/excelbundle
   

   Register the bundle in config/app.php under providers (Laravel 5.5+ uses auto-discovery, but manual registration may be needed for older versions):

   AgenceGw\ExcelBundle\AgenceGwExcelBundle::class,
   

2. Basic Usage Inject the ExcelManager service into your controller/service:

   use AgenceGw\ExcelBundle\Manager\ExcelManager;
   
   public function __construct(ExcelManager $excelManager) {
       $this->excelManager = $excelManager;
   }
   

3. First Use Case: Export to XLSX

   public function exportToExcel() {
       $excel = $this->excelManager->createExcel('my_file.xlsx');
       $sheet = $excel->getSheet(0);
   
       $sheet->setCellValue('A1', 'Hello, Excel!');
       $sheet->setCellValue('B1', 'Laravel + PHPExcel');
   
       return $this->excelManager->download($excel, 'my_file.xlsx');
   }
   

Implementation Patterns

Common Workflows

Reading Excel Files

public function importExcel(Request $request) {
    $file = $request->file('excel_file');
    $excel = $this->excelManager->readExcel($file->getPathname());

    $sheet = $excel->getSheet(0);
    $data = [];

    foreach ($sheet->getRowIterator() as $row) {
        $data[] = $row->getValues();
    }

    return response()->json($data);
}

Dynamic Sheet Creation

public function generateMultiSheetReport() {
    $excel = $this->excelManager->createExcel('report.xlsx');

    // Add first sheet
    $sheet1 = $excel->createSheet();
    $sheet1->setTitle('Sales Data');
    $sheet1->setCellValue('A1', 'Product');
    $sheet1->setCellValue('B1', 'Revenue');

    // Add second sheet
    $sheet2 = $excel->createSheet();
    $sheet2->setTitle('Expenses');
    $sheet2->setCellValue('A1', 'Category');
    $sheet2->setCellValue('B1', 'Amount');

    return $this->excelManager->download($excel, 'report.xlsx');
}

Styling and Formatting

public function styledExport() {
    $excel = $this->excelManager->createExcel('styled.xlsx');
    $sheet = $excel->getSheet(0);

    // Header style
    $headerStyle = $excel->getStyleFactory()->createStyle();
    $headerStyle->getFont()->setBold(true);
    $headerStyle->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

    $sheet->setCellValue('A1', 'ID');
    $sheet->setCellValue('B1', 'Name');
    $sheet->getStyle('A1:B1')->applyFromArray($headerStyle->toArray());

    return $this->excelManager->download($excel, 'styled.xlsx');
}

Integration with Laravel Services

public function exportUserData() {
    $users = User::all();
    $excel = $this->excelManager->createExcel('users.xlsx');
    $sheet = $excel->getSheet(0);

    $sheet->setCellValue('A1', 'ID');
    $sheet->setCellValue('B1', 'Email');
    $sheet->setCellValue('C1', 'Created At');

    $row = 2;
    foreach ($users as $user) {
        $sheet->setCellValue('A' . $row, $user->id);
        $sheet->setCellValue('B' . $row, $user->email);
        $sheet->setCellValue('C' . $row, $user->created_at->format('Y-m-d'));
        $row++;
    }

    return $this->excelManager->download($excel, 'users.xlsx');
}

Gotchas and Tips

Pitfalls and Debugging

1. Memory Limits

   • Large Excel files (>10MB) may hit PHP memory limits. Use chunking or optimize data before export:

     ini_set('memory_limit', '512M'); // Temporarily increase limit
     

   • For very large datasets, consider streaming or batch processing.

2. File Path Handling

   • Ensure file paths are absolute when reading files:

     $filePath = storage_path('app/uploads/report.xlsx');
     $excel = $this->excelManager->readExcel($filePath);
     

3. Sheet Indexing

   • Sheets are zero-indexed. Accessing non-existent sheets throws exceptions:

     // Throws exception if no sheet exists at index 5
     $sheet = $excel->getSheet(5);
     

4. Encoding Issues

   • Special characters (e.g., é, ñ) may corrupt Excel files. Use UTF-8 encoding explicitly:

     $excel->getProperties()->setCreator('UTF-8 Creator');
     

5. PDF/Odt Support Limitations

   • The bundle uses PHPExcel, which primarily supports .xlsx/.xls. PDF/ODT support is limited and may require additional libraries (e.g., mikehaertl/phpwkhtmltopdf for PDF).

Tips and Tricks

1. Reuse Styles Define styles once and reuse them:

   $boldStyle = $excel->getStyleFactory()->createStyle()
       ->getFont()
       ->setBold(true);
   
   $sheet->getStyle('A1:A10')->applyFromArray($boldStyle->toArray());
   

2. Merge Cells

   $sheet->mergeCells('A1:B1');
   $sheet->setCellValue('A1', 'Merged Header');
   

3. Auto-Size Columns

   $sheet->getColumnDimension('A')->setAutoSize(true);
   

4. Protect Sheets

   $sheet->getProtection()->setSheet(true);
   $sheet->getProtection()->setPassword('secret');
   

5. Leverage Events Use events for pre/post-processing:

   $excel->addListener(new MyExcelEventListener());
   

6. Validation Validate Excel files before processing:

   if (!$excel->isValid()) {
       throw new \RuntimeException('Invalid Excel file');
   }
   

7. Laravel Artisan Commands Create custom commands for bulk exports:

   use AgenceGw\ExcelBundle\Manager\ExcelManager;
   
   class ExportUsersCommand extends Command {
       protected $excelManager;
   
       public function __construct(ExcelManager $excelManager) {
           $this->excelManager = $excelManager;
           parent::__construct();
       }
   
       public function handle() {
           // Export logic here
       }
   }
   

8. Testing Mock ExcelManager in tests:

   $this->mock(ExcelManager::class)->shouldReceive('download')->once();