getValue only working on A1

Topics: Developer Forum
May 9, 2011 at 1:06 AM

I have an xlsx spreadsheet (Excel 2007) with A1 = "Practice Header Id", B1 = "Adviser Id", C1 = "Agent Id" etc.

I am trying to check these exist and use the following coe:-

    require_once '../../includes/phpexcel/Classes/PHPExcel/IOFactory.php';
   
    $inputFileName = $fl; // $fl does contain the correct xlsx file
   
    //  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter 
    class MyReadFilter implements PHPExcel_Reader_IReadFilter
    {
        public function readCell($column, $row, $worksheetName = 'Client List') {
            //  Exclude row 1 and columns greater than Y
            //if ($row > 1) {
                if (in_array($column,range('A','AL'))) {
                    return true;
                }
            //}
            return false;
        }
    }
   
    //  Create an Instance of our Read Filter 
    $filterSubset = new MyReadFilter();
   
   
    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objReader->setReadDataOnly(true);   
    //  Tell the Reader that we want to use the Read Filter 
    $objReader->setReadFilter($filterSubset);
    //  Load only the rows and columns that match our filter to PHPExcel 
    try {
        // Load $inputFileName to a PHPExcel Object
        $objPHPExcel = $objReader->load($fl);
    } catch(Exception $e) {
        die('Error loading file: '.$e->getMessage());
    }
   
    // check headings are correct
    $ok = 'Y';
    $sheet = $objPHPExcel->getActiveSheet();
    $colA = $sheet->getCell('A1')->getValue();
    if (strtoupper($colA) != strtoupper('Practice Header Id')) {
        $ok = 'N';
    }
    echo ' 1 '.$colA.'  '.$ok;
   
    $colB = $sheet->getCell('B1')->getValue();
    if (strtoupper($colB) != strtoupper('Adviser Id')) {
        $ok = 'N';
    }
    echo ' 2 '.$colB.'  '.$ok;

    $colC = $sheet->getCell('C1')->getValue();
    if (strtoupper($colC) != strtoupper('Agent Id')) {
        $ok = 'N';
    }
    echo ' 3 '.$colC.'  '.$ok;

 

I get this returned:-

1 Practice Header Id Y 2 N 3 N

For some reason I do not understand it does not seem to be reading the value of cells B1 and C1.

 

Any ideas please.

Coordinator
May 9, 2011 at 6:32 AM

range('A','AL') isn't giving you what you expect....

When using non-numeric values with range, character incrementing doesn't apply in the same way as if you use $x = 'A', $x++; so you can't use range() to specify a span of columns from a single character reference to a two character reference. range('A','M') will work because it's a single character range; range('A','AL') won't work (and will only give you 'A'). You'll need to define your array of valid columns slightly differently.

if (in_array(PHPExcel_Cell::columnIndexFromString($column),
             range(PHPExcel_Cell::columnIndexFromString('A'),
                   PHPExcel_Cell::columnIndexFromString('AL')))) { 
May 9, 2011 at 7:15 AM

Thanks for the prompt reply. Unfortunately I must be missing something. Neither of the 2 bits of code for the class work:-

 

    //  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter 
    class MyReadFilter implements PHPExcel_Reader_IReadFilter
    {
        public function readCell($column, $row, $worksheetName = 'Client List') {
            if (in_array(PHPExcel_Cell::columnIndexFromString($column),
                 range(PHPExcel_Cell::columnIndexFromString('A'),
                 PHPExcel_Cell::columnIndexFromString('AL')))) {
                    return true;
                } else {
                    return false;
                }
        }
    }
This gives nothing

    //  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter 
    class MyReadFilter implements PHPExcel_Reader_IReadFilter
    {
        public function readCell($column, $row, $worksheetName = 'Client List') {
                if (in_array($column,range(1,38))) {
                    return true;
                } else {
                    return false;
                }
        }
    }

This gives

1 N 2 N 3 N

What have I got wrong please?