thinkphp Excel上传、读取、数据导出

首先安装composer
在thinkphp根目录执行安装命令 composer require phpoffice/phpexcel安装phpExcel类

Excel上传

   public function exportExcel()
    {
        $file = request()->file('excel');
        if (!file_exists($_SERVER['DOCUMENT_ROOT'] .'/uploads/excel/')) {
            mkdir($_SERVER['DOCUMENT_ROOT'] .'/uploads/excel/', 0777, true);
        }
        $info = $file->move($_SERVER['DOCUMENT_ROOT'] .'/uploads/excel/');
        if ($info->getExtension() != 'xlsm' && $info->getExtension() != 'xls')
        {
            return '格式不正确';
        }
        $saveUrl = str_replace('\', '/', $_SERVER['DOCUMENT_ROOT']  . '/uploads/excel/' . $info->getSaveName());

        //调用excel读取数据
        $res = $this->getExcelData($saveUrl, $info->getExtension());
        if (empty($res))
        {
            return '数据为空';
        }
        return $res;
    }

Excel读取数据

   public function getExcelData($filePath, $type){
        try
        {
            header("content-type:text/html;charset=utf-8");
            if ($type == 'xlsm')
            {
                $reader = PHPExcel_IOFactory::createReader('Excel2007');
            }
            else
            {
                $reader = PHPExcel_IOFactory::createReader('Excel5');
            }
            //载入excel文件
            $excel = $reader->load($filePath,$encode = 'utf-8');
            //读取第一张表
            $sheet = $excel->getSheet(0);
            //获取总行数
            $row_num = $sheet->getHighestRow();
            //获取总列数
            $data = []; 
            for ($i = 2; $i <= $row_num; $i ++) {
                $data[$i-2]['brand_name']  = $sheet->getCell("A".$i)->getValue();
                $data[$i-2]['brand_mark']  = $sheet->getCell("B".$i)->getValue();
                $data[$i-2]['brand_num']  = $sheet->getCell("C".$i)->getValue();
                $data[$i-2]['brand_money']  = $sheet->getCell("D".$i)->getValue();
            }
            return $data;
        }
        catch (Exception $exception)
        {
            return null;
        }
    }

excel导出

   public function excel($data, $sheetArr)
    {
        try
        {
//        $sheetArr = [
//            'p_num' => '商品ID',
//            'p_name' => '商品名称',
//            'p_money' => '专柜价（元）',
//        ];
            $sArr = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P"];
            $PHPExcel = new PHPExcel();//实例化phpexcel
            $PHPSheet = $PHPExcel->getActiveSheet()
                ->setTitle("货品信息");//设置表内部名称
            $PHPSheet->setCellValue("A1", "序号");
            $i = 1;
            foreach ($sheetArr as $s)
            {
                $PHPSheet->setCellValue($sArr[$i] . 1, $s);
                $PHPSheet->getStyle($sArr[$i])->getAlignment()->setWrapText(true);//是否换行
                $i++;
            }
            $num = 2;
            //数据
            foreach ($data as $k => $v) {
                if (empty($v))
                {
                    continue;
                }
                $PHPSheet->setCellValue("A" . $num, $num-1);
                $i2 = 1;
                foreach ($sheetArr as $index => $s)
                {
                    $PHPSheet->setCellValue($sArr[$i2] . $num, $v[$index]);
                    $i2++;
                }
                // 图片生成
                $objDrawing[$k] = new PHPExcel_Worksheet_Drawing();
                $imgArr = unserialize($this->mb_unserialize($v['p_url']));
                $imgPath = str_replace("http://www.luxuryscm.com/","/", $imgArr[0]);
                $objDrawing[$k]->setPath($_SERVER['DOCUMENT_ROOT'] . $imgPath);
                // 设置宽度高度
                $objDrawing[$k]->setHeight(100);//照片高度
                $objDrawing[$k]->setWidth(100); //照片宽度
                /*设置图片要插入的单元格*/
                $objDrawing[$k]->setCoordinates($sArr[$i2].$num);
                // 图片偏移距离
                $objDrawing[$k]->setOffsetX(0);
                $objDrawing[$k]->setOffsetY(0);
                $objDrawing[$k]->setWorksheet($PHPExcel->getActiveSheet());
                $PHPSheet->getRowDimension($num)->setRowHeight(100);//设置高度
                $num++;
            }
            $PHPWriter = PHPExcel_IOFactory::createWriter($PHPExcel, "Excel2007");//创建生成的格式
            ob_end_clean();//清除缓冲区,避免乱码
            header('Content-Disposition: attachment;filename="商品信息.xlsx"');//下载下来的表格名
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header("Content-Type:application/force-download");
            header("Content-Type:application/vnd.ms-excel");
            header("Content-Type:application/octet-stream");
            header("Content-Type:application/download");;
            header("Content-Transfer-Encoding:binary");
            $PHPWriter->save("php://output");
            exit();
        }
        catch (Exception $ex)
        {
            exit($ex->getMessage());
        }
    }