Returning wrong values

Topics: Developer Forum
May 27, 2013 at 6:11 AM
Hi,
I have an excel sheet with so many formulae. I am using excel2007 for reading and writing the sheets. i get correct outputs after the calculations. but when i am fetching the the outputs to the html page it shows #VALUE!. ANybody helps me to solve this?

Thanks in advance..
Coordinator
May 27, 2013 at 9:53 AM
Check that all the functions used in these formulae are supported by PHPExcel. If you're writing to HTML, then PHPExcel needs to calculate all those formulae, and if it can't calculate a valid result you'll get a #VALUE.... but without knowing what your formulae contain, I can't do very much about it
May 28, 2013 at 4:43 AM
Edited May 28, 2013 at 6:17 AM
I used hlookup formula. Does it work with phpexcel?
Coordinator
May 28, 2013 at 6:33 AM
On 28/05/2013 05:43, soumyashreyas wrote:

From: soumyashreyas

I used hlookup formula. is it work with phpexcel?
Quoting from the documentation

> HLOOKUP CATEGORY_LOOKUP_AND_REFERENCE *** Not yet Implemented

-- 
Mark Baker
May 28, 2013 at 7:10 AM
Thanks Mark. Do you have any other alternative solution for this?
Coordinator
May 28, 2013 at 8:38 AM
Edited May 28, 2013 at 8:40 AM
Unfortunately, short of writing the code to implement HLOOKUP, I don't have any solution; though if you're loading and then using getCalculatedValue() to display the result without changing any of the underlying data, you can call getOldCalculatedValue() to return the result from the last time MS Excel itself recalculated a cell value. Note that as it is possible to disable autocalculation in MS Excel, this isn't guaranteed
May 28, 2013 at 11:17 AM
Edited May 28, 2013 at 11:27 AM
soumyashreyas wrote:
I used hlookup formula. Does it work with phpexcel?
Hi,

I just tried hlookup formula and had no problem in importing the result into mysql and into my html

I always use getCalculatedValue and so far not faced any problem importing cells with formula or linked cell values.

You can take a look at the image: HLOOKUP

Regards
Coordinator
May 28, 2013 at 11:29 AM
Edited May 28, 2013 at 11:30 AM
I'm impressed, clearly HLOOKUP is working for you even though it hasn't been coded and should simply return a string containing '#Not Yet Implemented'. I really don't know what to say. Are you running a modified version of PHPExcel? If so, it'd be wonderful if you could contribute the HLOOKUP code
May 28, 2013 at 11:51 AM
Edited May 28, 2013 at 11:55 AM
MarkBaker wrote:
I'm impressed, clearly HLOOKUP is working for you even though it hasn't been coded and should simply return a string containing '#Not Yet Implemented'. I really don't know what to say. Are you running a modified version of PHPExcel? If so, it'd be wonderful if you could contribute the HLOOKUP code
Hello Mark,

Honoured to receive your reply. I use the version 1.7.8, Released: Oct 12, 2012; I don't know about any modified version. When I was developing PHPExcel to import my excel data to mysql database, I simply followed your advices and LWol's guidance.

Just tested, even getFormattedValue is working without any issues.

I will post my entire code in few minutes.

Regards

NB: I am a total amateur programmer, yet to learn a lot.
May 28, 2013 at 12:48 PM
Edited May 28, 2013 at 12:50 PM
MarkBaker wrote:
I'm impressed, clearly HLOOKUP is working for you even though it hasn't been coded and should simply return a string containing '#Not Yet Implemented'. I really don't know what to say. Are you running a modified version of PHPExcel? If so, it'd be wonderful if you could contribute the HLOOKUP code
Hello Mark,

Sorry for the slight delay in replying, had to go down to water my plants for, they have been starving since morning.

Presenting here my PHPExcel code:
<?php
//In case you want the script to be executed at a predefined time interval, use this
 /*header('Refresh: 30'); */

$mysqli = new mysqli("localhost", "userid","pw","db"); 

if (mysqli_connect_errno()) {
    echo mysqli_connect_error();
    exit();
} 

require 'c:/server/www/classes/phpexcel.php';
require_once 'c:/server/www/classes/phpexcel/iofactory.php';

$inputFileName  = 'c:/server/users.xls';
$inputFileType  = 'Excel5'; 

$objReader      = PHPExcel_IOFactory::createReader("$inputFileType");
$objReader->setReadDataOnly(true);

$objPHPExcel    = $objReader->load("$inputFileName");
$objWorksheet   = $objPHPExcel->getActiveSheet();

$highestRow     = $objWorksheet->getHighestRow();
$highestColumn  = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
    echo '<tr>' . "\n";

for ($col = 0; $col <= $highestColumnIndex; ++$col) {
    echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue() . '</td>' . "\n"; 
/*  echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getFormattedValue() . '</td>' . "\n"; */

}
echo '</tr>' . "\n";    
}
echo '</table>' . "\n";

for($row = 2; $row <= $highestRow; ++$row) {
for($col = 0; $col < $highestColumnIndex; ++$col) {

    $rows[$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
/*  $rows[$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getFormattedValue(); */

}
// Prepared Statement
$stmt = $mysqli->prepare("INSERT INTO users (Username,Email,Gender,Country) VALUES (?,?,?,?)");

//Prepared Statement Bound
$stmt->bind_param('ssss', $Username, $Email, $Gender, $Country);

    $Username   ="$rows[0]";
    $Email      ="$rows[1]";
    $Gender     ="$rows[2]";
    $Country    ="$rows[3]";

//Prepared Statement Executed
$stmt->execute();
/*printf("%s Row Inserted.\n", $stmt->affected_rows); */
printf ("New Record id %d.\n", $mysqli->insert_id);

//Prepared Statement Closed
$stmt->close();

// If you don't want to use prepared statement, you can use this one
/* $mysqli->query("INSERT INTO users (Username,Email,Gender,Country) VALUES ('$rows[0]', '$rows[1]', '$rows[2]', '$rows[3]')"); */

}

$mysqli->close();
?>
Regards