<?php

/**
 * Created by JetBrains PhpStorm.
 * User: user
 * Date: 14-5-20
 * Time: 下午4:27
 * To change this template use File | Settings | File Templates.
 */
class Ship_JibaoController extends Ec_Controller_Action {

    public function preDispatch() {
        $this->tplDirectory = "ship/views/jibao-";
        // $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();
            $jibao_code = trim($this->_request->getParam("jibao_code", ""));
            $time_start = $this->_request->getParam("time_start", "");
            $time_end = $this->_request->getParam("time_end", "");
            $RELATIVE_FORM_ID = trim($this->_request->getParam("RELATIVE_FORM_ID", ""));
            $customer_code = trim($this->_request->getParam("customer_code", ""));
    
            $condition = array(
                'FORM_ID' => $jibao_code,
                'time_start' => $time_start,
                'time_end' => $time_end,
                'RELATIVE_FORM_ID'=>$RELATIVE_FORM_ID,
                'customer_code'=>$customer_code
            );
            $total = Service_ApiJibaoHeader::getJibaoJoinCustomerByCondition($condition, "count(*)");
            $page = $page > ceil($total / $pageSize) ? 1 : $page;
            $this->view->count = $total;
            $result = Service_ApiJibaoHeader::getJibaoJoinCustomerByCondition($condition, "*", $pageSize, $page, array('ajh_id desc'));
            /*if($total > 0){
                foreach($result AS $key=>$value){
                    $customer = Service_Customer::getByField($value['TRADE_CO'], 'trade_co');
                    $result[$key]['customer_code']    =   $customer['customer_code'];
                }
            }*/

            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 downloadAction() {

        $ajh_id = $this->_request->getParam('ajh_id', 0);
        if (empty($ajh_id)) {
            echo '没有传递参数';
            exit;
        }
        $basewidth = 10;
    
        
      
        $jibao_heads = Service_ApiJibaoHeader::getByCondition(array('ajh_id' => $ajh_id));
        if (empty($jibao_heads)) {
            echo '集报数据找不到';
            exit;
        }
        
        
        
        $jibao_head = $jibao_heads[0];
        //api_jibao_gather_details
        $detailCondition = array(
            'ajh_id' => $ajh_id,
            'jibao_id' => $jibao_head['FORM_ID']
        );
        $apijibaoDetail = Service_ApiJibaoGatherDetails::getByCondition($detailCondition, "*");
        if (empty($apijibaoDetail)) {
            echo '集报details数据找不到';
            exit;
        }
       
        $customer = Service_Customer::getByField($jibao_head['TRADE_CO'], 'trade_co');
        $trade_co = $customer['trade_co'];
        $trade_name = $customer['trade_name'];
        
        //api_jibao_gather_details_after
        /* $detailAfterCondition = array(
          'ajh_id'=>$ajh_id,
          'jibao_id'=>$jibao_head['FORM_ID']
          );
          $apijibaoDetailAfter = Service_ApiJibaoGatherDetailsAfter::getByCondition($detailAfterCondition,'*');
          if(empty($apijibaoDetailAfter)){
          echo '集报after数据找不到';
          exit;
          }
          //api_jibao_gather_details
          $detailCondition = array(
          'ajh_id'=>$ajh_id,
          'jibao_id'=>$jibao_head['FORM_ID']
          );
          $apijibaoDetail = Service_ApiJibaoGatherDetails::getByCondition($detailCondition,'*');
          if(empty($apijibaoDetail)){
          echo '集报before数据找不到';
          exit;
          } */


        $dataCounts = count($apijibaoDetail);
        $page = 1;
        $pagesize = 5;
        $total_pages = ceil($dataCounts / $pagesize);

        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');
        $objPHPExcel = new PHPExcel();
        //$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

        $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); //设置第一个工作表为活动工作表
            $pageMargins = $objPHPExcel->getActiveSheet($index)->getPageMargins();
            // 设置边距为0.5厘米 (1英寸 = 2.54厘米)
            $margin = 0.5 / 2.54;   //phpexcel 中是按英寸来计算的,所以这里换算了一下
            
            $pageMargins->setTop($margin);       //上边距
            $pageMargins->setBottom($margin); //下
            $pageMargins->setLeft($margin);      //左
            $pageMargins->setRight($margin);    //右
            $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth('1');//自动填充到页面的宽度
            //$sheet->getPageSetup()->setFitToHeight('1');//自动填充到页面的高度
            $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(10);
            $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('H4','保税港区');
            $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()->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', $jibao_head['PACK_NO']);
            $objPHPExcel->getActiveSheet()->getStyle('G8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue('K8', '毛重（公斤）');
            $objPHPExcel->getActiveSheet()->setCellValue('M8', $jibao_head['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', $jibao_head['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: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('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('P10')->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行	

            $totalValue = 0;
            for ($j = 0; $j < 5; $j++) {
                $data_index = $index * 5 + $j;
                $start_row_number = 12 + 3 * $j;
                $end_row_number = $start_row_number + 2;
                $middle_row_number = $start_row_number + 1;

                $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($apijibaoDetail[$data_index])) {
                    $row = $apijibaoDetail[$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()->getStyle("H{$middle_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()->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{$middle_row_number}", $row['QTY_2']);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValue("H{$end_row_number}", $row['G_QTY']);
                    $objPHPExcel->getActiveSheet()->getStyle("I{$start_row_number}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle("I{$middle_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{$middle_row_number}", $row['UNIT_2']);
                    $objPHPExcel->getActiveSheet()->setCellValue("I{$end_row_number}", $row['G_UNIT']);
                    $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']);
                    $totalValue+=$row['DECL_TOTAL'];
                }
            }
            //$objPHPExcel->getActiveSheet()->setCellValue('D10','总金额:'.$totalValue." USD 集报单号:".$jibao_head['FORM_ID']);




            //第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', '放行'); */ //add Notes by @Felix
        }//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(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(8);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(18);
        $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(12);
        //第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('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($apijibaoDetail[$j])) {
                continue;
            }
            $prow = $apijibaoDetail[$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}", $jibao_head['GROSS_WT']);
        $objPHPExcel->getActiveSheet()->setCellValue("L{$totalarea_row_index}", $jibao_head['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);
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);

        $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("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(15);
            $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($apijibaoDetail[$j])) {
                continue;
            }
            $prow = $apijibaoDetail[$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);
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(10);

        $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);
		$objPHPExcel->getActiveSheet()->getDefaultRowDimension(1)->setRowHeight(15);

        //第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);
		$objPHPExcel->getActiveSheet()->getDefaultRowDimension(2)->setRowHeight(15);

        for($j=3;$j<17;$j++){
            $objPHPExcel->getActiveSheet()->getRowDimension($j)->setRowHeight(10); //设置3到16行行高
        }
        //第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('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('H5', '日 期');
        $objPHPExcel->getActiveSheet()->setCellValue('H6', 'Date:');
        //$objPHPExcel->getActiveSheet()->setCellValue('J5','2014.05.04');	
        //第7-8行
        $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()->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);
        //第16行
        $objPHPExcel->getActiveSheet()->mergeCells("A16:E16");
        $objPHPExcel->getActiveSheet()->mergeCells("G16:H16");
        $objPHPExcel->getActiveSheet()->mergeCells("I16:K16");
        $objPHPExcel->getActiveSheet()->setCellValue("A16", '(1)货物名称及规格');
        $objPHPExcel->getActiveSheet()->setCellValue("F16", '(2)数 量');
        $objPHPExcel->getActiveSheet()->setCellValue("G16", '(3)单 价');
        $objPHPExcel->getActiveSheet()->setCellValue("I16", '(4)金 额(USD)');

        $objPHPExcel->getActiveSheet()->getStyle("A16")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("F16")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("G16")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("I16")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle("A16:K16")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A16:K16")->getBorders()->getTop()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("E16")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("E16")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("F16")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("F16")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("H16")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("H16")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("K16")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("K16")->getBorders()->getRight()->getColor()->setARGB('00000000');


        //第17行
        $objPHPExcel->getActiveSheet()->mergeCells("A17:E17");
        $objPHPExcel->getActiveSheet()->mergeCells("G17:H17");
        $objPHPExcel->getActiveSheet()->mergeCells("I17:K17");


        $objPHPExcel->getActiveSheet()->getStyle("A17")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("F17")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("G17")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle("I17")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue("A17", 'Name of commodity and Specification');
        $objPHPExcel->getActiveSheet()->setCellValue("F17", 'Quantity');
        $objPHPExcel->getActiveSheet()->setCellValue("G17", 'Unit Price');
        $objPHPExcel->getActiveSheet()->setCellValue("I17", 'Amount');
        $objPHPExcel->getActiveSheet()->getStyle("A17:K17")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A17:K17")->getBorders()->getTop()->getColor()->setARGB('00000000');
        $objPHPExcel->getActiveSheet()->getStyle("A17:K17")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("A17:K17")->getBorders()->getBottom()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("E17")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("E17")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("F17")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("F17")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("H17")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("H17")->getBorders()->getRight()->getColor()->setARGB('00000000');

        $objPHPExcel->getActiveSheet()->getStyle("K17")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objPHPExcel->getActiveSheet()->getStyle("K17")->getBorders()->getRight()->getColor()->setARGB('00000000');


        //产品信息从18行开始
        $data_area_rows = $dataCounts > 8 ? $dataCounts : 8;
        $total = 0;
        for ($j = 0; $j < $data_area_rows; $j++) {
            $k = $j + 1;
            $row_index = $j + 18;

            $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(10);

            $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($apijibaoDetail[$j])) {
                continue;
            }
            $prow = $apijibaoDetail[$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);
         */

        for ($i = 0; $i < $total_pages; $i++) {
            $objPHPExcel->setActiveSheetIndex($i);
            $objPHPExcel->getActiveSheet()->setCellValue('D10','总金额:'.$total." USD 集报单号:".$jibao_head['FORM_ID']);
        }

        $objPHPExcel->setActiveSheetIndex(0);
        //$file_name = 'jibao'.date('ymdhis').'.xlsx';
        $file_name = $jibao_head['FORM_ID'].'.xls';
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header("Content-Disposition:attachment;filename=\"{$file_name}\"");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

}