phpexcel 不确定结束单元格的导出并循环

来源：互联网发布：财经类杂志软件编辑：程序博客网时间：2024/05/16 16:01
try {    $nt_id = intval ( $this->_request->getParam ( 'nt_id' ) );    $row = $this->model->getRowById ( $nt_id );    $data = $this->ntdmodel->getListByZid ( $nt_id );/*明细表头*/    require 'PHPExcel/PHPExcel.php';    $objPHPExcel = new PHPExcel ();    $objPHPExcel->getActiveSheet ()->setCellValue ( 'A1', '('.$row ['nt_name'].')询价报价单' );    $endcell = chr ( ord ( 'A' ) + count ( $data ) - 1 );    $objPHPExcel->getActiveSheet ()->mergeCells ( 'A1:' . $endcell . '1' );    /*取得完成询价的厂商*/    $enterlist = $this->model->getOverenter($nt_id);    $col=2;    foreach($enterlist as $e){        // 合并单元格        $objPHPExcel->getActiveSheet ()->mergeCells ( 'B'.$col.':' . $endcell . $col );        $objPHPExcel->getActiveSheet ()->setCellValue ( 'A'.$col.'', '致：');        $objPHPExcel->getActiveSheet ()->setCellValue ( 'B'.$col.'', $row['es_name']);        ++$col;        $objPHPExcel->getActiveSheet ()->mergeCells ( 'B'.$col.':' . $endcell . $col );        $objPHPExcel->getActiveSheet ()->setCellValue ( 'A'.$col.'', '项目编号：');        $objPHPExcel->getActiveSheet ()->setCellValue ( 'B'.$col.'', $row['nt_bianhao']);        $i = 0;        ++$col;        foreach ( ( array ) $data as $rows ) {            // 列名            $objPHPExcel->getActiveSheet ()->setCellValue ( chr ( ord ( 'A' ) + $i ).$col, $rows ['ntd_title'] );            // 列宽            $objPHPExcel->getActiveSheet ()->getColumnDimension ( chr ( ord ( 'A' ) + $i ) )->setWidth ( 15 );            $i ++;        }        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col )->getFont ()->setSize ( 13 ); // 字体大小        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col.':' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getFont ()->setSize ( 12 ); // 字体大小        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col.':' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getFont ()->setBold ( true ); // 粗体        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col.':' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getFont ()->setName ( '宋体' );        /*厂商询价明细数据*/        ++$col;//5        $mxdata = $this->ntcmodel->getlistByZidesid($nt_id,null,$e['es_id']);        for($mi=0;$mi<count($mxdata);$mi++){            $value = $mxdata[$mi]['ntc_value'];            if($mxdata[$mi]['ntc_order']==4){                $value=$mxdata[$mi]['nta_price'];            }            if($mxdata[$mi]['ntc_order']==5){                $value=$mxdata[$mi]['nta_money'];            }            if($mi!=0 && $mi%count($data)==0){                ++$col;                $next=0;            }            $objPHPExcel->getActiveSheet ()->setCellValue ( chr ( ord ( 'A' ) + (($next==0||$next)?$next:$mi) ) . $col, $value );            if($next==0||$next)                ++$next;        }        unset($next);        /*问答部分*/        $queslist = $this->model->getquestion($nt_id,$e['es_id']);        ++$col;        $objPHPExcel->getActiveSheet ()->setCellValue ( 'A'.$col.'', '问题');        $objPHPExcel->getActiveSheet ()->mergeCells ( 'A'.$col.':D' . $col );        $objPHPExcel->getActiveSheet ()->setCellValue ( 'E'.$col.'', '选择答案');        $objPHPExcel->getActiveSheet ()->setCellValue ( 'F'.$col.'', '文本答案');        $objPHPExcel->getActiveSheet ()->mergeCells ( 'F'.$col.':' . $endcell . $col );        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col )->getFont ()->setSize ( 13 ); // 字体大小        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col.':' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getFont ()->setSize ( 12 ); // 字体大小        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col.':' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getFont ()->setBold ( true ); // 粗体        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col.':' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getFont ()->setName ( '宋体' );        ++$col;        $qnum=1;        foreach($queslist as $q){            $objPHPExcel->getActiveSheet ()->setCellValue ( 'A'.$col.'', $qnum.'、'.$q['q_title']);            $objPHPExcel->getActiveSheet ()->mergeCells ( 'A'.$col.':D' . $col );            $objPHPExcel->getActiveSheet ()->setCellValue ( 'E'.$col.'', $q['qd_answer']);            $objPHPExcel->getActiveSheet ()->setCellValue ( 'F'.$col.'', $q['qd_text']);            $objPHPExcel->getActiveSheet ()->mergeCells ( 'F'.$col.':' . $endcell . $col );            ++$col;$qnum++;        }        $objPHPExcel->getActiveSheet ()->setCellValue ( 'A'.$col.'', '询价单位:'.$row['es_name']);        $objPHPExcel->getActiveSheet ()->mergeCells ( 'A'.$col.':C' . $col );        $objPHPExcel->getActiveSheet ()->setCellValue ( 'D'.$col.'', '报价单位:'.$e['es_name']);        $objPHPExcel->getActiveSheet ()->mergeCells ( 'D'.$col.':' . $endcell . $col );        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col )->getFont ()->setSize ( 13 ); // 字体大小        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col.':' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getFont ()->setSize ( 12 ); // 字体大小        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col.':' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getFont ()->setBold ( true ); // 粗体        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col.':' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getFont ()->setName ( '宋体' );        ++$col;        $objPHPExcel->getActiveSheet ()->setCellValue ( 'A'.$col.'', '询价截止时间:'.$row['nt_endyxq']);        $objPHPExcel->getActiveSheet ()->mergeCells ( 'A'.$col.':C' . $col );        $objPHPExcel->getActiveSheet ()->setCellValue ( 'D'.$col.'', '报价联系人:'.$e['es_contact'].'             '.'手机:'.$e['es_mobile']);        $objPHPExcel->getActiveSheet ()->mergeCells ( 'D'.$col.':' . $endcell . $col );        ++$col;        $objPHPExcel->getActiveSheet ()->setCellValue ( 'A'.$col.'', '询价联系人:'.$row['nt_fzrname'].'           '.'电话:'.$row['nt_tel']);        $objPHPExcel->getActiveSheet ()->mergeCells ( 'A'.$col.':C' . $col );        $objPHPExcel->getActiveSheet ()->setCellValue ( 'D'.$col.'', '联系电话:'.$e['es_tel'].'                   '.'邮箱:'.$e['es_email']);        $objPHPExcel->getActiveSheet ()->mergeCells ( 'D'.$col.':' . $endcell . $col );        ++$col;        /*询价描述*/        $objPHPExcel->getActiveSheet ()->getStyle ( 'A'.$col.':' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getAlignment ()->setWrapText ( true );        $objPHPExcel->getActiveSheet ()->setCellValue ( 'A'.$col.'', '任务描述:'.strip_tags($row['nt_memo']));        $objPHPExcel->getActiveSheet ()->mergeCells ( 'A'.$col.':' . $endcell . $col );        ++$col;    }    // 设置行高    $objPHPExcel->getActiveSheet ()->getRowDimension ( 1 )->setRowHeight ( 42 );    $objPHPExcel->getActiveSheet ()->getRowDimension ( 1 )->setRowHeight ( 42 );    // 设置字体    $objPHPExcel->getActiveSheet ()->getStyle ( 'A1' )->getFont ()->setSize ( 13 ); // 字体大小    $objPHPExcel->getActiveSheet ()->getStyle ( 'A1:' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . '1' )->getFont ()->setSize ( 12 ); // 字体大小    $objPHPExcel->getActiveSheet ()->getStyle ( 'A1:' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . '1' )->getFont ()->setBold ( true ); // 粗体    $objPHPExcel->getActiveSheet ()->getStyle ( 'A1:' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . '1' )->getFont ()->setName ( '宋体' );    // 设置边框    $objPHPExcel->getActiveSheet ()->getStyle ( 'A1:' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . $col )->getBorders ()->getAllBorders ()->setBorderStyle ( PHPExcel_Style_Border::BORDER_THIN ); // 设置边框    // 设置对齐    $objPHPExcel->getActiveSheet ()->getStyle ( 'A1:' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . '1' )->getAlignment ()->setVertical ( PHPExcel_Style_Alignment::VERTICAL_CENTER ); // 垂直居中    $objPHPExcel->getActiveSheet ()->getStyle ( 'A1:' . chr ( ord ( 'A' ) + count ( $data ) - 1 ) . '1' )->getAlignment ()->setHorizontal ( PHPExcel_Style_Alignment::HORIZONTAL_CENTER ); // 水平居中    // 设置方向及大小    $objPHPExcel->getActiveSheet ()->getPageSetup ()->setOrientation ( PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE );    $objWriter = PHPExcel_IOFactory::createWriter ( $objPHPExcel, 'Excel5' ); // 创建表格类型，目前支持老版的excel5,和excel2007,也支持生成html,pdf,csv格式    $sheetname = $row ['nt_name'] . "厂商询价数据.xls";    $filename = iconv ( "utf-8", "gb2312", $sheetname );    header ( 'Content-Type: application/vnd.ms-excel' );    header ( 'Content-Disposition: attachment;filename="' . $filename . '"' );    header ( 'Cache-Control: max-age=0' );    $objWriter->save ( 'php://output' );    die ( json_encode ( array (        'message' => '导出完成',        'success' => true    ) ) );} catch ( Exception $e ) {    print_r($e->getMessage());    die ( json_encode ( array (        'message' => '导出失败',        'success' => false    ) ) );}
0 0