Ignoring calculated values when reading in Excel file with PHPExcel

You can run the calculation engine in debug mode to get a lot more detail about what it is doing to help diagnose such problems:

$cell = 'C3';
//  enable debugging
PHPExcel_Calculation::getInstance()->writeDebugLog = true;

$sheet = $objPHPExcel->getActiveSheet();
$formulaValue = $sheet->getCell($cell)->getValue();
echo '<b>'.$cell.' Value is </b>'.$formulaValue."<br />\n";

$calculate = false;
try {
    $tokens = PHPExcel_Calculation::getInstance()->parseFormula($formulaValue,$sheet->getCell($cell));
    echo '<b>Parser Stack :-</b><pre>';
    print_r($tokens);
    echo '</pre>';
    $calculate = true;
} catch (Exception $e) {
    echo "PARSER ERROR: ".$e->getMessage()."<br />\n";

    echo '<b>Parser Stack :-</b><pre>';
    print_r($tokens);
    echo '</pre>';
}

if ($calculate) {
    //  calculate
    try {
        $cellValue = $sheet->getCell($cell)->getCalculatedValue();
    } catch (Exception $e) {
        echo "CALCULATION ENGINE ERROR: ".$e->getMessage()."<br />\n";

        echo '<h3>Evaluation Log:</h3><pre>';
        print_r(PHPExcel_Calculation::getInstance()->debugLog);
        echo '</pre>';
    }
}

EDIT

No guarantees, because I'll need to test it a bit more, but modify line 1171 in the Classes/PHPExcel/Calculation/MathTrig.php file

This line currently reads

$returnValue += $sumArgs[$key];

change it to

$returnValue += $sumArgs[$key % count($sumArgs)];

It's assuming that the number of cells in the criteria range (Sheet3!A1:H530 in your case) matches the number of cells in the summed range (Sheet3!H1:H530) which clearly isn't the case when the criteria references 8 columns and 530 rows while the summed range only references 530 rows in a single column.

EDIT #2

While above Edit suggestion will fix the error, it'll return the wrong result. If you specify A1:H530 as your criteria, but only H1:H530 as your summed values in MS Excel, it only tests the first column (to match the potential number of summed results) and ignores columns B-H in the criteria.

A quick fix might be to modify lines 1161 which read:

if (empty($sumArgs)) {
    $sumArgs = $aArgs;
}

to

if (empty($sumArgs)) {
    $sumArgs = $aArgs;
} elseif (count($aArgs) > count($sumArgs)) {
    $aArgs = array_slice($aArgs,0,count($sumArgs));
}

and revert line 1171 (now 1173) back to

$returnValue += $sumArgs[$key];

I still need to check if flattenArray orders the cells correctly, but that's going to be a closer fix

EDIT #3

It'll take longer to fix PHPExcel's calc engine to handle mismatched criteria/summed arrays (my fix above won't work because PHPExcel flattens by row, while this need sto be flattened by column) - but as a temporary fix change your formula to use just one column in the criteria range

A simple solution is to create your own subclass of ViewPager that has a private boolean flag, isPagingEnabled. Then override the onTouchEvent and onInterceptTouchEvent methods. If isPagingEnabled equals true invoke the super method, otherwise return.

public class CustomViewPager extends ViewPager {

    private boolean isPagingEnabled = true;

    public CustomViewPager(Context context) {
        super(context);
    }

    public CustomViewPager(Context context, AttributeSet attrs) {
        super(context, attrs);
    }

    @Override
    public boolean onTouchEvent(MotionEvent event) {
        return this.isPagingEnabled && super.onTouchEvent(event);
    }

    @Override
    public boolean onInterceptTouchEvent(MotionEvent event) {
        return this.isPagingEnabled && super.onInterceptTouchEvent(event);
    }

    public void setPagingEnabled(boolean b) {
        this.isPagingEnabled = b;
    }
}

Then in your Layout.XML file replace any <com.android.support.V4.ViewPager> tags with <com.yourpackage.CustomViewPager> tags.

This code was adapted from this blog post.