How can I properly handle large negative numbers with bitshifting?

0

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.

php
excel
bit-manipulation
asked on Stack Overflow Mar 2, 2020 by Beatscribe

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0