<?
php
namespace App\Helpers;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Log;
use Spatie\SimpleExcel\SimpleExcelWriter;
class ExportHelper
{
protected array $allowedFields = [];
protected bool $convertToSnakeCase = false;
protected bool $trimHeaders = false;
protected array $exampleData = [];
protected int $chunkSize = 1000; // Default chunk size for database exports
protected string $outputFormat = 'xlsx'; // Default output format
/**
* Set allowed fields for the export.
*
* @param array $allowedFields Array of field names
* @return $this
*/
public function setAllowedFields(array $allowedFields): self
{
$this->validateAllowedFields($allowedFields);
$this->allowedFields = $allowedFields;
return $this;
}
/**
* Set whether to convert headers to snake_case.
*
* @param bool $convertToSnakeCase
* @return $this
*/
public function setConvertToSnakeCase(bool $convertToSnakeCase): self
{
$this->convertToSnakeCase = $convertToSnakeCase;
return $this;
}
/**
* Set whether to trim headers.
*
* @param bool $trimHeaders
* @return $this
*/
public function setTrimHeaders(bool $trimHeaders): self
{
$this->trimHeaders = $trimHeaders;
return $this;
}
/**
* Set example data for the export template.
*
* @param array $exampleData Array of example rows to include in the template
* @return $this
*/
public function setExampleData(array $exampleData): self
{
$this->validateExampleData($exampleData);
$this->exampleData = $exampleData;
return $this;
}
/**
* Set the chunk size for database exports.
*
* @param int $chunkSize Number of rows per chunk
* @return $this
*/
public function setChunkSize(int $chunkSize): self
{
if ($chunkSize <= 0) {
Log::error('Chunk size must be greater than zero.');
throw new \InvalidArgumentException('Chunk size must be greater than
zero.');
}
$this->chunkSize = $chunkSize;
return $this;
}
/**
* Set the output format (e.g., xlsx, csv).
*
* @param string $outputFormat Desired output format
* @return $this
*/
public function setOutputFormat(string $outputFormat): self
{
$supportedFormats = ['xlsx', 'csv'];
if (!in_array($outputFormat, $supportedFormats)) {
Log::error("Unsupported output format: $outputFormat. Supported formats
are: " . implode(', ', $supportedFormats));
throw new \InvalidArgumentException("Unsupported output format:
$outputFormat. Supported formats are: " . implode(', ', $supportedFormats));
}
$this->outputFormat = $outputFormat;
return $this;
}
/**
* Export a template file with headers and example data.
*
* @param string|null $filePath Path where the template file will be saved
(null for streaming)
* @param string|null $filename Custom filename for streamed file
* @return array ['success' => bool, 'message' => string]
*/
public function exportTemplate(?string $filePath = null, ?string $filename =
null): array
{
try {
// Validate that allowed fields are defined
if (empty($this->allowedFields)) {
Log::error('Allowed fields must be defined to generate a
template.');
return [
'success' => false,
'message' => 'Allowed fields must be defined to generate a
template.',
];
}
Log::info("Starting export to file: $filePath");
// Create the writer
if ($filePath) {
$writer = SimpleExcelWriter::create($filePath);
} else {
$writer = SimpleExcelWriter::streamDownload($filename ??
'template.' . $this->outputFormat);
}
// Write the header row
Log::debug('Write header row...');
$headers = $this->convertHeadersForExport();
$writer->addRow($headers);
// Add example data rows if provided
Log::debug('Adding example data...');
if (!empty($this->exampleData)) {
$this->validateExampleData($this->exampleData);
foreach ($this->exampleData as $row) {
$formattedRow = $this->formatRow($row);
$writer->addRow($formattedRow);
}
}
// Finalize the file
Log::debug('Finalizing file...');
if ($filePath) {
Log::info('Template file has been generated successfully.');
$writer->close();
} else {
Log::info('Template file has been generated successfully.');
$writer->toBrowser();
}
Log::info('Template file has been generated successfully.');
return [
'success' => true,
'message' => "Template file has been generated successfully.",
];
} catch (\Exception $e) {
Log::error('Template export failed: ' . $e->getMessage());
return [
'success' => false,
'message' => 'Error exporting template: ' . $e->getMessage(),
];
}
}
/**
* Export data directly from the database.
*
* @param Builder $query Query builder instance
* @param string|null $filePath Path where the file will be saved (null for
streaming)
* @param string|null $filename Custom filename for streamed file
* @return array ['success' => bool, 'message' => string]
*/
public function exportFromDatabase(Builder $query, ?string $filePath = null, ?
string $filename = null): array
{
try {
// Validate that allowed fields are defined
if (empty($this->allowedFields)) {
Log::error('Allowed fields must be defined to generate the
export.');
return [
'success' => false,
'message' => 'Allowed fields must be defined to generate the
export.',
];
}
// Create the writer
if ($filePath) {
Log::info('Data will be exported to: ' . $filePath);
$writer = SimpleExcelWriter::create($filePath);
} else {
Log::info('Data will be exported to the browser.');
$writer = SimpleExcelWriter::streamDownload($filename ??
'database_export.' . $this->outputFormat);
}
// Write the header row
$headers = $this->convertHeadersForExport();
$writer->addRow($headers);
// Fetch data from the database in chunks
$query->chunk($this->chunkSize, function ($results) use ($writer) {
foreach ($results as $row) {
$formattedRow = $this->formatRow($row);
$writer->addRow($formattedRow);
}
});
// Finalize the file
if ($filePath) {
$writer->close();
} else {
$writer->toBrowser();
}
Log::info('Data has been exported successfully.');
return [
'success' => true,
'message' => "Data has been exported successfully.",
];
} catch (\Exception $e) {
Log::error('Database export failed: ' . $e->getMessage());
return [
'success' => false,
'message' => 'Error exporting data: ' . $e->getMessage(),
];
}
}
/**
* Convert headers for export based on configuration.
*
* @return array
*/
protected function convertHeadersForExport(): array
{
try {
$headers = $this->allowedFields;
if ($this->convertToSnakeCase) {
$headers = array_map(function ($header) {
return str_replace(' ', '_', strtolower($header));
}, $headers);
}
if ($this->trimHeaders) {
$headers = array_map('trim', $headers);
}
return $headers;
} catch (\Exception $e) {
Log::error('Header conversion failed: ' . $e->getMessage());
throw $e;
}
}
/**
* Format a single row for export.
*
* @param mixed $row Data row to format
* @return array Formatted row
*/
protected function formatRow($row): array
{
try {
$formattedRow = [];
foreach ($this->allowedFields as $field) {
$formattedRow[] = data_get($row, $field, '');
}
return $formattedRow;
} catch (\Exception $e) {
Log::error("Error formatting row: {$e->getMessage()}");
throw $e;
}
}
/**
* Validate allowed fields.
*
* @param array $allowedFields Fields to validate
* @throws \InvalidArgumentException If validation fails
*/
protected function validateAllowedFields(array $allowedFields): void
{
if (empty($allowedFields)) {
Log::error('Allowed fields cannot be empty.');
throw new \InvalidArgumentException('Allowed fields cannot be empty.');
}
}
/**
* Validate example data.
*
* @param array $exampleData Example data to validate
* @throws \InvalidArgumentException If validation fails
*/
protected function validateExampleData(array $exampleData): void
{
Log::debug('Validating example data...');
if (!empty($exampleData)) {
Log::info(json_encode($exampleData));
foreach ($exampleData as $row) {
Log::debug('Validating row...');
if (!is_array($row)) {
Log::info(json_encode($row));
Log::error('Each row in example data must be an array.');
throw new \InvalidArgumentException('Each row in example data
must be an array.');
}
Log::debug('Row validated.');
}
}
Log::debug('Example data validated.');
}
}
/**
* use App\Helpers\ExportHelper;
* use Illuminate\Database\Eloquent\Builder;
*
* // Create an instance of ExportHelper
* $exportHelper = new ExportHelper();
*
* // Configure export settings
* $exportHelper->setAllowedFields(['name', 'email', 'created_at']);
* $exportHelper->setConvertToSnakeCase(true);
* $exportHelper->setTrimHeaders(true);
* $exportHelper->setChunkSize(500); // Set custom chunk size
* $exportHelper->setOutputFormat('csv'); // Export as CSV
*
* // Example data for template
* $exportHelper->setExampleData([
* ['name' => 'John Doe', 'email' => 'john@[Link]', 'created_at' => '2023-01-
01'],
* ['name' => 'Jane Smith', 'email' => 'jane@[Link]', 'created_at' => '2023-
02-01'],
* ]);
*
* // Export template
* $response = $exportHelper->exportTemplate(null, '[Link]');
* if ($response['success']) {
* echo $response['message'];
* } else {
* echo 'Error: ' . $response['message'];
* }
*
* // Export data from database
* $query = User::query(); // Assume User is an Eloquent model
* $response = $exportHelper->exportFromDatabase($query, null, 'users_export.csv');
* if ($response['success']) {
* echo $response['message'];
* } else {
* echo 'Error: ' . $response['message'];
* }
*/
<?php
namespace App\Helpers;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Log;
use Spatie\SimpleExcel\SimpleExcelWriter;
class ExportHelper
{
protected array $allowedFields = [];
protected bool $convertToSnakeCase = false;
protected bool $trimHeaders = false;
protected array $exampleData = [];
protected int $chunkSize = 1000; // Default chunk size for database exports
protected string $outputFormat = 'xlsx'; // Default output format
/**
* Set allowed fields for the export.
*
* @param array $allowedFields Array of field names
* @return $this
*/
public function setAllowedFields(array $allowedFields): self
{
$this->validateAllowedFields($allowedFields);
$this->allowedFields = $allowedFields;
return $this;
}
/**
* Set whether to convert headers to snake_case.
*
* @param bool $convertToSnakeCase
* @return $this
*/
public function setConvertToSnakeCase(bool $convertToSnakeCase): self
{
$this->convertToSnakeCase = $convertToSnakeCase;
return $this;
}
/**
* Set whether to trim headers.
*
* @param bool $trimHeaders
* @return $this
*/
public function setTrimHeaders(bool $trimHeaders): self
{
$this->trimHeaders = $trimHeaders;
return $this;
}
/**
* Set example data for the export template.
*
* @param array $exampleData Array of example rows to include in the template
* @return $this
*/
public function setExampleData(array $exampleData): self
{
$this->validateExampleData($exampleData);
$this->exampleData = $exampleData;
return $this;
}
/**
* Set the chunk size for database exports.
*
* @param int $chunkSize Number of rows per chunk
* @return $this
*/
public function setChunkSize(int $chunkSize): self
{
if ($chunkSize <= 0) {
Log::error('Chunk size must be greater than zero.');
throw new \InvalidArgumentException('Chunk size must be greater than
zero.');
}
$this->chunkSize = $chunkSize;
return $this;
}
/**
* Set the output format (e.g., xlsx, csv).
*
* @param string $outputFormat Desired output format
* @return $this
*/
public function setOutputFormat(string $outputFormat): self
{
$supportedFormats = ['xlsx', 'csv'];
if (!in_array($outputFormat, $supportedFormats)) {
Log::error("Unsupported output format: $outputFormat. Supported formats
are: " . implode(', ', $supportedFormats));
throw new \InvalidArgumentException("Unsupported output format:
$outputFormat. Supported formats are: " . implode(', ', $supportedFormats));
}
$this->outputFormat = $outputFormat;
return $this;
}
/**
* Export a template file with headers and example data.
*
* @param string|null $filePath Path where the template file will be saved
(null for streaming)
* @param string|null $filename Custom filename for streamed file
* @return array ['success' => bool, 'message' => string]
*/
public function exportTemplate(?string $filePath = null, ?string $filename =
null): array
{
try {
// Validate that allowed fields are defined
if (empty($this->allowedFields)) {
Log::error('Allowed fields must be defined to generate a
template.');
return [
'success' => false,
'message' => 'Allowed fields must be defined to generate a
template.',
];
}
Log::info("Starting export to file: $filePath");
// Create the writer
if ($filePath) {
$writer = SimpleExcelWriter::create($filePath);
} else {
$writer = SimpleExcelWriter::streamDownload($filename ??
'template.' . $this->outputFormat);
}
// Write the header row
Log::debug('Write header row...');
$headers = $this->convertHeadersForExport();
$writer->addRow($headers);
// Add example data rows if provided
Log::debug('Adding example data...');
if (!empty($this->exampleData)) {
$this->validateExampleData($this->exampleData);
foreach ($this->exampleData as $row) {
$formattedRow = $this->formatRow($row);
$writer->addRow($formattedRow);
}
}
// Finalize the file
Log::debug('Finalizing file...');
if ($filePath) {
Log::info('Template file has been generated successfully.');
$writer->close();
} else {
Log::info('Template file has been generated successfully.');
$writer->toBrowser();
}
Log::info('Template file has been generated successfully.');
return [
'success' => true,
'message' => "Template file has been generated successfully.",
];
} catch (\Exception $e) {
Log::error('Template export failed: ' . $e->getMessage());
return [
'success' => false,
'message' => 'Error exporting template: ' . $e->getMessage(),
];
}
}
/**
* Export data directly from the database.
*
* @param Builder $query Query builder instance
* @param string|null $filePath Path where the file will be saved (null for
streaming)
* @param string|null $filename Custom filename for streamed file
* @return array ['success' => bool, 'message' => string]
*/
public function exportFromDatabase(Builder $query, ?string $filePath = null, ?
string $filename = null): array
{
try {
// Validate that allowed fields are defined
if (empty($this->allowedFields)) {
Log::error('Allowed fields must be defined to generate the
export.');
return [
'success' => false,
'message' => 'Allowed fields must be defined to generate the
export.',
];
}
// Create the writer
if ($filePath) {
Log::info('Data will be exported to: ' . $filePath);
$writer = SimpleExcelWriter::create($filePath);
} else {
Log::info('Data will be exported to the browser.');
$writer = SimpleExcelWriter::streamDownload($filename ??
'database_export.' . $this->outputFormat);
}
// Write the header row
$headers = $this->convertHeadersForExport();
$writer->addRow($headers);
// Fetch data from the database in chunks
$query->chunk($this->chunkSize, function ($results) use ($writer) {
foreach ($results as $row) {
$formattedRow = $this->formatRow($row);
$writer->addRow($formattedRow);
}
});
// Finalize the file
if ($filePath) {
$writer->close();
} else {
$writer->toBrowser();
}
Log::info('Data has been exported successfully.');
return [
'success' => true,
'message' => "Data has been exported successfully.",
];
} catch (\Exception $e) {
Log::error('Database export failed: ' . $e->getMessage());
return [
'success' => false,
'message' => 'Error exporting data: ' . $e->getMessage(),
];
}
}
/**
* Convert headers for export based on configuration.
*
* @return array
*/
protected function convertHeadersForExport(): array
{
try {
$headers = $this->allowedFields;
if ($this->convertToSnakeCase) {
$headers = array_map(function ($header) {
return str_replace(' ', '_', strtolower($header));
}, $headers);
}
if ($this->trimHeaders) {
$headers = array_map('trim', $headers);
}
return $headers;
} catch (\Exception $e) {
Log::error('Header conversion failed: ' . $e->getMessage());
throw $e;
}
}
/**
* Format a single row for export.
*
* @param mixed $row Data row to format
* @return array Formatted row
*/
protected function formatRow($row): array
{
try {
$formattedRow = [];
foreach ($this->allowedFields as $field) {
$formattedRow[] = data_get($row, $field, '');
}
return $formattedRow;
} catch (\Exception $e) {
Log::error("Error formatting row: {$e->getMessage()}");
throw $e;
}
}
/**
* Validate allowed fields.
*
* @param array $allowedFields Fields to validate
* @throws \InvalidArgumentException If validation fails
*/
protected function validateAllowedFields(array $allowedFields): void
{
if (empty($allowedFields)) {
Log::error('Allowed fields cannot be empty.');
throw new \InvalidArgumentException('Allowed fields cannot be empty.');
}
}
/**
* Validate example data.
*
* @param array $exampleData Example data to validate
* @throws \InvalidArgumentException If validation fails
*/
protected function validateExampleData(array $exampleData): void
{
Log::debug('Validating example data...');
if (!empty($exampleData)) {
Log::info(json_encode($exampleData));
foreach ($exampleData as $row) {
Log::debug('Validating row...');
if (!is_array($row)) {
Log::info(json_encode($row));
Log::error('Each row in example data must be an array.');
throw new \InvalidArgumentException('Each row in example data
must be an array.');
}
Log::debug('Row validated.');
}
}
Log::debug('Example data validated.');
}
}
/**
* use App\Helpers\ExportHelper;
* use Illuminate\Database\Eloquent\Builder;
*
* // Create an instance of ExportHelper
* $exportHelper = new ExportHelper();
*
* // Configure export settings
* $exportHelper->setAllowedFields(['name', 'email', 'created_at']);
* $exportHelper->setConvertToSnakeCase(true);
* $exportHelper->setTrimHeaders(true);
* $exportHelper->setChunkSize(500); // Set custom chunk size
* $exportHelper->setOutputFormat('csv'); // Export as CSV
*
* // Example data for template
* $exportHelper->setExampleData([
* ['name' => 'John Doe', 'email' => 'john@[Link]', 'created_at' => '2023-01-
01'],
* ['name' => 'Jane Smith', 'email' => 'jane@[Link]', 'created_at' => '2023-
02-01'],
* ]);
*
* // Export template
* $response = $exportHelper->exportTemplate(null, '[Link]');
* if ($response['success']) {
* echo $response['message'];
* } else {
* echo 'Error: ' . $response['message'];
* }
*
* // Export data from database
* $query = User::query(); // Assume User is an Eloquent model
* $response = $exportHelper->exportFromDatabase($query, null, 'users_export.csv');
* if ($response['success']) {
* echo $response['message'];
* } else {
* echo 'Error: ' . $response['message'];
* }
*/