一、首先到PHPexcel官网上下载最新的PHPexcel类,下载解压缩一个classes文件夹,里面包含了PHPExcel.php类文件和PHPExcel的文件夹,这个类文件和文件夹是我们需要的,把classes解压到\TOPNew\ThinkPHP\Library\Org\Util目录下,将PHPExcel.php重命名为PHPExcel.class.php;然后你就可以开始编写你的控制器了喽。(注:我的PHPexcel版本是1.7.9的哦!)
二、视图代码
<div class="right_col" role="main">
<div class="row">
<div class="container">
<h1>人员绩效导出</h1>
<form action="{:U('Plansuper/performanceExport')}" method="post">
<a href="http://10.32.228.113/overt">14562584</a>
<div class="weui_cells weui_cells_form">
<div class="weui_cell weui_cell_select">
<div class="weui_cell_hd">
<label for="" class="weui_label">部门</label>
</div>
<div class="weui_cell_bd weui_cell_primary">
<select class="weui_select" name="department">
<option value=" " selected="selected">请选择部门</option>
<volist name="departments" id="department">
<option value="{$department.department}">{$department.department}</option>
</volist>
</select>
</div>
</div>
<div class="weui_cell weui_cell_select">
<div class="weui_cell_hd">
<label for="" class="weui_label">年度</label>
</div>
<div class="weui_cell_bd weui_cell_primary">
<select class="weui_select" name="year">
<option value=" " selected="selected">请选择年度</option>
<volist name="years" id="year">
<option value="{$year.year}">{$year.year}</option>
</volist>
</select>
</div>
</div>
<div class="weui_cell weui_cell_select">
<div class="weui_cell_hd">
<label for="" class="weui_label">季度</label>
</div>
<div class="weui_cell_bd weui_cell_primary">
<select class="weui_select" name="quarter">
<option value=" " selected="selected">请选择季度</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
</select>
</div>
</div>
</div>
<button type="submit" class="weui_btn weui_btn_primary" >导出</button>
</form>
</div>
</div>
</div>
三、控制器代码
/**
* 接收前台数据,进行处理后
* 传入exportOrderExcel
* 导出Excel
*/
public function performanceExport(){//导出Excel
$where = [
'department'=>$_POST['department'],
'year'=>$_POST['year'],
'quarter'=>$_POST['quarter']
];
$xlsName = $where['department']."人员绩效";
$xlsCell = array(
array('year','年度'),
array('quarter','季度'),
array('name','姓名'),
array('id_employee','工号'),
array('department','部门'),
array('office','科室'),
array('id_level','岗级'),
array('grade_total','季度总分'),
array('grade_other','党工团加分'),
array('grade_end','总分'),
array('if_grade','是否参评'),
array('grade_content','备注')
);
$xlsData = M('gradequarter_confirm')->where($where)
->field('year,quarter,name,id_employee,department,office,id_level,grade_total,grade_other,grade_end,if_grade,grade_content')
->order('department,office')
->select();
$this->exportOrderExcel($xlsName,$xlsCell,$xlsData);
}
/**
*
* execl数据导出
* 应用场景:人员绩效数据导出
* string $title 模型名(如Member),用于导出生成文件名的前缀
* array $cellName 表头及字段名
* array $data 导出的表数据
*
*/
function exportOrderExcel($title,$cellName,$data)
{
//引入核心文件
import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.Reader.Excel2007");
$objPHPExcel = new \PHPExcel();
//定义配置
$topNumber = 1;//表头有几行占用
$xlsTitle = iconv('utf-8', 'gb2312', $title);//文件名称
$fileName = $title.date('_YmdHis');//文件名称
$cellKey = array(
'A','B','C','D','E','F','G','H','I','J','K','L','M',
'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
);
//写在处理的前面(了解表格基本知识,已测试)
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);//所有单元格(行)默认高度
$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(10);//所有单元格(列)默认宽度
$objPHPExcel->getActiveSheet()->getRowDimension('0.2')->setRowHeight(30);//设置行高度
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);//设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);//设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);//设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);//设置列宽度
//处理表头
foreach ($cellName as $k=>$v)
{
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$k].$topNumber, $v[1]);//设置表头数据
$objPHPExcel->getActiveSheet()->freezePane($cellKey[$k].($topNumber+1));//冻结窗口
$objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getFont()->setBold(true);//设置是否加粗
// $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
if($v[3] > 0)//大于0表示需要设置宽度
{
$objPHPExcel->getActiveSheet()->getColumnDimension($cellKey[$k])->setWidth($v[3]);//设置列宽度
// $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
}
}
//处理数据
foreach ($data as $k=>$v)
{
foreach ($cellName as $k1=>$v1)
{
$objPHPExcel->getActiveSheet()->setCellValue($cellKey[$k1].($k+1+$topNumber), $v[$v1[0]]);
if($v['end'] > 0)
{
if($v1[2] == 1)//这里表示合并单元格
{
$objPHPExcel->getActiveSheet()->mergeCells($cellKey[$k1].$v['start'].':'.$cellKey[$k1].$v['end']);
// $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
}
}
if($v1[4] != "" && in_array($v1[4], array("LEFT","CENTER","RIGHT")))
{
$v1[4] = eval('return PHPExcel_Style_Alignment::HORIZONTAL_'.$v1[4].';');
//这里也可以直接传常量定义的值,即left,center,right;小写的strtolower
$objPHPExcel->getActiveSheet()->getStyle($cellKey[$k1].($k+1+$topNumber))->getAlignment()->setHorizontal($v1[4]);
}
}
}
//导出execl
header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
四、效果图
五、制章感言
很嗨皮!谢谢 _^o^_
友情提示:对了,要是提示报错的话,根据提示找到报错位置,先注释掉,试试看;因为我PHPExcel.class.php文件里有几个地方也报错,注释掉就可以了,不影响你的代码程序哦。