Information coming up as duplicate Columns phpexcel, mySql

Question

I am retrieving information from the database and exporting it to excel using PHPExcel. The retrieving of the information seems to work fine but Columns duplicate in the excel sheet. I use the following method to retrieve information:

public function monthlyKm($startDate, $endDate, $userid) { $query = "SELECT a.`travelDate`,MIN(a.`openning`) AS minimum, MAX(a.`closing`) AS maximum, MAX(a.`closing`)- MIN(a.`openning`) AS diff, b.`destination` FROM kilologs a INNER JOIN users u ON u.`userid` = a.`userid` INNER JOIN destination b ON a.`destid` = b.`destid` WHERE (a.`travelDate` BETWEEN CAST('".$startDate."' AS DATE) AND CAST('".$endDate."' AS DATE)) AND a.userid = $userid GROUP BY a.`travelDate`, a.`destid`"; $query_set = mysql_query($query) or die(mysql_error()); return $query_set; }

Information is supposed to display in the following way: enter image description here

But this what I get:

enter image description here

This is the code for generating the excelsheet:

$result_set = $kiloLog->monthlyKm( $startDate,$endDate,$userid); if(isset($_POST['download'])) { require_once 'src/PHPExcel.php'; try{ $sheet = new PHPExcel(); $sheet->getActiveSheet()->getSheetView()->setZoomScale(75); //set Metadata $sheet->getProperties()->setCreator('www.bitsofttech.co.za') ->setLastModifiedBy('www.bitsofttech.co.za') ->setTitle('Kilometer Logs') ->setKeywords('kilos logged report'); //set default settings $sheet->getDefaultStyle()->getAlignment()->setVertical( PHPExcel_Style_Alignment::VERTICAL_TOP); $sheet->getDefaultStyle()->getAlignment()->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $sheet->getDefaultStyle()->getFont()->setName('Calibri'); $sheet->getDefaultStyle()->getFont()->setSize(12); //Get reference to active spreadsheet in workbook $sheet->setActiveSheetIndex(0); $activeSheet = $sheet->getActiveSheet(); //Set Print Options $activeSheet->getPageSetup()->setOrientation( PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) ->setFitToWidth(1) ->setFitToHeight(0); $activeSheet->getHeaderFooter()->setOddHeader('&C&B&16' . $sheet->getProperties()->getTitle()) ->setOddFooter('&CPage &P of &N'); //Populate the sheet with data $row = mysql_fetch_assoc($result_set); print_r($row); $colHeaders = array_keys($row); $col = 'A'; $rowNum = 1; //set the column headings $activeSheet->setCellValue('A1','Date'); $activeSheet->getColumnDimension('A')->setAutoSize(true); $activeSheet->setCellValue('B1','Opening'); $activeSheet->getColumnDimension('B')->setAutoSize(true); $activeSheet->setCellValue('C1','Closing'); $activeSheet->getColumnDimension('C')->setAutoSize(true); $activeSheet->setCellValue('D1','Total'); $activeSheet->getColumnDimension('D')->setAutoSize(true); $activeSheet->setCellValue('E1','Destination'); $activeSheet->getColumnDimension('E')->setAutoSize(true); //Populate the individual cells do{ $col = 'A'; $rowNum++; foreach($row as $value){ $activeSheet->setCellValue($col++ . $rowNum, $value); } }while($row= mysql_fetch_array($result_set)); $activeSheet->getStyle('A2:A' .$rowNum)->getAlignment() ->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $activeSheet->getStyle('B2:B' .$rowNum)->getAlignment() ->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $activeSheet->getStyle('C2:C' .$rowNum)->getAlignment() ->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $activeSheet->getStyle('E2:E' .$rowNum)->getAlignment() ->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $activeSheet->getStyle('D2:D' .$rowNum)->getAlignment() ->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_RIGHT) ->setWrapText(true); //Give the spreadsheet a title $activeSheet->setTitle('Monthly Logs'); //Generate the Excel file and download header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="Monthlylogs.xlsx"'); header('Cache-Control: max-age=0'); $writer = PHPExcel_IOFactory::createWriter($sheet, 'Excel2007'); ob_end_clean(); $writer->save('php://output'); exit(); }catch(Exception $e){ $error = $e->getMessage(); echo $error; }

Where are the duplicates generated? In the data query or in the Excel file generation? — mins
– mins, Commented Mar 11, 2015 at 6:53

djlauk · Accepted Answer · 2015-03-11 09:46:50Z

Errrm... you mean columns (i.e. vertical lines; values within a data set) duplicate, right? Because I could not find any duplicate rows (i.e. horizontal lines; data sets).

Your problem is in your loop:

$row = mysql_fetch_assoc($result_set); // ... do{ // ... foreach($row as $value){ // use $value } } while ($row = mysql_fetch_array($result_set));

Notice, that you initialize $row with mysql_fetch_assoc, but for iterating you call out to mysql_fetch_array. Here's the method signature and some explanation from the documentation on mysql_fetch_array:

array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )

...

By using MYSQL_BOTH (default), you'll get an array with both associative and number indices. Using MYSQL_ASSOC, you only get associative indices (as mysql_fetch_assoc() works), using MYSQL_NUM, you only get number indices (as mysql_fetch_row() works).

TL;DR

answer updated below:

There are actually two problems:

Problem: The first line of data (excel row 2) is different from the following lines.

Cause: Different API calls for loop initialization and continuation.

Fix: Use the same API call in both places.
Problem: Each excel row after line 3 (data row 2 and later) has each value twice.

Cause: mysql_fetch_array() will by default create two indices per value: One numeric and one associative (i.e. column name).

Fix: Use different API call or specify $result_type.

Solution to both problems wrapped up in a code example:

while (($row = mysql_fetch_row($result_set)) !== FALSE) { // ... foreach ($row as $value) { // ... } }

Collectives™ on Stack Overflow

Information coming up as duplicate Columns phpexcel, mySql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related