Problems saving an existing file with format defined

Topics: Developer Forum, Project Management Forum, User Forum
Jul 12, 2009 at 3:15 PM

Greetings friends of the community, my problem is:

I have an excel file with a format, when opened for writing in, in some cells with specific values brought from a mysql database, as you make some calculations and charts, once I save the changes, when revising loses all formatting.

I wish I could help and explain how to solve this problem, because I need to present the information in the format handled by the client.

Developer
Jul 14, 2009 at 12:52 PM

Ugg... we may need some more information to get this solved.

For a start, can you try to do a plain read/write of the Excel template file you are using and see what output you get. I'm afraid your template contains column or row styles. These are not yet supported by PHPExcel which might explain why you are losing formatting.

 

Jul 14, 2009 at 5:09 PM

ok, thanks for your prompt response. And if as you say, I wish to save the file where the values have a format with styles, forms and validations. When trying to insert a value in a cell I get the following error message:

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

if you have any other suggestions in this regard I would appreciate that.

PD. I work at a record on file with no single format and this is the code with which I am doing the test:

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


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

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

   $objPHPExcel->getActiveSheet()->SetCellValue('A6', 'Hello');   
   $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);   
   $objWriter->save(str_replace('', '', "compresion.xls"));

?>


Developer
Jul 14, 2009 at 7:16 PM

As for the problem with the fatal error. This is unfortunately a known bug with the formula parser used by PHPExcel_Writer_Excel5. It does not handle absolute cell references in formulas like $A$1

You may vote for this work item:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=7895

Jul 16, 2009 at 3:13 PM

thanks again for your response Koyama. So in summary, I can not write the values you want in the cells of specific file format that handles the client?, And if so, there will be any other way to do this using phpexcel?

Developer
Jul 17, 2009 at 5:48 PM

>> So in summary, I can not write the values you want in the cells of specific file
>> format that handles the client?, And if so, there will be any other way to do this
>> using phpexcel?

You can try to work around the problem by inspecting the formulas in your Excel template compresion.xls, and see if you can change them to something Excel5 writer can handle, for example it may not hurt the formulas if you change $A$1 to A1. Excel5 writer can handle the latter, but not the first.