contents found inside the "wysiwyg" property of
* blockTableEditor blocks into a formatted Excel sheet.
*
* Layout:
* - Row 1 : merged title (black bg, white bold text, centred)
* - Row 2 : merged subtitle (blue bold text, centred) — only when a subtitle is provided
* - Next : column headers from | cells (black bg, white bold, autofilter)
* - Then : data rows ( | cells), wrapped text, thin borders
*/
class EntryTablesExport implements FromArray, WithStyles, WithEvents
{
protected array $htmlSources;
protected string $title;
protected ?string $subtitle;
/** 1-based row indices that carry the page title */
protected array $titleRows = [];
/** 1-based row indices that carry the subtitle */
protected array $subtitleRows = [];
/** 1-based row indices that carry column headers */
protected array $headerRows = [];
/** Total number of data columns (max across all tables) */
protected int $columnCount = 0;
/** Total number of rows written */
protected int $totalRows = 0;
public function __construct(array $htmlSources, string $title, ?string $subtitle = null)
{
$this->htmlSources = $htmlSources;
$this->title = $title;
$this->subtitle = $subtitle;
}
// -------------------------------------------------------------------------
// Data
// -------------------------------------------------------------------------
public function array(): array
{
$rows = [];
$rowNum = 0;
$first = true;
foreach ($this->htmlSources as $html) {
if (empty($html)) {
continue;
}
foreach ($this->extractTables($html) as $table) {
$headers = $table['headers'];
$dataRows = $table['rows'];
$colCounts = array_map('count', $dataRows);
$colCounts[] = count($headers);
$maxCols = !empty($colCounts) ? max($colCounts) : 5;
$this->columnCount = max($this->columnCount, $maxCols);
// Blank separator between multiple tables
if (!$first) {
$rows[] = array_fill(0, $maxCols, '');
$rowNum++;
}
$first = false;
// Title row
$titleRow = array_fill(0, $maxCols, '');
$titleRow[0] = $this->title;
$rows[] = $titleRow;
$this->titleRows[] = ++$rowNum;
// Subtitle row
if (!empty($this->subtitle)) {
$subtitleRow = array_fill(0, $maxCols, '');
$subtitleRow[0] = $this->subtitle;
$rows[] = $subtitleRow;
$this->subtitleRows[] = ++$rowNum;
}
// Header row
if (!empty($headers)) {
$rows[] = $headers;
$this->headerRows[] = ++$rowNum;
}
// Data rows
foreach ($dataRows as $row) {
$rows[] = $row;
$rowNum++;
}
}
}
if ($this->columnCount === 0) {
$this->columnCount = 5;
}
$this->totalRows = $rowNum;
return $rows;
}
// -------------------------------------------------------------------------
// Styles (row-level)
// -------------------------------------------------------------------------
public function styles(Worksheet $sheet): array
{
$styles = [];
foreach ($this->titleRows as $row) {
$styles[$row] = [
'font' => ['bold' => true, 'size' => 13, 'color' => ['argb' => 'FFFFFFFF']],
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['argb' => 'FF000000']],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_CENTER,
'vertical' => Alignment::VERTICAL_CENTER,
'wrapText' => true,
],
];
}
foreach ($this->subtitleRows as $row) {
$styles[$row] = [
'font' => ['bold' => true, 'size' => 12, 'color' => ['argb' => 'FF004080']],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_CENTER,
'vertical' => Alignment::VERTICAL_CENTER,
'wrapText' => true,
],
];
}
foreach ($this->headerRows as $row) {
$styles[$row] = [
'font' => ['bold' => true, 'color' => ['argb' => 'FFFFFFFF']],
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['argb' => 'FF000000']],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_LEFT,
'vertical' => Alignment::VERTICAL_CENTER,
'wrapText' => true,
],
];
}
return $styles;
}
// -------------------------------------------------------------------------
// After-sheet events: merge cells, autofilter, widths, borders
// -------------------------------------------------------------------------
public function registerEvents(): array
{
return [
AfterSheet::class => function (AfterSheet $event) {
$sheet = $event->sheet->getDelegate();
$lastCol = $this->indexToLetter($this->columnCount);
// Merge title rows across all columns
foreach ($this->titleRows as $row) {
$sheet->mergeCells("A{$row}:{$lastCol}{$row}");
$sheet->getRowDimension($row)->setRowHeight(32);
}
// Merge subtitle rows across all columns
foreach ($this->subtitleRows as $row) {
$sheet->mergeCells("A{$row}:{$lastCol}{$row}");
$sheet->getRowDimension($row)->setRowHeight(22);
}
// Autofilter on each header row
foreach ($this->headerRows as $row) {
$sheet->setAutoFilter("A{$row}:{$lastCol}{$row}");
$sheet->getRowDimension($row)->setRowHeight(22);
}
// Column widths
$defaultWidths = [22, 36, 22, 24, 32];
for ($i = 1; $i <= $this->columnCount; $i++) {
$col = $this->indexToLetter($i);
$width = $defaultWidths[$i - 1] ?? 20;
$sheet->getColumnDimension($col)->setWidth($width);
}
// Data rows: wrap text, min height, thin borders
$firstData = count($this->titleRows) + count($this->subtitleRows) + count($this->headerRows) + 1;
$borderStyle = [
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
'color' => ['argb' => 'FF999999'],
],
],
];
for ($r = $firstData; $r <= $this->totalRows; $r++) {
$range = "A{$r}:{$lastCol}{$r}";
$sheet->getStyle($range)->applyFromArray($borderStyle);
$sheet->getStyle($range)->getAlignment()->setWrapText(true);
$sheet->getRowDimension($r)->setRowHeight(45);
}
// Thin border on header rows too
foreach ($this->headerRows as $row) {
$sheet->getStyle("A{$row}:{$lastCol}{$row}")->applyFromArray($borderStyle);
}
},
];
}
// -------------------------------------------------------------------------
// HTML parsing
// -------------------------------------------------------------------------
/**
* @return array
*/
private function extractTables(string $html): array
{
$tables = [];
$dom = new \DOMDocument();
$previous = libxml_use_internal_errors(true);
$wrapped = ''
. $html . '';
if (!$dom->loadHTML($wrapped, LIBXML_HTML_NOIMPLIED | LIBXML_HTML_NODEFDTD)) {
libxml_clear_errors();
libxml_use_internal_errors($previous);
return $tables;
}
libxml_clear_errors();
libxml_use_internal_errors($previous);
foreach ($dom->getElementsByTagName('table') as $tableNode) {
$headers = [];
$rows = [];
foreach ($tableNode->getElementsByTagName('tr') as $rowNode) {
$cells = [];
$isHeaderRow = true;
foreach ($rowNode->childNodes as $cellNode) {
if (!($cellNode instanceof \DOMElement)) {
continue;
}
$tag = strtolower($cellNode->nodeName);
if ($tag !== 'td' && $tag !== 'th') {
continue;
}
if ($tag === 'td') {
$isHeaderRow = false;
}
$cells[] = $this->normalizeText($cellNode->textContent);
}
if (empty($cells)) {
continue;
}
// A row is treated as a header only if all its cells are |
// and no data rows have been written yet
if ($isHeaderRow && empty($rows)) {
$headers = $cells;
} else {
$rows[] = $cells;
}
}
if (!empty($headers) || !empty($rows)) {
$tables[] = ['headers' => $headers, 'rows' => $rows];
}
}
return $tables;
}
private function normalizeText(string $value): string
{
$value = html_entity_decode($value, ENT_QUOTES | ENT_HTML5, 'UTF-8');
$value = preg_replace('/\s+/u', ' ', $value);
return trim($value);
}
/** Convert 1-based column index to Excel letter (1→A, 26→Z, 27→AA …) */
private function indexToLetter(int $index): string
{
$letter = '';
while ($index > 0) {
$rem = ($index - 1) % 26;
$letter = chr(65 + $rem) . $letter;
$index = (int)(($index - 1) / 26);
}
return $letter;
}
}
| |