晨曦啊 晨曦啊

ThinkPHP+PHPExcel导入导出实现方法

in PHP,计算机 文章转载请注明来源!

下载PHPExcel;地址https://github.com/PHPOffice/PHPExcel
代码如下


<?php

namespace app\index\controller;
use think\Controller;
use think\Request;
use think\Loader;
use think\Db;

class Index extends Controller
{
    public function index()
    {
        return $this->fetch('/index');
    }

    //上传EXECL文件并用表格显示在网页上
    public function up(Request $request)
    {
        Loader::import('PHPExcel.Classes.PHPExcel');//加载PHPExcel
        //PHPExcel放在目录extend下,PHPExcel.Classes.PHPExcel就相当于
        //PHPExcel/Classes/PHPExcel;
        Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5');
        $file = $request->file('file');//获取表单上传文件
        $info = $file->validate(['ext' => 'xlsx,xls'])->move(ROOT_PATH . 'public' . DS . 'uploads');//上传验证后缀名,以及上传之后移动的地址
        if ($info) {
            $file_name = $info->getRealPath();  //获取文件地址
            if($info->getExtension()=='xls') {
                $objReader = \PHPExcel_IOFactory::createReader('Excel5');//支持xls后缀
            }else {
                $objReader = \PHPExcel_IOFactory::createReader('Excel2007');//支持xlsx后缀
            }
            $objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8
            $objReader->setReadDataOnly(true);
            $objWorksheet = $objPHPExcel->getActiveSheet();//获得当前活动状态的工作表,返回工作表对象
            echo '<table border="1" width="600px">' . "\n";
            foreach ($objWorksheet->getRowIterator() as $row) {
                echo '<tr>' . "\n";
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false);
                foreach ($cellIterator as $cell) {
                    echo '<td>' . $cell->getValue() . '</td>' . "\n";//得到值
                }
                echo '</tr>' . "\n";
            }
            echo '</table>' . "\n";
            //删除上传的文件
            //unlink($file_name);
            die;
            //表格数据插入数据库
            $excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式
            //array_shift($excel_array);  //删除第一个数组(标题);
            $city = [];
            foreach($excel_array as $k=>$v) {
                    $city[$k]['row1'] = $v[0];
                    $city[$k]['row2'] = $v[1];
                    $city[$k]['row3'] = $v[2];
                    $city[$k]['row4'] = $v[3];
                    $city[$k]['row5'] = $v[4];
            }
            Db::table('execl')->insertAll($city); //批量插入数据
        } else {
            echo $file->getError();
        }
    }

    //显示数据库表格信息
    public function table(){
        $data=Db::table('execl')->select();
        //dump($data);die;
        $this->assign('data',$data);
        return $this->fetch('/table');
    }

    //数据库导出到excel表文件,xls格式
    public function dotable(){
        $data=Db::table('execl')->select();
        Loader::import('PHPExcel.Classes.PHPExcel');//加载PHPExcel
        Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5');
        //对数据进行检验
        if(empty($data)||!is_array($data)){
            die("data must be a array");
        }
        $date=time();
        $fileName="{$date}.xls";
        //创建PHPExcel对象,注意,不能少了
        $objPHPExcel=new \PHPExcel();

        $objPHPExcel->getDefaultStyle()->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->setCellValue('A1','ID');
        $objPHPExcel->getActiveSheet()->setCellValue('B1','姓名');
        $objPHPExcel->getActiveSheet()->setCellValue('C1','款号');
        $objPHPExcel->getActiveSheet()->setCellValue('D1','件数');
        $objPHPExcel->getActiveSheet()->setCellValue('E1','单价');

        $i = 2;
        foreach($data as $r){
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$r['row1']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$i,$r['row2']);
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$i,$r['row3']);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$i,$r['row4']);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$i,$r['row5']);
            $i++;
        }

        $objPHPExcel->getActiveSheet()->setTitle('表格');
        $fileName = iconv("utf-8", "gb2312", $fileName);
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
        header("Content-Disposition: attachment;filename=\"$fileName\"");//告诉浏览器将输出文件的名称(文件下载)
        header('Cache-Control: max-age=0');//禁止缓存
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');//告诉浏览器下载
    }
}
jrotty WeChat Pay

微信打赏

jrotty Alipay

支付宝打赏

文章二维码

扫描二维码,在手机上阅读!

PHP计算机
发表新评论
前篇 后篇
雷姆
拉姆