企业🤖AI智能体构建引擎,智能编排和调试,一键部署,支持私有化部署方案 广告
##### 安装phpspreadsheet `composer require phpoffice/phpspreadsheet ` ##### 导出(php) ``` ~~~ use PhpOffice\PhpSpreadsheet\Cell\DataType; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Spreadsheet; ~~~ ``` ``` ~~~ public function index(){ if ($this->request->isAjax()){ $this->check_isPost(); $agency_sell_log = Db::name('agency_sell_log'); $start = $this->request->post("start", ''); $end = $this->request->post("end", ''); $where = []; $where['agency_user_id'] = $this->auth->id; $where['status'] = 3; if ($start > $end) { $where['trade_time'] = ['between time', [strtotime($end . ' 00:00:00'), strtotime($start . ' 23:59:59')]]; } else { $where['trade_time'] = ['between time', [strtotime($start . ' 00:00:00'), strtotime($end . ' 23:59:59')]]; } $adminList = $agency_sell_log ->field('id,money,trade_number,type,trade_time,agency_id') ->where($where)->select(); if (empty($adminList)) { $this->error(__('搜索的时间暂无成功的订单')); } $newExcel = new Spreadsheet(); //创建一个新的excel文档 $objSheet = $newExcel->getActiveSheet(); //获取当前操作sheet的对象 $objSheet->setTitle($this->userInfo['username'].'_打款记录'); //设置当前sheet的标题 //设置宽度为true,不然太窄了 $newExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $newExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $newExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $newExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); $newExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true); $newExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true); $newExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true); //设置第一栏的标题 $objSheet->setCellValue('A1', '商户会员名') ->setCellValue('B1', '购买积分') ->setCellValue('C1', '订单状态') ->setCellValue('D1', '单类型') ->setCellValue('E1', '交易号') ->setCellValue('F1', '交易时间') ->setCellValue('G1', '收款账号'); //第二行起,每一行的值,setCellValueExplicit是用来导出文本格式的。 //->setCellValueExplicit('C' . $k, $val['admin_password']PHPExcel_Cell_DataType::TYPE_STRING),可以用来导出数字不变格式 $agency = Db::name('agency'); foreach ($adminList as $k => $val) { $k = $k + 2; switch ($val['type']){ case 0:$type='会员单';break; case 1:$type='代付单';break; case 2:$type='商户会员单';break; case 3:$type='商户前两笔充值';break; default:$type='渠道代付';break; } $acc_no=$agency->where('id',$val['agency_id'])->value('acc_no'); $objSheet->setCellValue('A' . $k, $this->auth->username) ->setCellValue('B' . $k, $val['money']) ->setCellValue('C' . $k, '支付成功') ->setCellValue('D' . $k, $type) ->setCellValue('E' . $k, $val['trade_number']) ->setCellValue('F' . $k, date('Y-m-d H:i:s',$val['trade_time'])) ->setCellValueExplicit('G' . $k, $acc_no,DataType::TYPE_STRING); } $filename = $this->auth->username.'_'.$start.'__'.$end; $format = 'Xls'; // $format只能为 Xlsx 或 Xls if ($format == 'Xlsx') { header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); } elseif ($format == 'Xls') { header('Content-Type: application/vnd.ms-excel'); } header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format)); header('Cache-Control: max-age=0'); $objWriter = IOFactory::createWriter($newExcel, $format); $f=ROOT_PATH.'public/excel/'.$this->auth->username."/"; if (!is_dir($f)){ @mkdir($f,0777,true); } $save_name='excel/'. $this->auth->username . '/' . $filename . '.xlsx'; //保存本地 $objWriter->save(ROOT_PATH . 'public/' . $save_name); $data = [ 'down_url' => 'http://www.euback.com/'. $save_name ]; $this->success(__('Operation completed'), $data, 102); } } ~~~ ``` ##### 导入 ``` ~~~ use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\IOFactory; ~~~ ``` ``` ~~~ public function import(){ $file = $this->request->request('file'); if (!$file) { $this->error(__('Parameter %s can not be empty', 'file')); } $filePath = ROOT_PATH . DS . 'public' . DS . $file; if (!is_file($filePath)) { $this->error(__('No results were found')); } //实例化reader $ext = pathinfo($filePath, PATHINFO_EXTENSION); if (!in_array($ext, ['csv', 'xls', 'xlsx'])) { $this->error(__('Unknown data format')); } $type = pathinfo($filePath); $type = strtolower($type["extension"]); $types = ""; if ($type == 'xlsx') { $types = 'Xlsx'; } elseif ($type == 'xls') { $types = 'Xls'; } try { $objReader = IOFactory::createReader($types); $PHPExcel = $objReader->load($filePath); $currentSheet = $PHPExcel->getSheet(0); //读取excel文件中的第一个工作表 $highestColumnIndex = Coordinate::columnIndexFromString("C");//总列数 $allRow = $currentSheet->getHighestRow(); //取得一共有多少行 $dataList = []; //声明数组 /**从第三行开始输出*/ for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) { /**从第A列开始输出*/ for ($col = 1; $col < $highestColumnIndex; $col++) { if ($col) $dataList[] = $currentSheet->getCellByColumnAndRow($col, $currentRow)->getValue(); } } $arr=[]; foreach ($dataList as $k=>$v){ if(!empty($v)){ $arr[]=[ 'NickName'=>$v, 'IsUsed'=>0 ]; } } // writeLog($dataList,'test'); if (empty($dataList)){ $this->error('没有获取到昵称'); } writeLog($dataList,'test'); //用完就删除 文件 @unlink($filePath); $this->success('上传成功',''); } catch (Exception $exception) { $this->error($exception->getMessage()); } } ~~~ ``` ##### html (javascript) ``` $('.data-export').click(function(){ var startDate = $('#startDate').val(); var endDate = $('#endDate').val(); if(startDate=="" || endDate==""){ layer.msg('请选择开始和结束时间'); return ; } $.ajax({ url: '{:url("export_data")}', type: 'post', data: { start:startDate, end:endDate, }, dataType: 'json', beforeSend: function(data) { layer.load(1); },success: function(data) { if(data.code==1){ layer.open({ type: 1 ,title: false //不显示标题栏 ,closeBtn: false ,area: ['80%','20%'] ,shade: 0.5 ,id: 'LAY_layuipro' //设定一个id,防止重复弹出 ,btn: ['下载', '取消'] ,btnAlign: 'c' ,moveType: 0 //拖拽模式,0或者1 ,content: '<div style="padding: 30px; line-height: 22px; background-color: #393D49; color: #fff; font-weight: 300;">数据已导出,点击下载!</div>' ,success: function(layero){ var btn = layero.find('.layui-layer-btn'); btn.find('.layui-layer-btn0').attr({ href: data.data ,target: '_blank' }); } }); }else{ layer.msg(data.msg,{time:1500,icon:15}); } }, complete:function(){ layer.closeAll("loading"); }, error:function (){ layer.msg('未知错误'); layer.closeAll("load"); } }) }); ```