ThinkPHP5.1 利用PHPExcel进行数据的导入导出

最新推荐文章于 2024-02-23 10:17:21 发布

Lizjun`

最新推荐文章于 2024-02-23 10:17:21 发布

阅读量745

点赞数 2

分类专栏： php 文章标签： thinkphp excel php 数据库

本文链接：https://blog.csdn.net/A1572518271/article/details/109842111

版权

php 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

大神请绕道~

使用composer 安装 PHPExcel

composer require phpoffice/phpexcel

微信图片_20200808171850.png

这样就安装好了

导入

1-1 准备一个Excel表格

微信截图_20200808173244.png

1-2 编写代码，引入类库

use think\Controller;
use think\Request;
use PHPExcel;   //引入刚刚PHPExcel类库
use app\index\model\User;
use think\facade\Env;

1-3 编写导入的PHP代码

public function postExcel(Request $request)
{
    if ($request->isPost()) {
        $ext_arr = ['xlsx', 'xls'];
        //接收文件
        $file = $request->file('file');
        $file_type = $file->getInfo()['name'];
        //获取后缀
        $ext = explode('.', $file_type);
        $ext = end($ext);
        //判断时候是限制的文件格式
        if (!in_array($ext, $ext_arr)) {
            $this->error('文件格式有误');
        }
        if ($ext == 'xls') {
            $type = 'Excel5';
        } elseif ($ext == 'xlsx') {
            $type = 'Excel2007';
        }
        $tmp_name = $file->getInfo()['tmp_name'];


        //实例化类库
        $phpExcel = new PHPExcel;
        $reader = \PHPExcel_IOFactory::createReader($type);
        $objData = $reader->load($tmp_name, $encode = 'utf-8');
        $excel_array = $objData->getsheet(0)->toArray();
        //删除表格第一行
        unset($excel_array[0]);
        $i = 0;
        $insertData = [];
        foreach ($excel_array as $key => $value) {
            $insertData[$i]['id'] = $value[0];
            $insertData[$i]['name'] = $value[1];
            $insertData[$i]['pass'] = $value[2];
            $insertData[$i]['create_time'] = time();
            $insertData[$i]['update_time'] = time();
            $insertData[$i]['delete_time'] = $value[5];
            $insertData[$i]['status'] = $value[6];
            $i++;
        }
        $info = new User;
        $result = $info->saveExcel($insertData);
        if ($result) {
            $this->success('导入成功');
        } else {
            $this->error('导入失败');
        }


    }
}

1-4 最后

2.导出

2-1 编写导出的PHP代码

public function index()
    {
        $file_name = date('Ymd_His') . '_' . rand(1111, 9999);
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getActiveSheet()->setTitle("demo"); //给当前活动sheet设置名称
        $res = new Users();
        $result = $res->getExcecl(); //这里是查数据，就不一一写了。[懒惰]
        // 设置头信息
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1','ID')
            ->setCellValue('B1','名称')
            ->setCellValue('C1','密码')
            ->setCellValue('D1','创建时间')
            ->setCellValue('E1','修改时间')
            ->setCellValue('F1','删除时间')
            ->setCellValue('G1','状态');
        $num = 2;

        foreach ($result as $k => $v) {
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A' . $num, $v['id'])  //这里是对应数据库字段名
                ->setCellValue('B' . $num, $v['name'])
                ->setCellValue('C' . $num, $v['pass'])
                ->setCellValue('D' . $num, $v['create_time'])
                ->setCellValue('E' . $num, $v['update_time'])
                ->setCellValue('F' . $num, $v['delete_time'])
                ->setCellValue('G' . $num, $v['status']);
            $num++;
        }
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=".$file_name.".xls");
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        $objWriter->save("php://output");//输出文件

    }