【夜半写点啥】phpExcel读写excel模板并导入数据及图片

【概】最近一项工作是将保存在云端的工作数据和图片以报表形式导出到excel中，更直观体现工作数据。服务器端是php，自然用到了phpexcel。

【注】值得注意的是，偶尔在上传图片文件中可能会存在图片损坏或根据数据找不到图片的情况，这样以来phpexcel便不能正确导出excel，于是便想到了再向excel添加图片前检查一遍图片是否存在（用上了@fopen($url, 'r' )），若不存在则用默认（default.jpg）代替。

include '../excel/PHPExcel/IOFactory.php';
    $sql="select * from imgrecords where iid='{$iid}'";
    $row=$db->getRow($sql);
    $sdesc=$row['sdesc'];//项目描述
    $subject=json_decode($row['subject']);//项目耗用时间数组
    $client=$row['iclient'];
    $sqc="select * from client where cname='{$client}'";
    $roc=$db->getRow($sqc);
    $address=$roc['address'];
    $par = "/[\x80-\xff]/";
    $tel=preg_replace($par,"",$roc['tel']);
    $staff=$row['istaff'];
    $s=date('Y-m-d H:i:s',strtotime('-4 hour',strtotime($row['itime'])));
    $e=date('Y-m-d H:i:s',strtotime('+4 hour',strtotime($row['itime'])));
    $time=date("Y-m-d",strtotime($row['itime']));
    $usetime=$row['iusetime']*1+$row['iuntime'];
    $items=array(
        '项目1','项目2','项目3','其他'
    );
    //创建一个读Excel模版的对象
    $objReader = PHPExcel_IOFactory::createReader ( 'Excel5' );
    $objPHPExcel = $objReader->load ("../temple/sl.xls" );
    //获取当前活动的表
    $i=0;
    foreach ($items as $val){
        $words="总计用时".$usetime."分钟，总计工作时间".$row['iusetime']."分钟，本项目【".$val."】用时".$subject[$i]."分钟。";
        $objPHPExcel->setActiveSheetIndex($i);//把新创建的sheet设定为当前活动sheet
        $objActSheet = $objPHPExcel->getActiveSheet ();
        $objActSheet->setTitle ( $val );
        $objActSheet->setCellValue ( 'B2', $client );
        $objActSheet->setCellValue ( 'E2', $tel );
        $objActSheet->setCellValue ( 'B3', $address);
        $objActSheet->setCellValue ( 'E3', date ( 'Y年m月d日', time () ));
        $objActSheet->setCellValue ( 'B4', $staff );
        $objActSheet->setCellValue ( 'E4', $time );
        $objActSheet->setCellValue ( 'B9', $words);
        $objActSheet->setCellValue ( 'B10', $sdesc);
        switch ($i)
        {
            case 0:
                $sql="select * from images,getimg where getimg.gettime between '{$s}' and '{$e}' and getimg.gkeys=images.keys and getimg.gclient='{$client}' and getimg.username='{$staff}' and getimg.gclass like '%类型1%'";
                break;
            case 1:
                $sql="select * from images,getimg where getimg.gettime between '{$s}' and '{$e}' and getimg.gkeys=images.keys and getimg.gclient='{$client}' and getimg.username='{$staff}' and getimg.gclass like '%类型2%'";
                break;
            case 2:
                $sql="select * from images,getimg where getimg.gettime between '{$s}' and '{$e}' and getimg.gkeys=images.keys and getimg.gclient='{$client}' and getimg.username='{$staff}' and getimg.gclass like '%类型3%'";
                break;
            default:
                $sql="select * from images,getimg where getimg.gettime between '{$s}' and '{$e}' and getimg.gkeys=images.keys and getimg.gclient='{$client}' and getimg.username='{$staff}' and getimg.gclass like '%其他%'";
        }
        $rows=$db->getAll($sql);
        $n=12;$m=A;
        foreach ($rows as $v){
            if($m>F){
                $m=A;
                $n++;
            }
            $url='../../../remote/upload/images/'.$v['img'];
            if( @fopen($url, 'r' ) )
            {
            }
            else
            {
                $url="../../img/icon2.png";
            }
            // 图片生成start
            $objDrawing[$i] = new \PHPExcel_Worksheet_Drawing();
            $objDrawing[$i]->setPath($url);
            // 设置宽度高度
            $objDrawing[$i]->setHeight(80);//照片高度
            $objDrawing[$i]->setWidth(80); //照片宽度
            /*设置图片要插入的单元格*/
            $objDrawing[$i]->setCoordinates($m.$n);
            // 图片偏移距离
            $objDrawing[$i]->setOffsetX(12);
            $objDrawing[$i]->setOffsetY(12);
            $objDrawing[$i]->setWorksheet($objPHPExcel->getActiveSheet());
            //图片生成end
            $objActSheet->getRowDimension($n)->setRowHeight(100);
            $m++;
        }
        $i++;
    }
    $filename = time ();
    $objWriter = PHPExcel_IOFactory::createWriter ( $objPHPExcel, 'Excel5' ); //在内存中准备一个excel2003文件
    $objWriter->save("../excel/download/".$filename.".xls");//保存文件
    echo $filename.".xls";

wish see you again