How to read only certain rows and column and determine if it is a date

Topics: Developer Forum, Project Management Forum, User Forum
Jun 26, 2012 at 3:34 AM

I am trying to read only certain rows and columns.  But when I loop through the worksheet, it is still pulling all the columns and rows.  And how can I check if it is a date?  I tried the PHPExcel_Shared_Date and it is not working.  What am I doing wrong??  Please help.

$inputFileName = "../files/sampleSheet.xlsx";
$sheetname = 'TestSheet1';

    //file type
    $inputFileType = 'Excel2007';
   
    class MyReadFilter implements PHPExcel_Reader_IReadFilter
    {
        public function readCell($column, $row, $worksheetName = '') {
            // Read row 8 to 12 and column T and U only
            if ($row >= 8 && $row <= 12) {
               if (in_array($column,range('T','U'))) {
                 return true;
                }
            }

            return false;
        }
    }
   
    $filterSubset = new MyReadFilter();
   
    /**  Create a new Reader of the type defined in $inputFileType  **/
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
   

    /**  Advise the Reader of which WorkSheets we want to load  **/
    $objReader->setLoadSheetsOnly($sheetname);
   
    $objReader->setReadFilter($filterSubset);
           
    /** Load $inputFileName to a PHPExcel Object  **/
    $objPHPExcel = $objReader->load($inputFileName);

   
    $objWorksheet = $objPHPExcel->setActiveSheetIndexByName($sheetname);
   
   
    $i=0;
    foreach ($objWorksheet->getRowIterator() as $row)
    {
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);
       
       
        if($i <= 25)
        {
            foreach ($cellIterator as $cell)
            {       
               
                $cellValue = trim($cell->getValue());
               
                print "<p>$cellValue</p>";

       $dateValue = PHPExcel_Shared_Date::ExcelToPHP($cellValue);
       print "date is ".date('d-M-Y',$dateValue);


            }
        }
        else
        {
            die("askjfksajfkfskajkfs");
        }
        $i++;
    } 

Coordinator
Jun 26, 2012 at 7:43 AM

The iterators start at row 1 and at column A by default

To start a row iterator at row 10

foreach ($objWorksheet->getRowIterator(10) as $row)

The cell iterator doesn't support a start column, so you'll need to keep track of this yourself

 

$cellIterator->setIterateOnlyExistingCells(false);


Tells the column iterator to return cells that don't exist, which includes columns A through S

 

Does a cell contain an Excel date/time stamp?

if(PHPExcel_Shared_Date::isDateTime($cell)) {
    // value is an Excel date/time stamp
} else {
    // value is not an Excel date/time stamp
}

 

Jun 26, 2012 at 3:37 PM

Mark -- I want to thank you for such quick response.  THANK YOU!!  I am newbie to using phpExcel in terms of reading spreadsheet. And your solution works. However, I have a question.  I can manipulate the date to return the correct date via php, but when I perform the date conversion, it is off by one day. 

For example, the date should be 9/30/2008 ... it is returning as 9/29/2008.  The original excel date is 39721.  Do you know what I might be doing wrong?

Again, thank you for your help.