Formula not retained after reading in excel file

Topics: Developer Forum
Aug 18, 2010 at 5:36 PM
Edited Aug 20, 2010 at 1:38 PM

Hi.

I am currently trying to read in a .xls file, change a few values, then output the file as another .xls.

The spreadsheet has 2 worksheets of concern:

  1. datasheet - contains around 12,000 rows of data generated from a mySQL database
  2. Type Size Summ - contains tables that analyse the data in the first worksheet

The process is carried out in three stages:

  1. Read in the file (Tables1&2(2009format).xls)
  2. Edit the data in the first worksheet
  3. Output the file to the computer

Steps 1 and 2 work perfectly but the formulas contained in the second worksheet are removed from the cells and their last calculated value is stored in the cells instead.

Here is the code I am using:

---

 

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

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

$objPHPExcel = PHPExcel_IOFactory::load("Tables1&2(2009format).xls");
$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();

// Database stuff

$result = mysql_query("SELECT colIncome,colType,jobLvl,FTESalary,region FROM college,employee WHERE college.colCode=employee.colCode");
$numRows = mysql_num_rows($result);
$lastRow = $numRows + 1;
$i=2;
while($row = mysql_fetch_assoc($result)) {
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $row['colIncome']);
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $row['colType']);
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $row['jobLvl']);
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $row['FTESalary']);
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $row['region']);
	$i++;
}

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="table1and2 - '.$date.'.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

---

The original excel file still has the formulas intact when I download it from the server without processing it using phpExcel.

 

Any help would be greatly appreciated.

 

Cheers.

Dan.

 

 

Aug 19, 2010 at 11:58 AM
Edited Aug 20, 2010 at 11:10 AM

Hi, I am in a very similar situation, in fact i was to post the problem in this forum.

I have my template like you, make some changes in some values, and a worksheet that shoud not be modified by PHPexcel loses formulas.

In my case, all the formulas in that worksheet are not lost, it seems that only formulas with values that are changed in others worksheets disappears. It is not relacionated with excel formats (happens reading and writting with excel5 and excel2007).

 

EDIT: it happens with the last version of  in windows with php 5.2.6 and linux with php 5.2.11. It seems to be a problem with de loader. The code

$objPHPExcel = PHPExcel_IOFactory::load($tempExcel);
$objPHPExcel->setActiveSheetIndex(1);   		
$aux = $objPHPExcel->getActiveSheet()->getCell('B5')->getValue();
echo $aux;
   	   		
shows a number, and with this particular excel file should be a formula.

 

Cheers.

Cristóbal.

Coordinator
Aug 20, 2010 at 12:59 PM
Edited Aug 20, 2010 at 1:00 PM

Can you perhaps upload a sample file that is losing formulas when read into PHPExcel, to give me some idea of the formulas that are cuasing problems? This is not normal behaviour, so I really need a specific test that replicates the error to help identify its cause.

 

Alternatively, could you try replicating the problem using the latest SVN code, because there have been a number of fixes implemented there related to formulae that reference cells on other worksheets.

Aug 20, 2010 at 1:35 PM

Hi Mark,

Thanks for your reply.

Please find attached a copy of the file before and after it is read.

Do not be alarmed by the lack of data in the datasheet in the second file, these values are drawn from a database and will be more complete at a later stage.

I am using phpExcel version 1.7.3c and php version 5.2.4-2ubuntu5.10.

In the mean time please would you be more specific as to how I may go about getting hold of and installing the latest SVN code.

danieljameswood_before.xls

danieljameswood_after.xls

Thanks again.

Dan.

Coordinator
Aug 20, 2010 at 2:02 PM

@daniel

Looking at your samples, the formulas in your workbook are Excel Array formulas, e.g.

{=COUNT(IF(datasheet!$B$2:$B$11188>0,IF(datasheet!$C$2:$C$11188=1,IF(datasheet!$A$2:$A$11188>0, IF(datasheet!$A$2:$A$11188<5,datasheet!$D$2:$D$11188)))))}

with the leading/trailing wavy brackets

 

Unfortunately, this is a feature of Excel that we have only just started to implement for Excel2007, and is not yet available in the calculation engine, or in Excel5.

 

Aug 20, 2010 at 2:05 PM

Hi Mark,

Thanks for your reply.

Any idea when this feature will become available for Excel2007?

Cheers.

Dan.

Aug 20, 2010 at 5:20 PM

Hi Mark,

Just one follow up question.

One possible way around this problem would be to only edit the sheet with the data in and leave the sheet with the formulas in untouched. Is it possible to tell phpExcel to 'skip-over' one sheet as it were or is the whole workbook processed together?

Cheers.

Dan.

Coordinator
Aug 20, 2010 at 11:17 PM

PHPExcel doesn't edit workbook files: it (optionally) maps data from various formats into a PHPExcel object, allows retrieval/manipulation of that object, and provides writers that map the structure of that PHPExcel object into different file formats.

The PHPExcel object neither knows nor cares how it was created and populated, whether by loading from file via a Reader object, or via instantiating a new PHPExcel() and manually setting the cell values. When reading from file, the reader is simply instantiating a PHPExcel object, and calling exactly the same methods to populate that data as you would if you were creating from new. Any information read from a file that can be mapped to the attributes of the PHPExcel object is stored, and then the file is forgotten.

Likewise, the Writer object is unaware whether the PHPExcel object that it is asked to write has been read from a file, or created by hand. It isn't a case of editing, simply of creating an object (perhaps by reading or instantiation in user code) and then saving that object as a file formatted according to a particular standard. Only attributes stored in the PHPExcel object can be written, because that is the only data accessible to the writer.

To restructure the code in such a way that a file could be edited "in situ" would be a significant undertaking, and that's the only way that I can envisage maintaining everything from the structure of a loaded file (other than gradually adding support for those features against the PHPExcel object); especially awkward as we'd have to provide layers allowing all the different supported file formats to be edited "in situ", while keeping it transparent to you as a developer so that the appropriate methods such as setFormat() mapped onto whichever. It would also reduce the flexibility of PHPExcel to read data from files of one format, and write to another, without significant changes.

 

As light entertainment, I'm currently re-reading the published specifications for xls and xlsx, particularly with regard to charting and pivot tables; but I'm also paying particular attention to functions from the Analysis Toolkit in xls files, and Array formulae. The next release (1.7.4 - primarily bugfixes) is due sometime next week (delayed from the beginning of the month while I sorted out problems with cloning worksheets while using cell cacheing, and some issues with column and row ranges in formulae). Once that's available, my main focus will be adding the charting (and possibly pivot table) features for 1.7.5 (initial target date will be the end of October); and then (hopefully) Analysis Toolkit functions in xls (if I can fathom how they're stored), Array Formulae, and some additional statistical and financial functions in the calculation engine will be targetted for the 1.7.6 release (initial target date will be December).

Aug 24, 2010 at 11:06 AM

Hi again,

I could not fix my problem using the lastests svn codes. This is the file, it is quite simple, perhaps it is my mistake.

http://www.megaupload.com/?d=JV1BRVVG

This file is made by hand, my code should change some cells in every execution. For test, this is the code i'm running:

$objPHPExcel = PHPExcel_IOFactory::load($tempExcel);
   		     		
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save("aux.xls");		

$tempExcel is that file. The first time i run that, the formulas does not go, despite of the colours of the tabs are missing, so something is changed.

If you run again that code, and the input file is that "aux.xls", the formulas in the first worksheet are gone (formulas from B to M and 8 to 38). Don't look a lot in the formulas, all are basically the same formula referencing different cells.

Thank you for your attention.

 

Cristóbal

 

Coordinator
Aug 24, 2010 at 9:16 PM

@CygnusSaint

I've replicated your problem with trying to read into PHPExcel a workbook that was previously written by PHPExcel. The problem arises with the tRef3d references (references to cells in a different sheet). The Excel5 Writer is writing these with a token of 0x7A; but the Excel5 Reader only registers tokens 0x3A and 0x5A for tRef3d. 0x3A, 0x5A and 0x7A are synonymous, they all work the same way, which is why the written file would open correctly in Excel; but the PHPExcel Excel5 reader wasn't recognising 0x7A and falling back to its default behaviour of setting the cell to the previously calculated value rather than the formula that it couldn't correctly parse. I've modified the reader, and double checked all the other tokens that have such synonyms (adding  a number of other synonyms for 0x20 (tNum), 0x63 (tName), 0x66 (tMemArea), 0x27 and 0x67 (tMemErr), 0x69 (tMemFunc) and 0x7B (tArea3d). This fix has been checked into SVN, and will be included as a part of (Thursday's) 1.7.4 release.

I'm still investigating the issues with colour.

Aug 25, 2010 at 10:02 AM
Edited Aug 25, 2010 at 10:02 AM

Hello Mark,

Thank you so much for your attention. My headaches are gone :)

Aug 27, 2010 at 7:25 AM

Hi Mark,

I have tries this issue with PHPExcel-1.7.4 but I could not read cell of another sheet. It is still giving following fatal error.

Fatal error: Call to a member function cellExists() on a non-object in D:\web\phpexcel-1.7.4\Classes\PHPExcel\Calculation.php on line 3137

Here is sample file.

http://hotfile.com/dl/64996053/8992a5d/sample_file.xlsx.html

Please see D2 & D3 cells, these are using reference of 2nd sheet's cell.

Awaiting you response.

Thanks,

Muhammad Sohail

Coordinator
Aug 27, 2010 at 8:48 AM
Edited Aug 27, 2010 at 8:50 AM
sohailisonline wrote:

I have tries this issue with PHPExcel-1.7.4 but I could not read cell of another sheet. It is still giving following fatal error.

Fatal error: Call to a member function cellExists() on a non-object in D:\web\phpexcel-1.7.4\Classes\PHPExcel\Calculation.php on line 3137

Here is sample file.

http://hotfile.com/dl/64996053/8992a5d/sample_file.xlsx.html

Please see D2 & D3 cells, these are using reference of 2nd sheet's cell.

 I hate to say this Muhammad, but the sample file works perfectly for me.

Using the following code: 

error_reporting(E_ALL);
set_time_limit(0);
date_default_timezone_set('Europe/London');
$inputFileName = 'sample_file.xlsx';
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);

$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();
PHPExcel_Calculation::getInstance()->writeDebugLog = true;

function testFormula($sheet,$cell) {
    $formulaValue = $sheet->getCell($cell)->getValue();
    echo '<b>'.$cell.' Value is </b>'.$formulaValue."<br />\n";
    $expectedValue = $sheet->getCell($cell)->getOldCalculatedValue();
    echo '<b>'.$cell.' Expected Value is </b>'.$expectedValue."<br />\n";
    try {
       $cellValue = $sheet->getCell($cell)->getCalculatedValue();
       echo '<b>'.$cell.' Calculated Value is </b>'.$cellValue."<br />\n";
       echo '<h3>Evaluation Log:</h3><pre>';
       print_r(PHPExcel_Calculation::getInstance()->debugLog);
       echo '</pre>';
    } catch (Exception $e) {
       echo "CALCULATION ENGINE ERROR: ".$e->getMessage()."<br />\n";
    }
}
testFormula($sheet,'D2');
testFormula($sheet,'D3');

gives me a result of:

D2 Value is =C2*(1+'FSC table'!A2)
D2 Expected Value is 3998.4000000000005
D2 Calculated Value is 3998.400000000001

Evaluation Log:

Array
(
    [0] => Result table!D2 -> Evaluating Cell C2 in current worksheet
    [1] => Result table!D2 -> Evaluation Result for cell Result table!C2 is a floating point number with a value of 3570
    [2] => Result table!D2 -> Evaluating Cell A2 in worksheet FSC table
    [3] => Result table!D2 -> Evaluation Result for cell FSC table!A2 in worksheet FSC table is a floating point number with a value of 0.12
    [4] => Result table!D2 -> Evaluating 1 + 0.12
    [5] => Result table!D2 -> Evaluation Result is a floating point number with a value of 1.12
    [6] => Result table!D2 -> Evaluating 3570 * 1.12
    [7] => Result table!D2 -> Evaluation Result is a floating point number with a value of 3998.400000000001
)

D3 Value is =C3*(1+'FSC table'!A2)
D3 Expected Value is 2349.7600000000002
D3 Calculated Value is 2349.76

Evaluation Log:

Array
(
    [0] => Result table!D3 -> Evaluating Cell C3 in current worksheet
    [1] => Result table!D3 -> Evaluation Result for cell Result table!C3 is a floating point number with a value of 2098
    [2] => Result table!D3 -> Evaluating Cell A2 in worksheet FSC table
    [3] => Result table!D3 -> Evaluation Result for cell FSC table!A2 in worksheet FSC table is a floating point number with a value of 0.12
    [4] => Result table!D3 -> Evaluating 1 + 0.12
    [5] => Result table!D3 -> Evaluation Result is a floating point number with a value of 1.12
    [6] => Result table!D3 -> Evaluating 2098 * 1.12
    [7] => Result table!D3 -> Evaluation Result is a floating point number with a value of 2349.76
)
Which is exactly what I'd expect.
Apr 21, 2014 at 12:40 PM
Hi Mark,
I have also the similar problem.
when I edit the previously existed excel2007 sheet, the validations are vanished. so help me in accessing the excel sheet in such a manner that when I edit it only the value of cell is update, not the validations are vanished.