public function water_log_export(){
ini_set(memory_limit,'1024M');
// $data = $m->field('eq_id,img,create_time,loss,last_loss,ps_id')->select();
$data=M()
->table(array('zgpt_instrument'=>'in','zgpt_equipment'=>'eq'))
->where('in.eq_id=eq.id and eq.type=1')
->field('eq_id,img,create_time,loss,last_loss,ps_id')
->select();
// 导出Exl
import("ORG.Util.PHPExcel");
import("ORG.Util.PHPExcel.Reader.Excel2007");
import("ORG.Util.PHPExcel.IOFactory");
import("ORG.Util.PHPExcel.Worksheet.Drawing");
$objPHPExcel = new \PHPExcel();
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
$objActSheet = $objPHPExcel->getActiveSheet();
//合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A1:U1'); //合并
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//加边框
$objPHPExcel->getActiveSheet()->getStyle('A2:U2')->applyFromArray(
array(
'borders' => array(
'top' => array(
'style' => \PHPExcel_Style_Border::BORDER_THIN
)
)
)
);
$objActSheet->setCellValue('A1', '园区总水表抄表统计');
//表头数组
$tableheader = array('水表种类','照片','时间','抄表见数（吨）','用水量（吨）','抄表人','备注','水表种类','照片','时间','抄表见数（吨）','用水量（吨）','抄表人','备注','水表种类','照片','时间','抄表见数（吨）','用水量（吨）','抄表人','备注');
//填充表头信息
$j = 0;
for($i = A;$i <= U;++$i) {
$objPHPExcel->getActiveSheet()->setCellValue($i.'2',$tableheader[$j]);
$objPHPExcel->getActiveSheet()->getColumnDimension($i)->setWidth(20);
$objPHPExcel->getActiveSheet(0)->getStyle($i.'2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet(0)->getStyle($i.'2')->getFill()->getStartColor()->setARGB('ff00b050');
$j++;
}
$objPHPExcel->getActiveSheet()->getStyle( 'A1')->getFont()->setSize(16);
/*for($i = 0;$i <1000;$i++){
$data[] = $data[0];
}*/
$cols = 7; //字段数量
foreach ($data as $k => $v) {
for($i = 0;$i < $cols;$i++){
$col = strtoupper(chr(65+$i+$cols*($k%3)));
$row = ceil(($k+1)/3)+2;
$eq=M('equipment')->where(array('id'=>$v['eq_id']))->find();
$last_loss=$v['loss']-$v['last_loss'];
$person=M('person')->where(array('ps_id'=>$v['ps_id']))->find();
$val=array(
$eq['name'],
//$v['img'],
'',
date('Y-m-d H:i',$v['create_time']),
$v['loss'],
$last_loss,
$person['ps_name'],
''
);
if($col==B || $col==I || $col==P){
// 图片生成
// $objDrawing = new \PHPExcel_Worksheet_Drawing();
// $objDrawing->setPath($_SERVER['DOCUMENT_ROOT'].$v['img']);
// 设置宽度高度
// $objDrawing->setWidth(140); //照片宽度
// 图片偏移距离
// $objDrawing->setOffsetX(0);
// $objDrawing->setOffsetY(0);
// $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
// $objDrawing->setResizeProportional(false);
//设置图片要插入的单元格
// $objDrawing->setCoordinates(strtoupper(chr(65+$i+$cols*($k%3))).$row); //照片
$objDrawing = new \PHPExcel_Worksheet_MemoryDrawing ();
/* 设置图片路径 切记：只能是本地图片 */
$img = $_SERVER['DOCUMENT_ROOT'].$v['img'];
// $newim = imagecreate (200, 116);
$newim= imagecreatetruecolor(300,174); //创建真彩图像资源
$img = imagecreatefromjpeg($img);
$width = imagesx($img);
$height = imagesy($img);
imagecopyresized($newim,$img,0,0,0,0,300,174,$width,$height);
// imagejpeg($dst_im); //输出压缩后的图片
$objDrawing->setName('erweima');
$objDrawing->setDescription('erweima');
$objDrawing->setImageResource($newim);
$objDrawing->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG); ////渲染方法
$objDrawing->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
/* 设置图片高度 */
$objDrawing->setWidth(140); //照片宽度
// $objDrawing->setHeight ( 100 );
/* 设置图片要插入的单元格位置 */
$objDrawing->setCoordinates(strtoupper(chr(65+$i+$cols*($k%3))).$row);
// 写入图片在指定格中的X坐标值
$objDrawing->setOffsetX ( 0);
// 写入图片在指定格中的Y坐标值
$objDrawing->setOffsetY ( 0);
$objDrawing->setWorksheet ( $objPHPExcel->getActiveSheet () );
}
// unset($objDrawing);
// 表格高度
$objActSheet->getRowDimension($k)->setRowHeight(60);
$objPHPExcel->getActiveSheet()->setCellValueExplicit($col.$row,"$val[$i]",\PHPExcel_Cell_DataType::TYPE_STRING);
}
// break;
}
// 设置第一行和第二行表格高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(30);
$fileName = '园区水表抄表统计.xls';
// $date = date("Y-m-d",time());
// $fileName .= "_{$date}.xls";
$fileName = iconv("utf-8", "gb2312", $fileName);
//设置活动单指数到第一个表,所以Excel打开这是第一个表
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"$fileName\"");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); //文件通过浏览器下载
// END
}
