Hi Everyone!
Can anybody point me to the right direction or check what am i doing wrong. Im trying to export data from php to excel using phpexcel( which is working fine if im am only getting 1 specific record). but when i tried adding a loop to create a workbook im having a problem. the data does not match the actual record from DB
this is the script
Currently editing:
/home/msicph/public_html/sedaatria/demo/toexcel/genexcel.php
Encoding: Reopen Switch to Code Editor Close Save
<?php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
date_default_timezone_set('Europe/London');
/** PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
$today = date('Y-m-d');
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("templates/Billing.xls");
try {
include '../includes/global_config.php';
$q1= $con->prepare("SELECT name FROM accounts_info");
$q1->execute();
include '../includes/pdo_config.php';
while ($row = $q1->fetch()) {
$name=$row['name'];
$stmts = $con->prepare("SELECT * FROM reservations WHERE
`duedate` = DATE_SUB(CURDATE(), INTERVAL - 3 DAY) and resfor='$name' ");
$stmts->execute();
$objPHPExcel->getActiveSheet()->setCellValue('I1', PHPExcel_Shared_Date::PHPToExcel(time()));
$baseRow = 5;
foreach($stmts as $r => $dataRow) {
$billname=$dataRow['resfor'];
$rental_date=$dataRow['rental_date'];
$rental_time=$dataRow['rental_time'];
$row = $baseRow + $r;
$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $dataRow['resnum'])
->setCellValue('B'.$row, $dataRow['name'])
->setCellValue('C'.$row, date('M d, Y', strtotime($dataRow['rental_date']))." / ".$dataRow['rental_time'])
->setCellValue('D'.$row, $dataRow['service_type'])
->setCellValue('E'.$row, $dataRow['modetrans'])
->setCellValue('F'.$row, $dataRow['from'])
->setCellValue('G'.$row, $dataRow['to'])
->setCellValue('H'.$row, $dataRow['duedate'])
->setCellValue('I'.$row, $dataRow['total_rates']);
$add=$r + 10;
$last=$r + 5;
$basecell="I".$add;
$objPHPExcel->getActiveSheet()->setCellValue($basecell,'=SUM(I4'.':I'.$last.')');
$objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace(__FILE__,'excelfiles/'.$name.'.xls',__FILE__));
//$objWriter->save(str_replace('.php', '.xls', __FILE__));
echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
// Echo done
echo date('H:i:s') , " Done writing file" , EOL;
echo 'File has been created in ' , getcwd() , EOL;
} //END OF FOREACH
} //end of GET NAME
} // TRY
catch(PDOException $e) {
//echo "Error: " . $e->getMessage();
}
$con = null;
