Can not show last row that sumarize of each columns on web

Topics: Developer Forum, Project Management Forum, User Forum
Oct 15, 2009 at 4:07 AM
Edited Oct 15, 2009 at 4:13 AM

I am a new phpexcel.I have some problems. My application read data in excel file and display data in table on web.

I have a problem it can not display when it have summary row in excel.if I remove it that is Ok.

My excel have 58 columns and 250 rows I am not sure the error came from memory or not?

I work with exel5.

I use phpexcel 1.70.

However I use phpexcel 1.6.7 that is OK but I face error about the formulas show wrong value. example

  in excel
        =(100000*12)

 read it show on table it display   60 it seem like  1.0E+5*12

Could you please help me??

 

 

Developer
Oct 15, 2009 at 12:54 PM

Can you try to run this test on your server:

<?php
ini_set('precision', 16);
echo 10000;
?>

Question 1:

Do you get this?

10000

or this?

1.0E+5

Question 2:

What version of PHP are you using and on which platform?

Oct 15, 2009 at 2:21 PM

Thank you for your response

Answer1: 

       I  get the result is 10000.

Answer2:

      I use The AppServ Open Project - 2.5.10 for Windows that use php version 5.2.6

 

Please advise me?

Developer
Oct 15, 2009 at 2:51 PM

With the Excel file you are reading (read.xls), when you run this test:

<?php
$file = 'read.xls';
$reader = PHPExcel_IOFactory::createReaderForFile($file);
$excel = PHPExcel_IOFactory::load($file);

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

What do you get when you view write.html in your browser? Still same kind of error?

Oct 15, 2009 at 3:28 PM

Hi Koyama san

   I already tested your script I found other error as below

   Fatal error: Class 'ZipArchive' not found in C:\AppServ\www\npl\Classes\PHPExcel\Reader\Excel2007.php on line 217


<?php
/** Error reporting */
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
/** PHPExcel */
include 'PHPExcel.php';
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

$file = 'test.xls';
$reader = PHPExcel_IOFactory::createReaderForFile($file);
$excel = PHPExcel_IOFactory::load($file);
$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');
$writer->writeAllSheets();
$writer->save('write.html');
?>

Please advise me?

 

Developer
Oct 15, 2009 at 3:34 PM

Oh, sorry, didn't know zip extension was missing on your server.

Try this instead:

<?php
/** Error reporting */
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
/** PHPExcel */
include 'PHPExcel.php';
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

$reader = new PHPExcel_Reader_Excel5();
$excel = $reader->load('test.xls');
$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');
$writer->writeAllSheets();
$writer->save('write.html');
?>

Oct 15, 2009 at 3:55 PM

Hi Koyama san
 
  I still found the error as below.
 
   Fatal error: Class 'PHPExcel_Reader_Excel5' not found in C:\AppServ\www\npl\test3.php on line 11
   
  my script as

  <?php
/** Error reporting */
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
/** PHPExcel */
include 'PHPExcel.php';
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

$reader = new PHPExcel_Reader_Excel5();
$excel = $reader->load('test.xls');
$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');
$writer->writeAllSheets();
$writer->save('write.html');
?>

Anyway,it may be help you to solve this problem. I use PHPexcel version 1.7.0 and update patch with 29032

 

Developer
Oct 15, 2009 at 3:59 PM

Ups. Forgot to include the class, try this:

<?php
/** Error reporting */
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
/** PHPExcel */
include 'PHPExcel.php';
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
include 'PHPExcel/Reader/Excel5.php';

$reader = new PHPExcel_Reader_Excel5();
$excel = $reader->load('test.xls');
$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');
$writer->writeAllSheets();
$writer->save('write.html');
?>

Oct 15, 2009 at 4:53 PM

Hi koyama san

After I tested

in excel I use formulas

=1000000*5

 when I run script  write.html show  5.0E+6

Please advise me?

 

Developer
Oct 16, 2009 at 1:30 AM

Question 3:

What is result of this:

<?php
ini_set('precision', 16);
echo (float)10000;
?>

Question 4:

What is result of this:

 

<?php
echo (float)10000;
?>
Oct 16, 2009 at 2:40 AM

Hi Koyama san

   Answer3:

           I get the result is 10000.

           However if I change from 10000 to 100000 that display 1.0E+5

  Answer4:

            it diplay 10000 if I change  from 10000 to 10000 that display 100000

             

 

Developer
Oct 16, 2009 at 3:12 AM

My mistake, I meant 100000 and not 10000. Thanks for pointing this out.

As far as I know this is a PHP-bug which was fixed in PHP 5.2.8. If you upgrade, the error should disappear.

Alternatively, you can locate this line in PHPExcel/Calculation/Functions.php

if ($savedPrecision < 16) {
    ini_set('precision',16);
}

And simply comment it out like this:

//if ($savedPrecision < 16) {
//    ini_set('precision',16);
//}

Related thread:

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=47242

Oct 16, 2009 at 5:20 AM

Hi Koyama san

   Thank you for your strong support.

   I already solve bug about the problem of formulas calculate display in web.

   My solution is  comment in PHPExcel/Calculation/Functions.php.I still not upgrade PHP.

   However this way fix for phpexcel version 1.6.7 when I use this version is OK.

  but when I use this with phpexcel version1.7.0  It can not render data to web page.It display

  "The Page cannot be displayed" I don't know that why can not use with this version.?

The detail as below

   My excel have colums A until BG and have 183 rows

  and last row (183) is summary data and use formulas example as below

  =SUM(R7:R182).........................  ,  =SUM(BG7:BG182)

  When I run phpexcel version 1.6.7 after fixed that you suggest is OK.but I use version 1.70 it display

  "The Page cannot be displayed" 

  However I remove summary line it can data on web.

Please advise me? If I upgrade PHP. it is solve this problem isn't it?

 

Developer
Oct 16, 2009 at 2:37 PM

>> but when I use this with phpexcel version1.7.0  It can not render data to web page.It display
>>  "The Page cannot be displayed" I don't know that why can not use with this version.?

Using PHPExcel 1.7.0 and commenting the mentioned lines what do you get when you run the code again from previous post:

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=72039#Post246076

Is the HTML file generated OK?

Oct 22, 2009 at 10:59 AM

Hi Koyama san

   Sorry response late.After I try to use other webserver.I use wampserver2.0 instead appserv that use php version 5.2.9-2.

The result not work! I try to test follow you suggest

<?php
/** Error reporting */
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
/** PHPExcel */
include 'PHPExcel.php';
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
include 'PHPExcel/Reader/Excel5.php';

$reader = new PHPExcel_Reader_Excel5();
$excel = $reader->load('test.xls');
$writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');
$writer->writeAllSheets();
$writer->save('write.html');
?>
It display "Internet Expoler cannot display the webpage"
Anyway It creat file "write.html" but it nothing to display any data!!! 
In case I remove summary row It's work.
  =SUM(R7:R182)......................... =SUM(BG7:BG182) --If it have this line it not work!!
But
=SUM(R7:R182)............. =SUM(Q7:Q182) It's work?
Please advise me
Developer
Oct 22, 2009 at 7:55 PM

This is strange. Can you upload test.xls so we can check? You can upload the file here: http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10749

Oct 24, 2009 at 3:06 PM

I've already uploaded file to you the file name is "test.xls " please check

Developer
Oct 24, 2009 at 8:17 PM

The problem has finally been identified. PHPExcel_Reader_Excel5 was at fault.

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

Download latest source code tomorrow:

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

Oct 26, 2009 at 3:47 AM

Thank you for  your support.

I've already download lastest source code phpexcel-29169 and copy to replace it.

I face 2 problems

Fist, when I call it show

Warning: Call-time pass-by-reference has been deprecated in C:\wamp\www\npltest\Classes\PHPExcel\Calculation.php on line 2876
Warning: Call-time pass-by-reference has been deprecated in C:\wamp\www\npltest\Classes\PHPExcel\Calculation.php on line 2883

Below is my code to call excel

$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load(test.xls);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();

$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

The second when I create excel by application the charecter is abnormal like below

IH2" #'! 2#0+5I .- 2552#'! "-@I+%7-@4*#1"L

Anyway I try to copy only folder Reader from  phpexcel-29169\Classes\PHPExcel it 's OK.

I'm not sure your lastest source code have some problems

Developer
Oct 26, 2009 at 4:33 AM

1)

Bug report created:

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

2)

>> The second when I create excel by application the charecter is abnormal like below

>> IH2" #'! 2#0+5I 

Where do you see these characters? In HTML or in Excel? How can I test?

Oct 26, 2009 at 5:23 AM

1). Do you need my sample excel for test?

2). I see in Excel 2003. sample script as below


<?php
/* Here there will be some code where you create $objPHPExcel */
// redirect output to client browser
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="myfile.xls"');
header('Cache-Control: max-age=0'); 
//header('Content-Transfer-Encoding: binary');


/** Error reporting */
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
/** PHPExcel */
include 'PHPExcel.php';
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(8);

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true); 
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello Phpexecl');

$objWriter->save('php://output');

?>

However I feeling if you can solve the first issue it may be fix second issue also

Developer
Oct 26, 2009 at 5:43 AM

>> 1). Do you need my sample excel for test?

No, we just wait and see if MarkBaker responds to the bug report. He is calculation expert.

>> 2). I see in Excel 2003. sample script as below

Hmm... I tried your script, and it worked fine. Maybe you have saved as UTF-8 with BOM instead of without BOM. Look at this thread:

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=71957

Otherwise, try to download latest source code:

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

There you should find example: Tests/01simple-download-xls.php. Try to see if it works.

 

Oct 26, 2009 at 6:04 AM

1). OK. I am waitting to your fix this problem

2). The First time I think this cause from my file save the wrong type.After I check it's correct.It was saved as UTF-8 without BOM.

Anyway It still not work. I already uploaded file for my the result.

Coordinator
Oct 26, 2009 at 10:20 AM

Re problem 1: Warning: Call-time pass-by-reference has been deprecated in C:\wamp\www\npltest\Classes\PHPExcel\Calculation.php on line 2883

Fixed and should be available in tomorrows SVN download: see Work Item for details of how to fix it yourself if you want to remove the warnings before my change is available for download

Developer
Oct 26, 2009 at 11:44 PM

>> Anyway It still not work. I already uploaded file for my the result.

I see the warning message in your uploaded file. I had misunderstood. In that case it should be working with latest source code after modifications by MarkBaker.