ThinkPhp excel 导入导出 demo_thinkphp 数据库导出操作demo-优快云博客

本文链接：https://blog.youkuaiyun.com/ww_smile7/article/details/77113644

首先要去下载一个PHPExcel 文件

在前台html页面进行上传文件：如：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<P><a href="{:U('Index/expUser')}" >导出数据并生成excel</a></P><br/>
<form action="{:U('Index/add_excel')}" method="post" enctype="multipart/form-data">
    <input type="file" name="import"/>
    <input type="submit" value="导入"/>
</form>
</body>
</html>

excel 导入

 /*
     * excel 倒入
     * */
    public function add_excel()
    {
        if (!empty($_FILES)) {

            $config = array(

                'exts' => array('xlsx', 'xls'),

                'maxSize' => 3145728000,

                'rootPath' => "./Public/",

                'savePath' => 'Uploads/',

                'subName' => array('date', 'Ymd'),

            );

            $upload = new \Think\Upload($config);

            if (!$info = $upload->upload()) {

                $this->error($upload->getError());
            }
            $file_name = $upload->rootPath . $info['import']['savepath'] . $info['import']['savename'];
            $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式
            $res = $this->goods_import($file_name, $extension);
            if($res){
                echo "ok";
            }else{
                echo "no";
            }

        }}      public function goods_import($filename, $exts)
    {
        import("Org.Util.PHPExcel");
        if($exts == 'xls'){
            import("Org.Util.PHPExcel.Reader.Excel5");
            $PHPReader=new \PHPExcel_Reader_Excel5();
        }else if($exts == 'xlsx'){
            import("Org.Util.PHPExcel.Reader.Excel2007");
            $PHPReader=new \PHPExcel_Reader_Excel2007();
        }
        $objPHPExcel = $PHPReader->load($filename);
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        for($a=3;$a<=$highestRow;$a++){
            $data['username'] = $objPHPExcel->getActiveSheet()->getCell("A" . $a)->getValue();
            $data['pwd'] = $objPHPExcel->getActiveSheet()->getCell("B" . $a)->getValue();
            $data['phone'] = $objPHPExcel->getActiveSheet()->getCell("C" . $a)->getValue();
            $data['sex'] = $objPHPExcel->getActiveSheet()->getCell("D" . $a)->getValue();
            if($data['username'] instanceof \PHPExcel_RichText) {   //富文本转换字符串
                $data['username'] = $data['username']->__toString();
            }
            if($data['pwd'] instanceof \PHPExcel_RichText) {   //富文本转换字符串
                $data['pwd'] = $data['pwd']->__toString();
            }

            if($data['phone'] instanceof \PHPExcel_RichText) {   //富文本转换字符串
                $data['phone'] = $data['phone']->__toString();
            }

            if($data['sex'] instanceof \PHPExcel_RichText) {   //富文本转换字符串
                $data['sex'] = $data['sex']->__toString();
            }

            $User = M("username");
            $User->add($data);
        }
        if ($User)
        {
            return true;
        }else{
            return false;
        }
    }



excel导出
    /*
     * 导出excel
     * */

    public function expUser($name='Excel')
    {

        import('Org.Util.PHPExcel');
        $exc = new \PHPExcel();
        $objExcel = $exc->getActiveSheet();
        $objExcel->setTitle('用户列表');
        $objExcel->getStyle('A1')->getFont()->setBold(true)->setSize(16);
        //设置猎头宽
        $objExcel->getColumnDimension('A')->setWidth(50);
        $objExcel->getColumnDimension('B')->setWidth(30);
        $objExcel->getColumnDimension('C')->setWidth(30);
        $objExcel->getColumnDimension('D')->setWidth(30);
      
        //设置表头和列头对齐
        $objExcel->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objExcel->getStyle('A3:D1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //插入标题
        $objExcel->MergeCells('A1:D1');;
        $objExcel->setCellValue('A1', '商品列表');
        $objExcel->setCellValue('A3', '用户名')
            ->setCellValue('B3', '密码')
            ->setCellValue('C3', '手机号')
            ->setCellValue('D3', '性别');
        $list=M('username')->select();
        
        $i=4;
        foreach ($list as $k=>$val){
            $objExcel->setCellValue('A' . $i, $val['username'])
                ->setCellValue('B' . $i, $val['pwd'])
                ->setCellValue('C' . $i, $val['phone'])
                ->setCellValue('D' . $i, $val['sex']);
               $i++;
        }

//        $outputFileName = "output.xls";
//        header("Content-Type: application/force-download");
//        header("Content-Type: application/octet-stream");
//        header("Content-Type: application/download");
//        header('Content-Disposition:inline;filename="'.$outputFileName.'"');
//        header("Content-Transfer-Encoding: binary");
//        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
//        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
//        header("Pragma: no-cache");
//        $objExcel->save('php://output');

        
        
        
        //保存文档并返回文件名
        $objfile = \PHPExcel_IOFactory::createWriter($exc, 'Excel5');

        $filename =  'goods_list'. '.xls';
        //var_dump($dir);exit;
        $objfile->save($filename);

        //下载文件需要用到的头
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Length: ' . filesize($filename));
        header('Content-Disposition: attachment; filename=' . basename($filename));
        readfile($filename);

    }