Alihamra Alihamra - 1 month ago 7
MySQL Question

Simple PHP to Excel exports wrong values

I have the following PHP Code needed to exprt from mysql table called sold_items via PHP to an excel .xls file:

/** Error reporting */
error_reporting(E_ALL);

/** Include PHPExcel */
require_once ('Classes/PHPExcel.php');



// Create new PHPExcel object
$objPHPExcel = new PHPExcel();


// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");


$query = "SELECT * from sold_items GROUP by date";
$result = mysql_query($query);

while ( $row2 = mysql_fetch_assoc($result) ) {
if ( !isset($curdate) || $curdate != $row2['date'] ) {
$curdate= $row2['date'];


$query2 = "SELECT item_no,qty,price,date,time from sold_items where date = '".$curdate."'";
$result2 = mysql_query($query2);



//Excuting Values from Mysql to Excel
$row = 2; // 1-based index
while($row_data = mysql_fetch_assoc($result2)) {

$col = 0;
//Row Headers
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'Item No')
->setCellValue('B1', 'QTY')
->setCellValue('C1', 'Selling')
->setCellValue('D1', 'Date')
->setCellValue('E1', 'Time');
//Row Values
foreach($row_data as $key=>$value) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
$col++;
}
$row++;
}


// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');


//Check if File Name Exists
$name = 'C:/Sales-'.$row2['date'].'.xls';
$index = 1;
while(file_exists($name)) {
$name = $name.'--'.$index.".xls";
$index++;
}



// Save Excel 2007 file
//'C:/M10-Sales-'.$tdate.'.xls'
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($name);

}
}


I have the following Mysql table as follows :

item_no qty price date
x1 2 12 2014-4-22
x2 3 15 2014-4-22
x3 5 10 2014-4-23
x2 10 10 2014-4-23
x5 4 12 2014-4-23


The output result in excel are two files, each file should contain the items for each date ( 2014-4-22 and 2014-4-23

The problem is that when i open file 2014-4-22.xls the items are as follows x1,x2 which they are exactly correct but when opening 2014-4-23.xls the excel lists all items in both dates, items are x1,x2,x3,x2 and x5...Why is that? I can't seem to find or locate the problem of doing this? Any looping issues in the code above?

Please help.

Answer

First, rewrite the query where you're getting the dates:

$query = "SELECT * from sold_items GROUP by date";

should be

$query = "SELECT DISTINCT date from sold_items";

If you just want a list of dates, just query for that.

Second, the issue with PHPExcel seems to be that you're re-using the same PHPExcel object for both worksheets. Basically, you write the values for the first date & save the worksheet with the name "2014-04-22.xls". Then you add the values for the second date without clearing the data from the first sheet and save it with the name "2014-04-23.xls".