excel 导出

作者: 猪猪静的爸爸 | 来源:发表于2019-08-10 14:57 被阅读0次

<?php
/**

导出Excel数据
*/
class Product_ExportExcelCommodity
{
protected $page;

public function __construct() {
```
 //引入phpexcel文件，这里要修改成你文件的路径
 include_once("PHPExcel.php");
 include_once("PHPExcel/IOFactory.php");
```
}

/**
- 设置架构sheet
- @param \PHPExcel $objPHPExcel
- @param array $dataSource 格式如下：
- [
- ```
 [   'name' => '设计部',
```
- ```
     'children' => ['设计师', '设计主管']
```
- ```
 ],
```
- ```
 [   'name' => '业务部',
```
- ```
     'children' => ['业务员', '业务主管']
```
- ```
 ]
```
- ]
- @return bool
  */
  public function setArchitecture(\PHPExcel $objPHPExcel,$ dataSource = [], $sheet="architecture",$ col1="A", $col3="B")
  {
  
  if(empty( $objPHPExcel)) { return false; }$ msgWorkSheet = new \PHPExcel_Worksheet( $objPHPExcel,$ sheet); //创建一个工作表
  $objPHPExcel->addSheet($ msgWorkSheet); //插入工作表
  //列，若children数据多，需扩展
  
  $col=$ this->letter(260);
  //var_dump( $col);exit; foreach ($ dataSource as $key =>$ value) {
  $num =$ key + 1;
  $objPHPExcel->getSheetByName($ sheet)->SetCellValue("A". $num,$ value['name']);
  $pCount = count($ value['children']) - 1;
  if( $pCount >= 0) { foreach ($ value['children'] as $pKey =>$ pValue) {
  $objPHPExcel->getSheetByName($ sheet)->SetCellValue( $col[$ pKey]. $num,$ pValue);
  }
  $objPHPExcel->addNamedRange( new \PHPExcel_NamedRange($ value['name'],
  $objPHPExcel->getSheetByName($ sheet),
  'B'. $num.':'.$ col[ $pCount].$ num
  )
  );
  }
  
  }
  $objPHPExcel->addNamedRange( new \PHPExcel_NamedRange( "flanche$ col1 $col3",$ objPHPExcel->getSheetByName( $sheet), 'A1:A'.count($ dataSource)
  )
  );
  return true;
  }
public function setArchitectureCompany(\PHPExcel $objPHPExcel,$ dataSource = [],$sheet="architecture")
{
```
 if(empty($objPHPExcel)) {
     return false;
 }

 try{
     $msgWorkSheet = new \PHPExcel_Worksheet($objPHPExcel, $sheet); //创建一个工作表
     $objPHPExcel->addSheet($msgWorkSheet); //插入工作表

     foreach ($dataSource as $key => $value) {
         //var_dump($value);exit;
         $num=$key+1;
         $letter=$this->getLetter(count($value));
         for ($i=0;$i<count($value);$i++){
             //echo $letter[$i].$num.'//';
             $objPHPExcel->getSheetByName($sheet)->SetCellValue($letter[$i].$num, $value[$i]);
         }
     }
     //exit;
     return true;
 }catch (Exception $e){
     /* 直接下载后台的模板 */
     Common_Common::write_log('错误编码'.$e->getCode());
     Common_Common::write_log('报错文件'.$e->getFile());
     Common_Common::write_log('错误line：'.$e->getLine());
     Common_Common::write_log('错误信息'.$e->getMessage());
     Common_Common::write_log('错误时间：'.date("Y-m-d H:i:s", time()));
     exit;
 }
```
}

/**
- 设置联动下拉菜单的选项
- @param \PHPExcel $objPHPExcel
- @param int $row 行
- @param string $col1 下拉框1的列标识
- @param string $col2 下拉框2的列标识 */ public function setRelationDropList(\PHPExcel$ objPHPExcel, $row = 1,$ col1 = 'A', $col2 = 'B') {$ objValidation = $objPHPExcel->getActiveSheet()->getCell($ col1. $row)->getDataValidation();$ objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST );
  $objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION );$ objValidation->setAllowBlank(false);
  $objValidation->setShowInputMessage(true);$ objValidation->setShowErrorMessage(true);
  $objValidation->setShowDropDown(true);$ objValidation->setErrorTitle('输入错误');
  $objValidation->setError('不在列表中的值');$ objValidation->setPromptTitle('请选择');
  $objValidation->setPrompt('请从列表中选择一个值.');$ objValidation->setFormula1("=flanche $col1$ col2");
  
  $objValidation =$ objPHPExcel->getActiveSheet()->getCell( $col2.$ row)->getDataValidation();
  $objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST );$ objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
  $objValidation->setAllowBlank(false);$ objValidation->setShowInputMessage(true);
  $objValidation->setShowErrorMessage(true);$ objValidation->setShowDropDown(true);
  $objValidation->setErrorTitle('输入错误');$ objValidation->setError('不在列表中的值');
  $objValidation->setPromptTitle('请选择');$ objValidation->setPrompt('请从列表中选择一个值.');
  $objValidation->setFormula1('=INDIRECT($ '. $col1.'$ '.$row.')');
  }
/**
- Excel导出demo带联动下拉框[部门和职位]
- @param string $subject 表格主题
- @param array $data 导出数据
- @param array $title 表格的字段名
- @param array $dataSource 格式如下：
- [
- ```
 [   'name' => '设计部',
```
- ```
     'children' => ['设计师', '设计主管']
```
- ```
 ],
```
- ```
 [   'name' => '业务部',
```
- ```
     'children' => ['业务员', '业务主管']
```
- ```
 ]
```
- ]
- @param string $col1 下拉框1的列标识
- @param string $col2 下拉框2的列标识 */ public function export($ filename){
  // Create new PHPExcel object
  $objPHPExcel = new \PHPExcel(); // Set properties$ objPHPExcel->getProperties()->setCreator("ctos")
  ->setLastModifiedBy("ctos")
  ->setTitle("Office 2007 XLSX Test Document")
  ->setSubject("Office 2007 XLSX Test Document")
  ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
  ->setKeywords("office 2007 openxml php")
  ->setCategory("Test result file");
  //设置标题样式
  
  //获取excel批注
  // $plaintext =$ objPHPExcel->getActiveSheet()->getComment( 'E1');
  // var_dump($plaintext);exit;
  
  $import=new Common_ExcelImport();$ title= $import->listfba($ filename);
  $titleRow=$ this->letterOne(count( $title));$ objPHPExcel->getActiveSheet()->getStyle( $titleRow[0].':'.$ titleRow[count( $title)-1])->getFont()->setBold(true);$ objPHPExcel->getActiveSheet()->getStyle( $titleRow[0].':'.$ titleRow[count( $title)-1])->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);$ objPHPExcel->getActiveSheet()->getStyle( 'A1:U1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
  $objPHPExcel->getActiveSheet()->getStyle( 'A1:L1')->getFill()->getStartColor()->setARGB('FFFFFFE1');$ objPHPExcel->getActiveSheet()->getStyle( 'R1:U1')->getFill()->getStartColor()->setARGB('FFFFFFE1');
  $objPHPExcel->getActiveSheet()->getComment( 'D1')->getText()->createTextRun('0：普货');$ objPHPExcel->getActiveSheet()->getComment( 'D1')->getText()->createTextRun("\r\n" ); //添加更多批注
  $objPHPExcel->getActiveSheet()->getComment( 'D1')->getText()->createTextRun('1：含电');$ objPHPExcel->getActiveSheet()->getComment( 'D1')->getText()->createTextRun("\r\n" ); //添加更多批注
  $objPHPExcel->getActiveSheet()->getComment( 'D1')->getText()->createTextRun('2：纯电');$ objPHPExcel->getActiveSheet()->getComment( 'D1')->getText()->createTextRun("\r\n" ); //添加更多批注
  $objPHPExcel->getActiveSheet()->getComment( 'D1')->getText()->createTextRun('3：纺织品');$ objPHPExcel->getActiveSheet()->getComment( 'D1')->getText()->createTextRun("\r\n" ); //添加更多批注
  $objPHPExcel->getActiveSheet()->getComment( 'D1')->getText()->createTextRun('4：易碎品');$ objPHPExcel->getActiveSheet()->getComment( 'J1')->getText()->createTextRun('请参照sheet2品类表格.');
  $objPHPExcel->getActiveSheet()->getComment( 'K1')->getText()->createTextRun('必填，链接长度不能超过1000个字符.');$ objPHPExcel->getActiveSheet()->getComment( 'L1')->getText()->createTextRun('必填，只能输入“Y”或“N”，如果不是，直接报错.');
  $objPHPExcel->getActiveSheet()->getComment( 'M1')->getText()->createTextRun('当是否品牌为“Y”时必填，不能超过100个字符.');$ objPHPExcel->getActiveSheet()->getComment( 'N1')->getText()->createTextRun('当是否品牌为“Y”时必填，不能超过100个字符.');
  $objPHPExcel->getActiveSheet()->getComment( 'R1')->getText()->createTextRun('必填，输入国家二字码，目前只能输入“CN”.');$ objPHPExcel->getActiveSheet()->getComment( 'S1')->getText()->createTextRun('必填，币种只能为USD，输入范围在0.01-99999999.99.');
  $objPHPExcel->getActiveSheet()->getComment( 'T1')->getText()->createTextRun('必填，输入国家二字码，输入的国家二字码只能为状态为“可用”的海外仓库对应的国家二字码，目前仅支持US、GB、CZ、AU、FR、ES、IT;支持输入多个，进口国/地区的二字码之间用英文逗号分隔.');$ objPHPExcel->getActiveSheet()->getComment( 'U1')->getText()->createTextRun('必填，币种只能为USD，输入的范围在0.01-99999999.99；支持输入多个，进口申报单价之间用英文逗号分隔.');
  
  $objPHPExcel->getActiveSheet() ->getStyle('A1:U1')->getAlignment()->setWrapText(true);//自动换行$ objPHPExcel->getActiveSheet()->setTitle('产品');
  $objPHPExcel->setActiveSheetIndex(0) ->getColumnDimension("A") ->setAutoSize(true);$ objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35);
  // $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('60'); for($ i='A'; $i<='U';$ i++){
  // echo $i;$ objPHPExcel->getActiveSheet()->getColumnDimension($i)->setWidth('20');
  }
  
  // $objPHPExcel->getActiveSheet()->getColumnDimension( 'A')->setAutoSize(true); //内容自适应 for($ a = 0; $a < count($ title); $a++){$ objPHPExcel->setActiveSheetIndex(0)->setCellValue( $titleRow[$ a], $title[$ a]);
  }

    //品牌
    $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;
    }
}

}

excel 导出

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读