PHPExcel 导出公共方法,可根据需要改变行样式,纵向合并单元格
$title = [
['field'=>'group_sn','width'=>25,'title'=>'大版单号'],
['field'=>'pro_customer_name','width'=>25,'title'=>'客户名'],
['field'=>'estimate_completion_time','width'=>25,'title'=>'预计完成时间'],
['field'=>'process_end_time','width'=>25,'title'=>'实际完成时间'],
['field'=>'device_name','width'=>25,'title'=>'机器'],
['field'=>'estimate_printing_time','width'=>25,'title'=>'理论加工时长(分)'],
['field'=>'process_time','width'=>25,'title'=>'实际加工时长(分)'],
['field'=>'machine_time','width'=>30,'title'=>'分段加工时长(分)'],
['field'=>'interval_machine_time','width'=>30,'title'=>'分段加工时间'],
['field'=>'plane_num_all','width'=>30,'title'=>'总下机数'],
['field'=>'plane_num','width'=>30,'title'=>'分段下机数'],
['field'=>'operation_name','width'=>30,'title'=>'机长'],
['field'=>'hold_abnormal_reason','width'=>25,'title'=>'hold单原因'],
['field'=>'time_difference','width'=>25,'title'=>'超时(分)'],
['field'=>'yield_rate','width'=>25,'title'=>'达成率'],
];
//样式 示例:字段 time_difference 大于 0 的行,字体颜色为FF0000
$conditions = [
[
'field'=>'time_difference',
'symbol'=>'gt',
'value'=>0,
'style'=>[
'font' =>['color'=>['rgb' => 'FF0000']]
]
]
];
//纵向合并单元格 示例:已group_sn字段分组,值相同的合并,include_field(选择字段合并),exclude_field(排除字段合并),可选择多个
$merge_cells = [
'field'=>'group_sn', //已该字段为分组,值相同的合并
'include_field'=>[], //合并选择字段
'exclude_field'=[
'machine_time',
'operation_name',
'plane_num',
'interval_machine_time'
], //合并排除字段
];
//调用
exportDataToExcel($title,$data,$exportName,true,false,$conditions,$merge_cells);
/**
* Excel导出
* @param array $title
* @param array $data
* @param string $fileName 表名
* @param bool $bold 列名是否加粗
* @param bool $returnFile 是否下载
* @param array $conditions 单元格样式
* 示例 $conditions = array('field'=>'time_difference','symbol'=>'gt','value'=>0,'style'=>array('font' => array('color' => array('rgb' => 'FF0000'),),));
* @return bool|string
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
* @throws PHPExcel_Writer_Exception
*/
function exportDataToExcel($title = array(), $data = array(), $fileName = '', $bold = true,$returnFile = false,$conditions = array(),$merge_cells = array()){
if (empty($title)) {
return false;
}
if (!empty($merge_cells)){
$num_arr = array_column($data,$merge_cells['field']);
$arr = array_count_values($num_arr);
}
vendor("Excel.PHPExcel");
$objPHPExcel = new PHPExcel();
$count = count($title);
for ($i = 0; $i < $count; $i++) {
$i_name = PHPExcel_Cell::stringFromColumnIndex($i);//处理列大于26个的方法
//设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension($i_name)->setWidth($title[$i]['width']);
//设置列名
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($i_name . '1', $title[$i]['title']);
//列名是否加粗
if ($bold) {
$objPHPExcel->getActiveSheet()->getStyle($i_name . '1')->getFont()->setBold(true);
}
//设置列名高度
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
//设置居中
$objPHPExcel->getActiveSheet()->getStyle($i_name . '1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($i_name . '1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($i_name . '1')->getAlignment()->setWrapText(true);
//数据处理
if($data){
foreach ($data as $key => $item) {
if (isset($title[$i]['field']) && !empty($item[$title[$i]['field']])){
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($i_name . ($key + 2), $item[$title[$i]['field']]);
//添加样式
if (!empty($conditions)){
foreach ($conditions as $condition){
if ($condition['field'] == $title[$i]['field']){
// gt 表示 > 大于 egt 表示 >= 大于等于 lt 表示 < 小于 elt 表示 <= 小于等于 eq 表示 = 等于 neq 表示 = 不等于”
switch ($condition['symbol'])
{
case 'gt':
if ($item[$title[$i]['field']] > $condition['value']){
$objPHPExcel->getActiveSheet()->getStyle('A' . ($key + 2) . ':BI' . ($key + 2))->applyFromArray($condition['style']);
}
break;
case 'egt':
if ($item[$title[$i]['field']] >= $condition['value']){
$objPHPExcel->getActiveSheet()->getStyle('A' . ($key + 2) . ':BI' . ($key + 2))->applyFromArray($condition['style']);
}
break;
case 'lt':
if ($item[$title[$i]['field']] < $condition['value']){
$objPHPExcel->getActiveSheet()->getStyle('A' . ($key + 2) . ':BI' . ($key + 2))->applyFromArray($condition['style']);
}
break;
case 'elt':
if ($item[$title[$i]['field']] <= $condition['value']){
$objPHPExcel->getActiveSheet()->getStyle('A' . ($key + 2) . ':BI' . ($key + 2))->applyFromArray($condition['style']);
}
break;
case 'eq':
if ($item[$title[$i]['field']] == $condition['value']){
$objPHPExcel->getActiveSheet()->getStyle('A' . ($key + 2) . ':BI' . ($key + 2))->applyFromArray($condition['style']);
}
break;
case 'neq':
if ($item[$title[$i]['field']] != $condition['value']){
$objPHPExcel->getActiveSheet()->getStyle('A' . ($key + 2) . ':BI' . ($key + 2))->applyFromArray($condition['style']);
}
break;
default:
}
}
}
}
}
if (!empty($merge_cells)){
//纵向合并
foreach ($arr as $kk=>$vv) {
if($vv>1){
$keys = array_keys($num_arr,$kk);
$end = $keys[count($keys)-1]+2;
$start = $end-$vv+1;
//合并排除字段
if (!empty($merge_cells['exclude_field']) && !in_array($title[$i]['field'],$merge_cells['exclude_field'])){
$objPHPExcel->getActiveSheet()->mergeCells($i_name.$start.":".$i_name.$end);
}
//合并选择字段
if (!empty($merge_cells['include_field']) && in_array($title[$i]['field'],$merge_cells['include_field'])){
$objPHPExcel->getActiveSheet()->mergeCells($i_name.$start.":".$i_name.$end);
}
}
}
}
//设置居中
$objPHPExcel->getActiveSheet()->getStyle($i_name . ($key + 2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($i_name . ($key + 2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($i_name . ($key + 2))->getAlignment()->setWrapText(true);
}
}
}
$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);
$fileName = $fileName ? $fileName : date("Ymd", time());
if($returnFile){
$dirPath = '/';//注意要设置为绝对路径
$newFileName = date('YmdHis').'_'.$fileName;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($dirPath.'/'.$newFileName);
return $newFileName;
}else{
// excel头参数
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '-' . date('Ymd His') . '.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//excel5为xls格式,excel2007为xlsx格式
$objWriter->save('php://output');
exit;
}
}
导出效果