Excel 2007 - php 5.2.8 - getColumnDimension('')->setWidth()

Topics: User Forum
Dec 31, 2008 at 10:04 PM
Hi All,

First off, love the project. Meets a very big need in the PHP community.

I am trying to set the width of each column in a spreadsheet to meet a very strict format. The actual value for the widths are off by a various amount (can't find a pattern). Using paper size of A4 and displaying to the browser using the code below. I found a suggestion that it may be related to font so tried setting the default font to a monospace font (Courier New) and found no difference. The file is being opened using Excel 2007.

Any ideas? Need any additional info?

//Set column widths                                                          //ACTUAL
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(0.54);  //0.33
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12.75); //12
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(16);    //15.29
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12.5);  //11.71
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(5.88);  //5.14
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(0.92);  //0.5
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(9.63);  //8.86
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(4.88);  //4.14
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(14.25); //13.57

Paper size code

//Set paper size to A4
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

File serving code

// Save Excel 2007 file
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=testing.xlsx");
header("Content-Transfer-Encoding: binary ");

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');





Developer
Jan 1, 2009 at 7:37 PM
You are right that the column width set using PHPExcel does not correspond to the value seen when one opens the Excel file in MS Office Excel.

Your best option is probably to read off the widths from a template and then use those values when you set the widths.

The steps would be as follows:

1) Create in MS Office Excel 2007 your template document where you set the widths accurately. For example, that means a width of 0.54 for column 'A' from your example. Save the document as template.xlsx

2) Read the template.xlsx using PHPExcel_Reader_Excel2007 and write down the values you get for the widths.
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->getWidth();
These will differ slightly from the values seen in MS Office Excel.

3) Use the values from 2) in your setWidth(<width>) methods for the final generation of your xlsx file.

Why is column width not the same?

Column width is really a big mess in Excel. MS Office Excel is operating with several different measures for column width. Let's try out a simple experiment. Create a blank Excel document in MS Office Excel 2007 and save it as xlsx and xls. We set the width of column 'D' to 22.14 (160 px), just an arbitrary width.
http://img145.imageshack.us/img145/5615/rendering.png

If you change file extension from xlsx to zip, unzip it, and look at the stored value for width of column 'D' it says instead 22.85546875 which is obviously different from 22.14.
http://img145.imageshack.us/img145/1671/xlsx.png

If you look at the xls document in a BIFF8 viewer the story is the same. You find the value 0x16DB = 5851. This column width measure is in fractions of 1/256, so we get 5851 / 256 = 22.85546875 which is the same as in xlsx.
http://img145.imageshack.us/img145/3077/xls.png

The point is that there is no place in the Excel document you will find the value 22.14 (or 160 px) stored. Those values are computed by the specific application MS Office Excel 2007.

If you don't have this algorithm, then this is a big problem when you programatically want to set a column width to a fixed number of pixels. I am not sure whether the algorithm is being kept secret by Microsoft or not.

There is some more info here, but it does not seem to suffice for the construction of the algorithm.
http://support.microsoft.com/kb/214123

If someone knows more, please post.
Jan 5, 2009 at 6:51 PM
Thanks for the quick reply. One of the best forum replies I have ever seen. I will give it a shot!
Developer
Jan 6, 2009 at 5:12 AM
I forgot to mention one thing. When you create your template, make sure that the workbook's normal (default) font matches the default font you intend to use in your final workbook. Otherwise, the method will not work.

The font I am talking about is the font that is used for the column and row headings (A, B, C,... 1, 2,...). In MS Office Excel 2007, to my knowledge, you can only set this default font before you create the workbook. Method is described here:
http://www.dummies.com/how-to/content/changing-excel-2007s-default-font-and-font-size.html
If you have a workbook and you are not sure what the default font is, hit the button to create a new sheet and look at what it says for the font in the style toolbar. This reveals the default font.

In PHPExcel you can change this font from any sheet using for example
$sheet->getDefaultStyle()->getFont()->setName('Arial');
$sheet->getDefaultStyle()->getFont()->setSize(20);

Notice, in PHPExcel this font defaults to Calibri 10. In MS Office Excel 2007, this is usually Calibri 11; in earlier versions of MS Office Excel this is typically Arial 10.

Jul 23, 2009 at 8:05 PM

I'm running into a similar issue as the above where if I set the width of my columns in PHP, then in the generated Excel file, the columns are exactly 0.71 less than what I entered. I see in the developer documentation it mentions this and recommends to change the default font to Arial, but I tried this and I wasn't able to get it to work.  Is anyone else running into this issue?  I noticed that if I downgrade to PHPExcel 1.6.4, then my columns widths in the PHP match directly with the generated Excel file.  This behavior of the columns being 0.71 less started in PHPExcel 1.6.5.  The code I have been using to test is:

$workbook = new PHPExcel;
$workbook->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial');
$workbook->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(10);

$writer = PHPExcel_IOFactory::createWriter($workbook, 'Excel5');
$writer->save('write.xls');

Thanks for your help.

Ryan

 

Developer
Jul 24, 2009 at 10:33 PM

@rbro: Column width is unfortunately a mess in Excel. When you use setWidth(10) in PHPExcel, the width 10 does not correspond to the column width as shown in Microsoft Office Excel. See explanation in my above reply.

>> I see in the developer documentation it mentions this and recommends to change
>> the default font to Arial, but I tried this and I wasn't able to get it to work.

You are probably right on this, I think the documentation is wrong here and we should remove the advice if it is wrong.

>> I noticed that if I downgrade to PHPExcel 1.6.4, then my columns widths in the PHP
>> match directly with the generated Excel file.  This behavior of the columns being 0.71 less
>> started in PHPExcel 1.6.5.

Yes, I understand the confusion. If I remember correctly, what you are describing holds for Excel5 writer, but not Excel2007 writer. From 1.6.4 to 1.6.5 Excel5 writer was modified to match the same units as Excel2007 writer. The change was intentional.

Jul 28, 2009 at 2:28 PM

Thanks for your help.  I will check through your source code history, but do you happen to know what file or files were changed between 1.6.4 and 1.6.5 to have setWidth match the same units as the Excel2007 writer?  In my particular case, I am only generating Excel5 files, so I was thinking I could run a locally modified version of PHPExcel that has the version of setWidth where the column widths match the generated files.

 

Thanks,

Ryan

 

Developer
Jul 28, 2009 at 5:44 PM

>> but do you happen to know what file or files were changed between 1.6.4 and
>> 1.6.5 to have setWidth match the same units as the Excel2007 writer?

The change was made here:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=7944

What was done was to remove this line in the _storeColinfo() method in Classes\PHPExcel\Writer\Excel5\Worksheet.php

$coldx   += 0.72;            // Fudge. Excel subtracts 0.72 !?

Sep 21, 2009 at 10:16 AM
Edited Sep 21, 2009 at 10:20 AM

Hi All,

If i set:

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4);        => 3,29

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4,1);     => 3,43

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4,2);     => 3,43

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4,3);     => 3,57

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4,4);     => 3,71

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4,5);     => 3,71

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4,6);     => 3,86

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4,7);     => 4

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4,8);     => 4,14

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4,9);     => 3,14

and if i set:

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15,5)    => 14,71

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16,5)    => 15,71

...

you want to set ???

have fun ^^

^^! sorry, my english very bad

Developer
Sep 21, 2009 at 1:29 PM

I will see if I can add some explanation about column widths in the PHPExcel developer documentation for next release PHPExcel 1.7.1.