Page Scaling Settings Get Lost In Excel2007 Download

Topics: User Forum
Aug 16, 2009 at 11:21 PM
Edited Aug 16, 2009 at 11:50 PM

Background:

To save a lot of unnecessary coding and to provide continuity I have setup up a number of XLS and XLST templates that are used for data downloads from my application. These templates have properties like page scaling set to help users print out their data. The templates have a scaling setting of 1 page(s) wide by <blank> tall (set using the Page Setup... menu in Excel). This has the effect of printing all of the columns on 1 page rather then across multiple pages. The templates are not complex - no formulas, charts, etc. - just some basic cell formatting (font style, bold for header row, etc.). The sequence of events are:

  1. User selects the file type (xls, xlsx) to download.
  2. File type template is read and specific data header & rows are added.
  3. File is saved to the php://output stream for download.

Issue:

When using the XLSX (Excel2007) the page scaling properties set in the template (1 page(s) wide by <blank> tall) on the downloaded file are reset to the Excel default (Adjust to 100% normal size). However, this is NOT the case for XLS (Excel5) downloads. On XLS downloaded files the page scaling properties set in the template are maintained.

In short, here is how I accomplish writing out the Excel2007 document:

//OMITTED - Get XLSX template file path

//Load the template file
$objPhpExcel = PHPExcel_IOFactory::load($template_file);

//OMITTED - Code to add my data rows WITHOUT changing any of the preset template properties

//Instantiate the Excel2007 Writer
$objWriter = PHPExcel_IOFactory::createWriter($objPhpExcel, 'Excel2007');
//Set office compatibility in case the user has an older version of Excel
$objWriter->setOffice2003Compatibility(true);

//OMITTED - Code to set appropriate download headers

//Save the file to the php://output stream
$objWriter->save('php://output');

Notes:

  • The XLSX template files are generated using Excel 2002 with the Compatibility Pack (SP2) installed. On saving then reopening the XLST template files the scaling settings are maintained (e.g. they are not lost when saving the file under the compatibility pack).
  • The downloaded XLSX file works fine in all other respects. All template document properties and formatting properties are maintained. The downloaded file is not corrupt and and the file opens without any errors.
  • I have tried disabling the writer's office compatibility setting but to no effect. Scaling settings are not maintained either way.
  • OS: Windows XP 5.1.2600 SP3
  • Excel: 2002 (10.6854.6854 ) SP3 w/ Compatibility Pack (SP2)
  • Web Server: Microsoft-IIS/5.0
  • PHP Version: 5.2.10
  • PHPExcel: 1.7.0

I'm not sure if this is a bug or something I am doing wrong. Any insight would be appreciated. Thanks!

Developer
Aug 16, 2009 at 11:50 PM

I recall this issue which is exactly what you are describing:

http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=7610

According to Maarten, the problem is that the fit-to-width value 1 is stored in some weird place, difficult to extract from some binary data. This was very annoying because one would have to add the setFitToWidth(1) manually between the read/write of the template. (Note: It only seemed to happen for the value 1, and not 2, 3, etc.)

Now, it is a long time ago I had that issue, so I might like to do some more testing such as checking whether OpenOffice.org 3 is able to read the value correctly. Anyway, it might comfort you that you are not the only one having this problem.

Developer
Aug 17, 2009 at 12:11 AM

Edit: I just checked OpenOffice.org 3, it understands fit-to-width = 1 ok. I think our assumption needs to be checked again and the work item I linked to re-opened. Will investigate this and post back here tomorrow.

Aug 17, 2009 at 12:38 AM

Thanks for doing that leg work. I have in the interim explicitly coded the following:

$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);

which indeed works to set the downloaded file's scaling settings as per my original intentions. However, as I'm sure you well know, this is not an ideal solution. If at some point I wish to change the scaling settings in my template(s) I'd have to go back and rip out (or change) all this workaround code. Hopefully this is something I (we) are simply misunderstanding and, if not, can be fixed without too much trouble. Really LOVE this product. Thanks for your help and look forward to your findings.

Developer
Aug 17, 2009 at 2:13 AM

Should now be fixed:

http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=7610

Fix will be available in source code release tomorrow:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Aug 17, 2009 at 12:32 PM

Just tested SVN 28490 and the issue seems to be resolved. Thanks so much for your diligence and attention on this. It is truly appreciated!