1

I generate Excel reports by loading Excel sheets, that I generated with Excel and fill in some data by PHP code.

Now, I wanted to convert those Excel sheets to PDF and because I saw that the PHPExcel 1.7.x supports creating PDF I thought: perfect, I do not have to go over a OpenOffice Engine, because that engine was not useful to generate PDFs for Web-Users in real-time.

But: Beside I did not have any chance to use the PDF writer from PHPExcel to write a loaded Excel template. It was not even possible to load and write delivered xls files.

I tried to build up the excel by PHP code only and drop the excel template: the excel-to-pdf already fails with the simplest generated excel that merges two cells. Is this writer completely unusable for such a task? Appendix, 2013-06-09: Sorry, this could be understood wrong: It could be all my fault or I just did not understand the core principles.

The following simple excel generation code fails if it comes to output the pdf: 

<?php /** Error reporting */ error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); date_default_timezone_set('Europe/London'); if (PHP_SAPI == 'cli') die('This example should only be run from a Web Browser'); /** Include PHPExcel */ require_once 'app/_includes/PHPExcel_1.7/PHPExcel.php'; $rendererName = PHPExcel_Settings::PDF_RENDERER_TCPDF; $rendererLibrary = 'tcpdf'; $rendererLibraryPath = '/inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/'. $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' ); } // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set document properties $objPHPExcel->getProperties()->setCreator("Company X") ->setDescription("Test document for PDF, generated using PHP classes."); $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); // Add some data $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', 'Date') ->setCellValue('B1', 'Time') ->setCellValue('B2', 'Start') ->setCellValue('C2', 'End'); // This line generated errors inside the output pdf -> corrupts PDF $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B1:C1'); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(4); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Simple'); $objPHPExcel->getActiveSheet()->setShowGridLines(false); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client.s web browser (PDF) header('Content-Type: application/pdf'); header('Content-Disposition: attachment;filename="p1.pdf"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF'); $objWriter->save('php://output'); exit;

Appendix, 2013-06-09: This is the beginning of the generated PDF file:

Notice: Array to string conversion in /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php on line 1236 Call Stack: 0.0010 257896 1. {main}() /inet/xxx/HTTP/devel/p1.php:0 0.0243 7798872 2. PHPExcel_Writer_PDF->save() /inet/xxx/HTTP/devel/p1.php:62 0.0243 7799224 3. PHPExcel_Writer_PDF->__call() /inet/xxx/HTTP/devel/p1.php:62 0.0243 7799728 4. call_user_func_array() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.0243 7800064 5. PHPExcel_Writer_PDF_tcPDF->save() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.0309 8678376 6. PHPExcel_Writer_HTML->generateSheetData() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF/tcPDF.php:119 0.0313 8683176 7. PHPExcel_Writer_HTML->_generateRow() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php:436 Warning: Illegal string offset 'width' in /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php on line 1257 Call Stack: 0.0010 257896 1. {main}() /inet/xxx/HTTP/devel/p1.php:0 0.0243 7798872 2. PHPExcel_Writer_PDF->save() /inet/xxx/HTTP/devel/p1.php:62 0.0243 7799224 3. PHPExcel_Writer_PDF->__call() /inet/xxx/HTTP/devel/p1.php:62 0.0243 7799728 4. call_user_func_array() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.0243 7800064 5. PHPExcel_Writer_PDF_tcPDF->save() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.0309 8678376 6. PHPExcel_Writer_HTML->generateSheetData() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF/tcPDF.php:119 0.0313 8683176 7. PHPExcel_Writer_HTML->_generateRow() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php:436 Warning: Invalid argument supplied for foreach() in /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php on line 1315 Call Stack: 0.0010 257896 1. {main}() /inet/xxx/HTTP/devel/p1.php:0 0.0243 7798872 2. PHPExcel_Writer_PDF->save() /inet/xxx/HTTP/devel/p1.php:62 0.0243 7799224 3. PHPExcel_Writer_PDF->__call() /inet/xxx/HTTP/devel/p1.php:62 0.0243 7799728 4. call_user_func_array() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.0243 7800064 5. PHPExcel_Writer_PDF_tcPDF->save() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.0309 8678376 6. PHPExcel_Writer_HTML->generateSheetData() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF/tcPDF.php:119 0.0313 8683176 7. PHPExcel_Writer_HTML->_generateRow() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php:436 0.0320 8701296 8. PHPExcel_Writer_HTML->_assembleCSS() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php:1267 %PDF-1.7

Appendix, 2013-06-10: I tried the 21pdf.php example, with tcpdf. It shows me some warnings but the pdf is generated. These is the php output:

21:55:52 Create new PHPExcel object 21:55:52 Set document properties 21:55:52 Add some data 21:55:52 Add comments 21:55:52 Add rich-text string 21:55:52 Merge cells 21:55:52 Protect cells 21:55:52 Set cell number formats 21:55:52 Set column widths 21:55:52 Set fonts 21:55:52 Set alignments 21:55:52 Set thin black border outline around column 21:55:52 Set thick brown border outline around Total 21:55:52 Set fills 21:55:52 Set style for header row using alternative method 21:55:52 Unprotect a cell 21:55:52 Add a hyperlink to the sheet 21:55:52 Add a drawing to the worksheet 21:55:52 Add a drawing to the worksheet 21:55:52 Add a drawing to the worksheet 21:55:52 Play around with inserting and removing rows and columns 21:55:52 Set header/footer 21:55:52 Set page orientation and size 21:55:52 Rename first worksheet 21:55:52 Create a second Worksheet object 21:55:52 Add some data 21:55:52 Set the worksheet tab color 21:55:52 Set alignments 21:55:52 Set column widths 21:55:52 Set fonts 21:55:52 Add a drawing to the worksheet 21:55:52 Set page orientation and size 21:55:52 Rename second worksheet 21:55:52 Hide grid lines 21:55:52 Set orientation to landscape 21:55:52 Write to PDF format using tcPDF Notice: Array to string conversion in /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php on line 1236 Call Stack: 0.0010 258104 1. {main}() /inet/xxx/HTTP/devel/21pdf.php:0 0.1375 8979304 2. PHPExcel_Writer_PDF->save() /inet/xxx/HTTP/devel/21pdf.php:80 0.1375 8979656 3. PHPExcel_Writer_PDF->__call() /inet/xxx/HTTP/devel/21pdf.php:80 0.1375 8980096 4. call_user_func_array() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.1375 8980432 5. PHPExcel_Writer_PDF_tcPDF->save() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.1569 9542416 6. PHPExcel_Writer_HTML->generateSheetData() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF/tcPDF.php:119 0.2014 9683184 7. PHPExcel_Writer_HTML->_generateRow() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php:436 Warning: Illegal string offset 'width' in /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php on line 1257 Call Stack: 0.0010 258104 1. {main}() /inet/xxx/HTTP/devel/21pdf.php:0 0.1375 8979304 2. PHPExcel_Writer_PDF->save() /inet/xxx/HTTP/devel/21pdf.php:80 0.1375 8979656 3. PHPExcel_Writer_PDF->__call() /inet/xxx/HTTP/devel/21pdf.php:80 0.1375 8980096 4. call_user_func_array() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.1375 8980432 5. PHPExcel_Writer_PDF_tcPDF->save() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.1569 9542416 6. PHPExcel_Writer_HTML->generateSheetData() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF/tcPDF.php:119 0.2014 9683184 7. PHPExcel_Writer_HTML->_generateRow() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php:436 Warning: Invalid argument supplied for foreach() in /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php on line 1315 Call Stack: 0.0010 258104 1. {main}() /inet/xxx/HTTP/devel/21pdf.php:0 0.1375 8979304 2. PHPExcel_Writer_PDF->save() /inet/xxx/HTTP/devel/21pdf.php:80 0.1375 8979656 3. PHPExcel_Writer_PDF->__call() /inet/xxx/HTTP/devel/21pdf.php:80 0.1375 8980096 4. call_user_func_array() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.1375 8980432 5. PHPExcel_Writer_PDF_tcPDF->save() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF.php:87 0.1569 9542416 6. PHPExcel_Writer_HTML->generateSheetData() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/PDF/tcPDF.php:119 0.2014 9683184 7. PHPExcel_Writer_HTML->_generateRow() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php:436 0.2019 9685336 8. PHPExcel_Writer_HTML->_assembleCSS() /inet/xxx/HTTP/devel/app/_includes/PHPExcel_1.7/PHPExcel/Writer/HTML.php:1267 21:55:53 File written to 21pdf_tcPDF.pdf Call time to write Workbook was 0.9275 seconds 21:55:53 Current memory usage: 16 MB 21:55:53 Peak memory usage: 18 MB 21:55:53 Done writing files File has been created in /inet/xxx/HTTP/devel

Interesting side note: take a look at the different output of tcpdf and mPDF. I think I know now, why you leave the choice of the PDF generator open to the user. tcpdf generated example mPDF generated example

Improve this question
5
  • 1
    You say it "fails".... please clarify exactly what you mean by "fails"... If you are getting error messages, then it's useful to know what those error messages actually say! Commented Jun 9, 2013 at 16:07
  • As the developer of the library in question, you're suggesting that it can't even generate an xls file.... be assured that it can; and it can also generate PDF files with merged cells; but I need a little more information (such as error messages) to try and diagnose what is going wrong Commented Jun 9, 2013 at 16:09
  • Hi, Mark Baker, I just read my post again, that I wrote a bit in a hurry and I am afraid to say it is missing a certain tone of respect to the work contained in that project and to all that work you did for the community. I apologize for that and I am happy you nevertheless answered to me. Meanwhile I put the requested information. Commented Jun 9, 2013 at 19:53
  • As a first check: does the 21pdf.php script in /Examples work if you set the $rendererName and $rendererLibraryPath values: this should include a merged cell. As a simpler test, the PHP renderer first generates an HTML file that is converted to PDF using the appropriate library, so you could also run 17html.php to see if the HTML is generated correctly; or run your own script with the HTML Writer to see what it generates. Commented Jun 9, 2013 at 22:46
  • Yes, the PDF rendering is a pain: tcPDF isn't a good renderer. I much prefer using mPDF and DomPDF myself, which provide much cleaner output; but each has pros and cons in terms of quality, memory usage and speed, so its easier to leave the choice up to the individual user. Commented Jun 10, 2013 at 22:16

1 Answer 1

Reset to default
2

Quick and Dirty fix;

Change line 1236 of PHPExcel/Writer/HTML.php, which reads:

$cssClass .= ' style' . $pSheet->getCell($endCellCoord)->getXfIndex();

to

if (!$this->_useInlineCss) { $cssClass .= ' style' . $pSheet->getCell($endCellCoord)->getXfIndex(); }
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you! The patch works perfectly. I wonder, should I do something different in my own php file, like defining or avoiding certain CSS element or is this the only way to get around this I tried to figure out the differences from the working 21pdf.php example but I could not see it. Thank you very much.
There's no easy way to get round it, and it won't be a clean fix for merge cells with borders, I need to work that out still

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.