Entire data present in the Excel even after filtering

Topics: Developer Forum, User Forum
Dec 16, 2014 at 9:58 AM
Hi,


I am working on a project in Symfony 2.1.PHPExcel version is 1.8.0. I use Doctrine to retrieve the data from Db and filter if required.
$em = $this->getDoctrine()->getManager();
                    $guardquery = $em->createQueryBuilder()
                            ->select('g.attendancePopupTime', 'd.atmName', 'd.region', 'd.zone', 'd.state')
                            ->from('ATMMonitorAPIBundle:GuardMonitor', 'g')
                            ->innerJoin('ATMMonitorAPIBundle:DeviceAtmInfo', 'd', Join::WITH, 'd.deviceId = g.deviceId');
                  
                    if ($userZones[0]['userZones'] != '0') {
                        $guardquery->innerJoin('ATMMonitorAPIBundle:RegisteredDevices', 'r', Join::WITH, 'r.deviceId = g.deviceId')
                                ->where('r.deviceZone IN (:devicezone)')
                                ->setParameter('devicezone', $zone_array);
                    }

                    if (isset($dateLow)) {
                        $guardquery->andWhere('g.attendancePopupTime BETWEEN :date_low and :date_high')
                                ->setParameter('date_low', $dateLow)
                                ->setParameter('date_high', $dateHigh);
                    }

                    $finalAttendanceQuery = $guardquery->getQuery();
                    $attendanceResult = $finalAttendanceQuery->getArrayResult();
This is my query and by giving variables as 2014-12-1 as $dateLow and 2014-12-8 as $dateHigh, the query returns 122 rows. There are 579 rows in the database. The data returned after filtering is proper and I am able insert it into Excel using the following code.
$phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();
                      $phpExcelObject->getProperties()->setCreator("")
                      ->setLastModifiedBy("Administrator")
                      ->setTitle("ATTENDANCE DETAILS XLSX")
                      ->setSubject("ATTENDANCE DETAILS  XLSX")
                      ->setDescription("EXCEL document for Attendance Details");
                      $phpExcelObject->setActiveSheetIndex(0);
                      $phpExcelObject->getActiveSheet()->setTitle('GUARD_ATTENDANCE - DETAILS');
                      $phpExcelObject->getActiveSheet()
                      ->SetCellValue('A3', "STATE")
                      ->SetCellValue('B3', "ZONE")
                      ->SetCellValue('C3', "REGION")
                     ->SetCellValue('D3', "DATE")
                    
                     ->SetCellValue('A1', "GUARD ATTENDANCE RECORDS");
              $count = count($attendanceResult);
               $rowCount = 4;

                      for ($i = 0; $i < $count; $i++) {
 $phpExcelObject->getActiveSheet()->SetCellValue('A' . $rowCount, $attendanceResult[$i]['state']);
 $phpExcelObject->getActiveSheet()->SetCellValue('B' . $rowCount, $attendanceResult[$i]['zone']);
 $phpExcelObject->getActiveSheet()->SetCellValue('C' . $rowCount, $attendanceResult[$i]['region']);
 if ($attendanceResult[$i]['attendancePopupTime'] instanceof \DateTime) {
       $attendanceDate = $attendanceResult[$i]['attendancePopupTime']->format('d-m-Y');
                            }
$phpExcelObject->getActiveSheet()->SetCellValue('D' . $rowCount, $punchTime);                 
$phpExcelObject->getActiveSheet()->SetCellValue('E' . $rowCount, count($attendanceResult));
$rowCount++
}

$writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
$response = $this->get('phpexcel')->createStreamedResponse($writer); 

$response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
$response->headers->set('Content-Disposition', 'attachment;filename=AttendanceDetails.xls'); 

$response->headers->set('Pragma', 'public');
$response->headers->set('Cache-Control', 'maxage=1');
return $response;
The variable $count has the value 122 before entering the for loop. In the excel generated there are 579 rows(entire data available in DB ) of data instead of 122 rows obtained after filtration. The column E of the excel also shows the values 579 instead of 122. The for loop also gets executed 579 times instead of 122 times. Some how, the array $attendanceResult changes while inserting data in phpExcel.

I tried saving contents of $attendanceResult into another array and using that array to insert data into the excel. The same issue exists there too.
Please help as I couldn't find anything wrong with the code.

Thanks in Advance
Dec 16, 2014 at 5:08 PM
Solved the problem. The issue had nothing to do with phpexcel. Sorry for the trouble

Thanks a lot