1、批量导入
public function importExcel(){
$authority = $this->getUserAuthority('order_input', 'batch_import');
if ($authority['code'] != 0) {
return json($authority);
}
$file = request()->file('files');
if(empty($file)){
return printMsg(-1, "请上传文件");
}
// 移动到框架应用根目录/uploads/ 目录下
//$info = $file->validate(['size'=>(100*1024*1024),'ext'=>'xls,xlsx'])->move('static/uploads/');
$ext_arr = ['xlsx'];
//接收文件
$file_type = $file->getInfo()['name'];
//获取后缀
$ext = explode('.', $file_type);
$ext = end($ext);
//判断时候是限制的文件格式
if (!in_array($ext, $ext_arr)) {
return printMsg(-1,'文件格式有误,只支持Excel2007，xlsx后缀的文件');
}
//实例化类库 // 读取excel文件
require_once './../extend/PHPExcel/IOFactory.php';
require_once './../extend/PHPExcel/PHPExcel.php';
if ($ext == 'xls') {
$PHPReader = new \PHPExcel_Reader_Excel5();
} elseif ($ext == 'xlsx') {
$PHPReader = new \PHPExcel_Reader_Excel2007();
}
$tmp_name = $file->getInfo()['tmp_name'];
$objData = $PHPReader->load($tmp_name, $encode = 'utf-8');
$excel_array = $objData->getsheet(0)->toArray();
//删除表格第一行
unset($excel_array[0]);
$insertData = [];
$userInfo = $this->getTokenUser();
$date = date('Y-m-d H:i:s', $this->request->time());
if (count($excel_array) > 500){
return printMsg(-1,'批量导入行数不能大于500条');
}
//todo 线上对应的数据
$project_name_id = 13;
$source = '腾讯游戏';
$project_source_detail_id = 115;
$depargroup = 29;
$designer_uid = 1114;
Db::startTrans();
try {
foreach ($excel_array as $key => $value) {
$line = $key+1;
try {
$d2 = date("Y-m-d",($value[2] - 25569) * 86400);
$d3 = date("Y-m-d",($value[3] - 25569) * 86400);
$d5 = date("Ym",($value[5] - 25569) * 86400);
}catch (Exception $exception){
Db::rollback();
return printMsg(-1, "第".$line."行，日期格错误");
}
$line = $key+1;
if (empty($value[0])){
Db::rollback();
return printMsg(-1, "第".$line."行，单号不能为空");
}
if (empty($value[1])){
Db::rollback();
return printMsg(-1, "第".$line."行，需求名称不能为空");
}
if (!$this->isDate($d2)){
Db::rollback();
return printMsg(-1, "第".$line."行，需求开始日期格式不正确");
}
if (!$this->isDate($d3)){
Db::rollback();
return printMsg(-1, "第".$line."行，需求结束日期格式不正确");
}
if (!is_numeric($value[4])){
Db::rollback();
return printMsg(-1, "第".$line."行，价格格式不正确");
}
if (!$this->isDate2($d5)){
Db::rollback();
return printMsg(-1, "第".$line."行，需求归属日期格式不正确");
}
$achievementData = [
"type" => 1, #1:报价
"project_name" => $project_name_id,
"project_name_id" => $project_name_id,
"demand_name" => $value[1],
"demander" => '腾讯',
"start_time" => $d2,
"end_time" => $d3,
"user_id" => $userInfo['id'],
"create_time" => $date,
"update_time" => $date,
];
if (!empty($value[0])) {
$achievementData['sn'] = trim($value[0]);
}
$achiveId = $this->orderAchievement->insertGetId($achievementData);
$orderInputData = [];
$orderData = [
'source' => $source,
'project_source_detail_id' => $project_source_detail_id,
'terminal' => '报价',
'type' => '报价',
'nature' => '报价',
'text' => '报价',
'nitprice' => $value[4],
'workload' => 1,
'status' => 1, #1:报价
'create_time' => $date,
'update_time' => $date,
];
$orderId = $this->orderModel->insertGetId($orderData);
$orderInputData = [
'num' => 1,
'project' => $project_name_id,
'order_time' => $d5,
'personnel' => $userInfo['id'],
'order' => $orderId,
'depargroup' => $depargroup,
'type' => 1, #1:报价
'designer_uid' => $designer_uid,
'order_achievement_id' => $achiveId,
'create_time' => $date,
'update_time' => $date,
];
$this->orderInputModel->insertGetId($orderInputData);
$this->newLog('导入报价类');
}
Db::commit();
} catch (\Exception $e) {
// 回滚事务
Db::rollback();
throw new \Exception('导入失败');
return printMsg(self::myx_code + 10, "导入失败:" . $e->getMessage());
}
return printMsg(0, "导入成功");
}
private function isDate($str) {
$pattern = '/^\d{4}-\d{2}-\d{2}$/'; // 使用正则表达式匹配 YYYY-MM-DD 格式的日期
if (preg_match($pattern, $str)) {
return true;
} else {
return false;
}
}
private function isDate2($str) {
$pattern = '/^\d{4}\d{2}$/'; // 使用正则表达式匹配 YYYY-MM-DD 格式的日期
if (preg_match($pattern, $str)) {
return true;
} else {
return false;
}
}
2、批量导出
public function download(Request $request)
{
/*$authority = $this->getUserAuthority('order_input', 'download');
if ($authority['code'] != 0) {
return json($authority);
}
$res = controller('OrderAchievement')->index($request, true);*/
//return printMsg(0, "成功", $res);
$order = OrderModel::where('source', '腾讯游戏')->where('status', '0')->select()->toArray();
$title = [];
foreach ($order as $k => $v) {
if (empty($title[$v['terminal']])) {
$title[$v['terminal']] = [
$v['nature'] => [
$v['type'] => [$v['text']]
]
];
} else {
if (empty($title[$v['terminal']][$v['nature']])) {
$title[$v['terminal']][$v['nature']] = [
$v['type'] => [$v['text']]
];
} else {
if (empty($title[$v['terminal']][$v['nature']][$v['type']])) {
$title[$v['terminal']][$v['nature']][$v['type']] = [$v['text']];
} else {
$title[$v['terminal']][$v['nature']][$v['type']][] = $v['text'];
}
}
}
}
//return printMsg(0, "成功", $title);
//die();
$fileName = "需求订单";
require_once './../extend/PHPExcel/IOFactory.php';
require_once './../extend/PHPExcel/PHPExcel.php';
$obj = new \PHPExcel();
// 设置当前sheet
$obj->setActiveSheetIndex(0);
// 设置当前sheet的名称
$obj->getActiveSheet()->setTitle('需求订单');
// 列标
$cellKey = ['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'];
foreach (['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'] as $k => $v) {
foreach (['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'] as $sk => $sv) {
$cellKey[] = $v . $sv;
}
}
//处理表头标题
//$obj->getActiveSheet()->mergeCells('A1:' . $cellKey[(count($title) - 1) + 4] . '1');//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
$obj->setActiveSheetIndex(0)->setCellValue('A1', '需求订单列表(仅腾讯需求)');
$obj->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$obj->getActiveSheet()->getStyle('A1')->getFont()->setSize(11);
$obj->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$obj->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$leftNumber = 4; //左边位移数量
// 填充表头
$obj->getActiveSheet()
->setCellValue('A2', '来源')
->setCellValue('B2', '单号')
->setCellValue('C2', '项目名称')
->setCellValue('D2', '类型');
//合并单元格
$obj->getActiveSheet()->mergeCells('A2:A5');
$obj->getActiveSheet()->mergeCells('B2:B5');
$obj->getActiveSheet()->mergeCells('C2:C5');
$obj->getActiveSheet()->mergeCells('D2:D5');
//居中
$obj->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
->getActiveSheet()->getStyle('B2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
->getActiveSheet()->getStyle('C2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
->getActiveSheet()->getStyle('D2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$obj->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
->getActiveSheet()->getStyle('B2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
->getActiveSheet()->getStyle('C2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
->getActiveSheet()->getStyle('D2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$i = 0;
foreach ($title as $k => $v) {
$obj->getActiveSheet()->setCellValue(($cellKey[$i + $leftNumber]) . '2', $k);
$obj->getActiveSheet()->getStyle(($cellKey[$i + $leftNumber]) . '2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$i2 = $i;
foreach ($title[$k] as $kj => $vj) {
$i3 = $i;
$obj->getActiveSheet()->setCellValue(($cellKey[$i + $leftNumber]) . '3', $kj);
$obj->getActiveSheet()->getStyle(($cellKey[$i + $leftNumber]) . '3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
foreach ($title[$k][$kj] as $skj => $svj) {
$i4 = $i;
$obj->getActiveSheet()->setCellValue(($cellKey[$i + $leftNumber]) . '4', $skj);
$obj->getActiveSheet()->getStyle(($cellKey[$i + $leftNumber]) . '4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
foreach ($svj as $ik => $iv) {
$obj->getActiveSheet()->setCellValue(($cellKey[$i + $leftNumber]) . '5', $iv);
$obj->getActiveSheet()->getStyle(($cellKey[$i + $leftNumber]) . '5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$i++;
}
$obj->getActiveSheet()->mergeCells(($cellKey[$i4 + $leftNumber]) . '4' . ':' . ($cellKey[$i + 3]) . '4');
}
$obj->getActiveSheet()->mergeCells(($cellKey[$i3 + $leftNumber]) . '3' . ':' . ($cellKey[$i + 3]) . '3');
}
$obj->getActiveSheet()->mergeCells(($cellKey[$i2 + $leftNumber]) . '2' . ':' . ($cellKey[$i + 3]) . '2');
}
$obj->getActiveSheet()->mergeCells('A1:' . $cellKey[$i + 4] . '1');
/*$obj->getActiveSheet()->mergeCells('A2:A3');
$obj->getActiveSheet()->mergeCells('B3:B3');
$obj->getActiveSheet()->mergeCells('C3:C3');
$obj->getActiveSheet()->mergeCells('D3:D3');
$obj->getActiveSheet()->mergeCells('E2:G2');
$obj->getActiveSheet()->mergeCells('H2:J2');
$obj->getActiveSheet()
->setCellValue('E3', '首页')
->setCellValue('F3', '内页')
->setCellValue('G3', '排版')
->setCellValue('H3', '首页')
->setCellValue('I3', '内页')
->setCellValue('J3', '排版');*/
// 导出
ob_clean();
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx');
header('Cache-Control: max-age=1');
$objWriter = \IOFactory::createWriter($obj, 'Excel2007');
$objWriter->save('php://output');
}