Fit excel to pdf

Topics: Developer Forum, Project Management Forum, User Forum
Jan 21, 2010 at 4:45 PM

Php version doesn't matter. I'm running phpexcel 1.7.2

I'd like some help, with formatting a pdf. I'm currently trying to convert an excel sheet to pdf. I can do this but I can't seem to fit ithe excel sheets in the pdf document. So I was wondering if it's possible to make the excel document, fit the pdf. So the excel shrinks, until it fits in. I tried landscaping, though I don't see a difference between normal, and landscape...

I hope someone can help me. This is my code:

 

<?php
error_reporting(E_ALL);

require_once '../Classes/PHPExcel/IOFactory.php';

if (!file_exists("temptopdf.xlsx")) {
     exit("Test file was not found..\n");
}

$objPHPExcel = PHPExcel_IOFactory::load("temptopdf.xlsx");
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(1);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save(str_replace('.php', '.pdf', __FILE__));
echo "Done writing!";
?>
Thanks in advanced.

Developer
Jan 21, 2010 at 5:05 PM

It may not be easy to achieve shrink-to-fit. The problem is that TCPDF (which PHPExcel uses) has somewhat limited features.

If you export to HTML (inline CSS) using PHPExcel, you can send to generated HTML to mPDF. I once tried that class and I believe it would automatically do shrink-to-fit (width). Some experiments may be needed.

Jan 21, 2010 at 5:40 PM

Experience programming, or experience with this class. Because I do have experience programming... The only problem I have is... I need to keep the styles used in the excel sheets. Like, column colour, bold underlined italic.... if this is possible, I would much rather prefer knowing how to do this in html, so I can alter it, and write it to pdf.

Developer
Jan 21, 2010 at 5:52 PM

Try to start exporting to HTML like this:

$excel = PHPExcel_IOFactory::load('temptopdf.xlsx');

$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');
$writer->setUseInlineCSS(true);
$writer->save('write.html');

Look at the HTML that is generated (write.html).

mPDF can convert HTML -> PDF. That is what I am suggesting that you try. Bold, underlined, italic should be working too.

Jan 21, 2010 at 6:12 PM

I tried, and it works. It takes italic, bold, etc... but only one sheet, but I guess i can fix this... Don't know how, If you could tell me how, I won't have to look.

another question though, something goes wrong with the number format. sometimes things such as '#NUM!' and ' 5.684341886080801E-14' apear. Also the numer format is wrong as in: 193283.92 in stead of 193.283,92

 

Developer
Jan 22, 2010 at 5:17 AM

To write all sheets:

$excel = PHPExcel_IOFactory::load('temptopdf.xlsx');

$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');
$writer->writeAllSheets();
$writer->setUseInlineCSS(true);
$writer->save('write.html');

As for the other questions, can you upload the Excel file so we can see? You can upload files here: http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10749

Jan 22, 2010 at 10:25 AM

I tried uploading there, i just have no idea what happend with the file :P So I uploaded it on my server:

Html output: http://www.vandenacker.be/pdftest/Tests/write.html
xls file: http://www.vandenacker.be/pdftest/Tests/temptopdf.xlsx *I noticed opening is not working, so you can click right mouse button and save target as*

These are the files. Now I noticed, that the empty cells, with underline on it, make small lines, in both the pdf and the html. Not a big problem, just have to make the sheet better, or check if the cell is empty. *Speaking of empty cells, check out the html output, it's to long, it goes on with empty cells. I'd like to prevent that*

ps: Thanks for your help :) much appreciated

Developer
Jan 22, 2010 at 3:35 PM
solow wrote:

sometimes things such as '#NUM!'

This looks like a bug. We need to consult MarkBaker. Work item created:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=11587

 

solow wrote:

and ' 5.684341886080801E-14' apear.

This is a bug. Now fixed:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=11588

 

solow wrote:

Also the numer format is wrong as in: 193283.92 in stead of 193.283,92

PHPExcel looks in the servers locale settings to find the appropriate decimal and thousands separators. If it fails, set decimal and thousands separators explicitly like below.

 

PHPExcel_Shared_String::setThousandsSeparator('.');
PHPExcel_Shared_String::setDecimalSeparator(',');

$excel = PHPExcel_IOFactory::load('temptopdf.xlsx');

$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');
$writer->setUseInlineCSS(true);
$writer->save('write.html');

We are working on some locale features, so in the future this may be handled in a better way.

 

solow wrote:

These are the files. Now I noticed, that the empty cells, with underline on it, make small lines, in both the pdf and the html. Not a big problem, just have to make the sheet better, or check if the cell is empty.

Now fixed:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=11590

Jan 22, 2010 at 3:38 PM

Wow, So in fact i found some minor bugs huh. I love it when am helping out haha. now, why do you direct to that topics? If it's fixed, where do i download the new classes?

Developer
Jan 22, 2010 at 3:45 PM
solow wrote:

Wow, So in fact i found some minor bugs huh. I love it when am helping out haha. now, why do you direct to that topics? If it's fixed, where do i download the new classes?

There is check-out every 24 hours. Next source code release should be available in about 17 hours from here:

http://phpexcel.codeplex.com/SourceControl/list/changesets

Jan 22, 2010 at 3:46 PM

Okay. I will download it from there tommorow. btw i commented the fix with the underline bug.