Read file value incorrect

[ytilotti]

Hi all,

I'm using PhpSpreadsheet to read a file with complex formula.
I don't understand why the file is read in preview but I have a different result when I open the file in Excel (not in preview).

Is there a maximum depth for VLOOKUP?

[oleibman]

No maximum depth as far as I know. Can you upload spreadsheet and/or code which demonstrates your problem?

[ytilotti]

I join the file cleaned of sensitive information: test.xlsx

With this code:

$reader = IOFactory::load('test.xlsx');

$line = 45;
$activesheet->setCellValue('A' . $line, 'Test1');
$activesheet->setCellValue('B' . $line, '100000');
$activesheet->setCellValue('C' . $line, '0.05');
++$line;
$activesheet->setCellValue('A' . $line, 'Test2');
$activesheet->setCellValue('B' . $line, '5000');
$activesheet->setCellValue('C' . $line, '0.09');
++$line;

$activesheet->setCellValue('B1', 'gc');

// Save the file
$readerSave = IOFactory::createWriter($reader, 'Xlsx');
$readerSave->save('test_tmp.xlsx');

$reader->setActiveSheetIndex(1)->getCell('L27')->getCalculatedValue();

I have 6976,91

But with this code:

$reader = IOFactory::load('test.xlsx');

$line = 45;
$activesheet->setCellValue('A' . $line, 'Test2');
$activesheet->setCellValue('B' . $line, '5000');
$activesheet->setCellValue('C' . $line, '0.09');
++$line;
$activesheet->setCellValue('A' . $line, 'Test1');
$activesheet->setCellValue('B' . $line, '100000');
$activesheet->setCellValue('C' . $line, '0.05');
++$line;

$activesheet->setCellValue('B1', 'gc');

// Save the file
$readerSave = IOFactory::createWriter($reader, 'Xlsx');
$readerSave->save('test_tmp.xlsx');

$reader->setActiveSheetIndex(1)->getCell('L27')->getCalculatedValue();

I have 6356,94. And if I open on Excel I have 6356,94 in preview but 6976,91 if open.

Crazy no? Don't understand why.

[oleibman]

You do not have any code which initializes $activeSheet. For now, I will assume you've done something like the following after IOFactory::load:

$activesheet = $reader->getActiveSheet();

Please advise if that is not correct.

[oleibman]

I am not sure what you mean by "in preview". Could you explain?

I am at least able to confirm that, for your second sample code, PhpSpreadsheet is calculating the value as 6356.94, but Excel shows it as 6976.91. So you definitely have a problem; I'm just not sure what it is right now. Sometimes turning off branch pruning helps, but not in this case. I will continue to research, but it could take a while - there is a lot of indirection involved in evaluating this formula.

BTW, do you know which value (6356.94 or 6976.91) is the one you expect?

[ytilotti]

I expect 6976.91.

I'm talking about protected mode exactly, not "in preview". This is this message (sorry in french):

If I activate the modification, the value is 6976.91.

PhpSpreadsheet read the file with the protected mode but I don't understand why in the protected mode the value is incorrect. I would challenge Excel with maximum depth on the VLOOKUP, but is there a way round the problem?

We have already reduce number of VLOOKUP because the case 1 was in error before. But I don't know if we can do anymore.

[ytilotti]

I simplify the VLOOKUP and I have a good result. For me the bug isn't in PhpSpreadsheet but on Excel.
The depth must be limited when modification activation is not active.

But is there a way to open the file in PhpSpreadsheet with modification mode activated?