public function excelExporNameList(){
// https://www.cnblogs.com/windyet/articles/9711044.html
// /manage/BlgTb/test/number/2123/record_id/[1]/app_id/2123/user_id/201210/group_id/372/dept_id/905238/rid//export_type/word/params/{"page":1,"nums":10,"order_field":"id","order_type":"DESC"}/exportType/word/batchType/2
$user_id = input('user_id', 0);
$group_id = input('group_id', 0);
$dept_id = input('dept_id', 0);
$record_id = json_decode(input('record_id'), true);
$params = json_decode(input('params'), true);
$batchType = input('batchType', 1);
//获取app
$app_id = input('app_id', 0);
if ($app_id==0) {
var_dump('请传入app_id');
exit();
}
$app=db('comm_app')->where('id',$app_id)->find();
if (empty($app)) {
var_dump('微应用appid不存在');
exit();
}
//获取data
$where=[
'dept_id'=>$dept_id
];
if ($batchType==1) {
$where['id']=['in',$record_id];
}
$data=db($app['table_en'])->where($where)->order($params['order_field'].' '.$params['order_type'])->select();
//// 创建文档
$table_name='推荐参加中级专业技术职务任职资格评审人员名单';
$newExcel = new Spreadsheet(); //创建一个新的excel文档
$objSheet = $newExcel->getActiveSheet(); //获取当前操作sheet的对象
//设置当前sheet的标题
$objSheet->setTitle($table_name);
//默认列宽行高
$objSheet->getDefaultColumnDimension()->setWidth(10.4);
$objSheet->getDefaultRowDimension()->setRowHeight(15.6);
//设置第一列宽
$objSheet->getColumnDimension('A')->setWidth(4.1);
////设置总标题
$objSheet->getRowDimension('1')->setRowHeight(20.4);
$objSheet->setCellValue('A1',$table_name);
//合并单元格
$objSheet->mergeCells('A1:O1');
//设置总标题样式
$styleArray = [
'alignment' => [
'horizontal' => PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER,
'vertical' => PhpOfficePhpSpreadsheetStyleAlignment::VERTICAL_CENTER,
'wrapText' => true,
],
'font' => [
'name' => '宋体',
'bold' => true,
'size'=>16
],
];
$objSheet->getStyle('A1:O1')->applyFromArray($styleArray);
////设置第二行
$objSheet->mergeCells('A2:B2');
$objSheet->setCellValue('A2','单位:');
////设置列标题
$column='4';
$objSheet->getRowDimension($column)->setRowHeight(30);
$objSheet->setCellValue('A'.$column, '排序')
->setCellValue('B'.$column, '工号')
->setCellValue('C'.$column, '姓名')
->setCellValue('D'.$column, '申报专业技术职务')
->setCellValue('E'.$column, '出生年月')
->setCellValue('F'.$column, '参加工作年月')
->setCellValue('G'.$column, '最高学历')
->setCellValue('H'.$column, '毕业时间')
->setCellValue('I'.$column, '学制')
->setCellValue('J'.$column, '最高学位')
->setCellValue('K'.$column, '现专业技术职务')
->setCellValue('L'.$column, '评聘时间')
->setCellValue('M'.$column, '申报系列')
->setCellValue('N'.$column, '外语水平')
->setCellValue('O'.$column, '计算机应用能力');
$styleArray = [
'alignment' => [
'horizontal' => PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER,
'vertical' => PhpOfficePhpSpreadsheetStyleAlignment::VERTICAL_CENTER,
'wrapText' => true,
],
'font' => [
'name' => '宋体',
'bold' => false,
'size'=>10
],
'borders' => [
'allBorders' => [
'borderStyle' => PhpOfficePhpSpreadsheetStyleBorder::BORDER_THIN,
'color' => [
]
],
],
];
$objSheet->getStyle('A'.$column.':O'.$column)->applyFromArray($styleArray);
//填充数据
foreach ($data as $k => $v) {
$column=5+$k;
$objSheet->getRowDimension($column)->setRowHeight(30);
$objSheet->setCellValue('A'.$column, $v['id'])
->setCellValue('B'.$column, '工号')
->setCellValue('C'.$column, $v['xm'])
->setCellValue('D'.$column, $v['sbzyjszw'])
->setCellValue('E'.$column, $v['csny'])
->setCellValue('F'.$column, '参加工作年月')
->setCellValue('G'.$column, $v['zgxl'])
->setCellValue('H'.$column, $v['byrq'])
->setCellValue('I'.$column, $v['xz'])
->setCellValue('J'.$column, $v['zgxw'])
->setCellValue('K'.$column, $v['xzyjszw'])
->setCellValue('L'.$column, $v['ppsj'])
->setCellValue('M'.$column, $v['sbzyjszwxl'])
->setCellValue('N'.$column, $v['wysp'])
->setCellValue('O'.$column, $v['jsjyynl']);
$styleArray = [
'alignment' => [
'horizontal' => PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER,
'vertical' => PhpOfficePhpSpreadsheetStyleAlignment::VERTICAL_CENTER,
'wrapText' => true,
],
'font' => [
'name' => '宋体',
'bold' => false,
'size'=>10
],
'borders' => [
'allBorders' => [
'borderStyle' => PhpOfficePhpSpreadsheetStyleBorder::BORDER_THIN,
'color' => [
]
],
],
];
$objSheet->getStyle('A'.$column.':O'.$column)->applyFromArray($styleArray);
}
////说明1
$column+=2;
$objSheet->mergeCells('A'.$column.':L'.$column);
$objSheet->setCellValue('A'.$column,'说明：1 此表是各院、部处级单位正式推荐名单，表内各项，应核实无误。');
////说明2
$column+=1;
$objSheet->mergeCells('A'.$column.':L'.$column);
$objSheet->setCellValue('A'.$column,' 2 申报系列，注明教师、科研、工程、实验、教育管理、图书资料、档案、出版、卫生、会计和普教教师。');
////盖章行
$column+=2;
$objSheet->mergeCells('A'.$column.':B'.$column);
$objSheet->setCellValue('A'.$column,'单位（公章）：');
$objSheet->mergeCells('G'.$column.':H'.$column);
$objSheet->setCellValue('G'.$column,'单位负责人（签字）');
$objSheet->setCellValue('K'.$column,'登记人');
$objSheet->setCellValue('N'.$column,'日期:');
$this->downloadExcel($newExcel,$table_name, 'Xls');
}