excel 导出

2019-08-10  本文已影响0人  猪猪静的爸爸

<?php
/**

    //品牌
    $this->dropdownOptions($objPHPExcel,$filename,$come="A",$go="L");
    //品类语言
    $this->dropdownOptions($objPHPExcel,$filename,$come="B",$go="O");

    //出口国
    $this->dropdownOptions($objPHPExcel,$filename,$come="C",$go="R");

    //货物类型
    $this->dropdownOptions($objPHPExcel,$filename,$come="D",$go="E");

    //商品属性
    $this->dropdownOptions($objPHPExcel,$filename,$come="E",$go="D");

    $objPHPExcel->setActiveSheetIndex(0);

    //---sheet1//品类---//
    $dataSourceCategory=$this->category();
    //var_dump($dataSourceCategory);exit;
    $this->setArchitectureCompany($objPHPExcel, $dataSourceCategory,"Category品类");
    //---sheet2基础数据---//
    $dataSourceBasics=$import->list($filename,2,1);
    $setDataBasics=[
        [
            '名称',
            '说明',
        ]
    ];
    array_shift($dataSourceBasics);
    $dataSourceBasics=array_merge($setDataBasics,$dataSourceBasics);
    //var_dump($dataSourceBasics);exit;
    $this->setArchitectureCompany($objPHPExcel, $dataSourceBasics,"基础数据");

    //---sheet3成交单位表---//
    $dataSourceCompany=$import->list($filename,3,1);
    $setDataCompany=[
        [
            '成交单位英文名称',
            '成交单位中文名称',
            '是否常用',
        ]
    ];

    $dataSourceCompany=array_merge($setDataCompany,$dataSourceCompany);
    //var_dump($dataSourceCompany);exit;
    $this->setArchitectureCompany($objPHPExcel, $dataSourceCompany,"Unit Table成交单位表");
    $subject = '产品';
    ob_end_clean();//清除缓冲区,避免乱码
    // Redirect output to a client's web browser (Excel2007)
    header('Content-Type: application/vnd.ms-excel');
    header("Content-Disposition: attachment;filename=product.xlsx");
    header('Cache-Control: max-age=0');
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
}

public function dropDown(\PHPExcel $objPHPExcel,$col="D",$list='是,否')
{
    for ($i = 2; $i <= 10; $i++)
    {
        $objValidation2 = $objPHPExcel->getActiveSheet()->getCell($col . $i)->getDataValidation();
        $objValidation2->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
        $objValidation2->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
        $objValidation2->setAllowBlank(false);
        $objValidation2->setShowInputMessage(true);
        $objValidation2->setShowDropDown(true);
        $objValidation2->setPromptTitle('从列表中选取');
        $objValidation2->setPrompt('请从列表中选择一个值.');
        $objValidation2->setErrorTitle('输入错误');
        $objValidation2->setError('值不在列表中');
        $objValidation2->setFormula1('"' . $list . '"');
    }
}


// 根据字段个数，设置表头排序字母
public function letter($count=260){
    $letter_str = '';
    for($i='B',$k=0; $i<='Z'; $i++,$k++){
        if($k == $count){
            break;
        }

        // 最后一个取消逗号
        if($k == $count-1){
            $letter_str .= $i;
        } else {
            $letter_str .= $i.',';
        }
    }

    $letter = explode(',', $letter_str);
    return $letter;
}

// 根据字段个数，设置表头排序字母
public function letterOne($count=260){
    $letter_str = '';
    for($i='A',$k=0; $i<='Z'; $i++,$k++){
        if($k == $count){
            break;
        }

        // 最后一个取消逗号
        if($k == $count-1){
            $letter_str .= $i.'1';
        } else {
            $letter_str .= $i.'1,';
        }
    }

    $letter = explode(',', $letter_str);
    return $letter;
}

// 根据字段个数，设置表头排序字母
public function getLetter($count=260){
    $letter_str = '';
    for($i='A',$k=0; $i<='Z'; $i++,$k++){
        if($k == $count){
            break;
        }

        // 最后一个取消逗号
        if($k == $count-1){
            $letter_str .= $i;
        } else {
            $letter_str .= $i.',';
        }
    }

    $letter = explode(',', $letter_str);
    return $letter;
}

public function category(){
    // 获取 三级分类
    $apiCommon=new Common_ApiCommonSystem();
    $result=$apiCommon->apiSendCommon('0020009',array());

    if($result['ask'] != '1') {
        echo Ec::Lang("获取分类信息错误");
        exit;
    }

    $cateList = $result['data']['Data'];

    //  根据 CategoryLevel（等级） 进行分组
    $cateGroupLevel = [];
    foreach($cateList as $key=>$value) {
        $cateGroupLevel[$value['CategoryLevel']][] = $value;
    }

    // 一二三 级品类
    $first_pcoRows = !empty($cateGroupLevel[0]) ? $cateGroupLevel[0] : [];
    $second_pcoRows = !empty($cateGroupLevel[1]) ? $cateGroupLevel[1] : [];
    $third_pcoRows = !empty($cateGroupLevel[2]) ? $cateGroupLevel[2] : [];

    $first_pcoArr = [];
    foreach ($first_pcoRows as $key => $val) {
        $first_pcoArr[$val['ProductCategoryId']] = $val['CategoryNameEn'] . "【" . $val['CategoryName'] . "】";
    }

    $second_pcoArr = [];
    foreach ($second_pcoRows as $key => $val) {
        $second_pcoArr[$val['ProductCategoryId']] = $val['CategoryNameEn'] . "【" . $val['CategoryName'] . "】";
        $catArr2[$val['ProductCategoryId']] = $val['ParentId'];
    }

    $data[] = [
        Ec::Lang("一级品类"),
        Ec::Lang("二级品类"),
        Ec::Lang("三级品类"),
        Ec::Lang("最低申报价值（USD）"),
        Ec::Lang("品类ID")
    ];

    if ($third_pcoRows) {
        foreach ($third_pcoRows as $key => $val) {
            $key++;
            $cat_id2 = $val['ParentId'];
            $cat_id1 = $catArr2[$cat_id2];

            //一级品类
            $data[$key][0] = $first_pcoArr[$cat_id1];

            //二级品类
            $data[$key][1] = $second_pcoArr[$cat_id2];

            //三级品类
            $data[$key][2] = $val['CategoryNameEn'] . "【" . $val['CategoryName'] . "】";
            //最低申报价值
            $data[$key][3] = $val['MinDeclareValue'];
            //品类ID
            $data[$key][4] = $val['ProductCategoryId'];
        }
    }
    return $data;
}



public function dropdownOptions(\PHPExcel $objPHPExcel,$filename,$come="A",$go="L"){
    //品牌
    try{
        $import=new Common_ExcelImport();
        $brand = $import->deliveryWarehouseProduct($filename,4,$come);
        $str_brand=implode(",",$brand);
        $this->dropDown($objPHPExcel,$go,$str_brand);
        return true;
    }catch (Exception $e){
        /* 直接下载后台的模板 */
        Common_Common::write_log($e->getMessage());
        exit;
    }
}

}

上一篇 下一篇

猜你喜欢

热点阅读