Yii实现Excel导入三级分类数据表

来源：互联网发布：万达影城知乎编辑：程序博客网时间：2024/05/23 13:03

1.把已经下载好的PHPExcel类放入Vendor目录下

2.在项目入口文件index.PHP加载：

require dirname(dirname(__FILE__)).'/vendor/phpexcel/PHPExcel.php';

3.在config/web.php中的components键中加入：

'aliases' => [
'class'=>'PHPExcel', //如果不定义该类，将会报错。值就是在phpqrcode.php中定义的命名空间
'@phpqrcode' => '@vendor/phpexcel/PHPExcel/', //@phpqrcode 就是在phpqrcode.php中定义的命名空间，@vendor/phpqrcode 为该类的路径
],

4.直接上Controller.php代码

<?php
namespace app\controllers;
use Yii;
use yii\web\Controller;
use PHPExcel,PHPExcel_Reader_Excel2007,PHPExcel_Reader_Excel5,PHPExcel_IOFactory,PHPExcel_Cell;
class ShopCategoryController extends Controller{
public function actionImport(){
header("content-type:text/html;charset=utf-8");
$PHPExcel = new PHPExcel();
//print_r($PHPExcel);die;
$filePath = 'shop_category.xlsx';
//判断打开文件的方式
$objReader = new PHPExcel_Reader_Excel2007();
if(!$objReader->canRead($filePath)){
$objReader = new PHPExcel_Reader_Excel5();
if(!$objReader->canRead($filePath)){
echo 'no Excel';
return ;
}
}
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($filePath);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData = array();
for ($row = 1; $row <= $highestRow; $row++) {
for ($col = 0; $col < $highestColumnIndex; $col++) {
$excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
//print_r($excelData);die;
$res = array();
foreach($excelData as $key =>$val){
if(isset($res[$val[0]])){
$res[$val[0]][$val[1]][] = ltrim($val[2]);
}else{
$res[$val[0]] = [
$val[1]=>[
ltrim($val[2]),
]
];
}
}
//echo '<pre>';
//print_r($res);
//exit();
$connect=Yii::$app->db;
foreach($res as $one_key =>$one_val){
//print_r($one_key); die; //一级分类：美食购物休闲娱乐、健身医疗保健旅游、文化、教育其他
$sql_add = "insert into shop_category(p_name,p_id) values('".$one_key."',0)";
$connect->createCommand($sql_add)->execute();
$level_one_id = $connect->getLastInsertID();
foreach($one_val as $two_key => $two_val){
print_r($two_key); // 二级分类：中餐东南亚餐西餐美食、日韩餐小吃快餐。。。。。
$sql_add = "insert into shop_category(p_name,p_id) values('".$two_key."',$level_one_id)";
$connect->createCommand($sql_add)->execute();
$level_two_id = $connect->getLastInsertID();
foreach($two_val as $three_key =>$three_val){
$sql_add = "insert into shop_category(p_name,p_id) values('".$three_val."',$level_two_id)";
$connect->createCommand($sql_add)->execute();
}
}
}
echo "<script>alert('导入成功');window.history.go(-1);</script>";
}
}

0 0