thinkphp5做excel表导入导出

最新推荐文章于 2024-07-27 10:23:41 发布

xuxiaopang0417

最新推荐文章于 2024-07-27 10:23:41 发布

阅读量4k

点赞数 2

分类专栏：技术分享

本文链接：https://blog.csdn.net/xuxiaopang0417/article/details/82715929

版权

技术分享专栏收录该内容

23 篇文章 0 订阅

订阅专栏

最近用thinkphp5做excel导入导出，在网上发现代码各不相同，废了一些时间整合了一下，和网上大同小异，下面代码只要按照步骤粘贴复制，就能用，其余的数据库名和数据库中的字段自行更改
1、https://github.com/PHPOffice/PHPExcel自行下载
2、下载好了把classes文件改名为PHPExcel
3、这里写图片描述
废话不多说，直接粘贴复制

use think\Controller;
use think\Loader;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Cell;
use PHPExcel_Writer_Excel5;
use PHPExcel_Writer_Excel2007;

public function excel(){
        if (request()->isPost()) {

            Loader::import('PHPExcel.PHPExcel');
            Loader::import('PHPExcel.PHPExcel.PHPExcel_IOFactory');
            Loader::import('PHPExcel.PHPExcel.PHPExcel_Cell');
            //实例化PHPExcel
            $objPHPExcel = new PHPExcel();
            $file = request()->file('excel');
            if ($file) {
                $file_types = explode(".", $_FILES ['excel'] ['name']); // ["name"] => string(25) "excel文件名.xls"
                $file_type = $file_types [count($file_types) - 1];//xls后缀
                dump($file_type);
                die;
                $file_name = $file_types [count($file_types) - 2];//xls去后缀的文件名
                /*判别是不是.xls文件，判别是不是excel文件*/
                if (strtolower($file_type) != "xls" && strtolower($file_type) != "xlsx") {
                    echo '不是Excel文件，重新上传';
                    die;
                }
                $info = $file->rule('uniqid')->move(ROOT_PATH . 'public' . DS . 'excel');//上传位置
                $path = ROOT_PATH . 'public' . DS . 'excel' . DS;
                $file_path = $path . $info->getSaveName();//上传后的EXCEL路径
               //获取上传的excel表格的数据，形成数组
                $re = $this->actionRead($file_path, 'utf-8');
                array_splice($re, 1, 0);
                unset($re[0]);
                /*将数组的键改为自定义名称*/
                $keys = array('id', 'username', 'nickname', 'password', 'salt', 'avatar', 'email', 'loginfailure', 'logintime', 'createtime', 'updatetime','token','status');
                foreach ($re as $i => $vals) {
                    $re[$i] = array_combine($keys, $vals);
                }
                 //遍历数组写入数据库
                for ($i = 1; $i < count($re); $i++) {
                    $data = $re[$i];
                    $res = db('admin')->insert($data);
                }
            }
        }
    }

 public function actionRead($filename, $encode = 'utf-8')
    {
        $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;
    }

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form  action="{:url('admin/index/excel')}" enctype="multipart/form-data" method="post">
    <input type="file" name="excel" /> <br>
    <input type="submit" value="上传" />
</form>
</body>
</html>

//导出

   public function out(){
          $path = dirname(__FILE__); //找到当前脚本所在路径
          Loader::import("PHPExcel.PHPExcel");
          Loader::import("PHPExcel.PHPExcel.Writer.IWriter");
          Loader::import("PHPExcel.PHPExcel.Writer.Abstract");
          Loader::import("PHPExcel.PHPExcel.Writer.Excel5");
          Loader::import("PHPExcel.PHPExcel.Writer.Excel2007");
          Loader::import("PHPExcel.PHPExcel.IOFactory");
          $objPHPExcel = new PHPExcel();
          $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
          $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
          // 实例化完了之后就先把数据库里面的数据查出来
          $sql = db('admin')->select();
          // 设置表头信息
          $objPHPExcel->setActiveSheetIndex(0)
              ->setCellValue('A1', 'id')
              ->setCellValue('B1', 'username')
              ->setCellValue('C1', 'nickname')
              ->setCellValue('D1', 'password')
              ->setCellValue('E1', 'salt')
              ->setCellValue('F1', 'avatar')
              ->setCellValue('G1', 'email')
              ->setCellValue('H1', 'loginfailure')
              ->setCellValue('I1', 'logintime')
              ->setCellValue('J1', 'createtime')
              ->setCellValue('K1', 'updatetime')
              ->setCellValue('L1', 'token')
              ->setCellValue('M1', 'status');
          $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
          $count = count($sql);  //计算有多少条数据
          for ($i = 2; $i <= $count+1; $i++) {
              $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $sql[$i-2]['id']);
              $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $sql[$i-2]['username']);
              $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sql[$i-2]['nickname']);
              $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $sql[$i-2]['password']);
              $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $sql[$i-2]['salt']);
              $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $sql[$i-2]['avatar']);
              $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $sql[$i-2]['email']);
              $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $sql[$i-2]['loginfailure']);
              $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $sql[$i-2]['logintime']);
              $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $sql[$i-2]['createtime']);
              $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $sql[$i-2]['updatetime']);
              $objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $sql[$i-2]['token']);
              $objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $sql[$i-2]['status']);
          }
          $objPHPExcel->getActiveSheet()->setTitle('admin');      //设置sheet的名称
          $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
          $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来
          $PHPWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
          header('Content-Disposition: attachment;filename="admin.xlsx"');
          header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
          $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件

      }