beacon实现模板导出Excel - 深圳全飞鸿

<?php
//by syant
//FPY报表需要处理一下excel的模板下载
//defined('WORK_PATH') or define('WORK_PATH',dirname(__FILE__).DIRECTORY_SEPARATOR);
class REPAIR_00 extends tpage{
function REPAIR_00($app) {
parent::__construct($app);
}
/*
* 重构默认的Excel下载逻辑
*/
function createExcel(){
global $db, $config, $user, $report, $smarty;
$sqlstr = $smarty->tpl_vars["sql"];
$limit = 65000; //最大60K
//require_once("phar://beacon.inc.phar/page.inc.php");
include_once("page.inc.php");
$pager = new pager($limit, 1);
$data=$pager->execute($db, $sqlstr);
//$count=count($data); //数量
include_once($config->base_path."/libs/mi.excel.php");
//$objReader = PHPExcel_IOFactory::createReader ( 'Excel5' ); //如果模板是.xls的
$objReader = PHPExcel_IOFactory::createReader ( 'Excel2007' ); //如果模板是.xlsx的
$path = $config->base_path.DIRECTORY_SEPARATOR.'excel_template'.DIRECTORY_SEPARATOR."FPY.xlsx";//设置移动路径
$objPHPExcel = $objReader->load ($path);
$objPHPExcel->setActiveSheetIndex(0) ;
$objActSheet = $objPHPExcel->getActiveSheet();
//用于debug, 将SQL存在P2, 中间数据存在P3
$objActSheet->setCellValue('P2', $sqlstr);
$objActSheet->setCellValue('P3', var_export($data,true));
/*$ii=4;
foreach($smarty->tpl_vars as $kk=>$vv){
$objActSheet->setCellValue("P{$ii}", $kk);
$ii=$ii+1;
}
//echo print_r(debug_backtrace(),true);
*/
//填充模板内容
/*
SELECT '2021-06-13' DTBEGIN,'2021-06-13' DTEND,R.MODEL_NAME,r.GROUP_NAME,
sum(R.PASS_QTY) + sum(R.FAIL_QTY) Total_Qty,
sum(R.PASS_QTY) Pass_Qty, sum(R.FAIL_QTY) Fail_Qty,
format(100 * sum(R.FAIL_QTY)/IF((sum(R.PASS_QTY)+sum(R.FAIL_QTY))=0,1,(sum(R.PASS_QTY)+sum(R.FAIL_QTY))),2) Fail_Rate,
format(100 * sum(R.PASS_QTY)/IF((sum(R.PASS_QTY)+sum(R.FAIL_QTY))=0,1,(sum(R.PASS_QTY)+sum(R.FAIL_QTY))),2) Yield_Rate,
sum(R.REPASS_QTY) RePass_Qty,
sum(R.REFAIL_QTY) ReFail_Qty
from sfc.r102 R,sfc.c_model_desc_t B
WHERE R.WORK_DATE BETWEEN REPLACE('2021-06-13','-','') and REPLACE('2021-06-13','-','')
and r.model_name=b.model_name
Group by R.MODEL_NAME,R.GROUP_NAME
ORDER BY R.MODEL_NAME,GROUP_NAME
*/
$currow=5; //excel开始写的行
$k=0; $kk=0; $title="";
foreach($data as $ii=>$line){
$istart=$currow; $iend=$currow;
//B序号 C机型名称 D工站 E投入数 F良品数 G总不良数 H总不良率 I直通率 J不良项 K单项不良数 L单项不良率 M备注
$k=$k+1;
/*$objActSheet->setCellValue("B{$istart}", $k); //行号
$objActSheet->setCellValue("C{$istart}", $line["MODEL_NAME"]);
$objActSheet->setCellValue("D{$istart}", $line["GROUP_NAME"]);
$objActSheet->setCellValue("E{$istart}", $line["Total_Qty"]);
$objActSheet->setCellValue("F{$istart}", $line["Pass_Qty"]);
$objActSheet->setCellValue("G{$istart}", $line["Fail_Qty"]);
$objActSheet->setCellValue("H{$istart}", $line["Fail_Rate"]."%");
$objActSheet->setCellValue("I{$istart}", $line["Yield_Rate"]."%"); */
if($line["Fail_Qty"]>0){
$sql="SELECT a.test_code,ifnull(b.error_desc,a.test_code) error_desc,count(serial_number) qty
FROM SFC.r_repair_t a left join sfc.c_error_code_t b on a.test_code=b.error_code
WHERE a.MODEL_NAME='".$line["MODEL_NAME"]."' AND a.TEST_GROUP='".$line["GROUP_NAME"]."'
AND a.TEST_TIME between str_to_date('".$line["DTBEGIN"]." 00:00:00','%Y-%m-%d %H:%i:%s')
and str_to_date('".$line["DTEND"]." 23:59:59','%Y-%m-%d %H:%i:%s')
group by b.error_desc,a.test_code order by count(serial_number) desc";
$table=$db->getAll($sql);
$icount=count($table);
$istart=$currow; $iend=$currow;
foreach($table as $tmp=>$row){
$objActSheet->setCellValue("J{$currow}", $row["test_code"]);
$objActSheet->setCellValue("K{$currow}", $row["error_desc"]);
$objActSheet->setCellValue("L{$currow}", $row["qty"]);
$rate=round($row["qty"]/$line["Fail_Qty"]*100,2);
$objActSheet->setCellValue("M{$currow}", "{$rate}%");
$objActSheet->setCellValue("N{$currow}", "");
$iend=$currow;//结束的行！
$currow=$currow+1;
}
if($icount>0){
$objActSheet->mergeCells("B{$istart}:B{$iend}");
$objActSheet->mergeCells("C{$istart}:C{$iend}");
$objActSheet->mergeCells("D{$istart}:D{$iend}");
$objActSheet->mergeCells("E{$istart}:E{$iend}");
$objActSheet->mergeCells("F{$istart}:F{$iend}");
$objActSheet->mergeCells("G{$istart}:G{$iend}");
$objActSheet->mergeCells("H{$istart}:H{$iend}");
$objActSheet->mergeCells("I{$istart}:I{$iend}");
}
}else{
$currow=$currow+1;
}
$objActSheet->setCellValue("B{$istart}", $k); //行号
$objActSheet->setCellValue("C{$istart}", $line["MODEL_NAME"]);
$objActSheet->setCellValue("D{$istart}", $line["GROUP_NAME"]);
$objActSheet->setCellValue("E{$istart}", $line["Total_Qty"]);
$objActSheet->setCellValue("F{$istart}", $line["Pass_Qty"]);
$objActSheet->setCellValue("G{$istart}", $line["Fail_Qty"]);
$objActSheet->setCellValue("H{$istart}", $line["Fail_Rate"]."%");
$objActSheet->setCellValue("I{$istart}", $line["Yield_Rate"]."%");
if($line["Fail_Rate"]>2){
$objActSheet->getStyle("H{$istart}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objActSheet->getStyle("H{$istart}")->getFill()->getStartColor()->setARGB('FFFFAAAA') ;
}else if($line["Fail_Rate"]>0){
$objActSheet->getStyle("H{$istart}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objActSheet->getStyle("H{$istart}")->getFill()->getStartColor()->setARGB('FFFFFF99') ;
}
$title="FPY报表,日期[".$line["DTBEGIN"]." 00:00时至 ".$line["DTEND"]." 23:59时]生产状况";
};
#测试设置边框
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array('argb' => 'FF000000'),
),
),
);
$objActSheet->setCellValue("B3", $title);
$objActSheet->getStyle("B5:N{$iend}")->applyFromArray($styleArray);
$filename = uniqid(time(),true);
ob_end_clean(); //fix by syant !
//header('Content-Type: application/vnd.ms-excel');
header('pragma:public');
header('Content-Disposition: attachment;filename="FPY' . $filename . '.xlsx"');
//header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit; //by syant ,很重要！
}
}
?>

复制代码