excel 导出导入 / 开普饭

/*** 导出* @param* @param* @return*/public function exportexcel(){set_time_limit(0);ini_set('memory_limit', '1024M');vendor("PHPExcel.PHPExcel.PHPExcel");vendor("PHPExcel.PHPExcel.Writer.IWriter");vendor("PHPExcel.PHPExcel.Writer.Abstract");vendor("PHPExcel.PHPExcel.Writer.Excel5");vendor("PHPExcel.PHPExcel.Writer.Excel2007");vendor("PHPExcel.PHPExcel.IOFactory");//$data = Db::table('enroll_activity_hotel')->where('is_del', '0')->field('name,address,phone,departure_time,two_room,three_room')->select();$name = '酒店列表';$excel = new \PHPExcel(); //引用phpexceliconv('UTF-8', 'gb2312', $name); //针对中文名转码$header = ['酒店名称', '酒店地址', '酒店电话', '发车时间', '两人间数', '三人间数']; //表头,名称可自定义$excel->setActiveSheetIndex(0);$excel->getActiveSheet()->setTitle($name); //设置表名$excel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(18);$excel->getActiveSheet()->getColumnDimension('A')->setWidth(30);$excel->getActiveSheet()->getColumnDimension('B')->setWidth(30);$excel->getActiveSheet()->getColumnDimension('C')->setWidth(30);$excel->getActiveSheet()->getColumnDimension('D')->setWidth(30);$excel->getActiveSheet()->getColumnDimension('E')->setWidth(30);$excel->getActiveSheet()->getColumnDimension('F')->setWidth(30);$letter = array('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', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');//生成表头for ($i = 0; $i < count($header); $i++) {//设置表头值$excel->getActiveSheet()->setCellValue("$letter[$i]1", $header[$i]);//设置表头字体样式$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setName('宋体');//设置表头字体大小$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setSize(14);//设置表头字体是否加粗$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setBold(true);//设置表头文字水平居中$excel->getActiveSheet()->getStyle("$letter[$i]1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置文字上下居中$excel->getActiveSheet()->getStyle($letter[$i])->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置单元格背景色$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFill()->getStartColor()->setARGB('FFFFFFFF');$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFill()->getStartColor()->setARGB('FF6DBA43');//设置字体颜色$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->getColor()->setARGB('FFFFFFFF');}//写入数据$count = Db::table('enroll_activity_hotel')->where('is_del', '0')->count('id');$nums = ceil($count / 2000);for ($i = 0; $i < $nums; ++$i) {$data = Db::table('enroll_activity_hotel')->where('is_del', '0')->field('name,address,phone,departure_time,two_room,three_room')->limit(($i * 2000) . ',' . (2000))->select();if (!empty($data)) {$param = $i * 2000;//写入数据foreach ($data as $k => $v) {$n = $param + $k + 2;//列字体居中$excel->getActiveSheet()->getStyle('A' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$excel->getActiveSheet()->getStyle('B' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$excel->getActiveSheet()->getStyle('C' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$excel->getActiveSheet()->getStyle('D' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$excel->getActiveSheet()->getStyle('E' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$excel->getActiveSheet()->getStyle('F' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$excel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);//从第二行开始写入数据（第一行为表头）$excel->getActiveSheet()->setCellValue('A' . $n, $v['name']);$excel->getActiveSheet()->setCellValue('B' . $n, $v['address']);$excel->getActiveSheet()->setCellValue('C' . $n, $v['phone']);$excel->getActiveSheet()->setCellValue('D' . $n, $v["departure_time"]);$excel->getActiveSheet()->setCellValue('E' . $n, $v['two_room']);$excel->getActiveSheet()->setCellValue('F' . $n, $v['three_room']);}} else {break;}}unset($data);//清理缓冲区，避免中文乱码ob_end_clean();//清除缓冲区,避免乱码header('Content-Type: application/vnd.ms-excel');header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');header('Cache-Control: max-age=0');//导出数据$res_excel = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');$res_excel->save('php://output');}

/*** daoru* @param* @param* @return*/public function implodexcel(){set_time_limit(0);if (!empty($_FILES)) {//获取表单上传文件if (!empty($_FILES['import']['error'])) {return array('status' => 0, 'msg' => '文件上传失败');}$tmp_file = $_FILES ['import'] ['tmp_name'];$file_types = explode(".", $_FILES ['import'] ['name']);$file_type = $file_types [count($file_types) - 1];/*判别是不是.xls文件，判别是不是excel文件*/if (strtolower($file_type) != "xlsx" && strtolower($file_type) != "xls") {$this->error('不是Excel文件，重新上传');}/*设置上传路径*//*百度有些文章写的上传路径经过编译之后斜杠不对。不对的时候用大写的DS代替，然后用连接符链接就可以拼凑路径了。*/$savePath = ROOT_PATH . 'public' . DS . 'upload' . DS;/*以时间来命名上传的文件*/$str = date('Ymdhis');$file_name = $str . "." . $file_type;/*是否上传成功*/if (!copy($tmp_file, $savePath . $file_name)) {$this->error('上传失败');}/**注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入*/$res = $this->read($savePath . $file_name, "UTF-8", $file_type);//传参,判断office2007还是office2003$uniques = array_filter(array_column($res, 0));$filpuni = array_flip(array_flip($uniques));if (count($uniques) != count($filpuni)) {return array('status' => 0, 'msg' => "酒店名称有重复项，请处理后再导入");}$datas = Db::table('enroll_activity_hotel')->where('is_del', '0')->field('id,name,address,phone,departure_time,two_room,three_room')->select();$anames = array_column($datas, 'name');$couludate = array_column($datas, null, 'name');//print_r($anames);die;Db::startTrans();try {/*对生成的数组进行数据库的写入*/foreach ($res as $k => $v) {//如果导入数据为空，跳过if (empty(array_filter($v))) {continue;}if ($k > 1) {$mblength = mb_strlen($v[0], 'UTF8');if ($mblength > 50 || $mblength < 1) {Db::rollback();return array('status' => 0, 'msg' => '酒店名' . $v[0] . '：字符长度不符请控制在2-50字符以内');}$pholength = mb_strlen($v[2], 'UTF8');if ($pholength > 20 || $pholength < 1) {Db::rollback();return array('status' => 0, 'msg' => '酒店' . $v[0] . '：电话长度不符');}if(!is_numeric($v[2])) {Db::rollback();return array('status' => 0, 'msg' => '酒店' . $v[0] . '：电话必须为数字');}$adreslength = mb_strlen($v[1], 'UTF8');if ($adreslength > 100 || $adreslength < 1) {Db::rollback();return array('status' => 0, 'msg' => '酒店' . $v[0] . '：地址长度不符');}$timelength = mb_strlen($v[3], 'UTF8');if ($timelength > 20 || $timelength < 1) {Db::rollback();return array('status' => 0, 'msg' => '酒店' . $v[0] . '：发车时间长度不符');}$toromlength = mb_strlen($v[4], 'UTF8');$treromlength = mb_strlen($v[5], 'UTF8');if ($toromlength < 1 || $treromlength < 1) {Db::rollback();return array('status' => 0, 'msg' => '酒店' . $v[0] . '：房间数必须填写,没有请填写数字为0');}if(!is_numeric($v[4])) {Db::rollback();return array('status' => 0, 'msg' => '酒店' . $v[0] . '：房间数必须为数字');}if(!is_numeric($v[5])) {Db::rollback();return array('status' => 0, 'msg' => '酒店' . $v[0] . '：房间数必须为数字');}$isexit = array_search($v[0], $anames);if (false === $isexit) {$data[$k]['name'] = $v[0];$data[$k]['address'] = $v[1];$data[$k]['phone'] = $v[2];$data[$k]['departure_time'] = $v[3];$data[$k]['two_room'] = $v[4];$data[$k]['three_room'] = $v[5];$data[$k]['is_open'] = 1;$data[$k]['add_admin_id'] = session('user.id');$data[$k]['add_time'] = time();$data[$k]['update_admin_id'] = session('user.id');$data[$k]['update_time'] = time();} else {//如果数据表中有本账户，判断他最新导入的表格是否修改过信息if ($couludate[$v[0]]['address'] != $v[1] || $couludate[$v[0]]['phone'] != $v[2] || $couludate[$v[0]]['departure_time'] != $v[3] || $couludate[$v[0]]['two_room'] != $v[4] || $couludate[$v[0]]['three_room'] != $v[5]) {$dataup[$k]['id'] = $couludate[$v[0]]['id'];$dataup[$k]['address'] = $v[1];$dataup[$k]['phone'] = $v[2];$dataup[$k]['departure_time'] = $v[3];$dataup[$k]['two_room'] = $v[4];$dataup[$k]['three_room'] = $v[5];$dataup[$k]['update_admin_id'] = session('user.id');$dataup[$k]['update_time'] = time();}}}}if (isset($dataup) || isset($data)) {//如果有更新的数据，批量更新if (isset($dataup)) {foreach ($dataup as $key => $value) {$updas = Db::table('enroll_activity_hotel')->where('id', $value['id'])->update($value);if (!$updas) {Db::rollback();return array('status' => 0, 'msg' => $e->getMessage());}}}if (isset($data)) {//插入的操作最好放在循环外面$result = Db::table('enroll_activity_hotel')->insertAll($data);if (!$result) {Db::rollback();return array('status' => 0, 'msg' => $e->getMessage());}}Db::commit();return array('status' => 1, 'msg' => "导入成功");} else {Db::rollback();return array('status' => 0, 'msg' => "重复导入");}} catch (\Exception $e) {Db::rollback();return array('status' => 0, 'msg' => $e->getMessage());}}}/*** 导入方法附属方法* @param* @param* @return*/public function read($filename, $encode, $file_type){vendor("PHPExcel.PHPExcel.PHPExcel");vendor("PHPExcel.PHPExcel.Writer.IWriter");vendor("PHPExcel.PHPExcel.Writer.Abstract");vendor("PHPExcel.PHPExcel.Writer.Excel5");vendor("PHPExcel.PHPExcel.Writer.Excel2007");vendor("PHPExcel.PHPExcel.IOFactory");if (strtolower($file_type) == 'xls')//判断excel表类型为2003还是2007{Vendor("Excel.PHPExcel.Reader.Excel5");$objReader = \PHPExcel_IOFactory::createReader('Excel5');} elseif (strtolower($file_type) == 'xlsx') {Vendor("Excel.PHPExcel.Reader.Excel2007");$objReader = \PHPExcel_IOFactory::createReader('Excel2007');}$objReader->setReadDataOnly(true);$objPHPExcel = $objReader->load($filename);$objWorksheet = $objPHPExcel->getActiveSheet();$highestRow = $objWorksheet->getHighestRow();$highestColumn = $objWorksheet->getHighestColumn();$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);$excelData = array();for ($row = 1; $row <= $highestRow; $row++) {for ($col = 0; $col < $highestColumnIndex; $col++) {$excelData[$row][] = (string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();}}return $excelData;}

Excel的基础入门操作，协助你掌握从零基础入门到高级技巧应用（全7集）视频教程下载地址：https://306t.com/file/686368-477371689

excel 导出导入

相关推荐