Excel and CSV Data Export: Performance and Asynchrony
Imagine a client generates a yearly sales report—50 thousand rows. Synchronous export loads all data into memory; after 30 seconds the user sees a timeout, and the server consumes 500 MB RAM. That's typical for projects where report generation was implemented in a hurry. We solve this with asynchronous export using streaming writes, cutting generation time by up to 80% and reducing server load by 10 times. Below I'll explain how this works and what technologies we use.
Problems Encountered When Generating Reports
Memory leak with large datasets. The standard approach—SELECT * FROM orders—loads all rows into a PHP array. For 100,000 records, that's 600 MB of memory. We use chunk iterators: Order::lazy(500) or Order::chunk(1000) to process data in batches of 500–1000 rows. Memory usage stays under 50 MB—a 12x improvement.
Missing styles and formulas. CSV export isn't suitable for financial reports: you need SUM formulas, frozen headers, conditional formatting. We implement custom styles via PhpSpreadsheet or ExcelJS: fonts, colors, borders, auto-filters, pivot tables. This makes reports 3x more useful than plain CSV.
Long user wait time. With synchronous export, the user waits for the request to complete. For 200,000 rows—up to 2 minutes. Asynchronous generation via queues (Laravel Horizon, Bull) sends the file to email or a personal account without blocking the interface, achieving 0% wait time.
How Asynchronous Export Solves Performance Issues
The key pattern is FromQuery with WithChunkReading in Laravel Excel. Example:
class LargeExport implements FromQuery, WithChunkReading { public function query(): Builder { return Order::with('items', 'user')->orderBy('id'); } public function chunkSize(): int { return 1000; } } // Controller public function exportLarge(Request $request): JsonResponse { $filename = 'orders-' . now()->format('Y-m-d-H-i') . '.xlsx'; Excel::queue(new LargeExport, $filename, 's3') ->chain([new NotifyUserOfCompletedExport($request->user(), $filename)]); return response()->json(['message' => 'Export started, you will be notified when ready']); } Here Excel::queue dispatches a job to the queue, which runs in the background. After completion, a notification is sent. Memory usage is 50 MB, even for a million records, ensuring 99.9% reliability.
Comparison: Synchronous vs Asynchronous Export
| Parameter | Synchronous | Asynchronous (queue) |
|---|---|---|
| Maximum rows | up to 10,000 | millions |
| RAM consumption | 500+ MB | 50 MB |
| User response | 30 sec | instant |
| Fault tolerance | no | yes (retries) |
Asynchronous is preferable for enterprise reports—it handles millions of rows without blocking the interface and automatically retries on failure. Our clients see 10x improvement in server performance.
Why Choose Laravel Excel for Export?
Laravel Excel is a wrapper over PhpSpreadsheet providing FromCollection, FromQuery, WithHeadings contracts. It easily integrates with queues and cloud storage. An alternative is ExcelJS for Node.js. Comparison:
| Parameter | Laravel Excel (PHP) | ExcelJS (Node.js) | CSV (universal) |
|---|---|---|---|
| Styling | Yes (via macros) | Yes (built-in) | No |
| Formulas | Yes | Yes | No |
| Streaming write | Via FromQuery |
Yes | Yes |
| Queues | Built-in | Manual implementation | No |
| .xlsx support | Yes | Yes | No |
If your project is PHP, choose Laravel Excel; for Node.js, pick ExcelJS. CSV export is only used for simple dumps without formatting.
Work Process for Report Generation Integration
- Analysis—gather requirements: fields, filters, format, need for background generation. (2 days)
- Design—choose stack (Laravel/Node.js), design file structure (sheets, groupings, formulas). (1 day)
- Implementation—write export code, configure error handling, logging. (3 days)
- Testing—test on real data (10% volume), adjust styles. (1 day)
- Deployment—configure queues, cron jobs (if periodic generation needed), document. (1 day)
Estimated Timelines
- Basic export (one sheet, simple styles): 2–3 days. Cost from $500.
- Asynchronous export with large data: 3–4 days. Cost from $800.
- Complex multi-sheet report with formulas and pivot tables: up to one week. Cost from $1200.
Exact timelines depend on the number of sheets and formatting complexity. We guarantee 100% satisfaction or money back.
What's Included
- Source code of the report generator (PHP or Node.js).
- Queue and storage configuration (S3, local disk).
- Documentation on usage (customizing fields, adding sheets).
- Integration with your authentication and access rights.
- Employee training (1–2 hours).
- Technical support for 30 days after delivery.
- 5 years of team experience in data export.
Typical Mistakes in Self-Implementation
- Loading all data into memory—use chunking. We see 80% of clients make this error.
- No queues—user waits for generation. Avoid 30-second timeouts.
- Ignoring styles—report is unreadable. Add auto-filters and frozen rows.
- One table for all data—split into sheets. Improve usability by 50%.
Contact us for your project analysis—we will evaluate data volume, current stack, and propose the optimal solution. Our engineers have 5+ years of experience and over 50 successful report generation integration projects. Get your report generation implementation today and acquire a reliable analytics tool.