Halo om richard..
sorry for late response,.
here is my example at GLTagProfit_Loss.php to show tag report for 12 months (period) and produce it in excell report..
Code:
else if (isset($_POST['PrintEXCELL']))
{
include('includes/header.inc');
echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">';
echo '<div>';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
<input type="hidden" name="FromPeriod" value="' . $_POST['FromPeriod'] . '" />
<input type="hidden" name="ToPeriod" value="' . $_POST['ToPeriod'] . '" />';
$NumberOfMonths = $_POST['ToPeriod'] - $_POST['FromPeriod'] + 1;
if ($NumberOfMonths >12)
{
echo '<br />';
prnMsg(_('A period up to 12 months in duration can be specified') . ' - ' . _('the system automatically shows a comparative for the same period from the previous year') . ' - ' . _('it cannot do this if a period of more than 12 months is specified') . '. ' . _('Please select an alternative period range'),'error');
include('includes/footer.inc');
exit;
}
$sql = "SELECT lastdate_in_period
FROM periods
WHERE periodno='" . $_POST['ToPeriod'] . "'";
$PrdResult = DB_query($sql, $db);
$myrow = DB_fetch_row($PrdResult);
$PeriodToDate = MonthAndYearFromSQLDate($myrow[0]);
require_once dirname(__FILE__) . '/includes/excell/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("WebERP")
->setLastModifiedBy("WebERP")
->setTitle("Office 2007 XLSX Document")
->setSubject("Office 2007 XLSX Document")
->setDescription("WebERP.")
->setKeywords("office 2007 openxml php")
->setCategory("Office Documents");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Period ' . $_POST['FromPeriod'] . 'To : ' . $_POST['ToPeriod'] . ' Actual Cost')->setCellValue('C2', $_POST['FromPeriod']);
$objPHPExcel->getActiveSheet()->setCellValue('D2', $_POST['FromPeriod']+1) ->setCellValue('E2', $_POST['FromPeriod']+2);
$objPHPExcel->getActiveSheet()->setCellValue('F2', $_POST['FromPeriod']+3) ->setCellValue('G2', $_POST['FromPeriod']+4);
$objPHPExcel->getActiveSheet()->setCellValue('H2', $_POST['FromPeriod']+5) ->setCellValue('I2', $_POST['FromPeriod']+6);
$objPHPExcel->getActiveSheet()->setCellValue('J2', $_POST['FromPeriod']+7) ->setCellValue('K2', $_POST['FromPeriod']+8);
$objPHPExcel->getActiveSheet()->setCellValue('L2', $_POST['FromPeriod']+9) ->setCellValue('M2', $_POST['FromPeriod']+10);
$objPHPExcel->getActiveSheet()->setCellValue('N2', $_POST['FromPeriod']+11) ->setCellValue('O2', $_POST['FromPeriod']+12);
// Tampilkan tags,
$SQL = "SELECT tagref,
tagdescription
FROM tags
ORDER BY tagdescription";
$result=DB_query($SQL,$db);
$k=3;
while ($myrow=DB_fetch_array($result)) // loop all tags
{
$k++;
$buff="A".$k;
$objPHPExcel->getActiveSheet()->setCellValue($buff,$myrow['tagdescription']);
### BEGIN : Show all account with this->tags
/*
* $SQLTAGS = "SELECT accountgroups.sectioninaccounts, accountgroups.groupname, accountgroups.parentgroupname, gltrans.account, chartmaster.accountname,
Sum(CASE WHEN (gltrans.periodno>='" . $_POST['FromPeriod'] . "' AND gltrans.periodno<='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalAllPeriods,
Sum(CASE WHEN (gltrans.periodno='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalThisPeriod
FROM chartmaster
INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
INNER JOIN gltrans ON chartmaster.accountcode= gltrans.account
WHERE accountgroups.pandl=1 AND gltrans.tag='" . $myrow['tagref'] . "'
GROUP BY accountgroups.sectioninaccounts,
accountgroups.groupname,
accountgroups.parentgroupname,
gltrans.account,
chartmaster.accountname
ORDER BY accountgroups.sectioninaccounts,
accountgroups.sequenceintb,
accountgroups.groupname,
gltrans.account";
*/
/* patch by kelox */
$SQLTAGS = "SELECT accountgroups.sectioninaccounts, accountgroups.groupname, accountgroups.parentgroupname, gltrans.account, chartmaster.accountname,
Sum(CASE WHEN (gltrans.periodno>='" . $_POST['FromPeriod'] . "' AND gltrans.periodno<='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalAllPeriods,
Sum(CASE WHEN (gltrans.periodno='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalThisPeriod
FROM gltrans
INNER JOIN chartmaster ON chartmaster.accountcode= gltrans.account
INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
WHERE accountgroups.pandl=1 AND gltrans.tag='" . $myrow['tagref'] . "'
GROUP BY accountgroups.sectioninaccounts,
accountgroups.groupname,
accountgroups.parentgroupname,
gltrans.account,
chartmaster.accountname
ORDER BY accountgroups.sectioninaccounts,
accountgroups.sequenceintb,
accountgroups.groupname,
gltrans.account";
$restag = DB_query($SQLTAGS,$db,_('No general ledger accounts were returned by the SQL because'),_('The SQL that failed was'));
while ($tagrow=DB_fetch_array($restag)) //loop total
{
//echo '<pre>'; print_r($tagrow); echo '</pre>';
$k++;
$buffB="B".$k;
$tagdesc= $tagrow['account'] . " " . $tagrow['accountname'];
$objPHPExcel->getActiveSheet()->setCellValue($buffB,$tagdesc);
$buffTotal="O".$k;
$objPHPExcel->getActiveSheet()->setCellValue($buffTotal,locale_number_format($tagrow['TotalAllPeriods'],$_SESSION['CompanyRecord']['decimalplaces']));
## populate for 12 periods from $post['fromPeriod'];
##first periods
$firstperiod=$_POST['FromPeriod'];
$firstperiod=$firstperiod-1;
//Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Des
//$arrayMONTH=array{'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sept','Oct','Nov','Des'};
$arrayCELL = array("C","D","E","F","G","H","I","J","K","L","M","N");
for($periodcount=0;$periodcount<12;$periodcount++)
{
//echo $arrayCELL[$periodcount]; //Cell
$firstperiod=$firstperiod+1; // Increment each month until 12 month
/*
$SQLeachtag = "SELECT Sum(CASE WHEN (gltrans.periodno='" . $firstperiod . "') THEN gltrans.amount ELSE 0 END) AS TTLthisPeriod
FROM chartmaster
INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
INNER JOIN gltrans ON chartmaster.accountcode= gltrans.account
WHERE accountgroups.pandl=1 AND gltrans.tag='" . $myrow['tagref'] . "' AND account='". $tagrow['account'] ."'
GROUP BY accountgroups.sectioninaccounts,
accountgroups.groupname,
accountgroups.parentgroupname,
gltrans.account,
chartmaster.accountname
ORDER BY accountgroups.sectioninaccounts,
accountgroups.sequenceintb,
accountgroups.groupname,
gltrans.account";
*/
$SQLeachtag = "SELECT Sum(CASE WHEN (gltrans.periodno='" . $firstperiod . "') THEN gltrans.amount ELSE 0 END) AS TTLthisPeriod
FROM gltrans
INNER JOIN chartmaster ON chartmaster.accountcode= gltrans.account
INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
WHERE accountgroups.pandl=1 AND gltrans.tag='" . $myrow['tagref'] . "' AND account='". $tagrow['account'] ."'
GROUP BY accountgroups.sectioninaccounts,
accountgroups.groupname,
accountgroups.parentgroupname,
gltrans.account,
chartmaster.accountname
ORDER BY accountgroups.sectioninaccounts,
accountgroups.sequenceintb,
accountgroups.groupname,
gltrans.account";
$reseachtag = DB_query($SQLeachtag,$db,_('No general ledger accounts were returned by the SQL because'),_('The SQL that failed was'));
$tagthisperiod = DB_fetch_array($reseachtag);
$buffhorizontal = $arrayCELL[$periodcount] . $k; // ex : C1, D1, E1, F1.. etc..
$objPHPExcel->getActiveSheet()->setCellValue($buffhorizontal,$tagthisperiod['TTLthisPeriod']);
}
##end populate for 12 periods from $post['fromPeriod'];
}
## END : Show all account with this->tags
}
/*
* SHOW ALL ACCOUNT THAT HAS NOT BEEN TAGGED..
*/
$k=$k+3;
$NoTag = "A".$k;
$objPHPExcel->getActiveSheet()->setCellValue($NoTag,"Account with no tag ");
##########################
$k=$k+3;
$k++;
$buff="A".$k;
/* patch by kelox */
$SQLTAGS = "SELECT accountgroups.sectioninaccounts, accountgroups.groupname, accountgroups.parentgroupname, gltrans.account, chartmaster.accountname,
Sum(CASE WHEN (gltrans.periodno>='" . $_POST['FromPeriod'] . "' AND gltrans.periodno<='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalAllPeriods,
Sum(CASE WHEN (gltrans.periodno='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalThisPeriod
FROM gltrans
INNER JOIN chartmaster ON chartmaster.accountcode= gltrans.account
INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
WHERE accountgroups.pandl=1 AND gltrans.tag=0
GROUP BY accountgroups.sectioninaccounts,
accountgroups.groupname,
accountgroups.parentgroupname,
gltrans.account,
chartmaster.accountname
ORDER BY accountgroups.sectioninaccounts,
accountgroups.sequenceintb,
accountgroups.groupname,
gltrans.account";
$restag = DB_query($SQLTAGS,$db,_('No general ledger accounts were returned by the SQL because'),_('The SQL that failed was'));
while ($tagrow=DB_fetch_array($restag)) //loop total
{
//echo '<pre>'; print_r($tagrow); echo '</pre>';
$k++;
$buffB="B".$k;
$tagdesc= $tagrow['account'] . " " . $tagrow['accountname'];
$objPHPExcel->getActiveSheet()->setCellValue($buffB,$tagdesc);
$buffTotal="O".$k;
$objPHPExcel->getActiveSheet()->setCellValue($buffTotal,locale_number_format($tagrow['TotalAllPeriods'],$_SESSION['CompanyRecord']['decimalplaces']));
## populate for 12 periods from $post['fromPeriod'];
##first periods
$firstperiod=$_POST['FromPeriod'];
$firstperiod=$firstperiod-1;
//Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Des
//$arrayMONTH=array{'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sept','Oct','Nov','Des'};
$arrayCELL = array("C","D","E","F","G","H","I","J","K","L","M","N");
for($periodcount=0;$periodcount<12;$periodcount++)
{
$firstperiod=$firstperiod+1; // Increment each month until 12 month
$SQLeachtag = "SELECT Sum(CASE WHEN (gltrans.periodno='" . $firstperiod . "') THEN gltrans.amount ELSE 0 END) AS TTLthisPeriod
FROM gltrans
INNER JOIN chartmaster ON chartmaster.accountcode= gltrans.account
INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
WHERE accountgroups.pandl=1 AND gltrans.tag=0 AND account='". $tagrow['account'] ."'
GROUP BY accountgroups.sectioninaccounts,
accountgroups.groupname,
accountgroups.parentgroupname,
gltrans.account,
chartmaster.accountname
ORDER BY accountgroups.sectioninaccounts,
accountgroups.sequenceintb,
accountgroups.groupname,
gltrans.account";
$reseachtag = DB_query($SQLeachtag,$db,_('No general ledger accounts were returned by the SQL because'),_('The SQL that failed was'));
$tagthisperiod = DB_fetch_array($reseachtag);
$buffhorizontal = $arrayCELL[$periodcount] . $k;
$objPHPExcel->getActiveSheet()->setCellValue($buffhorizontal,$tagthisperiod['TTLthisPeriod']);
}
}
##########################
//echo date('H:i:s') , " Set column widths" , EOL;
//$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
//echo date('H:i:s') , " Rename worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Tag Reports');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
//simpan
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
//$excellfile= 'GLTagProfit_Loss.xlsx';
//echo Dirname($_SERVER[PHP_SELF]);
//echo str_replace('.php','.xlsx',htmlspecialchars($_SERVER['PHP_SELF']));
echo '<a href='. str_replace('.php','.xlsx',htmlspecialchars($_SERVER['PHP_SELF'])) . '>Download Here</a>';
//header('Location: '.$excellfile );
}
i guess your problem is that the error happened when you try to directly redirect to download.. try to create the excell file at the server first, and create a download link to download it manually..