<?php
/**
 * Created by JetBrains PhpStorm.
 * User: user
 * Date: 15-9-11
 * Time: 10:01
 * To change this template use File | Settings | File Templates.
 */
class Ship_IntoTaxController extends Ec_Controller_Action {

    public function preDispatch() {
        $this->tplDirectory = "ship/views/into-";
        // $this->tplDirectory = "merchant/account_config/";
        $this->customerAuth = new Zend_Session_Namespace("customerAuth");
    }

    /* 集报数据 */

    public function listAction() {
        if ($this->getRequest()->isPost()) {
            $page = $this->_request->getParam('page', 1);
            $pageSize = $this->_request->getParam('pageSize', 20);
    
            $page = $page ? $page : 1;
            $pageSize = $pageSize ? $pageSize : 20;

            $params = $this->_request->getParams();
            $receiving_code = trim($this->_request->getParam("receiving_code", ""));
            $time_start = $this->_request->getParam("time_start", "");
            $time_end = $this->_request->getParam("time_end", "");
    
            $condition = array(
                'receiving_code' => $receiving_code,
                'receiving_status_arr'=>array(4,5,6,7),
                'form_type'=>'IJ2',
                'dateFor' => $time_start,
                'dateTo' => $time_end
            );
            $total = Service_Receiving::getByCondition($condition, "count(*)");
            $page = $page > ceil($total / $pageSize) ? 1 : $page;
            $this->view->count = $total;
            $result = Service_Receiving::getByCondition($condition, "*", $pageSize, $page, array('receiving_id desc'));
            if($total > 0){
                foreach($result as $key=>&$value){
                   //$customer = Service_Customer::getByField($value['TRADE_CO'], 'trade_co');
                    	$result[$key]['customer_code']    =   $value['customer_code'];
                        $fullPath = APPLICATION_PATH .'/../data/tuishui/'.$value['receiving_code'];
                        $fullPath=str_replace("\\","/",$fullPath);
                        if(file_exists($fullPath)){
                             $result[$key]['file']=0;
                        }else{
                            $result[$key]['file']=1;
                        }
                    
                }
            }
            
            exit(Zend_Json::encode(array(
                'state' => $total ? 1 :0,
                'total' => $total,
                'data' => $result,
                'size' => $pageSize,
                'page' => $page,
            )));
        }

        echo Ec::renderTpl($this->tplDirectory . "list.tpl", 'layout');
    }

    //下载盖章说明
    public function downloadgaizhangAction() {
        $file = 'gaizhang' . date('YmdH:i:s') . ".doc";
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename=' . basename($file));
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        header('Pragma: public');
        $fullPath = APPLICATION_PATH . '/../data/file/gaizhang.doc';
        echo file_get_contents($fullPath);
        exit;
    }

    //下载委托报关
    public function downloadweituoAction() {
        $file = 'weituo' . date('YmdH:i:s') . ".doc";
        ;
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename=' . $file);
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        header('Pragma: public');
        $fullPath = APPLICATION_PATH . '/../data/file/weituo.doc';
        echo file_get_contents($fullPath);
        exit;
    }
    
    /* 报关单号码下载 colin */

    public function createAction() {
        header("Content-Type:text/html;charset=utf-8");
        $receiving_code = $this->_request->getParam('receiving_code', 0);
        if (empty($receiving_code)) {
            echo '没有传递参数';
            exit;
        }

        $receiving = Service_Receiving::getByCondition(array('receiving_code' => $receiving_code));
        if (empty($receiving)) {
            echo '入库数据找不到';
            exit;
        }
        
        $receiving = $receiving[0];
        $customer = Service_Customer::getByField($receiving['customer_code'], 'customer_code');
        $trade_co = $customer['trade_co'];
        $trade_name = $customer['trade_name'];
        $detailCondition=array('receiving_code'=>$receiving_code);
          $receivingXml = Service_ReceivingXml::getByCondition($detailCondition,'*');
         // $receivingDetail=Service_ReceivingXmlDetail::getByCondition(array('from_id'=>$receiving['receiving_code']),'*','goods_id');
          $test='/[-]+/';
          $x=array();
         /* foreach ($receivingXmlDetail as $key => &$value) {
              if (!preg_match($test, $value['sub_receiving_code'])) {
                  //$xml[$value['sub_receiving_code']][] =$value;
                    $xml[]=$value;
              }
          }*/
           foreach ($receivingXml as $key => $value) {
              if (preg_match($test, $value['sub_receiving_code'])) {
                    $x[]=$value;
              }
          }
          if(empty($x)){
          echo '数据找不到';
          exit;
          }
          $receivingXml=$x; 
     //print_r($receivingXml);exit;
          
        $count = count($receivingXml);

        //存在则清空，不存在则创建
      $fullPath = APPLICATION_PATH .'/../data/tuishui/'.$receiving['receiving_code'];
      $fullPath=str_replace("\\","/",$fullPath);
       if (!file_exists($fullPath)) {
            //mkdir($fullPath,'0777',true);
            mkdir($fullPath);
            chmod($fullPath,0777);
        }else{
           chmod($fullPath,0777);
           $dh=opendir($fullPath);
          while ($file=readdir($dh)) {
            if($file!="." && $file!="..") {
              $fpath=$fullPath."/".$file;
                  unlink($fpath);
                }
            }
            closedir($dh);
        }
   
        require_once("PHPExcel.php");
        require_once("PHPExcel/Reader/Excel2007.php");
        require_once("PHPExcel/Reader/Excel5.php");
        require_once("PHPExcel/Writer/Excel5.php");
        require_once("PHPExcel/Writer/Excel2007.php");
        require_once('PHPExcel/IOFactory.php');
        for($a = 0;$a < $count; $a++){
        $objPHPExcel = new PHPExcel();
        //$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
     
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
   
        $receivingXmlDetail=Service_ReceivingXmlDetail::getByCondition(array('sub_receiving_code'=>$receivingXml[$a]['sub_receiving_code']),'*','goods_id');
        $dataCounts=count($receivingXmlDetail);
        $page = 1;
        $pagesize = 4;
        $total_pages = ceil($dataCounts / $pagesize);
        $sheet_list = array();

        for ($i = 0; $i < $total_pages; $i++) {
            if ($i == 0) {
                $sheet['title'] = "报关单";
                $objPHPExcel->setActiveSheetIndex(0); //设置第一个工作表为活动工作
                $objPHPExcel->getActiveSheet()->setTitle($sheet['title']);
            } else {
                $sheet['title'] = "报关单" . ($i + 1);
                $msgWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $sheet['title']); //创建一个工作表
                $objPHPExcel->addSheet($msgWorkSheet); //插入工作表				
            }
            $sheet_list[] = $sheet;
        }
        foreach ($sheet_list as $index => $sheet) {
            //index相当于page								
            $objPHPExcel->setActiveSheetIndex($index); //设置第一个工作表为活动工作表
            $objPHPExcel->getActiveSheet()->setTitle($sheet['title']); //设置工作表名称
            $objPHPExcel->getActiveSheet()->getTabColor()->setARGB('FF3366FF');
            $objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
            $objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
            $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);
            //第一行
            $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
            $objPHPExcel->getActiveSheet()->setCellValue('A1', '中华人民共和国出口报关单');
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('宋体');
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(22);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(49.5);


            //第二行		
            $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(8);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(5);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(5);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->mergeCells('D2:E2');
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(8);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(8);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(5);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(7);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(7);
            $objPHPExcel->getActiveSheet()->mergeCells('J2:K2');
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(5);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(12);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(5);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(10);
            $objPHPExcel->getActiveSheet()->getStyle('A2:P2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A2:P2')->getBorders()->getBottom()->getColor()->setARGB('00000000');



            $objPHPExcel->getActiveSheet()->setCellValue('J2', '海关编号：');
            $objPHPExcel->getActiveSheet()->setCellValue('D2', '预录入编号：');
            $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);

            //第三行
            $objPHPExcel->getActiveSheet()->mergeCells('A3:B3');
            $objPHPExcel->getActiveSheet()->setCellValue('A3', '出口口岸：');
            $objPHPExcel->getActiveSheet()->mergeCells('C3:F3');
            $objPHPExcel->getActiveSheet()->mergeCells('H3:J3');
            $objPHPExcel->getActiveSheet()->mergeCells('K3:L3');
            $objPHPExcel->getActiveSheet()->mergeCells('O3:P3');
            $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getBorders()->getBottom()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('F3')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('F3')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('J3')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('J3')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('M3')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('M3')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('P3')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P3')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->setCellValue('C3', '前海港区/5349'); //预填
            $objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


            $objPHPExcel->getActiveSheet()->setCellValue('G3', '备案号');
            $objPHPExcel->getActiveSheet()->setCellValue('K3', '出口日期');
            $objPHPExcel->getActiveSheet()->setCellValue('N3', '申报日期');



            //第四行
            $objPHPExcel->getActiveSheet()->mergeCells('A4:B4');
            $objPHPExcel->getActiveSheet()->mergeCells('C4:F4');
            $objPHPExcel->getActiveSheet()->mergeCells('H4:I4');
            $objPHPExcel->getActiveSheet()->mergeCells('J4:K4');
            $objPHPExcel->getActiveSheet()->mergeCells('L4:M4');
            $objPHPExcel->getActiveSheet()->mergeCells('O4:P4');
            $objPHPExcel->getActiveSheet()->getStyle('G4')->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('O4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            //$objPHPExcel->getActiveSheet()->getStyle('O4')->getFill()->getStartColor()->setARGB('8099CC00');
            $objPHPExcel->getActiveSheet()->getStyle('A4:P4')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A4:P4')->getBorders()->getBottom()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('F4')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('F4')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('I4')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('I4')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('M4')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('M4')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('P4')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P4')->getBorders()->getRight()->getColor()->setARGB('00000000');


            $objPHPExcel->getActiveSheet()->setCellValue('A4', '经营单位');
            $objPHPExcel->getActiveSheet()->setCellValue('C4', "{$trade_name} （{$trade_co}）");
            $objPHPExcel->getActiveSheet()->getStyle('C4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('G4', '运输方式');
            $objPHPExcel->getActiveSheet()->setCellValue('J4', '运输工具名称');
            $objPHPExcel->getActiveSheet()->setCellValue('L4', '分送集报');
            $objPHPExcel->getActiveSheet()->getStyle('L4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('N4', '提运单号');


            //第五行
            $objPHPExcel->getActiveSheet()->mergeCells('A5:B5');
            $objPHPExcel->getActiveSheet()->mergeCells('C5:F5');
            $objPHPExcel->getActiveSheet()->mergeCells('G5:H5');
            $objPHPExcel->getActiveSheet()->mergeCells('I5:J5');
            $objPHPExcel->getActiveSheet()->mergeCells('K5:L5');
            $objPHPExcel->getActiveSheet()->mergeCells('O5:P5');
            $objPHPExcel->getActiveSheet()->getStyle('O5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            //$objPHPExcel->getActiveSheet()->getStyle('O5')->getFill()->getStartColor()->setARGB('8099CC00');
            $objPHPExcel->getActiveSheet()->setCellValue('A5', '发货单位');
            $objPHPExcel->getActiveSheet()->getStyle("C5")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('C5', "{$trade_name} （{$trade_co}）");
            $objPHPExcel->getActiveSheet()->setCellValue('G5', '贸易方式');
            $objPHPExcel->getActiveSheet()->getStyle('I5')->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('A5:P5')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A5:P5')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('F5')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('F5')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('J5')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('J5')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('M5')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('M5')->getBorders()->getRight()->getColor()->setARGB('00000000');


            $objPHPExcel->getActiveSheet()->getStyle('P5')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P5')->getBorders()->getRight()->getColor()->setARGB('00000000');


            $objPHPExcel->getActiveSheet()->getStyle("I5")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("M5")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("O5")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('I5', '一般贸易（0110）');
            $objPHPExcel->getActiveSheet()->setCellValue('K5', '征税性质');
            $objPHPExcel->getActiveSheet()->setCellValue('M5', '一般征税');
            $objPHPExcel->getActiveSheet()->setCellValue('N5', '结汇方式');
            $objPHPExcel->getActiveSheet()->setCellValue('O5', 'T/T');

            //第六行		
            $objPHPExcel->getActiveSheet()->mergeCells('A6:B6');
            $objPHPExcel->getActiveSheet()->mergeCells('C6:E6');
            $objPHPExcel->getActiveSheet()->mergeCells('F6:G6');
            $objPHPExcel->getActiveSheet()->mergeCells('H6:J6');
            $objPHPExcel->getActiveSheet()->mergeCells('L6:M6');
            $objPHPExcel->getActiveSheet()->mergeCells('O6:P6');
            $objPHPExcel->getActiveSheet()->getStyle('L6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('O6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle('O6')->getFill()->getStartColor()->setARGB('8099CC00');

            $objPHPExcel->getActiveSheet()->getStyle('E6')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('E6')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('J6')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('J6')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('M6')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('M6')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('P6')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P6')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('A6:P6')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A6:P6')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->setCellValue('A6', '许可证号');
            $objPHPExcel->getActiveSheet()->setCellValue('F6', '运抵国（地区）');
            $objPHPExcel->getActiveSheet()->setCellValue('H6', '中国境内');
            $objPHPExcel->getActiveSheet()->getStyle('H6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('K6', '指运港');
            $objPHPExcel->getActiveSheet()->setCellValue('L6', '中国境内');
            $objPHPExcel->getActiveSheet()->getStyle('L6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('N6', '境内货源地');
            $objPHPExcel->getActiveSheet()->setCellValue('O6', '深圳特区');

            //第七行		
            $objPHPExcel->getActiveSheet()->mergeCells('A7:B7');
            $objPHPExcel->getActiveSheet()->mergeCells('C7:E7');
            $objPHPExcel->getActiveSheet()->mergeCells('G7:H7');
            $objPHPExcel->getActiveSheet()->mergeCells('J7:K7');
            $objPHPExcel->getActiveSheet()->mergeCells('O7:P7');
            $objPHPExcel->getActiveSheet()->getStyle('G7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A7:P7')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A7:P7')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('E7')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('E7')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('H7')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('H7')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('K7')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('K7')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('M7')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('M7')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('P7')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P7')->getBorders()->getRight()->getColor()->setARGB('00000000');



            $objPHPExcel->getActiveSheet()->setCellValue('A7', '批准文号');
            $objPHPExcel->getActiveSheet()->setCellValue('F7', '成交方式');
            $objPHPExcel->getActiveSheet()->setCellValue('G7', 'FOB');
            $objPHPExcel->getActiveSheet()->getStyle('G7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('I7', '运费');
            $objPHPExcel->getActiveSheet()->setCellValue('L7', '保费');
            $objPHPExcel->getActiveSheet()->setCellValue('N7', '杂费');
            //第八行		
            $objPHPExcel->getActiveSheet()->mergeCells('A8:B8');
            $objPHPExcel->getActiveSheet()->mergeCells('C8:E8');
            $objPHPExcel->getActiveSheet()->mergeCells('H8:I8');
            $objPHPExcel->getActiveSheet()->mergeCells('K8:L8');
            $objPHPExcel->getActiveSheet()->mergeCells('O8:P8');
            $objPHPExcel->getActiveSheet()->getStyle('A8:P8')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A8:P8')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('E8')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('E8')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('G8')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('G8')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('J8')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('J8')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('M8')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('M8')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('P8')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P8')->getBorders()->getRight()->getColor()->setARGB('00000000');



            $objPHPExcel->getActiveSheet()->setCellValue('A8', '合同协议号');
            $objPHPExcel->getActiveSheet()->setCellValue('F8', '件数');
            //$objPHPExcel->getActiveSheet()->setCellValue('G8','103');
            $objPHPExcel->getActiveSheet()->setCellValue('H8', '包装种类');
            $objPHPExcel->getActiveSheet()->setCellValue('J8', '其他');
            $objPHPExcel->getActiveSheet()->setCellValue('G8', $receiving['pack_no']);
            $objPHPExcel->getActiveSheet()->getStyle('G8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('K8', '毛重（公斤）');
            $objPHPExcel->getActiveSheet()->setCellValue('M8', $receivingXml[$a]['gross_wt']);
            $objPHPExcel->getActiveSheet()->getStyle('M8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('N8', '净重（公斤）');
            $objPHPExcel->getActiveSheet()->getStyle('O8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('O8', $receivingXml[$a]['net_wt']);
            //第九行		
            $objPHPExcel->getActiveSheet()->mergeCells('A9:B9');
            $objPHPExcel->getActiveSheet()->mergeCells('C9:E9');
            $objPHPExcel->getActiveSheet()->mergeCells('G9:K9');
            $objPHPExcel->getActiveSheet()->mergeCells('M9:P9');
            $objPHPExcel->getActiveSheet()->getStyle('L9')->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('A9:P9')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A9:P9')->getBorders()->getBottom()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('E9')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('E9')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('K9')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('K9')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('P9')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P9')->getBorders()->getRight()->getColor()->setARGB('00000000');



            $objPHPExcel->getActiveSheet()->setCellValue('A9', '集装箱号');
            $objPHPExcel->getActiveSheet()->setCellValue('F9', '随附单据');

            $objPHPExcel->getActiveSheet()->setCellValue('L9', '生产厂家');

            //第10行		
            $objPHPExcel->getActiveSheet()->mergeCells('A10:C10');
            $objPHPExcel->getActiveSheet()->mergeCells('D10:F10');
            $objPHPExcel->getActiveSheet()->mergeCells('G10:H10');
            $objPHPExcel->getActiveSheet()->mergeCells('I10:P10');
            $objPHPExcel->getActiveSheet()->getStyle('A10:P10')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A10:P10')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->setCellValue('A10', '标记唛头及备注');
             $objPHPExcel->getActiveSheet()->setCellValue('G10', '入库单');
             $sum=0;
             //print_r($receivingXmlDetail);exit;
             foreach ($receivingXmlDetail as $key => $value) {
                if ($value['curr']) {
                    $currency = Service_Currency::getByField($value['curr'], 'currency_hs_code');
                    $curr=$currency['currency_code'];
                }
                 
                    $sum+=$value['decl_total'];
             }
             $objPHPExcel->getActiveSheet()->setCellValue('D10', '合计金额:'.$curr.$sum);
             $objPHPExcel->getActiveSheet()->setCellValue('I10', $receivingXml[$a]['sub_receiving_code']);
            //$objPHPExcel->getActiveSheet()->setCellValue('D10','前海湾保税港区跨境电子商务项目实测 退税 总价 CNY:577537.88');	
            $objPHPExcel->getActiveSheet()->getRowDimension('10')->setRowHeight(60);

            $objPHPExcel->getActiveSheet()->getStyle('P10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            

            $objPHPExcel->getActiveSheet()->getStyle('F10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('F10')->getBorders()->getRight()->getColor()->setARGB('00000000');
            //第11行		
            $objPHPExcel->getActiveSheet()->mergeCells('B11:C11');
            $objPHPExcel->getActiveSheet()->mergeCells('J11:L11');
            $objPHPExcel->getActiveSheet()->getRowDimension('11')->setRowHeight(15);
            $objPHPExcel->getActiveSheet()->getStyle('A11:P11')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A11:P11')->getBorders()->getBottom()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('P11')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P11')->getBorders()->getRight()->getColor()->setARGB('00000000');


            $objPHPExcel->getActiveSheet()->setCellValue('A11', '项号');
            $objPHPExcel->getActiveSheet()->setCellValue('B11', '商品编号');
            $objPHPExcel->getActiveSheet()->setCellValue('D11', '商品名称');
            $objPHPExcel->getActiveSheet()->setCellValue('D11', '商品名称');
            $objPHPExcel->getActiveSheet()->setCellValue('F11', '规格编号');
            $objPHPExcel->getActiveSheet()->setCellValue('H11', '数量');
            $objPHPExcel->getActiveSheet()->setCellValue('I11', '单位');
            $objPHPExcel->getActiveSheet()->setCellValue('J11', '最终目的国（地区）');
            $objPHPExcel->getActiveSheet()->setCellValue('M11', '单价');
            $objPHPExcel->getActiveSheet()->setCellValue('N11', '总价');
            $objPHPExcel->getActiveSheet()->setCellValue('O11', '币制');
            $objPHPExcel->getActiveSheet()->setCellValue('P11', '征免');
            //第12-26行	

            for ($j = 0; $j < 5; $j++) {
                $data_index = $index * 5 + $j;
                $start_row_number = 12 + 3 * $j;
                $end_row_number = $start_row_number + 2;

                $objPHPExcel->getActiveSheet()->mergeCells("A{$start_row_number}:A{$end_row_number}");
                $objPHPExcel->getActiveSheet()->mergeCells("B{$start_row_number}:C{$end_row_number}");
                $objPHPExcel->getActiveSheet()->mergeCells("D{$start_row_number}:D{$end_row_number}");
                $objPHPExcel->getActiveSheet()->mergeCells("E{$start_row_number}:G{$end_row_number}");
                $objPHPExcel->getActiveSheet()->mergeCells("J{$start_row_number}:K{$end_row_number}");
                $objPHPExcel->getActiveSheet()->mergeCells("M{$start_row_number}:M{$end_row_number}");
                $objPHPExcel->getActiveSheet()->mergeCells("N{$start_row_number}:N{$end_row_number}");
                $objPHPExcel->getActiveSheet()->mergeCells("O{$start_row_number}:O{$end_row_number}");
                $objPHPExcel->getActiveSheet()->mergeCells("P{$start_row_number}:P{$end_row_number}");
                $objPHPExcel->getActiveSheet()->getStyle("A{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                $objPHPExcel->getActiveSheet()->getStyle("E{$start_row_number}")->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyle("P{$start_row_number}:P{$end_row_number}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getActiveSheet()->getStyle("P{$start_row_number}:P{$end_row_number}")->getBorders()->getRight()->getColor()->setARGB('00000000');

                $objPHPExcel->getActiveSheet()->getStyle("A{$end_row_number}:p{$end_row_number}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getActiveSheet()->getStyle("A{$end_row_number}:P{$end_row_number}")->getBorders()->getBottom()->getColor()->setARGB('00000000');


                if (isset($receivingXmlDetail[$data_index])) {
                    $row = $receivingXmlDetail[$data_index];
                    if ($row['curr']) {
                        $currency = Service_Currency::getByField($row['curr'], 'currency_hs_code');
                        $row['curr'] = $currency['currency_code'];
                    }
                    if ($row['g_unit']) {
                        $utom = Service_ProductUom::getByField($row['g_unit'], 'pu_code');
                        $row['g_unit'] = $utom['pu_name'];
                    }

                    if ($row['unit_1']) {
                        $utom = Service_ProductUom::getByField($row['unit_1'], 'pu_code');
                        $row['unit_1'] = $utom['pu_name'];
                    }
                    if ($row['unit_2']) {
                        $utom = Service_ProductUom::getByField($row['unit_2'], 'pu_code');
                        $row['unit_2'] = $utom['pu_name'];
                    }

                    if ($row['origin_country']) {
                        $country = Service_Country::getByField($row['origin_country'], 'trade_country');
                        $row['origin_country'] = $country['country_name'];
                    }


                    $row['duty_mode'] = '照章纳税';
                    $row["origin_country"] = '香港/110';
                    $objPHPExcel->getActiveSheet()->getStyle("A{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("B{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("D{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("E{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("H{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->setCellValue("A{$start_row_number}", $data_index + 1);
                    $objPHPExcel->getActiveSheet()->setCellValue("B{$start_row_number}", $row["code_ts"]);
                    $objPHPExcel->getActiveSheet()->setCellValue("D{$start_row_number}", $row["g_name_cn"]);
                    $objPHPExcel->getActiveSheet()->setCellValue("E{$start_row_number}", $row["g_model"]);
                    $objPHPExcel->getActiveSheet()->setCellValue("H{$start_row_number}", $row['qty_1']);
                    if ($row['qty_2'] && $row['qty_2'] != '0') {
                        $objPHPExcel->getActiveSheet()->setCellValue("H{$end_row_number}", $row['qty_2']);
                    }
                    $objPHPExcel->getActiveSheet()->getStyle("I{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("H{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("H{$end_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("I{$end_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("J{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                    $objPHPExcel->getActiveSheet()->getStyle("M{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("N{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                    $objPHPExcel->getActiveSheet()->getStyle("O{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("P{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                    $objPHPExcel->getActiveSheet()->setCellValue("I{$start_row_number}", $row['unit_1']);
                    $objPHPExcel->getActiveSheet()->setCellValue("I{$end_row_number}", $row['unit_2']);
                    $objPHPExcel->getActiveSheet()->setCellValue("J{$start_row_number}", $row["origin_country"]);
                    $objPHPExcel->getActiveSheet()->setCellValue("M{$start_row_number}", $row['decl_price']);
                    $objPHPExcel->getActiveSheet()->setCellValue("N{$start_row_number}", $row['decl_total']);
                    $objPHPExcel->getActiveSheet()->setCellValue("O{$start_row_number}", $row['curr']);
                    $objPHPExcel->getActiveSheet()->setCellValue("P{$start_row_number}", $row['duty_mode']);
                }
            }




            //第27-29行		

            $objPHPExcel->getActiveSheet()->mergeCells('A27:P29');
            $objPHPExcel->getActiveSheet()->getStyle('A27')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
            $objPHPExcel->getActiveSheet()->setCellValue('A27', '税费征收情况');
            $objPHPExcel->getActiveSheet()->getStyle('A27:P29')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A27:P29')->getBorders()->getRight()->getColor()->setARGB('00000000');
            //第30-31行
            $objPHPExcel->getActiveSheet()->getStyle('A30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('B30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('B30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('C30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('C30')->getBorders()->getTop()->getColor()->setARGB('00000000');


            $objPHPExcel->getActiveSheet()->getStyle('D30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('D30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('D30')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('D30')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('E30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('E30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('F30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('F30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('G30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('G30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('H30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('H30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('I30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('I30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('J30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('J30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('K30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('K30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('L30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('L30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('M30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('M30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('N30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('N30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('O30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('O30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('P30')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P30')->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('P30')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P30')->getBorders()->getRight()->getColor()->setARGB('00000000');


            $objPHPExcel->getActiveSheet()->getStyle('A31')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A31')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('B31')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('B31')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('C31')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('C31')->getBorders()->getBottom()->getColor()->setARGB('00000000');


            $objPHPExcel->getActiveSheet()->getStyle('D31')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('D31')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('D31')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('D31')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('P31')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P31')->getBorders()->getRight()->getColor()->setARGB('00000000');


            $objPHPExcel->getActiveSheet()->setCellValue('A30', '录入员');
            $objPHPExcel->getActiveSheet()->setCellValue('C30', '录入单位');
            $objPHPExcel->getActiveSheet()->setCellValue('E30', '  兹申明以上申报无讹并承担法律责任');
            $objPHPExcel->getActiveSheet()->setCellValue('L30', '海关审单批注及放行日期（签章）');


            //第32-33行			
            $objPHPExcel->getActiveSheet()->getStyle('P32')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P32')->getBorders()->getRight()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getStyle('P33')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P33')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->setCellValue('A32', '报关员');
            $objPHPExcel->getActiveSheet()->setCellValue('L32', '审单');
            $objPHPExcel->getActiveSheet()->setCellValue('N32', '审价');
            $objPHPExcel->getActiveSheet()->setCellValue('G33', '申报单位（签章）');
            //第34行
            $objPHPExcel->getActiveSheet()->getStyle('P34')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P34')->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->setCellValue('A34', '单位地址');
            $objPHPExcel->getActiveSheet()->setCellValue('L34', '征税');
            $objPHPExcel->getActiveSheet()->setCellValue('N34', '统计');
            //第35行(空行)
            $objPHPExcel->getActiveSheet()->getStyle('P35')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P35')->getBorders()->getRight()->getColor()->setARGB('00000000');
            //第36行
            $objPHPExcel->getActiveSheet()->getStyle('A36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('A36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('B36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('B36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('C36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('C36')->getBorders()->getBottom()->getColor()->setARGB('00000000');


            $objPHPExcel->getActiveSheet()->getStyle('D36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('D36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('E36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('E36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('F36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('F36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('G36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('G36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('H36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('H36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('I36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('I36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('J36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('J36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('K36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('K36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('L36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('L36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('M36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('M36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('N36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('N36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('O36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('O36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('P36')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P36')->getBorders()->getBottom()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle('P36')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle('P36')->getBorders()->getRight()->getColor()->setARGB('00000000');


            $objPHPExcel->getActiveSheet()->setCellValue('A36', '邮编');
            $objPHPExcel->getActiveSheet()->setCellValue('D36', '电话');
            $objPHPExcel->getActiveSheet()->setCellValue('G36', '填制日期');
            $objPHPExcel->getActiveSheet()->setCellValue('L36', '查验');
            $objPHPExcel->getActiveSheet()->setCellValue('N36', '放行');
        }//foreach  所有报关单






        $title = "装箱单";
        $sheet_list[] = array('title' => $title);

        $msgWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $title); //创建一个工作表
        $objPHPExcel->addSheet($msgWorkSheet); //插入工作表		
        $objPHPExcel->setActiveSheetIndex(count($sheet_list) - 1);
        $objPHPExcel->getActiveSheet()->getTabColor()->setARGB('FF339966');
        $objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
        $objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(8);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(6);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
        //第1行
        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->setCellValue('E1', '装 箱 单');

        //第2-3行 
        $objPHPExcel->getActiveSheet()->mergeCells("L2:L3");
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->setCellValue('J2', '日 期：');
        $objPHPExcel->getActiveSheet()->setCellValue('J3', 'Date:');
        $objPHPExcel->getActiveSheet()->setCellValue('E2', 'PACKING LIST');
        //$objPHPExcel->getActiveSheet()->setCellValue('L2','2014.05.04');
        //第4-5行
        $objPHPExcel->getActiveSheet()->mergeCells("L4:L5");
        $objPHPExcel->getActiveSheet()->setCellValue('J4', '发 票 编 号');
        $objPHPExcel->getActiveSheet()->setCellValue('J5', 'Invoice No:');

        $objPHPExcel->getActiveSheet()->getStyle('L4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('L4')->getBorders()->getTop()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle('L5')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('L5')->getBorders()->getBottom()->getColor()->setARGB('00000000');


        //第6-7行
        $objPHPExcel->getActiveSheet()->mergeCells("L4:L5");
        $objPHPExcel->getActiveSheet()->mergeCells("C6:E7");
        $objPHPExcel->getActiveSheet()->mergeCells("L6:L7");
        $objPHPExcel->getActiveSheet()->getStyle('C7:E7')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('C7:E7')->getBorders()->getBottom()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle('L6')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('L6')->getBorders()->getTop()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle('L7')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('L7')->getBorders()->getBottom()->getColor()->setARGB('00000000');


        $objPHPExcel->getActiveSheet()->setCellValue('A6', '客 户');
        $objPHPExcel->getActiveSheet()->setCellValue('A7', 'To Messrs:');
        $objPHPExcel->getActiveSheet()->setCellValue('C6', 'A-LINK TELECOM INTERNATIONAL CO LIMITED');
        $objPHPExcel->getActiveSheet()->setCellValue('J6', '合 约 号');
        $objPHPExcel->getActiveSheet()->setCellValue('J7', 'Contract No:');
        //$objPHPExcel->getActiveSheet()->setCellValue('L6','J88880');
        //第8-9行
        $objPHPExcel->getActiveSheet()->mergeCells("E8:E9");
        $objPHPExcel->getActiveSheet()->mergeCells("G8:I9");
        $objPHPExcel->getActiveSheet()->mergeCells("L8:L9");

        $objPHPExcel->getActiveSheet()->setCellValue('A8', '船 名');
        $objPHPExcel->getActiveSheet()->setCellValue('A9', 'Shipped by');

        $objPHPExcel->getActiveSheet()->setCellValue('D8', '由');
        $objPHPExcel->getActiveSheet()->setCellValue('D9', 'From');

        $objPHPExcel->getActiveSheet()->setCellValue('F8', '至');
        $objPHPExcel->getActiveSheet()->setCellValue('F9', 'To');

        $objPHPExcel->getActiveSheet()->setCellValue('J8', '付 款 条 件');
        $objPHPExcel->getActiveSheet()->setCellValue('J9', 'Terms of Payment:');
        $objPHPExcel->getActiveSheet()->setCellValue('L8', 'T/T');
        //第10行
        $objPHPExcel->getActiveSheet()->getRowDimension('10')->setRowHeight(10);


        //第11行
        $objPHPExcel->getActiveSheet()->mergeCells("B11:D11");
        $objPHPExcel->getActiveSheet()->mergeCells("F11:I11");
        $objPHPExcel->getActiveSheet()->mergeCells("J11:K11");
        $objPHPExcel->getActiveSheet()->getStyle('A11:L11')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('A11:L11')->getBorders()->getTop()->getColor()->setARGB('00000000');
        $objPHPExcel->getActiveSheet()->getStyle('A11:L11')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('A11:L11')->getBorders()->getBottom()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("B11")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("E11")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("F11")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("J11")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("L11")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('A11')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('A11')->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->setCellValue('A11', '标记号码');
        $objPHPExcel->getActiveSheet()->setCellValue('B11', '货物名称及规格');
        $objPHPExcel->getActiveSheet()->setCellValue('E11', '总箱数');
        $objPHPExcel->getActiveSheet()->setCellValue('F11', '总数量');
        $objPHPExcel->getActiveSheet()->setCellValue('J11', '总毛重');
        $objPHPExcel->getActiveSheet()->setCellValue('L11', '净重');

        //第12行
        $objPHPExcel->getActiveSheet()->mergeCells("B12:D12");
        $objPHPExcel->getActiveSheet()->mergeCells("F12:I12");
        $objPHPExcel->getActiveSheet()->mergeCells("J12:K12");
        $objPHPExcel->getActiveSheet()->setCellValue('A12', 'Mark & No');
        $objPHPExcel->getActiveSheet()->setCellValue('B12', 'Description');
        $objPHPExcel->getActiveSheet()->setCellValue('E12', 'Ge.Crate');
        $objPHPExcel->getActiveSheet()->setCellValue('F12', 'Ge.Quantity');
        $objPHPExcel->getActiveSheet()->setCellValue('J12', 'G.W.:千克');
        $objPHPExcel->getActiveSheet()->setCellValue('L12', 'N.W.:千克');

        $objPHPExcel->getActiveSheet()->getStyle('A12')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle('A12')->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("D11:D12")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("D11:D12")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("E11:E12")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("E11:E12")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("I11:I12")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("I11:I12")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("K11:K12")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("K11:K12")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("L11:L12")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("L11:L12")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("B12")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("E12")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("F12")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("J12")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("L12")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //产品信息从13行开始
        $data_area_rows = $dataCounts > 8 ? $dataCounts : 8;

        for ($j = 0; $j < $data_area_rows; $j++) {
            $k = $j + 1;
            $row_index = $j + 13;
            $objPHPExcel->getActiveSheet()->setCellValue("A{$row_index}", $j + 1);
            $objPHPExcel->getActiveSheet()->mergeCells("B{$row_index}:D{$row_index}");
            $objPHPExcel->getActiveSheet()->mergeCells("F{$row_index}:I{$row_index}");
            $objPHPExcel->getActiveSheet()->mergeCells("J{$row_index}:K{$row_index}");

            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("B{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("F{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //水平方向上对齐
            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:L{$row_index}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:L{$row_index}")->getBorders()->getTop()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("L{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("L{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("E{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("E{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("I{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("I{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("K{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("K{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');


            if (!isset($receivingXmlDetail[$j])) {
                continue;
            }
            $prow = $receivingXmlDetail[$j];
            if ($prow['unit_1']) {
                $utom = Service_ProductUom::getByField($prow['unit_1'], 'pu_code');
                $prow['unit_1'] = $utom['pu_name'];
            }
            $QTY_1 = (FALSE !== strpos($prow['qty_1'], '.')) ? rtrim(rtrim($prow['qty_1'], '0'), '.') : $prow['qty_1'];
            $objPHPExcel->getActiveSheet()->setCellValue("B{$row_index}", $prow['g_name_cn']);
            $objPHPExcel->getActiveSheet()->setCellValue("F{$row_index}", $QTY_1 . $prow['unit_1']);
        }

        /* // 去除空白行
          //空白行
          $row_index = $row_index+1;
          $objPHPExcel->getActiveSheet()->mergeCells("B{$row_index}:D{$row_index}");
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:L{$row_index}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:L{$row_index}")->getBorders()->getTop()->getColor()->setARGB('00000000');
          $objPHPExcel->getActiveSheet()->mergeCells("B{$row_index}:D{$row_index}");
          $objPHPExcel->getActiveSheet()->mergeCells("F{$row_index}:I{$row_index}");
          $objPHPExcel->getActiveSheet()->mergeCells("J{$row_index}:K{$row_index}");

          $objPHPExcel->getActiveSheet()->getStyle("L{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("L{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("E{$row_index}:L{$row_index}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("E{$row_index}:L{$row_index}")->getBorders()->getBottom()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:B{$row_index}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:B{$row_index}")->getBorders()->getBottom()->getColor()->setARGB('00000000');
         */
        //$objPHPExcel->getActiveSheet()->setCellValue("A{$row_index}",'杨伟权');
        $totalarea_row_index = $row_index + 1; //合计所在行
        $totalarea_endrow_index = $totalarea_row_index + 1;

        //$objPHPExcel->getActiveSheet()->mergeCells("A{$totalarea_row_index}:B{$totalarea_row_index}");
        //$objPHPExcel->getActiveSheet()->mergeCells("A{$totalarea_endrow_index}:B{$totalarea_endrow_index}");
        $objPHPExcel->getActiveSheet()->mergeCells("B{$totalarea_row_index}:E{$totalarea_endrow_index}");
        $objPHPExcel->getActiveSheet()->mergeCells("J{$totalarea_row_index}:K{$totalarea_endrow_index}");
        $objPHPExcel->getActiveSheet()->mergeCells("L{$totalarea_row_index}:L{$totalarea_endrow_index}");
        //$objPHPExcel->getActiveSheet()->mergeCells("E{$totalarea_row_index}:E{$totalarea_endrow_index}");
        $objPHPExcel->getActiveSheet()->mergeCells("F{$totalarea_row_index}:I{$totalarea_endrow_index}");

        $objPHPExcel->getActiveSheet()->getStyle("A{$totalarea_endrow_index}:L{$totalarea_endrow_index}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A{$totalarea_endrow_index}:L{$totalarea_endrow_index}")->getBorders()->getBottom()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("A{$totalarea_row_index}:L{$totalarea_row_index}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A{$totalarea_row_index}:L{$totalarea_row_index}")->getBorders()->getTop()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("A{$totalarea_row_index}:A{$totalarea_endrow_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A{$totalarea_row_index}:A{$totalarea_endrow_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("D{$totalarea_row_index}:D{$totalarea_endrow_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("D{$totalarea_row_index}:D{$totalarea_endrow_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("E{$totalarea_row_index}:E{$totalarea_endrow_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("E{$totalarea_row_index}:E{$totalarea_endrow_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("I{$totalarea_row_index}:I{$totalarea_endrow_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("I{$totalarea_row_index}:I{$totalarea_endrow_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("K{$totalarea_row_index}:K{$totalarea_endrow_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("K{$totalarea_row_index}:K{$totalarea_endrow_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("L{$totalarea_row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("L{$totalarea_row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');
        $objPHPExcel->getActiveSheet()->getStyle("L{$totalarea_endrow_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("L{$totalarea_endrow_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');


        $objPHPExcel->getActiveSheet()->setCellValue("A{$totalarea_row_index}", '合计');

        $objPHPExcel->getActiveSheet()->setCellValue("A{$totalarea_endrow_index}", 'Total');

        $objPHPExcel->getActiveSheet()->getStyle("A{$totalarea_row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("A{$totalarea_endrow_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("J{$totalarea_row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("L{$totalarea_row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue("J{$totalarea_row_index}", $receivingXml[0]['gross_wt']);
        $objPHPExcel->getActiveSheet()->setCellValue("L{$totalarea_row_index}", $receivingXml[0]['net_wt']);

        $title = "发票";
        $sheet_list[] = array('title' => $title);
        $msgWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $title); //创建一个工作表
        $objPHPExcel->addSheet($msgWorkSheet); //插入工作表	
        $objPHPExcel->setActiveSheetIndex(count($sheet_list) - 1);
        $objPHPExcel->getActiveSheet()->getTabColor()->setARGB('FFFF0000');
        $objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
        $objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $basewidth=15;
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth($basewidth); //10
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth($basewidth * 0.8); //8
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth($basewidth * 1.92);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth($basewidth * 0.4);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth($basewidth * 0.72);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth($basewidth * 0.63);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth($basewidth * 0.48);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth($basewidth * 1.20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth($basewidth * 0.64);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth($basewidth * 0.80);



        //第1行
        $objPHPExcel->getActiveSheet()->mergeCells("A1:J1");
        $objPHPExcel->getActiveSheet()->setCellValue("A1", '发票');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //第2行
        $objPHPExcel->getActiveSheet()->mergeCells("A2:J2");
        $objPHPExcel->getActiveSheet()->setCellValue("A2", 'INVOICE');
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle("A2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //第3-7行
        $objPHPExcel->getActiveSheet()->mergeCells("C4:F5");
        $objPHPExcel->getActiveSheet()->mergeCells("A6:F7");
        $objPHPExcel->getActiveSheet()->mergeCells("I3:J4");
        $objPHPExcel->getActiveSheet()->mergeCells("I5:J6");

        $objPHPExcel->getActiveSheet()->getStyle("I5:J5")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("I5:J5")->getBorders()->getTop()->getColor()->setARGB('00000000');
        $objPHPExcel->getActiveSheet()->getStyle("I6:J6")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("I6:J6")->getBorders()->getBottom()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("C5:F5")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("C5:F5")->getBorders()->getBottom()->getColor()->setARGB('00000000');


        $objPHPExcel->getActiveSheet()->setCellValue("H3", '编 号');

        $objPHPExcel->getActiveSheet()->setCellValue("A4", '商  号');
        $objPHPExcel->getActiveSheet()->setCellValue("A5", 'Sold to Messrs:');
        $objPHPExcel->getActiveSheet()->setCellValue("C4", 'A-LINK TELECOM INTERNATIONAL CO LIMITED');
        $objPHPExcel->getActiveSheet()->setCellValue("H4", 'NO：');
        $objPHPExcel->getActiveSheet()->setCellValue("H5", '日期:');
        $objPHPExcel->getActiveSheet()->setCellValue("H6", 'DATE:');
        //$objPHPExcel->getActiveSheet()->setCellValue("I3",'I5585');
        //$objPHPExcel->getActiveSheet()->setCellValue("I5",'2014.05.04');
        //$objPHPExcel->getActiveSheet()->setCellValue('A10','1');
        //第8行
        //第9行
        $objPHPExcel->getActiveSheet()->mergeCells("B9:D9");
        $objPHPExcel->getActiveSheet()->mergeCells("E9:G9");
        $objPHPExcel->getActiveSheet()->mergeCells("I9:J9");
        $objPHPExcel->getActiveSheet()->getStyle('A9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('H9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('I9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('A10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('H10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('I10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue("A9", '标记号码');
        $objPHPExcel->getActiveSheet()->setCellValue("B9", '货物名称');
        $objPHPExcel->getActiveSheet()->setCellValue("E9", '数量');
        $objPHPExcel->getActiveSheet()->setCellValue("H9", '单 价');
        $objPHPExcel->getActiveSheet()->setCellValue("I9", '总金额（USD）');

        $objPHPExcel->getActiveSheet()->getStyle("A9:J9")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A9:J9")->getBorders()->getTop()->getColor()->setARGB('00000000');
        $objPHPExcel->getActiveSheet()->getStyle("A9:J9")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A9:J9")->getBorders()->getBottom()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("A9:A10")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A9:A10")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("D9:D10")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("D9:D10")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("G9:G10")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("G9:G10")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("H9:H10")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("H9:H10")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("J9:J10")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("J9:J10")->getBorders()->getRight()->getColor()->setARGB('00000000');



        //第10行
        $objPHPExcel->getActiveSheet()->mergeCells("B10:D10");
        $objPHPExcel->getActiveSheet()->mergeCells("E10:G10");
        $objPHPExcel->getActiveSheet()->mergeCells("I10:J10");
        $objPHPExcel->getActiveSheet()->setCellValue("A10", 'Mark & No');
        $objPHPExcel->getActiveSheet()->setCellValue("B10", 'Description');
        $objPHPExcel->getActiveSheet()->setCellValue("E10", 'Quantity');
        $objPHPExcel->getActiveSheet()->setCellValue("H10", 'Unit Price');
        $objPHPExcel->getActiveSheet()->setCellValue("I10", 'Amount');



        //产品信息从13行开始
        $data_area_rows = $dataCounts > 8 ? $dataCounts : 8;

        for ($j = 0; $j < $data_area_rows; $j++) {
            $k = $j + 1;
            $row_index = $j + 11;
            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue("A{$row_index}", $j + 1);
            $objPHPExcel->getActiveSheet()->mergeCells("B{$row_index}:D{$row_index}");
            $objPHPExcel->getActiveSheet()->mergeCells("E{$row_index}:G{$row_index}");
            $objPHPExcel->getActiveSheet()->mergeCells("I{$row_index}:J{$row_index}");

            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getRowDimension($row_index)->setRowHeight(30);
            $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');


            if (!isset($receivingXmlDetail[$j])) {
                continue;
            }
            $prow = $receivingXmlDetail[$j];
            if ($prow['g_unit']) {
                $QTY_1 = (FALSE !== strpos($prow['qty_1'], '.')) ? rtrim(rtrim($prow['qty_1'], '0'), '.') : $prow['qty_1'];
                $utom = Service_ProductUom::getByField($prow['g_unit'], 'pu_code');
                $prow['g_unit'] = $utom['pu_name'];
                $prow['g_unit'] = intval($prow['g_qty']) . $prow['g_unit'];
            }
            if ($prow['curr']) {
                $currency = Service_Currency::getByField($prow['curr'], 'currency_hs_code');
                $prow['curr'] = $currency['currency_code'];
                $prow['currency_symbol_left'] = $currency['currency_symbol_left'];
            }
            $subtotal = $prow['decl_total']; // $prow['DECL_PRICE']*$prow['G_QTY'];
            if ($subtotal) {
                $subtotal = Common_Common::ceil4bit($subtotal);
            }
            $total += $subtotal;

            $objPHPExcel->getActiveSheet()->getStyle("B{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("E{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("I{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            $objPHPExcel->getActiveSheet()->setCellValue("B{$row_index}", $prow['g_name_cn']);
            $objPHPExcel->getActiveSheet()->setCellValue("E{$row_index}", $prow['g_unit']);
            $objPHPExcel->getActiveSheet()->setCellValue("H{$row_index}", $prow['decl_price']);
            $objPHPExcel->getActiveSheet()->setCellValue("I{$row_index}", $subtotal);
        }
        /* // 去除空白行
          $row_index += 1;//空白行
          $objPHPExcel->getActiveSheet()->mergeCells("B{$row_index}:D{$row_index}");
          $objPHPExcel->getActiveSheet()->mergeCells("E{$row_index}:G{$row_index}");
          $objPHPExcel->getActiveSheet()->mergeCells("I{$row_index}:J{$row_index}");
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->getColor()->setARGB('00000000');
          $objPHPExcel->getActiveSheet()->getRowDimension($row_index )->setRowHeight(30);
          $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->getColor()->setARGB('00000000');
          $objPHPExcel->getActiveSheet()->getRowDimension($row_index )->setRowHeight(30);
          $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');
         */

        $row_index += 1; //合计区小写	
        $objPHPExcel->getActiveSheet()->mergeCells("B{$row_index}:D{$row_index}");
        $objPHPExcel->getActiveSheet()->mergeCells("E{$row_index}:G{$row_index}");
        $objPHPExcel->getActiveSheet()->mergeCells("I{$row_index}:J{$row_index}");
        $objPHPExcel->getActiveSheet()->setCellValue("H{$row_index}", '合计$');
        $objPHPExcel->getActiveSheet()->setCellValue("I{$row_index}", $total);
        $objPHPExcel->getActiveSheet()->getStyle("I{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //	$objPHPExcel->getActiveSheet()->setCellValue("I{$row_index}",$prow['currency_symbol_left'].$total);	
        $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->getColor()->setARGB('00000000');
        $objPHPExcel->getActiveSheet()->getRowDimension($row_index)->setRowHeight(30);

        $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');



        $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');


        $row_index += 1; //合计区大写
        //$row_end_index = $row_index+1; 	
        $objPHPExcel->getActiveSheet()->mergeCells("B{$row_index}:D{$row_index}");
        //$objPHPExcel->getActiveSheet()->mergeCells("B{$row_end_index}:D{$row_end_index}");
        //$objPHPExcel->getActiveSheet()->mergeCells("E{$row_index}:F{$row_index}");
        $objPHPExcel->getActiveSheet()->mergeCells("F{$row_index}:J{$row_index}");
        $objPHPExcel->getActiveSheet()->setCellValue("E{$row_index}", '合计$:');
        $objPHPExcel->getActiveSheet()->setCellValue("F{$row_index}", Common_Common::Chinese_RMB($total));
        $objPHPExcel->getActiveSheet()->getStyle("F{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //$objPHPExcel->getActiveSheet()->setCellValue("G{$row_index}",Common_Common::Chinese_RMB($total));	
        $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("B{$row_index}:D{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("B{$row_index}:D{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}:J{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}:J{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getBottom()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getRowDimension($row_index)->setRowHeight(30);
        //$objPHPExcel->getActiveSheet()->getRowDimension($row_end_index )->setRowHeight(30);	


        /* $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:A{$row_end_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);				
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:A{$row_end_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}:D{$row_end_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}:D{$row_end_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}:G{$row_end_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}:G{$row_end_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}:G{$row_end_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}:G{$row_end_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');



          $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}:J{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}:J{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("J{$row_end_index}:J{$row_end_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("J{$row_end_index}:J{$row_end_index}")->getBorders()->getRight()->getColor()->setARGB('00000000'); */


        /* $row_index = $row_end_index;
          $row_index+= 1;//空白行
          $objPHPExcel->getActiveSheet()->mergeCells("B{$row_index}:D{$row_index}");
          $objPHPExcel->getActiveSheet()->mergeCells("E{$row_index}:G{$row_index}");
          $objPHPExcel->getActiveSheet()->mergeCells("I{$row_index}:J{$row_index}");
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getTop()->getColor()->setARGB('00000000');
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:J{$row_index}")->getBorders()->getBottom()->getColor()->setARGB('00000000');
          $objPHPExcel->getActiveSheet()->getRowDimension($row_index )->setRowHeight(30);

          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("D{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("J{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000'); */

        $title = "合同";
        $sheet_list[] = array('title' => $title);
        $msgWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $title); //创建一个工作表
        $objPHPExcel->addSheet($msgWorkSheet); //插入工作表
        $objPHPExcel->setActiveSheetIndex(count($sheet_list) - 1);
        $objPHPExcel->getActiveSheet()->getTabColor()->setARGB('FFFFCC00');
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('宋体');
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(9);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $basewidth=12;
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth($basewidth * 0.8);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth($basewidth * 0.96);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth($basewidth * 0.84);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth($basewidth * 0.84);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth($basewidth * 0.72);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth($basewidth * 1.44);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth($basewidth * 1.36);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth($basewidth * 0.92);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth($basewidth * 0.32);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth($basewidth * 1.36);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth($basewidth * 0.80);

        //第1行 
        $objPHPExcel->getActiveSheet()->mergeCells("A1:K1");
        $objPHPExcel->getActiveSheet()->setCellValue('A1', '合同');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //第2行 
        $objPHPExcel->getActiveSheet()->mergeCells("A2:K2");
        $objPHPExcel->getActiveSheet()->setCellValue('A2', 'CONTRACT');
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle("A2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //第3-4行
        $objPHPExcel->getActiveSheet()->mergeCells("B3:F4");
        $objPHPExcel->getActiveSheet()->mergeCells("J3:K4");


        $objPHPExcel->getActiveSheet()->setCellValue('A3', '卖方');
        $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Sellers:');

        $objPHPExcel->getActiveSheet()->getStyle("B3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //$objPHPExcel->getActiveSheet()->setCellValue('B3', $trade_name);
        $objPHPExcel->getActiveSheet()->setCellValue('B3', '招海');
        $objPHPExcel->getActiveSheet()->setCellValue('H3', '合同号码');
        $objPHPExcel->getActiveSheet()->setCellValue('H4', 'Contract No:');
        //$objPHPExcel->getActiveSheet()->setCellValue('J3','E8888-2');
        //第5-6行

        $objPHPExcel->getActiveSheet()->getStyle("C5:F5")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("C5:F5")->getBorders()->getTop()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("J5:K5")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("J5:K5")->getBorders()->getTop()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->mergeCells("C5:F6");
        $objPHPExcel->getActiveSheet()->mergeCells("J5:K6");
        $objPHPExcel->getActiveSheet()->setCellValue('B5', '地址');
        $objPHPExcel->getActiveSheet()->setCellValue('B6', 'Address:');
        $objPHPExcel->getActiveSheet()->setCellValue('C5', '深圳市南山区临海大道53号前海保税港区5号楼4层');
        $objPHPExcel->getActiveSheet()->setCellValue('H5', '日 期');
        $objPHPExcel->getActiveSheet()->setCellValue('H6', 'Date:');
        //$objPHPExcel->getActiveSheet()->setCellValue('J5','2014.05.04');	
        //第7-8行
        $objPHPExcel->getActiveSheet()->setCellValue('C7', '0755-21629168');
        $objPHPExcel->getActiveSheet()->mergeCells("C7:F8");
        $objPHPExcel->getActiveSheet()->mergeCells("J7:K8");

        $objPHPExcel->getActiveSheet()->setCellValue('B7', '电话');
        $objPHPExcel->getActiveSheet()->setCellValue('B8', 'Telephone:');

        $objPHPExcel->getActiveSheet()->setCellValue('H7', '签约地点');
        $objPHPExcel->getActiveSheet()->setCellValue('H8', 'Signed at');
        //$objPHPExcel->getActiveSheet()->setCellValue('J7','深圳');

        $objPHPExcel->getActiveSheet()->getStyle("C7:F7")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("C7:F7")->getBorders()->getTop()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("J7:K7")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("J7:K7")->getBorders()->getTop()->getColor()->setARGB('00000000');


        //第9-10行
        $objPHPExcel->getActiveSheet()->mergeCells("B9:F10");
        $objPHPExcel->getActiveSheet()->setCellValue('A9', '买方');
        $objPHPExcel->getActiveSheet()->setCellValue('A10', 'Buyers:');
        $objPHPExcel->getActiveSheet()->setCellValue('B9', 'A-LINK TELECOM INTERNATIONAL CO LIMITED');
        $objPHPExcel->getActiveSheet()->getStyle("C9:F9")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("C9:F9")->getBorders()->getTop()->getColor()->setARGB('00000000');
        $objPHPExcel->getActiveSheet()->getStyle("J9:K9")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("J9:K9")->getBorders()->getTop()->getColor()->setARGB('00000000');


        //第11-12行
        $objPHPExcel->getActiveSheet()->mergeCells("C11:F12");

        $objPHPExcel->getActiveSheet()->getStyle("B11:F11")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("B11:F11")->getBorders()->getTop()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->setCellValue('B11', '地址');
        $objPHPExcel->getActiveSheet()->setCellValue('B12', 'Address:');

        //第13-14行
        $objPHPExcel->getActiveSheet()->mergeCells("C13:F14");
        $objPHPExcel->getActiveSheet()->setCellValue('B13', '电话');
        $objPHPExcel->getActiveSheet()->setCellValue('B14', 'Telephone:');
        $objPHPExcel->getActiveSheet()->getStyle("C13:F13")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("C13:F13")->getBorders()->getTop()->getColor()->setARGB('00000000');


        //第15-16行		
        $objPHPExcel->getActiveSheet()->setCellValue('A15', '经买卖双方确认根据下列条款订立本合同：');
        $objPHPExcel->getActiveSheet()->setCellValue('A16', 'This contract is made out by the Sellers and Buyers as per the follwing terms and conditions mutuilly confirmed:');

        $objPHPExcel->getActiveSheet()->getStyle("C15:F15")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("C15:F15")->getBorders()->getTop()->getColor()->setARGB('00000000');


        //第17行
        $objPHPExcel->getActiveSheet()->getRowDimension('17')->setRowHeight(10);
        //第18行
        $objPHPExcel->getActiveSheet()->mergeCells("A18:E18");
        $objPHPExcel->getActiveSheet()->mergeCells("G18:H18");
        $objPHPExcel->getActiveSheet()->mergeCells("I18:K18");
        $objPHPExcel->getActiveSheet()->setCellValue("A18", '(1)货物名称及规格');
        $objPHPExcel->getActiveSheet()->setCellValue("F18", '(2)数 量');
        $objPHPExcel->getActiveSheet()->setCellValue("G18", '(3)单 价');
        $objPHPExcel->getActiveSheet()->setCellValue("I18", '(4)金 额(USD)');

        $objPHPExcel->getActiveSheet()->getStyle("A18")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("F18")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("G18")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("I18")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle("A18:K18")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A18:K18")->getBorders()->getTop()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("E18")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("E18")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("F18")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("F18")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("H18")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("H18")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("K18")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("K18")->getBorders()->getRight()->getColor()->setARGB('00000000');


        //第19行
        $objPHPExcel->getActiveSheet()->mergeCells("A19:E19");
        $objPHPExcel->getActiveSheet()->mergeCells("G19:H19");
        $objPHPExcel->getActiveSheet()->mergeCells("I19:K19");


        $objPHPExcel->getActiveSheet()->getStyle("A19")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("F19")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("G19")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("I19")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue("A19", 'Name of commodity and Specification');
        $objPHPExcel->getActiveSheet()->setCellValue("F19", 'Quantity');
        $objPHPExcel->getActiveSheet()->setCellValue("G19", 'Unit Price');
        $objPHPExcel->getActiveSheet()->setCellValue("I19", 'Amount');
        $objPHPExcel->getActiveSheet()->getStyle("A19:K19")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A19:K19")->getBorders()->getTop()->getColor()->setARGB('00000000');
        $objPHPExcel->getActiveSheet()->getStyle("A19:K19")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A19:K19")->getBorders()->getBottom()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("E19")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("E19")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("F19")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("F19")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("H19")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("H19")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("K19")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("K19")->getBorders()->getRight()->getColor()->setARGB('00000000');


        //产品信息从20行开始
        $data_area_rows = $dataCounts > 8 ? $dataCounts : 8;
        $total = 0;
        for ($j = 0; $j < $data_area_rows; $j++) {
            $k = $j + 1;
            $row_index = $j + 20;

            $objPHPExcel->getActiveSheet()->mergeCells("A{$row_index}:E{$row_index}");
            $objPHPExcel->getActiveSheet()->mergeCells("G{$row_index}:H{$row_index}");
            $objPHPExcel->getActiveSheet()->mergeCells("I{$row_index}:K{$row_index}");

            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:K{$row_index}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:K{$row_index}")->getBorders()->getBottom()->getColor()->setARGB('00000000');
            $objPHPExcel->getActiveSheet()->getRowDimension("{$row_index}")->setRowHeight(30);

            $objPHPExcel->getActiveSheet()->getStyle("E{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("E{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("F{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("F{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

            $objPHPExcel->getActiveSheet()->getStyle("K{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle("K{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');


            if (!isset($receivingXmlDetail[$j])) {
                continue;
            }
            $prow = $receivingXmlDetail[$j];
            if ($prow['g_unit']) {
                $QTY_1 = (FALSE !== strpos($prow['qty_1'], '.')) ? rtrim(rtrim($prow['qty_1'], '0'), '.') : $prow['qty_1'];
                $utom = Service_ProductUom::getByField($prow['g_unit'], 'pu_code');
                $prow['g_unit'] = $utom['pu_name'];
                $prow['g_unit'] = intval($prow['g_qty']) . $prow['g_unit'];
            }
            if ($prow['curr']) {
                $currency = Service_Currency::getByField($prow['curr'], 'currency_hs_code');
                $prow['curr'] = $currency['currency_code'];
                $prow['currency_symbol_left'] = $currency['currency_symbol_left'];
            }
            $subtotal = $prow['decl_total']; // $prow['DECL_PRICE']*$prow['G_QTY'];
            if ($subtotal) {
                $subtotal = Common_Common::ceil4bit($subtotal);
            }
            $total += $subtotal;


            $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("F{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("G{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("I{$row_index}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            $objPHPExcel->getActiveSheet()->setCellValue("A{$row_index}", $prow['g_name_cn']);
            $objPHPExcel->getActiveSheet()->setCellValue("F{$row_index}", $prow['g_unit']);
            $objPHPExcel->getActiveSheet()->setCellValue("G{$row_index}", $prow['decl_price']);
            $objPHPExcel->getActiveSheet()->setCellValue("I{$row_index}", $subtotal);
        }
        /* // 去除空白行
          $row_index+= 1;//空白行
          $objPHPExcel->getActiveSheet()->mergeCells("A{$row_index}:E{$row_index}");
          $objPHPExcel->getActiveSheet()->mergeCells("G{$row_index}:H{$row_index}");
          $objPHPExcel->getActiveSheet()->mergeCells("I{$row_index}:K{$row_index}");
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:K{$row_index}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("A{$row_index}:K{$row_index}")->getBorders()->getBottom()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("E{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("E{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("F{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("F{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("H{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');

          $objPHPExcel->getActiveSheet()->getStyle("K{$row_index}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $objPHPExcel->getActiveSheet()->getStyle("K{$row_index}")->getBorders()->getRight()->getColor()->setARGB('00000000');
         */

        $row_index+= 1; //空白行
        $objPHPExcel->getActiveSheet()->getRowDimension("{$row_index}")->setRowHeight(10);
        $bottom_area_index = $row_index + 1; //底部开始		
        $current_row_index = $bottom_area_index;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "数量及总值允许有 5 %的增减");
        $objPHPExcel->getActiveSheet()->setCellValue("G{$current_row_index}", "总 值（USD）");
        //$objPHPExcel->getActiveSheet()->setCellValue("J{$current_row_index}",$prow['currency_symbol_left']);
        $current_row_index+=1;
        $objPHPExcel->getActiveSheet()->mergeCells("I{$current_row_index}:K{$current_row_index}");
        $objPHPExcel->getActiveSheet()->setCellValue("I{$current_row_index}", $total);
        $objPHPExcel->getActiveSheet()->getStyle("I{$current_row_index}:K{$current_row_index}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("I{$current_row_index}:K{$current_row_index}")->getBorders()->getBottom()->getColor()->setARGB('00000000');
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "   %more or less boty in amount and quantity allowed.");
        $objPHPExcel->getActiveSheet()->setCellValue("G{$current_row_index}", "Total Amount:");
        $current_row_index+=1;
        $current_endrow_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "(5)合同总值（大写）");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_endrow_index}", "Total Value in Word:");

        $objPHPExcel->getActiveSheet()->mergeCells("D{$current_row_index}:K{$current_endrow_index}");
        $objPHPExcel->getActiveSheet()->setCellValue("D{$current_row_index}", "合计（USD）：" . Common_Common::Chinese_RMB($total));

        $current_row_index = $current_endrow_index + 1;
        $current_endrow_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->mergeCells("D{$current_row_index}:K{$current_endrow_index}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "(6)包装及唛头");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_endrow_index}", "Packing and shipping Marks:");
        $objPHPExcel->getActiveSheet()->setCellValue("C{$current_row_index}", "纸箱");

        $current_row_index = $current_endrow_index + 1;
        $current_endrow_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "Time of Shiment:");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_endrow_index}", "(8)装运口岸和目的地");

        $objPHPExcel->getActiveSheet()->setCellValue("D{$current_row_index}", "深圳-");
        $objPHPExcel->getActiveSheet()->setCellValue("E{$current_row_index}", "香港");

        $current_row_index = $current_endrow_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "Loading Port & Destination:From");
        $objPHPExcel->getActiveSheet()->setCellValue("F{$current_row_index}", "with transhipment and partial shiments allowed.");

        $current_row_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "(9)保险：由卖方按发票全部金额110%投保至");
        $objPHPExcel->getActiveSheet()->setCellValue("F{$current_row_index}", "为止的____________险。按中国海洋运输保险条款办理。 ");

        $current_row_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", " Insurance:To be effectted by Seller for 110% of full invoice value covering");
        $objPHPExcel->getActiveSheet()->setCellValue("H{$current_row_index}", " ________________________ up");
        $current_row_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "only,subject to C.I.C.");
        $current_row_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "(10)付款条件");
        $objPHPExcel->getActiveSheet()->setCellValue("C{$current_row_index}", "T/T");
        $current_row_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "Terms of Payment:");

        $current_row_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "(11)装运标记");
        $current_row_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("A{$current_row_index}", "Shiping Marks:");
        $current_row_index = $current_row_index + 1; //空1行
        $current_row_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("B{$current_row_index}", "卖 方");
        $objPHPExcel->getActiveSheet()->setCellValue("G{$current_row_index}", "买 方");
        $current_row_index = $current_row_index + 1;
        $objPHPExcel->getActiveSheet()->setCellValue("B{$current_row_index}", "THE SELLERS");
        $objPHPExcel->getActiveSheet()->setCellValue("G{$current_row_index}", "THE BUYERS");

        /*
          $objDrawing = new PHPExcel_Worksheet_Drawing();
          $objDrawing->setName('Paid');
          $objDrawing->setDescription('Paid');
          $objDrawing->setPath('./images/login-background.jpg'); //图片引入位置
          $objDrawing->setCoordinates('B40'); //图片添加位置
          $objDrawing->setOffsetX(210);
          $objDrawing->setRotation(25);
          $objDrawing->setHeight(36);
          $objDrawing->getShadow()->setVisible (true );
          $objDrawing->getShadow()->setDirection(45);
          $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
         */
        /*
          $objRichText = new PHPExcel_RichText();
          $objRichText->createText('我的是 ');    //写文字
          //添加文字并设置这段文字粗体斜体和文字颜色
          $objPayable = $objRichText->createTextRun( '温热胜多负少');
          $objPayable->getFont()->setBold( true);
          $objPayable->getFont()->setItalic( true);
          $objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
          $objRichText->createText(',胜多负少.');
          //将文字写到A18单元格中
          $objPHPExcel->getActiveSheet()->getCell('M18')->setValue($objRichText);
         */
        $objPHPExcel->setActiveSheetIndex(0);
        //$file_name = 'jibao'.date('ymdhis').'.xlsx';
        $file_name = $receivingXml[$a]['sub_receiving_code'].'-'.date('ymdhis').'.xls';
        $objWriter->save($fullPath.'/'.$file_name);
        unset($objWriter);
    }
         
        if (file_exists($fullPath)) {
            
            $success='生成数据成功';
            iconv("GB2312//IGNORE","UTF-8",$success); 
        }else{
            $success='生成数据失败';
            iconv("GB2312//IGNORE","UTF-8",$success);
        }
        echo $success;
        exit;
    }
    public function downloadAction(){
        header("Content-Type:text/html;charset=utf-8");
        $receiving_code = $this->_request->getParam('receiving_code', '');
        if (empty($receiving_code)) {
            echo '没有传递参数';
            exit;
        }
        $fullPath = APPLICATION_PATH . '/../data/tuishui/'.$receiving_code.'/';
        $fullPath=str_replace("\\","/",$fullPath);
        //$fname=$fullPath.'/'.$receiving_code.'.zip';
       if (!file_exists($fullPath)) {
            echo '请生成报关单数据';
            exit;
        }else{

$fileNameArr=array();
$filename = $receiving_code.'-'.date('ymdhis') . ".zip"; 
$zip = new ZipArchive (); // 使用本类，linux需开启zlib，windows需取消php_zip.dll前的注释
if ($zip->open ( $filename, ZIPARCHIVE::CREATE ) !== TRUE) {
    
    exit ( '无法打开文件，或者文件创建失败' );
}
 $handler=opendir($fullPath); //打开当前文件夹由$path指定。
        while(($f=readdir($handler))!==false){
                 if($f != "." && $f != ".."){
                    $fileNameArr[]=$fullPath.$f;
                    }
            }
        //print_r($fileNameArr);exit;
foreach ( $fileNameArr as $val ) {
    if ($zip->open ( $filename, ZIPARCHIVE::CREATE ) == TRUE) {
        if (file_exists($val)) {
        $zip->addFile ( $val, basename ( $val ) ); 
        }
    }
    $zip->close(); // 关闭
}
header ( "Cache-Control: max-age=0" );
header ( "Content-Description: File Transfer" );
header ( 'Content-disposition: attachment; filename=' . basename ( $filename ) ); 
header ( "Content-Type: application/zip" ); 
header ( "Content-Transfer-Encoding: binary" ); 
header ( 'Content-Length: ' . filesize ( $filename ) ); 
@readfile ( $filename);
exit;
        }
    }
}