/**
* 导出EXCEL
* 导出Excel方法
*/
public function export()
{
if ($this->request->isPost()) {
require_once(VENDOR_PATH .'/PHPExcel-1.8/Classes/PHPExcel.php');//执行期间包含并运行指定文件(通俗一点，括号内的文件会执行一遍
set_time_limit(0);
$ids = $this->request->post('ids');
$excel = new \PHPExcel();
$excel->getProperties()
->setTitle("员工管理");
//设置表格参数
$excel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$excel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$excel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$excel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$excel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$excel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('N')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
//设置表头内容
$worksheet = $excel->setActiveSheetIndex(0)
->setCellValue('A1', 'id')
->setCellValue('B1', '姓名')
->setCellValue('C1', '手机号')
->setCellValue('D1', '证件类型')
->setCellValue('E1', '证件号')
->setCellValue('F1', '性别')
->setCellValue('G1', '生日')
->setCellValue('H1', '年龄')
->setCellValue('I1', '是否已婚')
->setCellValue('J1', '地址')
->setCellValue('K1', '民族')
->setCellValue('L1', '政治面貌')
->setCellValue('M1', '是否健康')
->setCellValue('N1', '学历')
->setCellValue('O1', '入职日期');
//根据情况搜索内容有其他条件可在select之前添加
if($ids=='all' || empty($ids)){
$list = $this->model->select();
} else {
$list = $this->model->select($ids);
} //循环添加表中内容 严谨一些可以在$val['']后添加 ?? '里面填入数据为空时的信息'
//$val[]里面填写字段名
foreach ($list as $k => $val) {
$k = $k + 2;
$worksheet->setCellValue('A' . $k, $val['id'])
->setCellValue('B' . $k, $val['name'])
->setCellValue('C' . $k, $val['tel'] ."\t")
->setCellValue('D' . $k, $val['type'])
->setCellValue('E' . $k, $val['typenum'] . "\t")
->setCellValue('F' . $k, $val['sex'])
->setCellValue('G' . $k, $val['birth'])
->setCellValue('H' . $k, $val['age'])
->setCellValue('I' . $k, $val['yeson'])
->setCellValue('J' . $k, $val['loc'])
->setCellValue('K' . $k, $val['han'])
->setCellValue('L' . $k, $val['face'])
->setCellValue('M' . $k, $val['jiank'])
->setCellValue('N' . $k, $val['xueli'])
->setCellValue('O' . $k, $val['rutime']);
} $excel->createSheet();
$title = "员工管理" . date("YmdHis");
header('Content-Type: applicationnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1'); header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$objWriter->save('php://output');
exit;
return;
}
}
/**
* 导出EXCEL
* 导入Excel方法
*/
public function import(){
require_once(VENDOR_PATH .'/PHPExcel-1.8/Classes/PHPExcel.php');
if($this->request->isPost()){
$apply_file = $this->request->file('file');
if (!$apply_file) {
$this->error('请上传数据文件');
}
$info = $apply_file->validate(['size'=>1567800,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . DS . 'public/uploads'. DS .'excel');
$excelname=$info->getSaveName();
$filePath = ROOT_PATH . DS . 'public/uploads/excel' . DS . $excelname;
if (!is_file($filePath)) {
$this->error('找不到数据文件，请重新上传');
}
$PHPReader = new \PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new \PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new \PHPExcel_Reader_CSV();
if (!$PHPReader->canRead($filePath)) {
$this->error('文件类型不合法');
}
}
}
$excel = $PHPReader->load($filePath, $encode = 'utf-8');
$sheet = $excel->getSheet(0);
$row_num = $sheet->getHighestRow();//获取总行数
$col_num = $sheet->getHighestColumn();//获取总列数
$arr = array('A','B','C','D','E','F','G');
$res_arr = array();// 一次读取一列
for ($row = 2; $row <= $row_num; $row++) {
$row_arr = array();
for ($column = 0; $arr[$column] != 'G'; $column++) {
$val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
$row_arr[] = $val;
}
$res_arr[] = $row_arr;
}
$result = [];
foreach($res_arr as $k => $v){
// $result[$k]['id'] = $v[0];
$result[$k]['title'] = $v[1];
$result[$k]['option'] = $v[2];
$result[$k]['A'] = $v[3];
$result[$k]['B'] = $v[4];
$result[$k]['C'] = $v[5];
}
$success = [];
$error = []; Db::startTrans();
try{
foreach ($result as $key => $v) {
$res = db('staff_info')->lock(true)->insert($v);
if($res){
array_push($success,$v);
}else{
array_push($error,$v);
}
}
Db::commit();
}catch(\Exception $e){
Db::rollback();
} $data = [
'success'=>$success,
'error'=>$error
];
$this->success('一共'.count($result).'条数据，成功导入'.count($success).'条数据，导入失败'.count($error).'条数据',$data);
}
}
发表评论 取消回复