登录

thinkphp - phpExcel怎么取得指定单元格的位置?

 <?php
 public function excelExport($customerList=false){
        C('OUTPUT_ENCODE', false);
        import("ORG.PHPExcel.PHPExcel");
        $objPHPExcel = new PHPExcel();
        $objProps = $objPHPExcel->getProperties();
        $objProps->setCreator("5kcrm");
        $objProps->setLastModifiedBy("5kcrm");
        $objProps->setTitle("5kcrm Customer");
        $objProps->setSubject("5kcrm Customer Data");
        $objProps->setDescription("5kcrm Customer Data");
        $objProps->setKeywords("5kcrm Customer Data");
        $objProps->setCategory("5kcrm");
        $objPHPExcel->setActiveSheetIndex(0);
        $objActSheet = $objPHPExcel->getActiveSheet();

        $objActSheet->setTitle('Sheet1');
        $ascii = 65;
        $cv = '';
        $field_list = M('Fields')->where('model = \'customer\'')->order('order_id')->select();
        $secondIndex = 0;
        foreach($field_list as $field){
            $cellName = $cv.chr($ascii).'1';
            $objActSheet->setCellValue($cv.chr($ascii).'1', $field['name']);
            $ascii++;
            if($ascii == 91){
                $ascii = 65;
                $cv = chr( $secondIndex + 65);
                $secondIndex++;
            }
        }



        $mark_customer_ascii = $ascii;
        $mark_customer_cv = $cv;
        //联系人字段
        $contacts_fields_list = array();
        $contacts_fields_list[0]['field'] = 'name';
        $contacts_fields_list[0]['name'] = '联系人姓名';
        $contacts_fields_list[1]['field'] = 'saltname';
        $contacts_fields_list[1]['name'] = '尊称';
        $contacts_fields_list[2]['field'] = 'post';
        $contacts_fields_list[2]['name'] = '职位';
        $contacts_fields_list[3]['field'] = 'telephone';
        $contacts_fields_list[3]['name'] = '电话';
        $contacts_fields_list[4]['field'] = 'email';
        $contacts_fields_list[4]['name'] = '邮件';
        $contacts_fields_list[5]['field'] = 'qq_no';
        $contacts_fields_list[5]['name'] = 'qq';
        $contacts_fields_list[6]['field'] = 'zip_code';
        $contacts_fields_list[6]['name'] = '邮编';
        $contacts_fields_list[7]['field'] = 'address';
        $contacts_fields_list[7]['name'] = '联系地址';
        $contacts_fields_list[8]['field'] = 'description';
        $contacts_fields_list[8]['name'] = '备注';

        $secondIndex = 0;

        foreach($contacts_fields_list as $field){
         
            $objActSheet->setCellValue($cv.chr($ascii).'1', $field['name']);
            $ascii++;
            if($ascii == 91){
                $ascii = 65;
                $cv = chr($secondIndex +65);
                $secondIndex++;
            }
        }
        $mark_contacts_ascii = $ascii;
        $mark_contacts_cv = $cv;

        if(is_array($customerList)){
            $list = $customerList;
        }else{
            $where['owner_role_id'] = array('in',implode(',', getSubRoleId()));
            $where['is_deleted'] = 0;
            $list = M('Customer')->where($where)->select();
        }

        $i = 1;
        foreach ($list as $k => $v) {
            $date = M('CustomerData')->where("customer_id = $v[customer_id]")->find();
            if(!empty($date)){
                $v = $v+$date;
            }
            $i++;
            $ascii = 65;
            $cv = '';
            $secondIndex = 0;

            foreach($field_list as $field){
                if($field['form_type'] == 'datetime'){
                    $objActSheet->setCellValue($cv.chr($ascii).$i, date('Y-m-d',$v[$field['field']]));
                }elseif($field['form_type'] == 'number' || $field['form_type'] == 'floatnumber' || $field['form_type'] == 'phone' || $field['form_type'] == 'mobile' || ($field['form_type'] == 'text' && is_numeric($v[$field['field']]))){
                    //防止使用科学计数法,在数据前加空格
                    $objActSheet->setCellValue($cv.chr($ascii).$i, ' '.$v[$field['field']]);
                }else{
                    $objActSheet->setCellValue($cv.chr($ascii).$i, $v[$field['field']]);
                }
                $ascii++;
                if($ascii == 91){
                    $ascii = 65;
                    $cv = chr($secondIndex+65);
                    $secondIndex++;
                }
            }
            //联系人
            $mark_ascii = $ascii;
            $mark_cv = $cv;
            $m_contacts = M('contacts');
            $m_rContactsCustomer = M('rContactsCustomer');
            $contactsIdArr = $m_rContactsCustomer->where('customer_id = %d', $v['customer_id'])->getField('contacts_id',true);
            $contacts_list = $m_contacts->field('name,saltname,post,telephone,email,qq_no,zip_code,address,description')->where(array('contacts_id'=>array('in',$contactsIdArr)))->select();
            if($contacts_list){
                foreach($contacts_list as $val){
                    foreach($contacts_fields_list as $valu){
                        //防止使用科学计数法,在数据前加空格
                        if($valu['field'] == 'telephone' || $valu['field'] =='qq_no'){
                            //            $objActSheet->setCellValue($cv.chr($ascii).$i, ' '.$val[$valu['field']]);
                        }else{
                            //            $objActSheet->setCellValue($cv.chr($ascii).$i, $val[$valu['field']]);
                        }
                        $ascii++;
                        if($ascii == 91){
                            $ascii = 65;
                            $cv .= chr(strlen($cv)+65);
                        }
                    }
                    $ascii = $mark_ascii;
                    $cv = $mark_cv;
                    $i++;
                }
                //$ascii--;
                $i--;
            }
        }
       
        $objActSheet->getStyle('CK1:CS1')->getFont()->getColor()->setARGB('FFFF0000');
        $current_page = intval($_GET['current_page']);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel;");
        header("Content-Disposition:attachment;filename=gscrm_customer_".date('Y-m-d',mktime())."_".$current_page.".xls");
        header("Pragma:no-cache");
        header("Expires:0");
        $objWriter->save('php://output');
        session('export_status', 0);
   $objActSheet->getStyle('CK1:CS1')->getFont()->getColor()->setARGB('FFFF0000');

这一行代码是给$contacts_fields_list这个数组设置背景颜色,请问我怎么取得这个数组的位置,是Excel文件的最后9个单元格,因为想现在这样 getStyle('CK1:CS1')写死的话字段有变化就错位了,CK1是倒数第9个,CS1是最后一个,怎么取得这两个位置,谢谢.

# PHP
PHPzhongPHPzhong2146 天前493 次浏览

全部回复(1) 我要回复

  • 高洛峰

    高洛峰2017-04-11 09:10:29

    $allColumn = $objActSheet->getHighestColumn(); //获取列数

    然后根据列数倒数?

    回复
    0
  • 取消回复发送