PHP导出excel

导出excel

---

生成excel

 public function execle($fileName = '', $data = [], $headArr = [])
    {
       // $headArr = ['日期', '客户', '服务内容', '服务类型'];
        // $data = [
//            ['石油', 23],
//            ['肥牛饭', 22]
//        ];
        vendor("PHPExcel.PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $execle = new \PHPExcel();
        $execle->getProperties();
        //设置表头
        $col = $this->foundExcelColumnName( count($headArr));
        $i = 0 ;
        foreach ($headArr as $v) {
            $execle->setActiveSheetIndex(0)->setCellValue($col[$i] . '1', $v);
            $i++;
        }
        //行写入
        $column = 2;
        $objActSheet = $execle->getActiveSheet();
        foreach ($data as $key => $rows){
            $k = 0;
            foreach ($rows as $keyName => $value) {
                $objActSheet->setCellValue($col[$k] . $column, $value);
                $k++;
            }
            $column++;
        }

        $filepath = ROOT_PATH . '/public/uploads/excel/';
        $filename = $filepath . $fileName;

        // 设置活动单指数到第一个表,所以Excel打开这是第一个表
        $execle->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename='$fileName'");
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($execle, 'Excel5');
        $objWriter->save($filename); //
        return $filename;
    }

生产A-AZ，或者A-CZ等方法，此方法是用着生产excle不定列

/**
     * php 生产A-AZ，或者A-CZ等方法，此方法是用着生产excle不定列
     * @param $columnTotal    对应的生成列数，是数字，例如写26，则生成结果是A-Z，如果写53，则生成A-AA
     * @return array|string
     */
    public function foundExcelColumnName($columnTotal) {
        if ($columnTotal > 702) {
            return '参数错误';
        }

        $_minBaseCode = 65;//asc2码，65代表A
        $_maxBaseCode = 90;//asc2码，90代表Z
        $_result = array();

        $_allBaseCode = array();
        for($i = $_minBaseCode; $i <= $_maxBaseCode; $i ++) {
            $_allBaseCode[] = chr($i);
        }

        $_firstCode = $_minBaseCode;
        $_secondFlag = 0;
        $_secondCode = $_minBaseCode - 1;
        for ($i=0; $i < $columnTotal; $i++) {
            if ($_firstCode > $_maxBaseCode) {
                $_firstCode = $_minBaseCode;
                ++ $_secondFlag;
                ++ $_secondCode;
            }

            if ($_secondFlag > 0) {
                $result[] = chr($_secondCode) . chr($_firstCode);
            } else {
                $result[] = chr($_firstCode);
            }

            ++ $_firstCode;
        }

        return $result;
    }

导入excel

---

 public function importExcel1()
    {
        set_time_limit(0);
        if (!empty($_FILES)) {
            $config = [
                'size' => 2097152,
                'ext' => 'xls,xlsx'
            ];

            $file = $this->request->file('file');
            $upload_path = str_replace('\\', '/', ROOT_PATH . 'public/uploads');
            $save_path = '/public/uploads/';
            $info = $file->validate($config)->move($upload_path);
            if ($info) {
                $result = [
                    'error' => 0,
                    'url' => str_replace('\\', '/', $save_path . $info->getSaveName())
                ];
            } else {
                $result = [
                    'error' => 1,
                    'message' => $file->getError()
                ];
            }
            $file_name = ROOT_PATH . $result['url'];
            $file_types = explode(".", $file_name);
            $exts = $file_types [count($file_types) - 1];
            vendor("PHPExcel.PHPExcel");
            if ($exts == 'xls') {
                import("Org.Util.PHPExcel.Reader.Excel5");
                $objReader = new \PHPExcel_Reader_Excel5();
            } else if ($exts == 'xlsx') {
                import("Org.Util.PHPExcel.Reader.Excel2007");
                $objReader = new \PHPExcel_Reader_Excel2007();
            } else {
                $objReader = \PHPExcel_IOFactory::createReader('Excel5');
            }
            $objPHPExcel = $objReader->load($file_name, $encode = 'utf-8');
            $sheet = $objPHPExcel->getSheet(0);
            $highestRow = $sheet->getHighestRow(); // 取得总行数
            $data = [];

            $tags = Db::name('tags');
            $time = time();
            $tagList = getTagLib();
            $tagRes = [];
            foreach ($tagList as $k => $v) {
                $tagRes[$v['type']] = $k;
            }
            for ($i = 2; $i <= $highestRow; $i++) {
                $data[$i]['name'] = is_object($objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue()) ? $objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue()->__toString() : $objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
                $data[$i]['level'] = is_object($objPHPExcel->getActiveSheet()->getCell("B" . $i)->getValue()) ? $objPHPExcel->getActiveSheet()->getCell("B" . $i)->getValue()->__toString() : $objPHPExcel->getActiveSheet()->getCell("B" . $i)->getValue();
                $data[$i]['type'] = is_object($objPHPExcel->getActiveSheet()->getCell("C" . $i)->getValue()) ? $objPHPExcel->getActiveSheet()->getCell("C" . $i)->getValue()->__toString() : $objPHPExcel->getActiveSheet()->getCell("C" . $i)->getValue();

                $data[$i]['type'] = $tagRes[$data[$i]['type']];
                $res = $tags->where(['name' => $data[$i]['name']])->find();
                if ($res) {
                    unset($data[$i]);
                }
            }
            $data = array_unset_tt($data, 'name');
            //插入的操作最好放在循环外面
            $result = $tags->insertAll($data);
            if ($result === false) {
                return ajaxReturn($this->cModel->getError());
            } else {
                return ajaxReturn(lang('action_success'), url('index'));
            }
        } else {
            return ajaxReturn($this->cModel->getError());
        }
    }

扩列生产是根据某位前辈的更改一下，如有侵权请联系博主