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(); //引用phpexcel
        iconv('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;
    }