PHPexcel 导出身份证问题

Post Time:2019-04-10 09:54:52 Views:1.99 K

$objPHPExcel->getActiveSheet()->setCellValue('C'.$i , $cardid);

上面代码导出excel,身份证格式会转成科学计数,添加转义符 “\t”,如下

$objPHPExcel->getActiveSheet()->setCellValue('C'.$i , $cardid."\t");

附录一段PHPexcel的导出代码

        public function getExcel(){
        $area=$this->input->post("keyword");
        $starttime=$this->input->post("txtstarttime");
        $endtime=$this->input->post("txtendtime");
        if($starttime!=''){
            $starttime=date("Y-m-d",strtotime($starttime));
            $starttime=$starttime.' 00:00:00';
        }
        if($endtime!=''){
            $endtime=date("Y-m-d",strtotime($endtime));
            $endtime=$endtime.' 23:59:59';
        }

        $data=$this->Police->getExcel($area,$starttime,$endtime);

        $this->load->database();
        $this->load->library('PHPExcel');
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle('警医邮预约订单');
        $objPHPExcel->getActiveSheet()->mergeCells('A1:I1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', '警医邮预约订单');

        $objPHPExcel->getActiveSheet()->getStyle('A:H')->getFont()->setSize(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
        $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()->getColumnDimension('I')->setWidth(30);

        $objPHPExcel->getActiveSheet()->getStyle('A:I')->getAlignment()->setWrapText(true);

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(
            array(
                'font' => array (
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                )
            )
        );

        $objPHPExcel->getActiveSheet()->setCellValue('A2', '预约流水')
            ->setCellValue('B2', '姓名')
            ->setCellValue('C2', '身份证号')
            ->setCellValue('D2', '电话')
            ->setCellValue('E2', '办理网点')
            ->setCellValue('F2', '渠道来源')
            ->setCellValue('G2', '预约时间')
            ->setCellValue('H2', '下单时间')
            ->setCellValue('I2', '备注');

        for($i=0;$i<count($data);$i++){
            $objPHPExcel->getActiveSheet()->setCellValue('A'.($i+3), $data[$i]['id'])
                ->setCellValue('B'.($i+3), $data[$i]['name'])
                ->setCellValue('C'.($i+3), $data[$i]['idno']."\t")
                ->setCellValue('D'.($i+3), $data[$i]['phone'])
                ->setCellValue('E'.($i+3), $data[$i]['place'])
                ->setCellValue('F'.($i+3), $data[$i]['channel'])
                ->setCellValue('G'.($i+3), date('Y-m-d',strtotime($data[$i]['seltime'])))
                ->setCellValue('H'.($i+3), $data[$i]['intime'])
                ->setCellValue('I'.($i+3), $data[$i]['orther']);
        }

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $failname=date("Ymd-His") . '-' . rand(100,999);
        $objWriter->save("excel/".$failname.".xlsx");
        $name['name']=$failname.".xlsx";
        ajax_success($name,NULL);
    }

model查询方法

    /**
     * 导出预约excel
     */
    function getExcel($keyword,$starttime,$endtime){
        set_time_limit(0);
        $this->load->helper("budata");

        $sql="SELECT id,name,idno,phone,place,orther,channel,seltime,intime FROM `gzyz_adinfo` WHERE status > 0";
        $params=array();
        if(!isEmpty($starttime)){
            $sql.=" and intime >=?";
            array_push($params, $starttime);
        }
        if(!isEmpty($endtime)){
            $sql.=" and intime <=?";
            array_push($params, $endtime);
        }
        if(!isEmpty($keyword)){
            $sql.=" and (name like concat('%',?,'%') or idno like concat('%',?,'%'))";
            array_push($params, $keyword);
            array_push($params, $keyword);
        }
        $sql.=" order by id";
        $data=$this->db->query($sql,$params)->result_array();
        return $data;
    }

Comments: 2
Time: 2019-04-15 23:54:08
文章不错非常喜欢
Time: 2019-05-08 20:56:15
我当初很简单的解决办法就是加空格。。。

「人生在世,留句话给我吧」

撰写评论