PDF Writers and Excel Headers

[kylejtuck]

Just wanting to confirm (before I sink more time into this) that the various PDF writers (I have tried the three options) do not support the headers defined in the generated Excel file.

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('file.xlsx');
$spreadsheet->setActiveSheetIndex(0);

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Mpdf');

$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing();
$drawing->setName('logo');
$drawing->setPath('./logo.jpg');

$spreadsheet->getActiveSheet()->getHeaderFooter()
	->addImage($drawing, \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooter::IMAGE_HEADER_LEFT);
$spreadsheet->getActiveSheet()->getHeaderFooter()
	->setOddHeader('&L&G&RPage &P of &N');

$pdf_path = '/tmp/out.pdf';
$writer->save($pdf_path);
?>

The defined header is not added to the out.pdf. If I use the Xlsx writer and save to out.xlsx it produces the expected output.

[MarkBaker]

That's correct, nor with the HTML Writer which is used as the basis for all of the PDF Writers

[kylejtuck]

Well, I hate giving up on something, so for those looking for a potential solution...

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// convert the Excel file to PDF without headers
$pdf_path = '/tmp/doc.pdf';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('excel_file.xlsx');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Mpdf');
$writer->writeAllSheets();
$writer->save($pdf_path);

// create new Mpdf objects to detect page orientation, import the above pdf file, add the header, and export
$mpdf = new \Mpdf\Mpdf();
$outpdf = new \Mpdf\Mpdf();
$total_pages = $mpdf->setSourceFile('/tmp/doc.pdf');
$outpdf->setSourceFile($pdf_path);
for ($i = 1; $i <= $total_pages; $i++) {
	// determine page orientation
	$m_tplId = $mpdf->importPage($i);
	$page_dimesions = $mpdf->useTemplate($m_tplId);
	// add a page to the output pdf based on orientation
	if ($page_dimesions['width'] > $page_dimesions['height'])
		$outpdf->addPage("L"); // Landscape
	else
		$outpdf->addPage("P"); // Portrait
	$o_tplId = $outpdf->importPage($i);
	$outpdf->useTemplate($m_tplId);
	// set the header for this imported page
	$outpdf->SetHTMLHeader('
		<table width="100%" style="vertical-align: bottom; font-family: Verdana, sans-serif; 
		    color: #000000; font-weight: bold; padding-bottom: 1em;">
			<tr>
				<td width="15%" style="vertical-align: top; padding-bottom: 1em;"><img src="image.jpg" /></td>
				<td width="70%" style="vertical-align: top; font-size: 18pt; text-align: center;">Document Title</td>
				<td width="15%" style="vertical-align: top; text-align: right; padding-right: 3em;">Page {PAGENO} / {nbpg}</td>
			</tr>
		</table>
	', 'O', true);
}
unlink($pdf_path);

// send the result to the browser
$pdf_path = 'doc.pdf';
$mpdf->Output($pdf_path, "D");
// Uncomment the following to save to file on local filesystem
//$pdf_path = '/tmp/doc2.pdf';
//$mpdf->Output($pdf_path, "F");
?>

I have a 3-column configuration for my header, but feel free to modify the header section as you see fit.

Edit Apr 23, 2022: I modified the code to use two Mpdf objects. One is used to detect the orientation (landscape or portrait) of the page being added, and the other is the PDF document you actually want. In the original answer, I had simply hard-coded landscape for all added pages.

Note that this method creates two layers on each page. The "bottom" layer is the template page, while the "top" layer has the header. Next step is to see how to merge the layers. :)