联系官方销售客服
1835022288
028-61286886
基于PHPoffice类去做excel表格数据导入导出,可以导出图片到excel,本人亲测一次性导入6000+条简单数据没什么问题,很快,在多就没试过
前端效果如下
导入,要点击确定才会进行导入数据
导出,如果不选择数据会提示选择数据然后进行导出
第一步,下载上传的附件并将文件解压到你的项目中,具体目录如下图所示。入不放心我上传的附件,可自行安装composer进行下载地址如下
https://phpspreadsheet.readthedocs.io/en/stable/
PS:在导入Excel的时候,要使用到文件上传接口,所以要现在后台创建一个文件上传字段,完成之后,要看一下上传接口对应的参数拿过来,然后替换我对应文件上传接口中的参数,这个文件上传字段不要删除,如果不想要在后台跟前台显示可以选择隐藏。
这是我使用的文件上传接口案列
http://census.cc/index.php?s=api&c=file&siteid=1&m=upload&p=020424c201c395dba90596754de3d341&fid=45
如何查看创建字段的上传接口以及参数
进入到后台添加页面,点f12进入到network中然后进行上传文件,上传之后,接口会在这里显示,打开之后,拿到对应的参数,并填入到,我写的ajax文件上传接口中对应的参数即可
第二步,在页面中添加导入导出按钮,我是加在对应模块的头部
代码如下:
前端js部分代码:
<script type="text/javascript"> if (App.isAngularJsApp() === false) { jQuery(document).ready(function() { if (jQuery().datepicker) { $('.date-picker').datepicker({ rtl: App.isRTL(), orientation: "left", autoclose: true }); } }); } function dr_module_delete() { var url = '{dr_url(APP_DIR.'/home/del')}&is_ajax=1'; var width = '50%'; var height = '60%'; if (is_mobile_cms == 1) { width = height = '90%'; } var data = $("#myform").serialize(); layer.open({ type: 2, title: '{dr_lang('删除确认')}', shadeClose: true, shade: 0, area: [width, height], btn: [lang['ok']], yes: function(index, layero){ var body = layer.getChildFrame('body', index); $(body).find('.form-group').removeClass('has-error'); // 延迟加载 var loading = layer.load(2, { shade: [0.3,'#fff'], //0.1透明度的白色背景 time: 5000 }); $.ajax({type: "POST",dataType:"json", url: url, data: $(body).find('#myform').serialize(), success: function(json) { layer.close(loading); if (json.code == 1) { layer.close(index); setTimeout("window.location.reload(true)", 2000) } else { $(body).find('#dr_row_'+json.data.field).addClass('has-error'); } dr_tips(json.code, json.msg); return false; }, error: function(HttpRequest, ajaxOptions, thrownError) { dr_ajax_alert_error(HttpRequest, ajaxOptions, thrownError); } }); return false; }, content: url+'&'+data }); } function dr_module_excelExport() { var arr = $("#myform").serializeArray(); if (arr.length <= 2){ layer.msg('请选择数据再进行导出操作'); return; } var info = []; arr.forEach(function (data){ if (data.name == 'ids[]') { info.push(data.value); } }); $.ajax({ type: "POST", dataType:"json", url: "{dr_url($uriprefix.'/dataExport')}", data: { 'data': info, {csrf_token()} : "{csrf_hash()}" }, success: function (data) { layer.open({ type: 1, title: '{dr_lang('是否导出')}', id: 'LAY_layuipro', shadeClose: true, shade: 0, btn: ['确定','否'], btnAlign: 'c' ,//按钮居中 yes: function(index, layero){ if (layero) { self.location.href="{dr_url($uriprefix.'/excelExport')}&id="+data; layer.close(index); }else { return false; } } }); } }); } </script> 这段js放到最底部,记得要引用js文件 <script type="text/javascript" src="{THEME_PATH}from/layui/layui.js"></script> <script> layui.use('upload', function(){ var $ = layui.jquery ,upload = layui.upload; upload.render({ elem: '#excelImport', url: '/index.php?s=api&c=file&siteid='+{SITE_ID}+'&m=upload&p=020424c201c395dba90596754de3d341&fid=45', accept: 'file', exts: 'xls|xlsx', field: "file_data", data: { file_data: function(){ return $('#excelImport').val(); }, {csrf_token()} : "{csrf_hash()}" }, done: function(res){ if(res.code == 1){ layer.open({ type: 1, title: '{dr_lang('是否导入')}', id: 'import', shadeClose: true, shade: 0, btn: ['确定','否'], btnAlign: 'c' ,//按钮居中 yes: function(index, layero){ if (layero) { $.ajax({ url: "{dr_url($uriprefix.'/excelImport')}", type: "POST", dataType: "json", data: { url: res.info.url, id: res.id, type: res.info.ext, {csrf_token()} : "{csrf_hash()}" }, success: function(data){ if (data.code == 1){ layer.msg('<span style="color: #fff">信息导入成功!</span>'); } }, error: function(data){ if (data.code == 0){ layer.msg('<span style="color: #fff">信息导入失败!请从第'+data.total+'条开始导入!</span>'); } } }); layer.close(index); }else { return false; } } }); }else{ return layer.msg('<span style="color: #fff">上传失败,请重试!</span>'); } } }); }); </script>
html部分
<ul class="page-breadcrumb"> {$menu} <li> <link type="text/css" rel="stylesheet" href="{THEME_PATH}from/layui/css/layui.css" /> <div class="layui-upload"> <button type="button" style="background: #fff; color: #888; padding: 0 0" class="layui-btn layui-btn-normal" id="excelImport"><i class="fa fa-plus"></i>导入</button> <i style="color: #DDDDDD" class="fa fa-circle"></i> </div> </li> <li> <a onclick="dr_module_excelExport()" id="excelExport" class=""> <i class="fa fa-minus"></i> 导出</a> <i class="fa fa-circle"></i> </li> </ul>
php部分
<?php namespace Phpcmf\Controllers\Admin; require 'vendor/autoload.php'; //写入口文件 use PhpOffice\PhpSpreadsheet\Spreadsheet; //引入类,这三个都要引入 use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use PhpOffice\PhpSpreadsheet\Worksheet\Drawing; /** * 二次开发时可以修改本文件,不影响升级覆盖 */ class Home extends \Phpcmf\Admin\Module { //获取要导出的数据id并返回给ajax public function dataExport(){ $arr = $_POST['data']; $data = implode(',',$arr); return json_encode($data); } //数据导出 public function excelExport() { $id = $_GET['id']; $line = 0; $spreadsheet = new Spreadsheet(); $image = new Drawing(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValueByColumnAndRow(1, 1, '户主关系');//这些数据对应excel的列信息 $sheet->setCellValueByColumnAndRow(2, 1, '姓名'); $sheet->setCellValueByColumnAndRow(3, 1, '证件名称'); $sheet->setCellValueByColumnAndRow(4, 1, '证件号码'); $sheet->setCellValueByColumnAndRow(5, 1, '籍贯'); $sheet->setCellValueByColumnAndRow(6, 1, '年龄'); $sheet->setCellValueByColumnAndRow(7, 1, '性别'); $sheet->setCellValueByColumnAndRow(8, 1, '户籍地'); $sheet->setCellValueByColumnAndRow(9, 1, '手机座机'); $sheet->setCellValueByColumnAndRow(10, 1, '座机电话'); $sheet->setCellValueByColumnAndRow(11, 1, '出生时间'); $sheet->setCellValueByColumnAndRow(12, 1, '死亡时间'); $sheet->setCellValueByColumnAndRow(13, 1, '是否低保'); $sheet->setCellValueByColumnAndRow(14, 1, '是否享受生活救助'); $sheet->setCellValueByColumnAndRow(15, 1, '是否暂住'); $sheet->setCellValueByColumnAndRow(16, 1, '暂住地'); $sheet->setCellValueByColumnAndRow(17, 1, '是否流动人口'); $sheet->setCellValueByColumnAndRow(18, 1, '是否矫正人员'); $sheet->setCellValueByColumnAndRow(19, 1, '是否涉毒人员'); $sheet->setCellValueByColumnAndRow(20, 1, '是否残疾'); $sheet->setCellValueByColumnAndRow(21, 1, '用人单位'); $sheet->setCellValueByColumnAndRow(22, 1, '社保信息'); $sheet->setCellValueByColumnAndRow(23, 1, '个人健康状况'); $sheet->setCellValueByColumnAndRow(24, 1, '实际居住地'); $sheet->setCellValueByColumnAndRow(25, 1, '实际居住详细地址'); $sheet->setCellValueByColumnAndRow(26, 1, '照片'); $styleArray = [ 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, ], ]; //查询并获取要导出的数据 $data = \Phpcmf\Service::M()->db->table(SITE_ID.'_census') ->select(' relation,name,head,card_name,card_id,hometown,age,sex,domicile,phone, tel,birthday,deadtime,is_guarantee,is_assistance,is_residence,temporary, population,correction,is_poison,is_deformity,employer,shebaoxinxi,health, residence,address ') ->where('id in('.$id.')') ->get()->getResultArray(); $relation = dr_field_options(16); $card_name = dr_field_options(19); $sex = dr_field_options(23); $is_guarantee = dr_field_options(30); $is_assistance = dr_field_options(31); $is_residence = dr_field_options(32); $population = dr_field_options(34); $correction = dr_field_options(35); $is_poison = dr_field_options(36); $is_deformity = dr_field_options(37); $shebaoxinxi = dr_field_options(39); $health = dr_field_options(40); //通过循环去匹配数据 for ($i = 0; $i < count($data); $i++) { $line = $i + 2;//代表从第几行开始 //$sheet->setCellValueByColumnAndRow(1)中的1,2,3代表上面的那一列 $sheet->setCellValueByColumnAndRow(1, $line, $relation[$data[$i]['relation']]); $sheet->setCellValueByColumnAndRow(2, $line, $data[$i]['name']); $sheet->setCellValueByColumnAndRow(3, $line, $card_name[$data[$i]['card_name']]); $sheet->setCellValueByColumnAndRow(4, $line, $data[$i]['card_id']); $sheet->setCellValueByColumnAndRow(5, $line, $data[$i]['hometown']); $sheet->setCellValueByColumnAndRow(6, $line, $data[$i]['age']); $sheet->setCellValueByColumnAndRow(7, $line, $sex[$data[$i]['sex']]); $sheet->setCellValueByColumnAndRow(8, $line, $data[$i]['domicile']); $sheet->setCellValueByColumnAndRow(9, $line, $data[$i]['phone']); $sheet->setCellValueByColumnAndRow(10, $line, $data[$i]['tel']); $sheet->setCellValueByColumnAndRow(11, $line, dr_date($data[$i]['birthday'],'Y-m-m H:i:s')); $sheet->setCellValueByColumnAndRow(12, $line, dr_date($data[$i]['deadtime'],'Y-m-m H:i:s')); $sheet->setCellValueByColumnAndRow(13, $line, $is_guarantee[$data[$i]['is_guarantee']]); $sheet->setCellValueByColumnAndRow(14, $line, $is_assistance[$data[$i]['is_assistance']]); $sheet->setCellValueByColumnAndRow(15, $line, $is_residence[$data[$i]['is_residence']]); $sheet->setCellValueByColumnAndRow(16, $line, $data[$i]['temporary']); $sheet->setCellValueByColumnAndRow(17, $line, $population[$data[$i]['population']]); $sheet->setCellValueByColumnAndRow(18, $line, $correction[$data[$i]['correction']]); $sheet->setCellValueByColumnAndRow(19, $line, $is_poison[$data[$i]['is_poison']]); $sheet->setCellValueByColumnAndRow(20, $line, $is_deformity[$data[$i]['is_deformity']]); $sheet->setCellValueByColumnAndRow(21, $line, $data[$i]['employer']); $sheet->setCellValueByColumnAndRow(22, $line, $shebaoxinxi[$data[$i]['shebaoxinxi']]); $sheet->setCellValueByColumnAndRow(23, $line, $health[$data[$i]['health']]); $sheet->setCellValueByColumnAndRow(24, $line, dr_linkagepos('address', $data[$i]['residence'], ' - ')); $sheet->setCellValueByColumnAndRow(25, $line, $data[$i]['address']); $url = parse_url(dr_thumb($data[$i]['head']));//获取图片对应的地址 //使用图片导出类 $drawing[$line] = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing[$line]->setName('头像'); $drawing[$line]->setDescription('头像'); $drawing[$line]->setPath('.'.$url['path']); $drawing[$line]->setWidth(100); $drawing[$line]->setHeight(100); $drawing[$line]->setCoordinates('Z'.$line);//z代表某一咧 $drawing[$line]->setOffsetX(0); $drawing[$line]->setOffsetY(0); $drawing[$line]->setWorksheet($spreadsheet->getActiveSheet()); $spreadsheet->getActiveSheet()->getRowDimension($line)->setRowHeight(100);//设置高度 } $filename = '户籍信息管理表('.date('YmdHis',SYS_TIME).').xlsx';//导出表格名称 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="'.$filename.'"'); header('Cache-Control: max-age=0'); //设置样式 $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(12); $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(12); $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15); $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(25); $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(15); $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(5); $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(5); $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15); $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15); $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(15); $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(18); $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(18); $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(9); $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(17); $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(9); $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(35); $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(13); $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(13); $spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(13); $spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(9); $spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(35); $spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(85); $spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(13); $spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(35); $spreadsheet->getActiveSheet()->getColumnDimension('Y')->setWidth(35); $spreadsheet->getActiveSheet()->getColumnDimension('Z')->setWidth(26); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); } //表格导入 public function excelImport() { $excel = parse_url($_POST['url']); $type = ucfirst($_POST['type']); $fileid = $_POST['id']; $this->db = \Config\Database::connect('default'); $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($type); $reader->setReadDataOnly(TRUE); $spreadsheet = $reader->load('.'.$excel['path']); //载入excel表格 $worksheet = $spreadsheet->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); // 总行数 $highestColumn = $worksheet->getHighestColumn(); // 总列数 $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5 $lines = $highestRow - 1; if ($lines <= 0) { exit('Excel表格中没有数据'); } $sql = "INSERT INTO 'dr_".SITE_ID."_census' ('relation','name','head','card_name','card_id','hometown','age','sex','domicile','phone', 'tel','birthday','deadtime','is_guarantee','is_assistance','is_residence','temporary', 'population','correction','is_poison','is_deformity','employer','shebaoxinxi','health', 'residence','address') VALUES "; $data = []; $relation = dr_field_options(16); $card_name = dr_field_options(19); $sex = dr_field_options(23); $is_guarantee = dr_field_options(30); $is_assistance = dr_field_options(31); $is_residence = dr_field_options(32); $population = dr_field_options(34); $correction = dr_field_options(35); $is_poison = dr_field_options(36); $is_deformity = dr_field_options(37); $shebaoxinxi = dr_field_options(39); $health = dr_field_options(40); //获取表格中的数据 for ($row = 0; $row < $lines; $row++) { $data[$row]['url'] = $worksheet->getCellByColumnAndRow(1, $row+2)->getValue(); foreach ($relation as $key => $re) { if ($re === $worksheet->getCellByColumnAndRow(2, $row+2)->getValue()) { $data[$row]['relation'] = $key; } } $data[$row]['name'] = $worksheet->getCellByColumnAndRow(3, $row+2)->getValue(); foreach ($card_name as $key => $ca) { if ($ca === $worksheet->getCellByColumnAndRow(4, $row+2)->getValue()) { $data[$row]['card_name'] = $key; } } $data[$row]['card_id'] = $worksheet->getCellByColumnAndRow(5, $row+2)->getValue(); $data[$row]['hometown'] = $worksheet->getCellByColumnAndRow(6, $row+2)->getValue(); $data[$row]['age'] = $worksheet->getCellByColumnAndRow(7, $row+2)->getValue(); foreach ($sex as $key => $se) { if ($se === $worksheet->getCellByColumnAndRow(8, $row+2)->getValue()) { $data[$row]['sex'] = $key; } } $data[$row]['domicile'] = $worksheet->getCellByColumnAndRow(9, $row+2)->getValue(); $data[$row]['phone'] = $worksheet->getCellByColumnAndRow(10, $row+2)->getValue(); $data[$row]['tel'] = $worksheet->getCellByColumnAndRow(11, $row+2)->getValue(); $data[$row]['birthday'] = strtotime($worksheet->getCellByColumnAndRow(12, $row+2)->getValue()); $data[$row]['deadtime'] = strtotime($worksheet->getCellByColumnAndRow(13, $row+2)->getValue()); foreach ($is_guarantee as $key => $is_gu) { if ($is_gu === $worksheet->getCellByColumnAndRow(14, $row+2)->getValue()) { $data[$row]['is_guarantee'] = $key; } } foreach ($is_assistance as $key => $is_as) { if ($is_as === $worksheet->getCellByColumnAndRow(15, $row+2)->getValue()) { $data[$row]['is_assistance'] = $key; } } foreach ($is_residence as $key => $is_re) { if ($is_re === $worksheet->getCellByColumnAndRow(16, $row+2)->getValue()) { $data[$row]['is_residence'] = $key; } } $data[$row]['temporary'] = $worksheet->getCellByColumnAndRow(17, $row+2)->getValue(); foreach ($population as $key => $po) { if ($po === $worksheet->getCellByColumnAndRow(18, $row+2)->getValue()) { $data[$row]['population'] = $key; } } foreach ($correction as $key => $co) { if ($co === $worksheet->getCellByColumnAndRow(19, $row+2)->getValue()) { $data[$row]['correction'] = $key; } } foreach ($is_poison as $key => $is_po) { if ($is_po === $worksheet->getCellByColumnAndRow(20, $row+2)->getValue()) { $data[$row]['is_poison'] = $key; } } foreach ($is_deformity as $key => $is_de) { if ($is_de === $worksheet->getCellByColumnAndRow(21, $row+2)->getValue()) { $data[$row]['is_deformity'] = $key; } } $data[$row]['employer'] = $worksheet->getCellByColumnAndRow(22, $row+2)->getValue(); foreach ($shebaoxinxi as $key => $she) { if ($she === $worksheet->getCellByColumnAndRow(23, $row+2)->getValue()) { $data[$row]['shebaoxinxi'] = $key; } } foreach ($health as $key => $he) { if ($he === $worksheet->getCellByColumnAndRow(24, $row+2)->getValue()) { $data[$row]['health'] = $key; } } $address = explode(' - ',$worksheet->getCellByColumnAndRow(25, $row+2)->getValue()); foreach ($address as $add) {} $area = \Phpcmf\Service::M()->db->table('linkage_data_1')->where('name', $add)->get()->getResultArray(); $data[$row]['residence'] = $area[0]['id']; $data[$row]['address'] = $worksheet->getCellByColumnAndRow(26, $row+2)->getValue(); $data[$row]['head'] = $worksheet->getCellByColumnAndRow(27, $row+2)->getValue(); $data[$row]['catid'] = 1; $data[$row]['inputip'] = \Phpcmf\Service::L('input')->ip_address(); $data[$row]['inputtime'] = SYS_TIME; $data[$row]['updatetime'] = SYS_TIME; } //将从Excel表格中获取到的数据插入到数据库中 foreach ($data as $key => $da){ if ($da['url'] == null || $da['url'] == ' '){ $da['fid'] = 0; $info = $this->db->table(SITE_ID.'_census')->insert($da); if ($info){ $fid = $this->db->insertID(); $card_id = $da['card_id']; if ($info) { $code = 1; }else{ $code = 0; $total = $key+2; } } }else{ if ($da['url'] == $card_id){ $da['fid'] = $fid; $da['url'] = ' '; $info = $this->db->table(SITE_ID.'_census')->insert($da); if ($info) { $code = 1; }else{ $code = 0; $total = $key+2; } } } } if ($info) { \Phpcmf\Service::M('Attachment')->file_delete($this->member['id'],$fileid);//删除附件,如果导入成功之后不想删除附件,删除这句话即可 return json_encode(['code'=>$code]); }else { \Phpcmf\Service::M('Attachment')->file_delete($this->member['id'],$fileid); return json_encode(['code'=>$code],['total'=>$total]); } } }
火车头采集器
火车头采集器,即装即用,支持所有模块,多文件采集,默认news模块,可以发布独立模块以及...
比如这样的方式,pos3就不一样的话就要改这样的方式就可以了丛林灰太狼
丛林灰太狼
哈,这个牛逼,看下