phpoffice php操作excel表格的导入和导出
https://packagist.org/packages/phpoffice/phpexcel
使用：
composer require phpoffice/phpexcel
控制器引入
//引入phpoffice use PHPExcel; use PHPExcel_IOFactory;
导出代码：
//execl模板下载
public function template_download()
{
$objExcel = new PHPExcel();
$objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objActSheet = $objExcel->getActiveSheet(0);
$objActSheet->setTitle('会员批量导入模板'); //设置excel的标题
$objActSheet->setCellValue('A1', '用户id');
$objActSheet->setCellValue('B1', '昵称');
$objActSheet->setCellValue('C1', '手机号');
$baseRow = 2; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
//默认数据
$explame_data = array(
array(
'user_id' => '1',
'nickname' => '小明',
'phone' => '15012345678',
),
);
foreach ($explame_data as $key => $value) {
$i = $baseRow + $key;
$objExcel->getActiveSheet()->setCellValue('A' . $i, $value['user_id']);
$objExcel->getActiveSheet()->setCellValue('B' . $i, $value['nickname']);
$objExcel->getActiveSheet()->setCellValue('C' . $i, $value['phone']);
}
$objExcel->setActiveSheetIndex(0);
//4、输出
$objExcel->setActiveSheetIndex();
header('Content-Type: applicationnd.ms-excel');
$time = date('Y-m-d');
header("Content-Disposition: attachment;filename=会员批量导入模板" . $time . ".xls");
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
导入代码：
public function import_batch_send()
{
header("content-type:text/html;charset=utf-8");
//上传excel文件
$file = request()->file('file');
//将文件保存到public/uploads目录下面
$info = $file->validate(['size' => 1048576, 'ext' => 'xls,xlsx'])->move('./uploads');
if ($info) {
//获取上传到后台的文件名
$fileName = $info->getSaveName();
//获取文件路径
$filePath = Env::get('root_path') . 'public' . DIRECTORY_SEPARATOR . 'uploads' . DIRECTORY_SEPARATOR . $fileName;
//获取文件后缀
$suffix = $info->getExtension();
//判断哪种类型
if ($suffix == "xlsx") {
$reader = \PHPExcel_IOFactory::createReader('Excel2007');
} else {
$reader = PHPExcel_IOFactory::createReader('Excel5');
}
} else {
return json(['status' => '1', 'message' => '文件过大或格式不正确导致上传失败-_-!']);
}
//载入excel文件
$excel = $reader->load($filePath, $encode = 'utf-8');
//读取第一张表
$sheet = $excel->getSheet(0);
//获取总行数
$row_num = $sheet->getHighestRow();
//获取总列数
$col_num = $sheet->getHighestColumn();
$import_data = []; //数组形式获取表格数据
for ($i = 2; $i <= $row_num; $i++) {
$import_data[$i]['nickname'] = $sheet->getCell("B" . $i)->getValue();
$import_data[$i]['phone'] = $sheet->getCell("C" . $i)->getValue();
}
if (empty($import_data)) {
return json(['status' => '1', 'message' => '数据解析失败']);
}
//校验手机号是否重复
$phone_array = array_column($import_data, 'phone');
$phone_ids = implode(',', $phone_array);
$result_phone = db('user')
->field('phone')
->where('phone', 'in', $phone_ids)
->select();
if (!empty($result_phone)) {
$result_phone_array = array_column($result_phone, 'phone');
$result_phone_ids = implode(',', $result_phone_array);
return json(['status' => '3', 'message' => '数据重复', 'result' => $result_phone_ids]);
}
//将数据保存到数据库
$res = db('user')->insertAll($import_data);
if ($res) {
return json(['status' => '2', 'message' => '导入成功']);
} else {
return json(['status' => '1', 'message' => '提交失败，请刷新重试']);
}
}
附征前端代码
<!DOCTYPE html>
<html>
<head>
<title>导入</title>
</head>
<body>
<form action="{:url('xxxx')}" enctype="multipart/form-data" method="post" >
上传文件:<input type="file" name="excel">
<input type='hidden' name="competition_id" value="{$competition_id}"/>
<input type='hidden' name="group_id" value="{$group_id}"/>
<input type="submit" value="提交文件">
</form>
<script src="http://libs.baidu.com/jquery/2.1.4/jquery.min.js"></script>
</body>
</html>
public function imports()
{
set_time_limit(0);
if ($file = request()->file('excel')) {
$data1 = $this->request->post();
$info = $file->validate(['ext' => 'xls,xlsx,csv'])->move('../public/uploads/files/');
if ($info) {
$filename = $info->getFilename();
$exts = explode('.', $filename)[1];
$file_root = ROOT_PATH . '/public/uploads/files/' . date('Ymd') . '/' . $filename;
header("content-type:text/html; charset=utf-8");
$file_root = iconv('utf-8', 'gbk', $file_root);
if (file_exists($file_root)) {//如果文件存在
if ($exts == 'xls') {
$PHPReader = new \PHPExcel_Reader_Excel5();
} else if ($exts == 'xlsx') {
$PHPReader = new \PHPExcel_Reader_Excel2007();
}
$PHPExcel = $PHPReader->load($file_root);
$sheet = $PHPExcel->getActiveSheet(0);//获得sheet
$highestRow = $sheet->getHighestRow(); // 取得共有数据数
$data = $sheet->toArray();
$limit = 2;
$rows = ceil($highestRow / $limit);
for ($r = 0; $r < $rows; $r++) {
$start = $r * $limit;
for ($i = $start; $i < $limit + $start; $i++) {
if ($i == 0 && $r == 0) {
//第一行不导入
continue;
}
if ($data[$i][0] == '') {
continue;
}
$betrayal = Db::name('competition_buy_log')
->where('competition_id', $data['competition_id'])
->max('betrayal');
if ($betrayal) {
++$betrayal;
} else {
$betrayal = 1;
}
// 导入人员
$order_no = get_order_sn('DRSH');
$add["order_sn"]=$order_no;
$add["betrayal"]=$betrayal;
$add["competition_id"]=$data1['competition_id'];
$add["group_id"]=$data1['group_id'];
// 代表队
$delegation = $data[$i][0];
$add['delegation'] = $delegation;
$add['pay_money'] = 0;
$add['is_pay'] =1;
$add['coach_name'] = $data[$i][1];
$add['coach_mobile'] =$data[$i][2];
$add['coach_email'] =$data[$i][3];
$add['is_Import'] =1;
$add['user_name'] =$data[$i][4];
$add['mobile'] =$data[$i][5];
$add['card_num'] =$data[$i][6];
$user_info = Db::name('user')->where("mobile",$data[$i][5])->find();
if($user_info){
$add['uid']=$user_info['id'];
CompetitionBuyLog::insert($add);
}
}
}
} else {
// $this->error("文件不存在");
return array("resultcode" => -5, "resultmsg" => "文件不存在", "data" => null);
}
} else {
return array("resultcode" => -5, "resultmsg" => "文件不存在", "data" => null);
}
unset($info);
unlink($file_root);
$this->success('导入成功');
die;
} else {
$this->error('上传失败,请上传指定格式的文件');
}
}
