2

I have a problem in which one of the read numbers is correct because in excel worksheet it's just a number, but the second one returns a formula. I have tried several methods suggested on other topics of forums but neither of them worked correctly. What else can I do to solve this problem? I just need to read couple of cells from my .xlsx excel file.

Code:

 include "PHPExcel-1.8\Classes\PHPExcel\IOFactory.php";
$inputFileName = 'C:\Users\tfd054it0154\Desktop\dane na spotkanie.xlsx';
 $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

//  Read Excel workbook
 try {
     $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
     $objPHPExcel = $objReader->load($inputFileName);
 } catch(Exception $e) {
     die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
 }
 $value1 = $objPHPExcel->getActiveSheet()->getCell('B8')->getValue();
 echo $value1;
 $value2 = $objPHPExcel->getActiveSheet()->getCell('C8')->getValue();
 echo $value2;
kpi townhall
  • 33
  • 1
  • 6
  • Possible duplicate of [Reading an Excel file in PHP](https://stackoverflow.com/questions/563670/reading-an-excel-file-in-php) – endo.anaconda Sep 27 '17 at 11:25

1 Answers1

0

If the cell contains a formula, then getValue() returns the formula itself, getCalculatedValue() calculates and returns the result of that formula.

As described in the PHPExcel documentation

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Yes, I used this before but got an error or the wrong number, can't remember that now and also I can't reproduce the error to show you. – kpi townhall Sep 27 '17 at 11:30
  • Well explaining the real problem helps.... but without knowing what the error is, it's almost impossible to help; and without knowing the formula and the data that is used in that formula, then it's impossible too work out why you're getting a wrong number – Mark Baker Sep 27 '17 at 11:33
  • I managed to reproduce the error The "30" number is correctly read with `getValue()` and the error then comes from the `getCalculatedValue()` Img: [link]https://imgur.com/a/uGHKY – kpi townhall Sep 27 '17 at 11:35
  • Well it looks like there's something odd in this mysterious formula that PHPExcel doesn't understand – Mark Baker Sep 27 '17 at 11:37
  • That's the formula: `=HLOOKUP($B$3-$B$2;'localization_path'!$4:$159;22;0) – kpi townhall Sep 27 '17 at 11:59
  • can this problem be because of the name of the formula written in other language than English in excel? – kpi townhall Sep 27 '17 at 12:32
  • I also found that when I type `getValue()` the formula is different than in Excel, the localization path is different, how can I fix that? – kpi townhall Sep 27 '17 at 12:36
  • One problem with the formula is the use of `;` as an argument separator for function arguments.... PHPExcel needs `,`, which should be what the excel file uses internally.... is that the formula returned by the call to `getValue()`? Or the formula that you see in MS Excel (which is localized) – Mark Baker Sep 27 '17 at 12:42
  • `=HLOOKUP($B$3-$B$2,[3]DAILY_OUTPUT!$4:$159,22,0)` <- this is with the use of `getValue()` and this is in Excel file( I placed here the full path) `=HLOOKUP($B$3-$B$2;'\\mycomp\pa\Plan\Planning\service\Output\OUTPUT 2014\[Output VS 2017_ACTUAL.xlsb]DAILY_OUTPUT'!$4:$159;22;0)` – kpi townhall Sep 27 '17 at 12:50
  • That's a reference to an external Excel file... PHPExcel cannot reasonably handle references to external files that may (or may not exist) – Mark Baker Sep 27 '17 at 13:16
  • 1
    If you have references like that in formulae, you can try using `getOldCalculatedValue()` but that isn't guaranteed to be accurate – Mark Baker Sep 27 '17 at 13:17
  • Well it works but not exactly correct. I copied my main excel file and with the use of a copy it shows good values, but on the original excel file the values are wrong? What happens? – kpi townhall Sep 27 '17 at 13:23