Magento后台上传excel批量生成订单

1. \app\design\adminhtml\default\default\template\sales\order\create\from.phtml

<div id="creat_order_by_excel" sytyle="padding-bottom:10px;">
    <form  action="<?php echo $this->getUrl('*/Sales_Order_Create/upload') ?>" method="post" enctype="multipart/form-data">
        <?php echo $this->getBlockHtml('formkey')?><!--form表单这个不能少-->
        <input type="file" name="file" id="file"/>
        <button type="submit" id="send" >执行</button>
    </form>
    <script>
        varsend=document.getElementById("send");
        send.οnclick=function(){
            var file=document.getElementById("file").value;
            if(file.length<1){
                alert('请选择文件');
                return false;
            }
        }
    </script>
</div>

2.控制器接收上传的文件，然后读取excel，生成订单

\app\code\core\Mage\Adminhtml\controllers\Sales\Order\CreateController.php

 /**
     * 处理通过excel表格创建订单的数据
     * @date 2015年5月13日
     * @param $filename 文件名（路径）
     * @return bool
     */
    public function createOrderByExcelAction($filename){
        include_once('PHPExcel/PHPExcel.php');//引入PHPExcel类文件，phpexcel库在lib/目录下
        $PHPExcel = new PHPExcel();// 实例化PHPExcel工具类
        $excel_fiel_path = Mage::getBaseDir('var') . DS . 'uploads' .$filename;  //excel文件的地址

        //判断文件是否存在
        if(!file_exists($excel_fiel_path)){
           return false;
        }


        //分析文件获取后缀判断是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();

            }

            //创建订单
           if($this->createOrder($strs) === false){
               return false;
           }

        }
    }

    /**
     * 封装创建订单的函数
     * @param $array
     * @return object
     */
    protected function createOrder($array){
        //处理数组
        $email = trim($array[0]); //excel中 客户email字段
        $sku = trim($array[1]); //excel中 sku商品编号
        $qty = trim($array[2]); //excel中 对于的数量
        $street = trim($array[3]); //收件人地址
        $city = trim($array[4]); //收件人城市
        $region = trim($array[5]); //收件人州或省
        $postcode = trim($array[6]); //收件人邮编
        $telephone = trim($array[7]); //收件人电话
        $first_name = trim($array[8]); //收件人姓名
        $middle_name = trim($array[9]); //收件人姓名
        $last_name = trim($array[10]); //收件人姓名


        $customerEmail = $email;
        $customer = $this->getCustomerByEmail($customerEmail);//得到customer对象

        //验证customer对象
        $customer_id = $customer->getId();
        if(empty($customer_id)){
          return false;
        }

        $transaction     = Mage::getModel('core/resource_transaction');

        $storeId         = $customer->getStoreId();
        //$reservedOrderId 获取即将要创建的订单 orderId
        $reservedOrderId = Mage::getSingleton('eav/config')->getEntityType('order')->fetchNewIncrementId($storeId);

        $order = Mage::getModel('sales/order');
        $order->setIncrementId($reservedOrderId)
            ->setStoreId($storeId)
            ->setQuoteId(0)
            ->setGlobal_currency_code('USD')
            ->setBase_currency_code('USD')
            ->setStore_currency_code('USD')
            ->setOrder_currency_code('USD');
        //这里我设置成'USD', 你可以根据自己的需求修改或添加

        //保存用户信息
        $order->setCustomer_email($customer->getEmail())
            ->setCustomerFirstname($customer->getFirstname())
            ->setCustomerLastname($customer->getLastname())
            ->setCustomerGroupId($customer->getGroupId())
            ->setCustomer_is_guest(0)
            ->setCustomer($customer);



        // 保存 Billing Address
        $billing        = $customer->getDefaultBillingAddress();
        $billingAddress = Mage::getModel('sales/order_address');
        $billingAddress->setStoreId($storeId)
            ->setAddressType(Mage_Sales_Model_Quote_Address::TYPE_BILLING)
            ->setCustomerId($customer->getId())
            ->setCustomerAddressId($customer->getDefaultBilling())
            ->setCustomer_address_id($billing->getEntityId())
            ->setPrefix($billing->getPrefix())
            ->setFirstname($billing->getFirstname())
            ->setMiddlename($billing->getMiddlename())
            ->setLastname($billing->getLastname())
            ->setSuffix($billing->getSuffix())
            ->setCompany($billing->getCompany())
            ->setStreet($billing->getStreet())
            ->setCity($billing->getCity())
            ->setCountry_id($billing->getCountryId())
            ->setRegion($billing->getRegion())
            ->setRegion_id($billing->getRegionId())
            ->setPostcode($billing->getPostcode())
            ->setTelephone($billing->getTelephone())
            ->setFax($billing->getFax());

        $order->setBillingAddress($billingAddress);

        //新的送货地址(shipping address)
        $newShippingAddress = array(
            'first_name'=>$first_name,
            'middle_name'=>$middle_name,
            'last_name'=>$last_name,
            'street'=>$street,
            'city'=>$city,
            'region'=>$region,
            'postcode'=>$postcode,
            'telephone'=>$telephone
        );
        //调用保存送货地址(Shipping Address)函数
        $newShippingAddress = $this->setShippingAddress($newShippingAddress,$customer);



        //把送货地址写入订单
        $order->setShippingAddress($newShippingAddress)
            ->setShipping_method('flatrate_flatrate')
            ->setShippingDescription('This is a test!');

        //这里可以根据你的需求来设置付款方式名称
        $orderPayment = Mage::getModel('sales/order_payment');
        $orderPayment->setStoreId($storeId)
            ->setCustomerPaymentId(0)
            ->setMethod('purchaseorder')
            ->setPo_number(' – ');

        $order->setPayment($orderPayment);


        //组装products
        $subTotal = 0;
        $products = array(
            "$sku" => array('qty' => $qty)
        );
        foreach ($products as $sku => $product) {
            //$_product  = Mage::getModel('catalog/product')->load($productId);//通过产品ID
            $_product = Mage::getModel('catalog/product')->loadByAttribute('sku',$sku);//通过产品SKU
            $rowTotal  = $_product->getPrice() * $product['qty'];
            $orderItem = Mage::getModel('sales/order_item');
            $orderItem->setStoreId($storeId)
                ->setQuoteItemId(0)
                ->setQuoteParentItemId(NULL)
                ->setProductId($_product->getId())
                ->setProductType($_product->getTypeId())
                ->setQtyBackordered(NULL)
                ->setTotalQtyOrdered($product['rqty'])
                ->setQtyOrdered($product['qty'])
                ->setName($_product->getName())
                ->setSku($_product->getSku())
                ->setPrice($_product->getPrice())
                ->setBasePrice($_product->getPrice())
                ->setOriginalPrice($_product->getPrice())
                ->setRowTotal($rowTotal)
                ->setBaseRowTotal($rowTotal);

            $subTotal += $rowTotal;
            $order->addItem($orderItem);
        }

        $order->setSubtotal($subTotal)
            ->setBaseSubtotal($subTotal)
            ->setGrandTotal($subTotal)
            ->setBaseGrandTotal($subTotal);

        $transaction->addObject($order);
        $transaction->addCommitCallback(array($order, 'place'));
        $transaction->addCommitCallback(array($order, 'save'));
        $transaction->save();
    }

    /**
     * 通过客户邮箱(customer_email)得到客户实例Mage_Customer_Model_Customer（一个object）
     * 成功返回该实例对象，失败返回false
     * @param $customer_email
     * @return bool|object
     */
    protected function getCustomerByEmail($customer_email){
        $customer = Mage::getModel("customer/customer");
        //$WebsiteId = Mage::app()->getWebsite('admin')->getId();
        $customer->setWebsiteId(1);//查看数据库，设置你自己的website_id
        $customer->loadByEmail($customer_email);
        if(!is_object($customer)){
            return false;
        }
        return $customer;
    }

    /**
     *设置送货地址 Shipping Address
     * @param $customer
     * @param $array
     * @return object
     */
    protected function setShippingAddress ($array,$customer){
        //查询region_id的值

        $shippingAddress = Mage::getModel('sales/order_address');
        $shippingAddress->setStoreId($customer->getStoreId())
            ->setAddressType(Mage_Sales_Model_Quote_Address::TYPE_SHIPPING)
            ->setCustomerId($customer->getId())
            ->setCustomerAddressId($customer->getDefaultShipping())
            ->setFirstname($array['first_name'])
            ->setMiddlename($array['middle_name'])
            ->setLastname($array['last_name'])
            ->setStreet(array($array['street']))
            ->setCity($array['city'])
            ->setCountry_id('US')
            ->setRegion($array['region'])
            ->setRegion_id($this->getRegionId($array['region']))
            ->setPostcode($array['postcode'])
            ->setTelephone($array['telephone']);
        if(!is_object($shippingAddress)){
            return false;
        }
        return $shippingAddress;
    }

    /**
     * 通过region_name获取region_id
     * @param $region_name
     * @return $region_id
     */
    protected function getRegionId($region_name){
        //获得region集合
//        $regionCollection = Mage::getModel('directory/region_api');
//        $region_id = '';
//        foreach($regionCollection as $region){
//            if($region['name'] == $region_name){
//                $region_id = $region['region_id'];
//                break;
//            }
//        }
//
//        return $region_id;

        $sql = "select region_id from directory_country_region where  default_name = '".$region_name."'";
        $handle = Mage::getSingleton('core/resource')->getConnection('core_write');
        $query = $handle->query($sql);
        $row = $query->fetch();
        return $row['region_id'];
    }