ThinkPHP excel导出

最新推荐文章于 2022-10-26 12:36:20 发布

jiangnanqbey

最新推荐文章于 2022-10-26 12:36:20 发布

阅读量378

点赞数

CC 4.0 BY-SA版权

分类专栏： thinkPHP 文章标签： thinkphp

本文链接：https://blog.youkuaiyun.com/jiangnanqbey/article/details/72875404

thinkPHP 专栏收录该内容

9 篇文章

订阅专栏

本文介绍如何使用ThinkPHP框架结合PHPExcel库实现从数据库中提取会员数据，并将其导出到Excel文件的方法。通过具体代码示例展示了设置表格样式、填充数据及文件下载等过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先将Class类放入Thinkphp/Library/vendor/Classes 【Classes是一个Excel导出类，文件夹】结合下面代码OK

/** 会员导出*/

   public function export()
   {
       $User = M('User');
       $list = $User->select();
       $this->cashToExcel($list);
   }

   public function cashToExcel($list){

        vendor("Classes.PHPExcel");
        //创建一个excel对象
        $objPHPExcel = new \PHPExcel();
        // Set properties 设置文件属性
        $objPHPExcel->getProperties()->setCreator("Excel")
        ->setLastModifiedBy("Excel")
        ->setTitle("Document")
        ->setSubject("Office Document")
        ->setDescription("Document for Office")
        ->setKeywords("Office openxml")
        ->setCategory("Result file");

        //set width 设置表格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);

        //设置水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        // set table header content 设置表头名称
        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '会员ID')
        ->setCellValue('B1', '会员名称')
        ->setCellValue('C1', '会员手机号')
        ->setCellValue('D1', '会员性别')
        ->setCellValue('E1', '会员年龄')
        ->setCellValue('F1', '会员邮箱')
        ->setCellValue('G1', '会员公司')
        ->setCellValue('H1', '会员状态');
        $rownum=1;
        foreach ($list as $key=>$value){
           $rownum++;
            if($value['u_sex']=='0'){
                $value['u_sex']="女";
                // $cart_code = $value['account_bank'];
            }
            if($value['u_sex']=='1'){
                $value['u_sex']="男";
                // $cart_code = $value['pay_code'];
            }
            if($value['u_status']=='1'){
                $value['u_status']="正常";
            }else{
               $value['u_status']="禁止";
            }

            // $value['create_time'] = $value['create_time'] ? date("Y-m-d H:i:s",$value['create_time']) : '';
            // $value['update_time'] = $value['update_time'] ? date("Y-m-d H:i:s",$value['update_time']) : '';

            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$rownum,$value['u_id'],\PHPExcel_Cell_DataType::TYPE_STRING);//设置数字的科学计数法显示为文本
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$rownum,$value['u_name'],\PHPExcel_Cell_DataType::TYPE_STRING);//设置数字的科学计数法显示为文本
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $rownum, $value['u_phone']);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D' . $rownum, $value['u_sex'],\PHPExcel_Cell_DataType::TYPE_STRING);//设置数字的科学计数法显示为文本
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $rownum, $value['u_age']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $rownum, $value['u_email']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $rownum, $value['u_company']);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . $rownum, $value['u_status']);
        }

        /////////////////////在生成一条合计的记录
        $rownum = $rownum + 1;

        $objPHPExcel->getActiveSheet()->setTitle('Simple');
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        $filename="userlist/".date('Y-m-d');
        // Redirect output to a client’s web browser (Excel5)
        ob_end_clean();//清除缓冲区,避免乱码
        header("Content-type:text/html;charset=utf-8");
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$filename.'.xls');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        //exit;

        $this->success("导出成功！");
        echo '<script>window.close();</script>';

    }