Problems converting .xlsx to PDF

Topics: Developer Forum, User Forum
Apr 1, 2014 at 9:55 PM
Edited Apr 1, 2014 at 9:56 PM
Hi,
i wrote the following code:
            $inputFileName = 'doc/ModUnico';
            $excel2 = PHPExcel_IOFactory::createReader('Excel2007');
            $excel2 = $excel2->load($inputFileName.'.xlsx');
            $excel2->setActiveSheetIndex(0);
            $excel2->getActiveSheet()->setCellValue('H5', '4');
            $objWriter = PHPExcel_IOFactory::createWriter($excel2, 'Excel2007');
            $objWriter->save($inputFileName.'_.xlsx');


            $objPHPexcel = PHPExcel_IOFactory::load($inputFileName.'_.xlsx');
            header('Content-Type: application/pdf');
            header('Content-Disposition: attachment;filename="test.pdf"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'PDF');
            $objWriter->writeAllSheets();
            $objWriter->setPreCalculateFormulas(false);
            $objWriter->save('php://output');
but the returned pdf seems to be corrupted, since whe i want to open it i get "Impossible to load file". Could it due to the complexity of the Excel file?
Coordinator
Apr 1, 2014 at 10:48 PM
What PDF rendering engine are you using? I don't see you setting it anywhere

And why are you creating the intermediate $inputFileName.'_.xlsx' file?
Apr 2, 2014 at 5:48 AM
Well, i don't know exactly what you mean but i forgot to post these three lines:
            error_reporting(E_ALL);
            date_default_timezone_set('Europe/London');
            require_once 'phpExcel/PHPExcel/IOFactory.php';
            require_once 'phpExcel/PHPExcel.php';

I create the intermediate file only to check if the setCellValue('H5', '4'); works correctly, it is only a test. Once i see that the whole works, i'll clean it.
Coordinator
Apr 2, 2014 at 6:57 AM
PHPExcel doesn't include an actual PDF Rendering engine library, juts wrappers to three of the more popular libraries, but you need to install either tcPDF, mPDF or DomPDF separately, and point PHPExcel to which one you have installed - See section 6.9.1 ("PHPExcel_Writer_PDF") of the developer documentation, and example 01simple-download-pdf.php in the /Examples folder
Apr 2, 2014 at 7:34 AM
OK, i'm new in php and in this stuff so i didn't knew about the renderer. I added the following code:
            $rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
            $rendererLibrary = 'mpdf.php';
            $rendererLibraryPath = dirname(__FILE__).'/MPDF57/' . $rendererLibrary;


            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'
                );
            }
it seems that the rendere is initilizazed correctly, since the message is not put out, but the result is the same...
Coordinator
Apr 2, 2014 at 8:10 AM
Then either save to a filesystem file and see if that file can be opened by a PDF reader; or open the resulting file in a text editor and look for leading/trailing whitespace characters, BOM headers, plaintext error messages, etc
Apr 2, 2014 at 8:22 AM
OK, when i comment the headers, i got the html error page:
( ! ) Fatal error: Maximum execution time of 30 seconds exceeded in C:\Program Files (x86)\Wamp\www\Terzer\MPDF57\mpdf.php on line 30620 
Call Stack
#   Time    Memory  Function    Location
1   0.0010  153984  {main}( )   ..\generatePdf3.php:0
2   21.0122 24642968    save ( )    ..\generatePdf3.php:59
3   21.0122 24643168    PHPExcel_Writer_PDF->__call( )  ..\generatePdf3.php:59
4   21.0122 24643448    call_user_func_array ( )    ..\PDF.php:87
5   21.0122 24643632    PHPExcel_Writer_PDF_mPDF->save( )   ..\PDF.php:87
6   29.8607 31357056    mPDF->WriteHTML( )  ..\mPDF.php:122
7   29.8717 31361400    mPDF->purify_utf8( )    ..\mpdf.php:12824
8   29.8727 33167792    mPDF->is_utf8( )    ..\mpdf.php:30641
Coordinator
Apr 2, 2014 at 9:22 AM
Well you are still saving as Excel2007 and then reloading again, all of which takes time; remove those spurious overheads and see if that reduces the execution time enough for the pdf writer to finish its work; and/or increase execution time
Apr 2, 2014 at 11:49 AM
Tried the following code:
            error_reporting(E_ALL);
            ini_set('display_errors', TRUE);
            ini_set('display_startup_errors', TRUE);
            date_default_timezone_set('Europe/London');
            require_once 'phpExcel/PHPExcel/IOFactory.php';
            require_once 'phpExcel/PHPExcel.php';


            $rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
            $rendererLibrary = 'MPDF57';
            $rendererLibraryPath = dirname(__FILE__).'/' . $rendererLibrary;


            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'
                );
            }

            $inputFileName = 'doc/ModUnico';

            $objPHPexcel = PHPExcel_IOFactory::load($inputFileName.'.xlsx');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'PDF');
            $objWriter->save('php://output');
Got same result/error.
Are you shure that ALL xlsx are supported?
Apr 2, 2014 at 12:02 PM
I've just tried a simple xlsx...and it works :/
Coordinator
Apr 2, 2014 at 12:34 PM
Then increase the php max execution time from 30 seconds to perhaps 60 seconds, I can't control how long it takes mpdf to generate the output
Apr 2, 2014 at 1:20 PM
I found other people with the similar problem
https://phpexcel.codeplex.com/discussions/399683
and
https://phpexcel.codeplex.com/discussions/261394

...but these post weren't useful...
Apr 2, 2014 at 1:29 PM
Edited Apr 2, 2014 at 1:42 PM
My xlsx has a size of 94k, includes only one sheet, reaches to cell AU372 for 5 DIN4 pages. When i cut it in order to have only one page it works.

Increased execution time:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 38 bytes)