[Magento代码]-导入excel添加分类，并关联产品到该分类下

原创于 2016-01-06 16:08:04 发布
· 479 阅读
0 ·
版权
Magento 专栏收录该内容
133 篇文章
订阅专栏
<?php
error_reporting(E_ALL);
set_time_limit(0);
define('MAGENTO', realpath(dirname(__FILE__)));
require_once MAGENTO . '/app/Mage.php';
umask(0);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);


/**
 * 添加产品所属分类
 *
 * @param $skustr          产品SKU拼接的字符串
 * @param $$categoryId  分类ID
 */
function add_category_product($skustr,$categoryId){
    $skuArray = explode('|',$skustr);
    foreach($skuArray as $sku){
        if(trim($sku)){

            $_product = Mage::getModel('catalog/product')->loadByAttribute('sku',trim($sku));
            if($_product){
                $productId = $_product->getId();

                //给该产品添加分类
                //先查询是否已经存在，存在就不新增
                $read= Mage::getSingleton('core/resource')->getConnection('core_read');
                $sql = "SELECT * from catalog_category_product WHERE category_id=$categoryId AND product_id=$productId";
                $results = $read->fetchAll($sql);
                if(!$results){
                    $write = Mage::getSingleton('core/resource')->getConnection('core_write');
                    $sql = "insert into catalog_category_product values($categoryId,$productId,1)";
                    $write->query($sql);
                }
            }
        }

    }
}


/**
 * 添加分类
 *
 * @param $name            分类名称
 * @param null $path       父分类path
 */
function addCategory($name,$path=null){

        if ($path) {
            $path = $path;
        }else{
            $path = '1/2';
        }

        $catalog = Mage::getModel('catalog/category');
        $catalog->setPath ($path)->setIsActive(0)->setName(trim($name));
        $catalog->save();

        $categoryId = $catalog->getData('entity_id');
        return $categoryId;
}

/**
 * 根据分类名称获取分类id
 *
 * @param $name 分类名称
 */
function getCategoryId($name){
    $name = trim($name);
    //查询该分类名是存在，如果存在就取出分类id
    $read= Mage::getSingleton('core/resource')->getConnection('core_read');
    $sql = "SELECT t1.entity_id
            FROM catalog_category_entity AS t1
            LEFT JOIN catalog_category_entity_varchar AS t2 ON t2.entity_id=t1.entity_id
            WHERE t2.value='{$name}' order by  entity_id desc";
    $results = $read->fetchAll($sql);
    $entity_id = $results[0]['entity_id'];
    if($entity_id){
        return $entity_id;
    }
}

/**
 * 根据分类名称获取分类path
 *
 * @param $name 分类名称
 */
function getCategoryPath($name){
    $name = trim($name);
    //查询该分类名是存在，如果存在就取出分类id
    $read= Mage::getSingleton('core/resource')->getConnection('core_read');
    $sql = "SELECT t1.path
            FROM catalog_category_entity AS t1
            LEFT JOIN catalog_category_entity_varchar AS t2 ON t2.entity_id=t1.entity_id
            WHERE t2.value='{$name}' order by  t1.entity_id desc";
    $results = $read->fetchAll($sql);
    $path = $results[0]['path'];
    if($path){
        return $path;
    }
}

/**
 * 判断当前分类是不是另一个的分类的子分子
 *
 * @param $current_name 当前分类名
 * @param $last_name     父分类
 */
function isLastCate($current_name,$last_name){
    $current_name = trim($current_name);
    $last_name = trim($last_name);

    //获取父分类id
    $parent_id = getCategoryId($last_name);


    //查询该分类名是存在，如果存在就取出分类id
    $read= Mage::getSingleton('core/resource')->getConnection('core_read');
    $sql = "SELECT *
            FROM catalog_category_entity AS t1
            LEFT JOIN catalog_category_entity_varchar AS t2 ON t2.entity_id=t1.entity_id
            WHERE t2.value='{$current_name}' AND t1.parent_id={$parent_id} AND t2.attribute_id=41
            ORDER BY t1.entity_id DESC";
    $results = $read->fetchAll($sql);
    if($results){
        return true;
    }else{
        return false;
    }
}

//============================导入excel start======================================  
require_once MAGENTO . '/lib/PHPExcel/PHPExcel.php';//引入PHPExcel类文件  
$PHPExcel = new PHPExcel();// 实例化PHPExcel工具类  
  
//excel文件的地址  
$excel_fiel_path = MAGENTO.'/var/importexport/Category.xlsx';  
  
//分析文件获取后缀判断是2007版本还是2003  
$extend = pathinfo("./" . $excel_fiel_path);  
$extend = strtolower($extend["extension"]);  
// 判断xlsx版本，如果是xlsx的就是2007版本的，否则就是2003  
if ($extend=="xlsx") {  
    $PHPReader = new PHPExcel_Reader_Excel2007();  
    $PHPExcel = $PHPReader->load($excel_fiel_path);  
}else{  
    $PHPReader = new PHPExcel_Reader_Excel5();  
    $PHPExcel = $PHPReader->load($excel_fiel_path);  
}  
  
  
/* 第二种方法*/  
$objWorksheet = $PHPExcel->getActiveSheet();  
$highestRow = $objWorksheet->getHighestRow();  
//echo 'highestRow='.$highestRow;  
//echo "<br>";  
$highestColumn = $objWorksheet->getHighestColumn();  
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数  
//echo 'highestColumnIndex='.$highestColumnIndex;  
//echo "<br>";  
$headtitle=array();  
for ($row = 2;$row <= $highestRow;$row++)  
{  
    $strs=array();  
    //注意highestColumnIndex的列数索引从0开始  
    for ($col = 0;$col < $highestColumnIndex;$col++)  
    {  
        $strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();  
  
    }  


    //遍历添加分类
    foreach($strs as $k=>$v){

       if(!empty($v) && $k!=0 && $k!=8){
           //先判断第一个分类是否存在
           if($k==1){
               $first_categoryId = getCategoryId($strs[1]);
               if(empty($first_categoryId)){ //不存在
                   $categoryId = addCategory($v); //新增分类
                   add_category_product($strs[8],$categoryId); //给该分类添加产品
               }else{
                   add_category_product($strs[8],$first_categoryId); //给该分类添加产品
               }
           }

           if($k>1 && $k<8){
               //判断当前分类是否是上一列分类的子分类
               if(!isLastCate($v,$strs[$k-1])){
                   $path = getCategoryPath($strs[$k-1]);
                   $categoryId = addCategory($v,$path);
                   add_category_product($strs[8],$categoryId); //给该分类添加产品
               }else{
                   $categoryId = getCategoryId($v);
                   add_category_product($strs[8],$categoryId); //给该分类添加产品
               }
           }
       }
    }





}  
//============================导入excel end======================================