PHP write to html formatting issue

Topics: Developer Forum, User Forum
Feb 8, 2011 at 7:30 PM

OK I finally got PHPExcel to do what I want, except now the formatting is weird.  Basically there is black backgrounds on all kind of random cells, and some cells are not the width they are set to in the document (they are wrapping)...

 

here is the code I used 

$excel = PHPExcel_IOFactory::load('../USGipv6/test_results/NPD/NPD-report.xlsx');

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

another problem is the only way I could figure out to even do this was from the forums, 
the function -> setUseInlineCSS which made another document work (otherwise there is random cells to infinity in every direction) was not even documented under Documentation->PHP Developer Documentation or PHP User Documentation

I was also confused because there is a lot of examples referred to PHP User Documentation that don't even exist, and there is random other examples in there....  just a heads up (i am not mad or anything, I understand this is a free service)

ideas to fix my excel?


Coordinator
Feb 8, 2011 at 9:04 PM
seanx820 wrote:

Basically there is black backgrounds on all kind of random cells.

This problem should have been resolved by the changes I made to "theme colours" between Christmas and New Year, and those changes are available in the latest daily download from the SVN repository.

 

seanx820 wrote:

and some cells are not the width they are set to in the document (they are wrapping)... 

See Work Item 15222 for details.

Autofit is also not a perfect calculation. If you want PHPExcel to perform an automatic width calculation, PHPExcel will approximate the column with to the width of the widest column value. See section 4.6.28 of the Developer documentation for details.

Note that, by default, PHPExcel uses a "quick and dirty" approximation to calculate a column width. A more accurate method is available, although there is a significant performance overhead to using this. You can set PHPExcel to use the more accurate method by using

PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

before writing your workbook.

seanx820 wrote:
another problem is the only way I could figure out to even do this was from the forums, 
the function -> setUseInlineCSS which made another document work (otherwise there is random cells to infinity in every direction) was not even documented under Documentation->PHP Developer Documentation or PHP User Documentation

I was also confused because there is a lot of examples referred to PHP User Documentation that don't even exist, and there is random other examples in there.

We're aware that the documentation isn't as good as it should be. The User Documentation – Reading Spreadsheet Files.doc was the first document in an attempt to improve this situation, and I'm writing further user documents on other features (Writing, Accessing Data, Setting Data, Setting Styles, Calculating, Charts, etc) trying to highlight all the options, with working examples. Unfortunately, the release build process missed the examples -- hopefully that problem is fixed for the next production release -- the examples are available in the SVN repository. It's a slow process, and I'll be working on it all over the next few releases. I'm also setting up a documentation site where I'll be making all these documents available to browse online.

 

Feb 9, 2011 at 4:08 PM

OK for the 2 items I wanted help with

1) Color was incorrect - FIXED

2) Cells are wrong Width - NOT FIXED

 

we have now fixed 1 of them, to fix the color I updated using your source code, which I did by clicking  'Source Code', downloading the entire thing then using just the Classes folder from the folder trunk to overwrite my current Classes folder. 

One new or newly noticed problem occurred.  When using a summation function in excel I get an incorrect answer, it is giving me a divide by 0 error.  I fixed this error by stopping the use of a function I was using COUNTIF(C3:C27, "*") which basically counted C:3 through C:27 and gave me 25 as the answer, making it so I did not have to manually add the cells together.  When I put the 25 in there statically the phpexcel could calculate correctly.  I don't know if that function is not supported, deprecated or what, but it was not a big deal to me.

 

What I am doing wrong on the width?

 

<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2010 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 - 2010 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.5, 2010-12-10
 */

/** Error reporting */
error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

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

$excel = PHPExcel_IOFactory::load('../USGipv6/test_results/NPD/NPD-report.xlsx');
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');

$writer->setUseInlineCSS(true);
$writer->save('npd.html');













Feb 10, 2011 at 5:28 PM

bump?  what am i doing wrong here?  

Coordinator
Feb 10, 2011 at 7:28 PM
seanx820 wrote:

bump?  what am i doing wrong here?  

Your doing nothing wrong. PHPExcel doesn't work perfectly: even PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT is a best estimate, more accurate than the default PHPExcel_Shared_Font::AUTOSIZE_METHOD_APPROX, but still an estimate.

Microsoft have only ever published this article describing how column widths are calculated. There is some description of the problems in this thread, and in section 4.6.28 of the developer documentation.

Work Item 10375 covers this issue, and some of the methods that we have tried to employ to come up with an accurate autofit method.

 

You can, of course, also look at the source (which can be found in the calculateColumnWidth() method of the PHPExcel_Shared_Font class. The code for AUTOSIZE_METHOD_APPROX is in the getTextWidthPixelsApprox() method, while the code for AUTOSIZE_METHOD_EXACT is in the getTextWidthPixelsExact() method. Helper methods for converting between Excel units and pixels are the pixelsToCellDimension() and cellDimensionToPixels() methods of the PHPExcel_Shared_Drawing class. Note that AUTOSIZE_METHOD_EXACT requires the GD library to be enabled: if it is not, then PHPExcel will revert to the getTextWidthPixelsApprox() method.

Feb 10, 2011 at 7:46 PM

is there a way to force "column 2" (the problem column that is to short and wrapping) to a css class rather than doing in-line css?  Then I could manually over-ride

with a css class or something...  maybe you have an example of something like this?  I tired using an example in the help but it just created tons of little tiny boxes (every box had borders even if there was nothing in it)

 

-S

Feb 16, 2011 at 5:12 PM

bump

Feb 28, 2011 at 9:14 PM

sorry to basically bump again, is there a way to override a width with css for the entire column?  I just want column 2 to be be twice the width...

Coordinator
Feb 28, 2011 at 9:38 PM

There is no way of overriding this if the built-in method of calculation isn't accurate enough.

Your only alternative would be to set the column width to autofit, force the calculation by hand using a call to PHPExcel_Shared_Font::calculateColumnWidth() then manually adjusting the result, followed by setting the column width to your adjusted size rather than the autofit size.

Mar 2, 2011 at 9:05 AM

There ist a second bug if you use $objWriter->setUseInlineCSS(true);

Then $objWriter->generateSheetData(); don't work without:  $objWriter->generateHTMLHeader(true);

The working code looks like:

$objWriter->setUseInlineCSS(true);
$objWriter->generateHTMLHeader(true);

$html = $objWriter->generateSheetData(); # the html-table

 

Greetings Frank

Mar 15, 2011 at 2:24 PM

here is my code now->
---------------
error_reporting(E_ALL);
date_default_timezone_set('Europe/London');


$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);

$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);$objWriter->save("cavanaugh.htm");


$excel = PHPExcel_IOFactory::load('../report.xlsx');PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');

$writer->setUseInlineCSS(true);echo $writer->generateHTMLHeader(true);
$html = $writer->generateSheetData();echo $html;

---------------


ok this code is cool because I did not know you could just echo the results and create a webpage like that (i was loading the php page then forwarding to the saved html page.  This will reload every time without the 'double load'.
This DOES NOT fixed the width of the 2nd column, it like wraps around.  Here is my example excel file http://www.seancav.com/report.xlsx can you take a look?


Mar 15, 2011 at 2:25 PM
Edited Mar 15, 2011 at 2:28 PM

 

here is my code now->
---------------
error_reporting(E_ALL);
date_default_timezone_set('Europe/London');
require_once '/Classes/PHPExcel/IOFactory.php';

$excel = PHPExcel_IOFactory::load('../report.xlsx');
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');


$writer->setUseInlineCSS(true);

echo $writer->generateHTMLHeader(true);
$html = $writer->generateSheetData();

echo $html;

 

---------------

 


ok this code is cool because I did not know you could just echo the results and create a webpage like that (i was loading the php page then forwarding to the saved html page.  This will reload every time without the 'double load'.
This DOES NOT fixed the width of the 2nd column, it like wraps around.  Here is my example excel file http://www.seancav.com/report.xlsx can you take a look?


Mar 16, 2011 at 3:38 PM

bump

Coordinator
Mar 16, 2011 at 5:13 PM
@seanx820

Read this thread

Then fix it yourself !

Mar 23, 2011 at 1:57 PM

Sean,

Remember, PHPExcel uses TCPDF to do the HTML output and it possibly could be the cause. I abandoned HTML output because of that reason.