Print Area Again...

Topics: User Forum
Apr 22, 2008 at 9:30 PM
Edited Apr 22, 2008 at 9:31 PM
This code does not set the print area. (version 1.6.0)

// Set page orientation, size, Print Area and Fit To Pages
$objPageSetup = new PHPExcelWorksheetPageSetup();
$objPageSetup->setPaperSize(PHPExcelWorksheetPageSetup::PAPERSIZE_LETTER);
$objPageSetup->setOrientation(PHPExcelWorksheetPageSetup::ORIENTATION_PORTRAIT);
$objPageSetup->setPrintAreaByColumnAndRow(1, 1, 5, $row);
// $objPageSetup->setPrintArea("A1:E".$row);
$objPageSetup->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->setPageSetup($objPageSetup);

Used both setPrintArea and setPrintAreaByColumnAndRow with same results.

What am I doing wrong?
Coordinator
Apr 28, 2008 at 6:49 AM
When I drop this in 01helloworld.php sample and change $row with "5", it works perfectly? (first column falls off)
Apr 28, 2008 at 6:53 PM

maartenba wrote:
When I drop this in 01helloworld.php sample and change $row with "5", it works perfectly? (first column falls off)


Well, it doesn't work in my program for some reason. It is not setting the print area at all. It does set the FitToWidth,
however.
Coordinator
Apr 29, 2008 at 7:19 AM
Are you sure $row is defined?
Apr 29, 2008 at 1:28 PM

maartenba wrote:
Are you sure $row is defined?

of course, it defined before a while loop and incremented in the loop.
added this and it showed the correct value:
$objPHPExcel->getActiveSheet()->setCellValue("A".$row + 1, $row);
Coordinator
Apr 29, 2008 at 3:48 PM
Just to make sure... You are using Excel2007 writer? Excel5 does not support this feature.

Here's my working example:
<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2008 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2008 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    ##VERSION##, ##DATE##
 */
 
/** Error reporting */
error_reporting(E_ALL);
 
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');
 
/** PHPExcel */
include 'PHPExcel.php';
 
/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';
 
// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();
 
// Add some data
echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
for ($i = 0; $i < 10; $i++) {
  for ($j = 1; $j < 10; $j++) {
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $j, 'Cell ' . $i . '-' . $j);
  }
}
 
// Set page orientation, size, Print Area and Fit To Pages
$objPageSetup = new PHPExcel_Worksheet_PageSetup();
$objPageSetup->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
$objPageSetup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objPageSetup->setPrintArea("E5:H7");
$objPageSetup->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->setPageSetup($objPageSetup);
 
// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Simple');
 
 
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
 
		
// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
 
// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";
 
// Echo done
echo date('H:i:s') . " Done writing file.\r\n";
Apr 30, 2008 at 2:26 AM
Edited Apr 30, 2008 at 2:26 AM

maartenba wrote:
Just to make sure... You are using Excel2007 writer? Excel5 does not support this feature.

Yes, I am using Excel5 because of the stupid Zip file problem and the fact that not many people have Excel2007 and a
file saved as Excel5 is forwardly compatible.

You know, I think you guys should mark features as not avail for certain versions. I don't see it marked in the docs anywhere.
You're telling me that Setting the PrintArea ISN'T SUPPORTED Excel5?
Coordinator
Apr 30, 2008 at 1:06 PM
You can check available features in the Excel5 version on PEAR SpreadSheet Excel (http://pear.php.net/package/SpreadsheetExcelWriter/redirected) as it is used under the covers for PHPExcel_WriterExcel5
Apr 30, 2008 at 2:01 PM
Edited Apr 30, 2008 at 2:02 PM
I could find no mention of features at that URL or the package's pages. In fact, it even HAS a printArea function where you can set the printArea by row/column.


maartenba wrote:
You can check available features in the Excel5 version on PEAR SpreadSheet Excel (http://pear.php.net/package/SpreadsheetExcelWriter/redirected) as it is used under the covers for PHPExcel_WriterExcel5

Coordinator
May 2, 2008 at 9:02 AM
Will have to map that. A work item is on its way!
Coordinator
May 2, 2008 at 9:03 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
May 5, 2008 at 6:40 AM
Will NOT be implemented due to bug: http://pear.php.net/bugs/bug.php?id=2146
May 5, 2008 at 2:22 PM

maartenba wrote:
Will NOT be implemented due to bug: http://pear.php.net/bugs/bug.php?id=2146


So, it DOES work in Excel2007 but won't be implemented in Excel5? Shame, shame.
Is there a way to input a macro into a spreadsheet so I can dynamically write the code to
set the printarea in an onload macro?
Coordinator
May 5, 2008 at 3:26 PM
If you have a look at the PHPExcelWriterExcel5 sources, you'll find a commented pice of code (search for "print area"). If you uncomment that, print area works (anther change will be needed regarding setVersion(8)).
May 5, 2008 at 5:01 PM

maartenba wrote:
If you have a look at the PHPExcelWriterExcel5 sources, you'll find a commented pice of code (search for "print area"). If you uncomment that, print area works (anther change will be needed regarding setVersion(8)).


Since we will never be using UTF-8, I don't think it'll matter for my uses.
Developer
Oct 21, 2008 at 11:19 PM
Update: Fixed per work item 7834
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=7834

Feb 6, 2012 at 3:57 PM

Hello guys,

my file being generated has an extra column which falls outside the printable area of ​​default. However if I set the print area, the number of rows is exceeding my spreadsheethas a print area, even if I set the print area smaller, some lines do not appear to be printed. My spreadsheet has seven columns A through H and 356 lines. I wish it werepossible to print all the columns and then the other lines that do not fit on one sheet, othersheets were printed. I can do this by setting printing excel, but when my client is to export the report to excel, I would leave the impression that the right way.
This is the report without defining the print area, leaving the other lines that do not fit on one sheet to another, as in standard mode printing:

https://lh4.googleusercontent.com/-gt7YRz8BFpI/TzAEugZFmNI/AAAAAAAAAO4/ElxaKSrW5aE/s512/normal.png


This one, I defined print area, however the other lines that exceed the sheet does notappear to be printed:

https://lh6.googleusercontent.com/-_kAQ9CiQmDc/TzAEu0SGjEI/AAAAAAAAAO8/Fmgr_o8m098/s512/normal2.jpg


I tried using header and footer as well and could not.


How can I solve the issue of printing and the use of the header and footer?