PDF Quote Generator

Topics: Developer Forum, Project Management Forum, User Forum
Feb 5, 2014 at 12:41 PM
I am using the following code to generate a quote which works perfectly but when the quote gets so big it moves to the next PDF worksheet and I would like to keep the headers at the top of each page. How can this be done?
<?php
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

// connection with the database
session_start();
include ('core/connect.php');

// require the PHPExcel file
require 'core/PHPExcel.php';


// simple query

$query = "SELECT UFPCODE,OEMCODE,PRODUCTDESC,QTY,SELLPRICE,LINETOTAL FROM ".$_SESSION['user']."_final_quote ORDER by UFPCODE ASC";
$headings = array('UFP','OEM','Product','Qty','Price','Line Total');


//  Change these values to select the Rendering library that you wish to use
//      and its directory location on your server
//$rendererName = PHPExcel_Settings::PDF_RENDERER_TCPDF;
$rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
//$rendererName = PHPExcel_Settings::PDF_RENDERER_DOMPDF;
//$rendererLibrary = 'tcPDF5.9';
$rendererLibrary = 'mPDF';
//$rendererLibrary = 'domPDF0.6.0beta3';
$rendererLibraryPath = 'core/PHPExcel/Writer/PDF/' . $rendererLibrary;

echo $rendererLibraryPath;


if ($result = mysql_query($query) or die(mysql_error())) {
    // Create a new PHPExcel object
    $objPHPExcel = new PHPExcel();
    
        $objDrawing = new PHPExcel_Worksheet_Drawing();
        $objDrawing->setName('logo');
        $objDrawing->setDescription('logo');
        $objDrawing->setPath('img/UFP_logo.gif');
        $objDrawing->setHeight(120);
        $objDrawing->setCoordinates('E1');
        $objDrawing->setOffsetX(-10);
            $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
            
            // Set document properties
$objPHPExcel->getProperties()->setCreator($_SESSION['user'])
                             ->setLastModifiedBy($_SESSION['user'])
                             ->setTitle("UFP Quote")
                             ->setSubject("UFP Quote")
                             ->setDescription("UFP Quote")
                             ->setKeywords("UFP");
    

    $rowNumber = 13;
    $col = 'A';
    foreach($headings as $heading) {
       $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading);
       $col++;
    }

    // Loop through the result set
    $rowNumber = 14;
    while ($row = mysql_fetch_row($result)) {
       $col = 'A';
       foreach($row as $cell) {
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
          $col++;
       }
       $rowNumber++;
    }
    $lastRow = $objPHPExcel->getActiveSheet()->getHighestRow();
    $column_E = 'E';
    $column_F = 'F';
    $calculate = '=SUM(F14:F' . $lastRow . ')';
    
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(5);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(6);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(6);
    
    $objPHPExcel->getActiveSheet()->getStyle('C1:C'.$objPHPExcel->getActiveSheet()->getHighestRow())
    ->getAlignment()->setWrapText(true);
        
    $objPHPExcel->setActiveSheetIndex(0)->mergeCells('D6:E6');
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells('D7:E7');
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells('D8:E8');
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells('D9:E9');
            $objPHPExcel->setActiveSheetIndex(0)->mergeCells('D10:E10');
    

    

    // Add some data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue($column_E . ($lastRow+1), 'Total')
            ->setCellValue($column_F . ($lastRow+1), $calculate)
            ->setCellValue('A11', ' ')
            ->setCellValue('A12', ' ')
            ->setCellValue('D6', 'Account No:')
            ->setCellValue('D7', 'Quote No:')
            ->setCellValue('D8', 'Date:')
            ->setCellValue('D9', 'Rep:')
            ->setCellValue('D10', 'Tel:')
            ->setCellValue('A6', $_SESSION['CustName'])
            ->setCellValue('A7', $_SESSION['CustAdr1'])
            ->setCellValue('A8', $_SESSION['CustAdr2'])
            ->setCellValue('A9', $_SESSION['CustTown'])
            ->setCellValue('A10', $_SESSION['CustPost'])
            ->setCellValue('F6', $_SESSION['CustNo'])
            ->setCellValue('F7', rand(19999,99999))
            ->setCellValue('F8', date("d/m/Y"))
            ->setCellValue('F9', $_SESSION['user'])
            ->setCellValue('F10', '651800');
                
    $objPHPExcel->getDefaultStyle()
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    // Freeze pane so that the heading line will not scroll
    $objPHPExcel->getActiveSheet()->freezePane('A14');
    
    $objPHPExcel->getActiveSheet()->setTitle('UFP Quote');
    
    $objPHPExcel->getActiveSheet()->setShowGridLines(false);
if (!PHPExcel_Settings::setPdfRenderer(
        $rendererName,
        $rendererLibraryPath
    )) {
    die(
        'NOTICE: Please set the $rendererName and $rendererLibraryPath values' .
        '<br />' .
        'at the top of this script as appropriate for your directory structure'
    );
}
    
// Redirect output to a client’s web browser (Excel2007)
// Redirect output to a client’s web browser (PDF)
header('Content-Type: application/pdf');
header('Content-Disposition: attachment;filename="'. $_SESSION['user'] .'-' . $_SESSION['CustNo'] . '-quote.pdf"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save('php://output');
exit;
header ('location: http://ufp-quote.ufp-uk.local/process3.php?&start=0&s=&f=&sort=id&ad=a');

}