I am using this php library to read in an Excel document. For the most part it works fine, but if I enter a large negative number, one more than 4 digits long, the following bit of code no longer returns the right result. I'm a little unclear on how to handle the sign with bitwise operations to adjust the code. The problem resides in line 4 when the first If evaluates to true:
function _GetIEEE754($rknum) {
if (($rknum & 0x02) != 0) {
// this code seems to fail once you get below -9999 to 5 digit negative numbers
$value = $rknum >> 2;
} else {
//mmp
// I got my info on IEEE754 encoding from
// http://research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html
// The RK format calls for using only the most significant 30 bits of the
// 64 bit floating point value. The other 34 bits are assumed to be 0
// So, we use the upper 30 bits of $rknum as follows...
$sign = ($rknum & 0x80000000) >> 31;
$exp = ($rknum & 0x7ff00000) >> 20;
$mantissa = (0x100000 | ($rknum & 0x000ffffc));
$value = $mantissa / pow( 2 , (20- ($exp - 1023)));
if ($sign) {
$value = -1 * $value;
}
//end of changes by mmp
}
if (($rknum & 0x01) != 0) {
$value /= 100;
}
return $value;
}
How can I modify the bitshifting to properly take into account the sign for large numbers? Here are test cases of how it should work:
Working test case:
Value in Excel: -1454.22 (read in as type mulrk “3238117489”)
_GetIEEE754(3238117489)
Returns: -1454.22
Failing test case:
Value in Excel: -12345.03 (read in as type mulrk “4290029287”)
_GetIEEE754(4290029287)
Returns: 10725073.21
Should return: -12345.03
I am aware that there are other newer libraries for importing Excel files, but this one is very integrated into a legacy project, and I'm hoping to avoid such refactoring if at all possible.
User contributions licensed under CC BY-SA 3.0