error when recording in a format with formulas, styles and graphics

Topics: Developer Forum, Project Management Forum, User Forum
Nov 11, 2009 at 2:21 AM

greetings, I have the following problem:

I have a format in Excel 2003 workbook with formulas, styles, graphics and other elements. to open the same, and intention to save a value in a cell determianda, I get the following error

 


Fatal error: Uncaught exception 'Exception' with message 'Syntax error: , lookahead: , current char: 0' in C:\wamp\www\Excel\PHPExcel\Writer\Excel5\Parser.php:1406 Stack trace: #0 C:\wamp\www\Excel\PHPExcel\Writer\Excel5\Parser.php(1312): PHPExcel_Writer_Excel5_Parser->_fact() #1 C:\wamp\www\Excel\PHPExcel\Writer\Excel5\Parser.php(1272): PHPExcel_Writer_Excel5_Parser->_term() #2 C:\wamp\www\Excel\PHPExcel\Writer\Excel5\Parser.php(1220): PHPExcel_Writer_Excel5_Parser->_expression() #3 C:\wamp\www\Excel\PHPExcel\Writer\Excel5\Parser.php(1207): PHPExcel_Writer_Excel5_Parser->_condition() #4 C:\wamp\www\Excel\PHPExcel\Writer\Excel5\Worksheet.php(965): PHPExcel_Writer_Excel5_Parser->parse(''% Humedad'!$A$...') #5 C:\wamp\www\Excel\PHPExcel\Writer\Excel5\Worksheet.php(484): PHPExcel_Writer_Excel5_Worksheet->_writeFormula(6, 3, '='% Humedad'!$A...', 16) #6 C:\wamp\www\Excel\PHPExcel\Writer\Excel5\Workbook.php(492): PHPExcel_Writer_Excel5_Worksheet->close() #7 C:\wamp\www\Excel\PHPExcel\Writer\Excel5\Workbook.php(233): PHPExcel_W in C:\wamp\www\Excel\PHPExcel\Writer\Excel5\Parser.php on line 1406

 

I wonder if I can do this program with the library phpexcel or not supported I want to do.

PD. I tried with a blank file (no formulas, no styles or graphics), which contains only text and numeric values, and if I save some value in a specific cell.

Annex also code that uses

 

<?php
require ("PHPExcel.php");
require ("PHPExcel/Writer/Excel5.php");
require ("PHPExcel/IOFactory.php");


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

$objPHPExcel = $objReader->load("asignacion.xls");
$objWorksheet = $objPHPExcel->getActiveSheet(0);

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 11, 'prueba');

$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save("asignacion.xls");

?>

Developer
Nov 11, 2009 at 2:38 AM

This looks like a bug in the formula parser for Excel5 writer.

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

Can you show us the file asignacion.xls so we can check? Please upload here:

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

Developer
Nov 12, 2009 at 5:27 AM
Edited Nov 12, 2009 at 5:28 AM

I tried this exactly the same as you:

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

$objPHPExcel = $objReader->load("asignacion.xls");
$objWorksheet = $objPHPExcel->getActiveSheet(0);

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 11, 'prueba');

$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save("asignacion-saved.xls");


But it worked fine as you can see:



asignacion-saved.xls

Why don't I get the error? Is it possible that you renamed a sheet since you tried originally? Also, what version of PHPExcel are you using?

 

Nov 23, 2009 at 2:29 AM

thanks for your prompt response, really try with the same file and actually gave me no error. Now, trying with the same file, but including real images, which had suppressed before send it, I get the same error.

You send the file again with the original images.

By the way, as that PHPExcel version am I using? which is the last?

Developer
Nov 23, 2009 at 4:32 AM
Edited Nov 23, 2009 at 4:35 AM

Strange, I still don't get any errors. I tried this using latest source code:

$excel = PHPExcel_IOFactory::load('asignacion.xls');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$writer->save('asignacion-saved-2.xls');


Look:

asignacion-saved-2.xls

Are you using latest source code? Here is latest source code

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

 

Nov 23, 2009 at 4:36 PM

PHPExcel updated version of the 29,473, the link you sent me, and no longer gives me the error.

however, when you run the code, I really writes that tells the cell but loses the original formatting (including the logo of the company). How would you get the formatting is retained as is?

then the code I use:

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

$objPHPExcel = $objReader->load("asignacion.xls");
$objWorksheet = $objPHPExcel->getActiveSheet(0);

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 11, 'prueba');

$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save("asignacion-saved.xls");

PD. I know no other way to maintain the original format, if there is another code, and if your chance, I appreciate what I facilites.

Thanks in advance

Developer
Nov 24, 2009 at 6:37 AM

The reason why you are losing styles is because you have this line:

$objReader->setReadDataOnly(true);

Try to remove it and you should be able to see styles and images.

Nov 24, 2009 at 7:06 PM

thanks for your reply, remove the line and kept the format.

Now, with the same code, but with this file (formato.xls), it hangs the browser to run the program.

try leaving only the first two leaves and ran, but lost the second style.

What could be the problem now? take the opportunity and went up the file in question.

Developer
Nov 27, 2009 at 3:05 AM

I can verify the bug. Will investigate tomorrow and post back here.

Nov 28, 2009 at 1:06 AM

ok, I appreciate it. In short, I need to write certain values in certain specific cells in each leaf of the file (formato.xls) you send, without losing the original format of it.

Jan 15, 2010 at 4:03 PM

Koyama Greetings, sorry to bother you again. You could investigate anything about the error that you said last time related to the discucion 74,737, which didst answer me this?

Developer
Jan 17, 2010 at 8:53 AM

Bug finally discovered and fixed:

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

Jan 31, 2010 at 7:39 PM

Koyama Greetings, I experimented with changes indicated in the link (http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=11528) indicastes me and throw me the following error:

Fatal error: Call to undefined method PHPExcel_Shared_Escher_DgContainer_SpgrContainer: setDgContainer () in D: \ wamp \ www \ Formato_Suelo_Excel \ PHPExcel \ Reader \ Excel5 \ Escher.php on line 451

I was using the library in his version and decided to update 29,473 to 38,617, which is the last to date, which has raised the correction on the above link. However, when running the code and the file (formato.xls), you already know, it remains to crash the application.

Any other suggestions?. Thanks again and sorry because I've actually bothered several times on the same topic.

Developer
Jan 31, 2010 at 11:48 PM
guarus wrote:

Koyama Greetings, I experimented with changes indicated in the link (http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=11528) indicastes me and throw me the following error:

Fatal error: Call to undefined method PHPExcel_Shared_Escher_DgContainer_SpgrContainer: setDgContainer () in D: \ wamp \ www \ Formato_Suelo_Excel \ PHPExcel \ Reader \ Excel5 \ Escher.php on line 451

I was using the library in his version and decided to update 29,473 to 38,617, which is the last to date, which has raised the correction on the above link. However, when running the code and the file (formato.xls), you already know, it remains to crash the application.

Any other suggestions?. Thanks again and sorry because I've actually bothered several times on the same topic.

I was quite sure it was fixed, but apparently not. Can you show the exact code you are using to produce this error?

Feb 1, 2010 at 2:26 AM

This is the code:

<?php
   require ("PHPExcel.php");
   require ("PHPExcel/Writer/Excel5.php");
   require ("PHPExcel/IOFactory.php");
   
   $objReader = PHPExcel_IOFactory::createReader('Excel5');
    
   $objPHPExcel = $objReader->load("formato.xls");
   $objWorksheet = $objPHPExcel->getActiveSheet(0);
    
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,7, 'prueba');
   
   $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
   $objWriter->save("asignacion-saved.xls");

?>

which is the same that I used throughout this topic. comment as you go down version of the library and 38,617 were still being hung application when I run the same. I am using the same file (formato.xls) that goes up anyway.

PD. the error that tells you the version I did with 29,473 of the library, but I stopped going out to get the 38,617 version, except that now only the application is hung with the specified file.

Developer
Feb 2, 2010 at 7:27 AM

Bug verified with the new formato.xls file. The problem is a different one this time.

Can you try with latest source code in one hour:

http://phpexcel.codeplex.com/SourceControl/list/changesets

Feb 2, 2010 at 9:26 PM

koyama thanks, actually solve the problem of infinite loop in question, but note that when assigning values to cells, either intra-or sheet or several, you lose the format (style) original, or at least my case, apart from not write in the rest of the cells indicated.
The loss of the format is bug?, Not writing above is bug? coupled to it gives me a circular reference message, if this is not associated with the above, but I do not appear to use other formats much more simple.

PD. Annex to the code, which essentially remains the same
, like formato.xls file.

<?php
   require ("PHPExcel.php");
   require ("PHPExcel/Writer/Excel5.php");
   require ("PHPExcel/IOFactory.php");
   
   $objReader = PHPExcel_IOFactory::createReader('Excel5');
    
   $objPHPExcel = $objReader->load("formato.xls");
   $objWorksheet = $objPHPExcel->getActiveSheet(0); //humedad
    
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,7, 'prueba');
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,19, '3');
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,19, '25');
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,19, '33');
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,19, '28');
   
   $objWorksheet = $objPHPExcel->getActiveSheet(1); // gr gruesa
    
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10,17, '12');
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10,18, '9');
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10,19, '7');

   $objWorksheet = $objPHPExcel->getActiveSheet(3); // proctor
    
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,16, '3');
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10,34, '300');
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10,137, '26');
   
   $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
   $objWriter->save("asignacion-saved.xls");

?>


Developer
Feb 21, 2010 at 2:17 AM
guarus wrote:

koyama thanks, actually solve the problem of infinite loop in question, but note that when assigning values to cells, either intra-or sheet or several, you lose the format (style) original, or at least my case, apart from not write in the rest of the cells indicated.
The loss of the format is bug?, Not writing above is bug? coupled to it gives me a circular reference message, if this is not associated with the above, but I do not appear to use other formats much more simple.

Bug identified.

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

Try with latest source code:

http://phpexcel.codeplex.com/SourceControl/list/changesets

May 14, 2010 at 4:35 PM

greetings koyama, with the correction above if you save now and do not lose the format (style) original, locally. Now courre the following:

Going up the program on the server and run it gives me the following error:

Catchable fatal error: Object of class PHPExcel_RichText_TextElement Could not be converted to string in
C: \ wamp \ www \ Kriel \ PHPExcel \ Calculation.php on line 2374

But when run locally, it gives no error and performs perfectly in excel writing. The only difference I see is that on the local computer I have Windows XP SP2 and Office 2003, while the server is Windows Server 2008 SP 1 and Office 2007. Would it have anything to do with the version of operating system and / or office? or some additional configuration at the program or library

PD. I am using the version 48790 of the library, which is the last to date. Then use the code in question, the excel file remains the same too (formato.xls).

 

<?php 
   require ("PHPExcel.php");
   require ("PHPExcel/Writer/Excel5.php");
   require ("PHPExcel/IOFactory.php");
  
   $objReader = PHPExcel_IOFactory::createReader('Excel5');
   
   $objPHPExcel = $objReader->load("formato.xls");
   $objWorksheet = $objPHPExcel->setActiveSheetIndex(0); //humedad
  
   $objPHPExcel->getActiveSheet()->setCellValue("D7", "prueba");
   $objPHPExcel->getActiveSheet()->setCellValue("D8", "escritura");
   $objPHPExcel->getActiveSheet()->setCellValue("D9", "casa");
  
   $objWorksheet = $objPHPExcel->setActiveSheetIndex(1); // gr gruesa
  
   $objPHPExcel->getActiveSheet()->setCellValue("K17", "12");
   $objPHPExcel->getActiveSheet()->setCellValue("K18", "9");
   $objPHPExcel->getActiveSheet()->setCellValue("K19", "7");   

   $objWorksheet = $objPHPExcel->setActiveSheetIndex(3); // proctor
   
   $objPHPExcel->getActiveSheet()->setCellValue("C16", "3");
   $objPHPExcel->getActiveSheet()->setCellValue("K34", "300");
   $objPHPExcel->getActiveSheet()->setCellValue("K37", "26");   
  
   $objWorksheet = $objPHPExcel->setActiveSheetIndex(4); // peso unitario
  
   $objPHPExcel->getActiveSheet()->setCellValue("F15", "3");
   $objPHPExcel->getActiveSheet()->setCellValue("F16", "300");
   $objPHPExcel->getActiveSheet()->setCellValue("F17", "26");
   $objPHPExcel->getActiveSheet()->setCellValue("F18", "99");
  
   $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
   $objWriter->save("asignacion-saved.xls");  

?>

May 20, 2010 at 1:16 PM
koyama good day, sorry to bother you again, but you could inquire about the last error (May 14 at 4:35 PM) you comment?
May 25, 2010 at 4:43 AM

Problem solved....

The problem was in the template file.

I removed a complex formula from the template and voila. It works.

 

 

May 25, 2010 at 1:39 PM

pkhera thanks. Now, could you tell me where in the code and / or the library must correct the error?

Jun 5, 2010 at 7:15 PM

pkhera discomfort apology, but still have not told me what I have to modify or remove in the library to work and not give me the error that post on May 14 at 4:35 PM.

thanks in advance

 

Jul 26, 2010 at 2:04 PM
Greetings to all developers and users of the library PHPExcel. Some time ago I had post an error when up and run an application on the server, since Local mode works without problems. This error is associated with the number of discussions 74 737 (Error When recording in a format with formulas, styles and graphics), specifically on May 14 at 24:05. The detail is that I was told that the problem had already been resolved (pkhera user), however most did not see the solution, and even lowering the latest version of the library, thinking I had the fix, the problem persists. If someone could tell me corrections that I do to solve the problem really appreciate it because I had to link multiple excel sheets, being able to do directly with the library, brought me several problems with data and formulas that there is contemplating , which I address each time they use the program. On the other hand, do not think it necessary to say that this will also benefit me, the users of this library, coupled with the fact that this entails feedback for everyone.