Unable to format output at all

Topics: User Forum
Oct 4, 2012 at 1:14 PM

Hi, I am a new user and seem to be unable to get anything other than landscape output, I am unable to set portrait, or to set the print range or set fit to page.

I am sure I am doing something wrong, but have been trying pretty much all of day and have made no progress whatsoever, the output is still identical - landscape printed on portrait paper with the right half cut off by the edge of the page.

I am using the latest code from github and are using dompdf as pdf output is essential, here's the code, I appreciate any help:

[code]

<?
    //error_reporting(E_ALL);
    date_default_timezone_set('Asia/Singapore');
   
    require_once 'phpexcel.new/Classes/PHPExcel/IOFactory.php';
    require_once 'phpexcel.new/Classes/PHPExcel.php';

    $file = "pcash.xlsx";
    if(isset($_GET['file'])) $file = $_GET['file'];
    echo $file."<br>";   

    define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');


    echo date('H:i:s') , " Create new PHPExcel object" , PHP_EOL;
    $objPHPExcel = new PHPExcel();

    $rendererName = PHPExcel_Settings::PDF_RENDERER_DOMPDF;
    $rendererLibrary = 'dompdf';
    $rendererLibraryPath = '' . $rendererLibrary;
    if (!PHPExcel_Settings::setPdfRenderer(
            $rendererName,
            $rendererLibraryPath
        )) {
        die(
            'NOTICE: Please set the $rendererName and $rendererLibraryPath values' .
            EOL .
            'at the top of this script as appropriate for your directory structure'
        );
    }

    $objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
    $objPHPExcel = $objPHPExcel->load($file); // Load Template
       
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('B10', '100')
        ->setCellValue('B11', '200')      
        ->setCellValue('B12', '300');


    // Set page orientation, size, Print Area and Fit To Pages
    $objPageSetup = new PHPExcel_Worksheet_PageSetup();
    $objPageSetup->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
    $objPageSetup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
    //$objPageSetup->setPrintArea("E5:H7");
    $objPageSetup->setFitToWidth(1);
    $objPHPExcel->getActiveSheet()->setPageSetup($objPageSetup);
       
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
       
    //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $pdf = str_replace(".xlsx",".pdf",$file);
    $objWriter->save($pdf);
   
    // Echo memory peak usage
    echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";
   
    echo "<br><br><a target='_blank' href='$pdf'>$pdf</a>";

?>

[/code]

 

 

Oct 5, 2012 at 8:40 AM
Edited Oct 5, 2012 at 8:58 AM

OK, spent another day trying to resolve this, I have tried both DOMPDF and mPDF both give completely different results and neither appear to respond to setting page size, orientation, or to fit to page.

Out of the 2 mPDFis closest to what it should look like, but unfortunately not close enough, dompdf tries to print everything at 100% scale without any scaling or attempt to fit to the page, even though the document it is reading is already set to fit to page and portrait mode.

Is there a later BETA which fixes any of this ??

I have also tried saving as xlsx and use openoffiice / jodconverter to print to PDF, this works on Ubuntu 10, but does not work on Ubuntu 12 in headless mode. I also have a posting regarding that on another forum, and with google have found others with the same problem but no solution.

I have also searched for other solutions but drawn a blank, so are really not having very much luck at the moment !!

Here's links to the 3 different outputs

Direct from Excel

DOMPDF

MPDF

 

Thanks

Oct 9, 2012 at 11:35 AM

Hello - Anybody there ????

Coordinator
Oct 10, 2012 at 1:09 PM

I've just pushed a fix to github that sets the page orientation for DomPDF... still trying to figure out why mPDF is ignoring the page orientation setting.

Oct 31, 2012 at 1:13 PM

Hi,

i was experiencing the same problem when trying to export a excel document to pdf in landscape mode using mPDF. I'm using PHPEXCEL 1.7.8

I have found a workaround, but you have to modify the mPDF.php file in the directory PDFPHPExcel\Writer\PDF at line 110.

The original block of code is:

        $pdf = new mpdf();
        $pdf->_setPageSize(strtoupper($paperSize), $orientation);
        $pdf->DefOrientation = $orientation;

The modified (working) block of code is:

        $pdf = new mpdf();
        $ortmp = $orientation;
        $pdf->_setPageSize(strtoupper($paperSize), $ortmp);
        $pdf->DefOrientation = $orientation;
        $pdf->AddPage($orientation);

it seems like the problematic line is the setPageSize function line which overrides the chosen orientation.

I have not investigated further on the problem, and I know this workaround is not the ideal solution, but it works.

Hopefully the PHPExcel team will provide a better solution.

 

Regards

Coordinator
Oct 31, 2012 at 6:22 PM

I can't understand why using the $ortmp variable should make any difference at all to the code; so the only thing I really see that you're doing differently is the call to $pdf->AddPage()

Oct 31, 2012 at 6:54 PM

Actually not. I found out that the variable $orientation changes value after the call to setPageSize() function.

I agree with you it makes no sense, but I have printed out this variable before and after the call and found the strange behavior. Thus the $ortmp variable, which is used only for the call to SetPageSize().

Actually, strictly speaking, calling or not the setPageSize with the variable copy in regards to page orientation in my test made no real difference. Even if you leave the original code and add only AddPage('L') (as I did first), it will create the document in landscape mode. I have then tried to use the $orientation variable, so the pdf page orientation would correctly reflect the excel one. I found it was not working, until I found the previously mentioned problem.

I have no idea why this happens, AFAIK a variable passed to a function in PHP should not be changed by the function itself, does it?

Coordinator
Oct 31, 2012 at 7:11 PM

Certainly a variable passed to a function shouldn't be changed unless the API explicitly declares it as pass by reference, and it should really explain why it's changed and what you should expect back as well, but mPDF isn't particularly well documented. In this case (having read through the relevant mPDF code) it seems like an arbitrary decision by the developers of mPDF, and totally unnecessary.

But thanks for the investigative work

Nov 28, 2012 at 2:24 PM
hafler wrote:

Hi,

i was experiencing the same problem when trying to export a excel document to pdf in landscape mode using mPDF. I'm using PHPEXCEL 1.7.8

I have found a workaround, but you have to modify the mPDF.php file in the directory PDFPHPExcel\Writer\PDF at line 110.

The original block of code is:

        $pdf = new mpdf();
        $pdf->_setPageSize(strtoupper($paperSize), $orientation);
        $pdf->DefOrientation = $orientation;

The modified (working) block of code is:

        $pdf = new mpdf();
        $ortmp = $orientation;
        $pdf->_setPageSize(strtoupper($paperSize), $ortmp);
        $pdf->DefOrientation = $orientation;
        $pdf->AddPage($orientation);

it seems like the problematic line is the setPageSize function line which overrides the chosen orientation.

I have not investigated further on the problem, and I know this workaround is not the ideal solution, but it works.

Hopefully the PHPExcel team will provide a better solution.

 

Regards

Hi,

I still had trouble printing my spreadsheet to landscape even doing all this however I came up with the answer and another solution.  The Main problem is that mPDF requires the papersize to be appended with '-L' for landscape pages.

in mPDF.php if you replace

// Create PDF
$pdf = new mpdf();
$pdf->_setPageSize(strtoupper($paperSize), $orientation);
$pdf->DefOrientation = $orientation;

with

// Crete PDF
if($orientation == 'L'){
    $paperSize  .= "-L";
}

$pdf = new mpdf('',    // mode - default ''
	        strtoupper($paperSize),    // format - A4, for example, default ''
        	0,     // font size - default 0
        	'',    // default font family
        	$printMargins->getLeft() * 72,    // margin_left
        	$printMargins->getRight() * 72,    // margin right
        	$printMargins->getTop() * 72,     // margin top
        	$printMargins->getBottom() * 72,    // margin bottom
        	0,     // margin header
        	0,     // margin footer
        	$orientation         // L - landscape, P - portrait
            );
	   
           // $pdf->_setPageSize(strtoupper($paperSize), strtolower($orientation));
           // $pdf->DefOrientation = $orientation;

This allows you to use the regular pagesetup options to control papersize, orientation and margins.

Apr 10, 2013 at 11:21 PM
Edited Apr 10, 2013 at 11:56 PM
Thanks Scott, your solution definitely helped.
HOWEVER, the margins are incorrect. The print margins in PHPExcel are specified in inches, while the print margins in mPDF are specified in millimeters. This simple change fixes it:
    $printMargins->getLeft() * 25.4,    // margin_left
    $printMargins->getRight() * 25.4,    // margin right
    $printMargins->getTop() * 25.4,     // margin top
    $printMargins->getBottom() * 25.4,    // margin bottom