关于PHPExcel导出单元格换行的问题

        最近有个需求是导出Excel表格,其中涉及到单元格手动换行的问题:作者1、作者2和作者3这三列,是由 姓名 手机号 单位 和科室拼接的,打算在同一单元格换行显示。

        实现方法是,用 "\r\n" 来换行,前提是,必须先设置一下单元格自动换行,具体代码为:$objPHPExcel->getActiveSheet()->getStyle('H'.$column)->getAlignment()->setWrapText(true);

        搜索到有的教程说 \r\n 必须是双引号,所以我这边直接设置的是双引号,单引号没有进行测试,小伙伴们如果换行不成功的话可以排除一下这个原因~

        粘一下我的代码吧,供参考。

        $where = array();
        $status = input("status");
        if($status||$status=='0'){
            $where['status'] = $status;
        }
        $list = db('gaojian')->where($where)->order('id desc')->select();
        
        vendor("PHPExcel.PHPExcel.IOFactory");
        $date = date("Y_m_d",time());
        $fileName = spstatus($status)."稿件列表_{$date}.xlsx";
        // //创建新的PHPExcel对象
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();
        // //设置表头
        // $key = ord("A");
        // $key2 = ord("A");
        // $colum2 = '';
        $objActSheet = $objPHPExcel->getActiveSheet();

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);//id
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);//作者姓名
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);//手机号
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);//邮箱地址
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);//省市县
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);//详细地址
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(50);//单位
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(35);//作者1
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(35);//作者2
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);//作者3
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(80);//稿件标题
        // $objPHPExcel->getActiveSheet()->getColumnDimension('')->setWidth(20);//附件
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);//提交时间
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);//阶段
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(30);//备注
        // $objPHPExcel->getActiveSheet()->getColumnDimension('')->setWidth(20);//是否支付
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);//支付方式
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(60);//支付单号

        

        // foreach($cloumnArr as $k=>$v){
        //     $objPHPExcel->getActiveSheet()->getColumnDimension($k)->setWidth(25);
        // }
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);   // 设置行高
        $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(30);   // 设置行高

        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', 'ID');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('B1', '作者姓名');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('C1', '手机号');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('D1', '邮箱地址');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('E1', '省市县');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('F1', '详细地址');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('G1', '单位');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('H1', '作者1');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('I1', '作者2');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('J1', '作者3');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('K1', '稿件标题');
        // $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('I1', '附件地址');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('L1', '提交时间');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('M1', '阶段');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('N1', '备注');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('O1', '支付方式');
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('P1', '支付单号');
        //exit;
        $column = 2;
        foreach($list as $key => $value){
            $column = $key+2;
            $objPHPExcel->getActiveSheet()->getRowDimension($column)->setRowHeight(30);   // 设置行高
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$column,$value['id']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$column,$value['writer']);    
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$column,$value['phone']); 
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$column,$value['email']);    
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$column,$value['province'].$value['city'].$value['area']); 
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$column,$value['address']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$column,$value['company']);
            $objPHPExcel->getActiveSheet()->getStyle('H'.$column)->getAlignment()->setWrapText(true);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$column,$value['writer']."\r\n".$value['phone']."\r\n".$value['company']."\r\n".$value['keshi']);
            $objPHPExcel->getActiveSheet()->getStyle('I'.$column)->getAlignment()->setWrapText(true);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$column,$value['writer1']."\r\n".$value['phone1']."\r\n".$value['company1']."\r\n".$value['keshi1']);
            $objPHPExcel->getActiveSheet()->getStyle('J'.$column)->getAlignment()->setWrapText(true);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$column,$value['writer2']."\r\n".$value['phone2']."\r\n".$value['company2']."\r\n".$value['keshi2']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$column,$value['title']);
            // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$column,"点击查看");
            // $objPHPExcel->getActiveSheet()->getCell('I'.$column)->getHyperlink()->setUrl("https://www.yxyjzz.com/public/static/index/uploads/gaojian/".$value['file']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L'.$column,date('Y-m-d H:i:s',$value['add_time']));
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M'.$column,spstatus($value['status']));
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N'.$column,$value['beizhu']);
            $pay_type = "";
            if($value['pay_type']==1){
                $pay_type = "支付宝";
            }elseif($value['pay_type']==2){
                $pay_type = "微信";
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O'.$column,$pay_type);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P'.$column,$value['trade_no']);
        }
        //***********************画出单元格边框*****************************
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                    //'color' => array('argb' => 'FFFF0000'),
                ),
            ),
        );

        $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$column)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //  单元格居中
         $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$column)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //  垂直居中
        $fileName = iconv("utf-8", "gb2312", $fileName);
        //重命名表
        $objPHPExcel->getActiveSheet()->setTitle('稿件列表');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        ob_end_clean();
        ob_start();
        //将输出重定向到一个客户端web浏览器(Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename=\"$fileName\"");
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
 
        $objWriter->save('php://output');
        exit;

参考的是这位同学的原创文章,他的描述很简洁,小伙伴们可以前去围观PHP导出Excel单元格内换行_是三哥呀的博客-CSDN博客_phpexcel 换行

  • 2
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
要使用PHPExcel导出Excel文件,首先需要下载PHPExcel库并将其放置在自己的网页代码目录中。然后在代码中引入PHPExcel对象,并通过调用`require_once("phpExcel_path")`函数来引入PHPExcel库。接下来,创建一个PHPExcel对象和一个PHPExcel_Writer_Excel5对象,并将PHPExcel对象传递给PHPExcel_Writer_Excel5对象作为参数。这样就可以对即将导出的Excel表格进行格式设置。 如果需要在Yii框架中使用PHPExcel导出Excel,可以参考Yii相关文档中关于PHPExcel的设置以及相关文件的修改技巧。 如果需要将不同的数据存入不同的sheet表中,可以使用`$excel->createSheet()`方法创建一个新的sheet,并通过`$excel->setActiveSheetIndex('索引')`方法设置当前活动的sheet索引,`$excel->getActiveSheet()->setTitle('sheet名称')`方法设置sheet的名称。这样就可以创建多个sheet了。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *3* [php导出Excel表格](https://blog.csdn.net/weixin_45647036/article/details/124247645)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* [Yii中使用PHPExcel导出Excel的方法](https://download.csdn.net/download/weixin_38528459/13038777)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值