thinkphp3.1和3.2引入phpexcel第三方类库方法及使用详解

原创
2017/05/30 02:28
阅读数 2.1K

一、phpexcel下载

 

二、引入说明

2.1 tp3.1引用phpexcel 

// 通常PHPExcel对象有两种实例化的方式
// 1. 通过new关键字创建空白文档
$phpexcel = newPHPExcel();


// 2. 通过读取已有的模板创建
$phpexcel =PHPExcel_IOFactory::createReader("Excel5")->load("template.xls");

2.2 tp3.2引用phpexcel 

 

三、DEMO

3.1 tp3.1导出excel

    /**
     * [export 会员导出]
     * @return [type] [description]
     */
    public function export(){
        // 获取数据
        $data = D('User')->where(array('role'=>2))->select();

        // 引入PHPExcel类库(插件放在Application/Class/PHPExcel180目录下)
        load('Class.PHPExcel180.PHPExcel',APP_PATH);
        
        // 创建PHPExcel对象
        $objPHPExcel = new PHPExcel();
        
        // 合并excel
        $objPHPExcel->getActiveSheet()->mergeCells('A1:H1');

        // 设置宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(48);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(48);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);

        // 设置行高
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);

        // 设置字体样式
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); //默认字体大小
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getFont()->setBold(true); //粗体

        // 设置垂直、水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // 设置边框
        $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getBorders()->getAllBorders()
            ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getBorders()->getAllBorders()
            ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        // 前两行单元格内容
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1','会员帐号')
            ->setCellValue('A2','用户名')
            ->setCellValue('B2','密码')
            ->setCellValue('C2','邮箱')
            ->setCellValue('D2','昵称')
            ->setCellValue('E2','登录时间')
            ->setCellValue('F2','登录次数')
            ->setCellValue('G2','注册时间')
            ->setCellValue('H2','状态');

        // 数据行设置
        for($i = 0;$i < count($data);$i++){

            // 设置字段值
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i+3), $data[$i]['username']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i+3), $data[$i]['password']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i+3), $data[$i]['email']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i+3), $data[$i]['nickname']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i+3), $data[$i]['login_time']?date('Y-m-d H:i:s',$data[$i]['login_time']):'');
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i+3), $data[$i]['times']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i+3), $data[$i]['reg_time']?date('Y-m-d H:i:s',$data[$i]['reg_time']):'');
            $objPHPExcel->getActiveSheet()->setCellValue('H' . ($i+3), $data[$i]['lock']?'锁定':'正常');

            // 设置垂直、水平居中
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($i+3).':H'.($i+3))->getAlignment()
                ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)
                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            // 设置行高
            $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);

            // 设置边框
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($i+3).':H'.($i+3))->getBorders()->getAllBorders()
                ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        }

        //sheet命名
        $objPHPExcel->getActiveSheet()->setTitle('user表');

        //默认打开的sheet
        $objPHPExcel->setActiveSheetIndex(0);

        //excel头参数
        header("Content-Type:application/vnd.ms-execl");
        header('Content-Disposition:attachment;filename=user表查询结果('.date('YmdHis').').xls');//日期文件名后缀
        header('Cache-Control:max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel2007为xlsx
        $objWriter->save('php://output');
    }

3.2 tp3.2导出excel

    /**
     * [export 会员导出]
     * @return [type] [description]
     */
    public function export(){
        // 获取数据
        $data = D('User')->where(array('role'=>2))->select();

        // 引入PHPExcel类库(插件放在Thinkphp/Library/Vendor/PHPExcel180目录下)
        vendor('PHPExcel180.PHPExcel');
        import("PHPExcel180.PHPExcel.Reader.Excel5");
        import("PHPExcel180.PHPExcel.IOFactory");

        // 创建PHPExcel对象(new的时候要在类名前加\)
        $objPHPExcel = new \PHPExcel();

        // 合并excel
        $objPHPExcel->getActiveSheet()->mergeCells('A1:H1');

        // 设置宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(48);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(48);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);

        // 设置行高
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);

        // 设置字体样式
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); //默认字体大小
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getFont()->setBold(true); //粗体

        // 设置垂直、水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getAlignment()
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // 设置边框
        $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getBorders()->getAllBorders()
            ->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getBorders()->getAllBorders()
            ->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

        // 前两行单元格内容
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1','会员帐号')
            ->setCellValue('A2','用户名')
            ->setCellValue('B2','密码')
            ->setCellValue('C2','邮箱')
            ->setCellValue('D2','昵称')
            ->setCellValue('E2','登录时间')
            ->setCellValue('F2','登录次数')
            ->setCellValue('G2','注册时间')
            ->setCellValue('H2','状态');

        // 数据行设置
        for($i = 0;$i < count($data);$i++){

            // 设置字段值
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i+3), $data[$i]['username']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i+3), $data[$i]['password']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i+3), $data[$i]['email']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i+3), $data[$i]['nickname']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i+3), $data[$i]['login_time']?date('Y-m-d H:i:s',$data[$i]['login_time']):'');
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i+3), $data[$i]['times']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i+3), $data[$i]['reg_time']?date('Y-m-d H:i:s',$data[$i]['reg_time']):'');
            $objPHPExcel->getActiveSheet()->setCellValue('H' . ($i+3), $data[$i]['lock']?'锁定':'正常');

            // 设置垂直、水平居中
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($i+3).':H'.($i+3))->getAlignment()
                ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            // 设置行高
            $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);

            // 设置边框
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($i+3).':H'.($i+3))->getBorders()->getAllBorders()
                ->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        }

        //sheet命名
        $objPHPExcel->getActiveSheet()->setTitle('user表');

        //默认打开的sheet
        $objPHPExcel->setActiveSheetIndex(0);

        //excel头参数
        header("Content-Type:application/vnd.ms-execl");
        header('Content-Disposition:attachment;filename=user表查询结果('.date('YmdHis').').xls');//日期文件名后缀
        header('Cache-Control:max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel2007为xlsx
        $objWriter->save('php://output');
    }

3.3 tp3.1导出csv

/**
 * 数据转csv格式的excle
 * @param  array $data      需要转的数组
 * @param  string $header   要生成的excel表头
 * @param  string $filename 生成的excel文件名
 *      示例数组:
        $data = array(
            '1,2,3,4,5',
            '6,7,8,9,0',
            '1,3,5,6,7'
            );
        $header='用户名,密码,头像,性别,手机号';
 */
function create_csv($data,$header=null,$filename='simple.csv'){
    // 如果手动设置表头;则放在第一行
    if (!is_null($header)) {
        array_unshift($data, $header);
    }
    // 防止没有添加文件后缀
    $filename=str_replace('.csv', '', $filename).'.csv';
    ob_clean();
    Header( "Content-type:  application/octet-stream ");
    Header( "Accept-Ranges:  bytes ");
    Header( "Content-Disposition:  attachment;  filename=".$filename);
    foreach( $data as $k => $v){
        // 如果是二维数组;转成一维
        if (is_array($v)) {
            $v=implode(',', $v);
        }
        // 替换掉换行
        $v=preg_replace('/\\s*/', '', $v);
        // 解决导出的数字会显示成科学计数法的问题
        $v=str_replace(',', "\\t,", $v);
        // 转成gbk以兼容office乱码的问题
        echo iconv('UTF-8','GBK',$v)."\\t\\r\\n";
    }
}

$data=array(
    '1,2,3,4,5',
    '6,7,8,9,0',
    '1,3,5,6,7'
    );
create_csv($data);

 

 

 

 

 

 

 

 

 

 

 

 

N、参考文章

本文写作时,参考了以下文章及网站,在此表示感谢:

白俊遥博客,thinkphp整合系列之phpexcel导入excel数据

展开阅读全文
打赏
0
1 收藏
分享
加载中
更多评论
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部