简单的PHP到Excel导出错误的值

Question

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 1

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".