PHPExcel - Incorrect data population during excel report generation

Question

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', 'application/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

@Belac: Had done that.The loop still gets executed 579 times. — Basher51, Dec 16 '14 at 17:05
Odd. Are you sure the query is excluding rows based on date? You might to look at http://stackoverflow.com/questions/11553183/select-entries-between-dates-in-doctrine-2 — Belac, Dec 16 '14 at 17:28
@Belac:The data is filtered properly and I got the filtered array,but the array changes when it gets exported to phpexcel — Basher51, Dec 16 '14 at 17:42
@Belac : Sometimes its the silly things that makes us pull our hair.Found the bug.The phpExcel was working correctly but the forms for the date filter in the website was sending null values hence the filter was not working properly,so therefore the report was getting generated for all the rows in the table..arrgh ! — Basher51, Dec 17 '14 at 03:41
Deleting or answering your own question wouldnt be bad then. This thread still shows up in the "unanswered" list. — C4d, Jan 17 '15 at 14:33

score 1 · Answer 1 · answered Mar 16 '15 at 03:57

"The data is filtered properly and I got the filtered array,but the array changes when it gets exported to phpexcel –"

Don't assume it is properly filtered until you debug or var_dump it. Making assumptions will not help you or us, it is just impossible what you are saying. PS : This question has already been answered by the post owner.

score 1 · Answer 2 · edited Dec 13 '17 at 10:52

1

$phpExcelObject->getActiveSheet()->setCellValueExplicit('A'.$row_count,$row['Value'], PHPExcel_Cell_DataType::TYPE_STRING);

Try above code to wrong population row and also change your excel format into .xlsx. You can do that using phpExcel.

edited Dec 13 '17 at 10:52

Andrii Pryimak

797
2
10
33

answered Dec 13 '17 at 10:32

Hasitha

129
1
11

score 1 · Answer 3 · answered Aug 20 '19 at 07:50

1

For you and those who are looking for Excel export implementation in PHP

PHPExcel is now deprecated (Official PHPExcel Github Repo and announcement)

You can use its best-suggested alternative - Php Spreadsheet (Phpspreadsheet documentation)

answered Aug 20 '19 at 07:50

Jhon Martin

21
1

PHPExcel - Incorrect data population during excel report generation

3 Answers3