yii2学习笔记 --- 导入Excel
冉永宁
composer安装 composer require "phpoffice/phpexcel": "*"
建一个上传模型Upload.php
<?php
namespace app\models;
use Yii;
use yii\base\Model;
class Upload extends Model{
public $file;
public function rules(){
return [
[['file'], 'file', 'extensions' => 'xls'],
];
}
public function attributeLabels(){
return [
'file'=>'文件上传'
];
}
}建一个视图文件import.php
<?php
use yii\widgets\ActiveForm;
?>
<input type="button" class="btn btn-xs btn-success" data-toggle="modal" data-target="#myModal" id="import" value="导入excell" />
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<?php $form = ActiveForm::begin(); ?>
<div class="modal-dialog" >
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal"
aria-hidden="true">×
</button>
<h4 class="modal-title" id="myModalLabel">
导入Excell
</h4>
</div>
<div class="modal-body">
<div class="row">
<div class="col-xs-12">
<div class="form-group">
<div class="col-sm-9">
<?= $form->field($model, 'file')->fileInput() ?>
<span class="Validform_checktip"></span>
</div>
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button type="submit" id='formbtn' class="btn btn-primary">
提交保存
</button>
<button type="button" class="btn btn-default" data-dismiss="modal">
关闭
</button>
</div>
</div><!-- /.modal-content -->
</div><!-- /.modal-dialog -->
<?php ActiveForm::end(); ?>
</div>
控制器代码
循环列数打印没数据，用一个笨方法写死掉
<?php
use PHPExcel;
use yii\web\UploadedFile;
use app\models\LoadForm;
?>
public function actionImport() {
//实例化
$model = new Upload();
if (Yii::$app->request->isPost) {
$file = UploadedFile::getInstance($model, 'file');//print_r($file);exit;
$path="upload/excel/".date("Ymd",time())."/";//print_r($path);exit;
if ($file && $model->validate()) {
if (!file_exists($path)) {
mkdir($path,0777, true);
}
$file->saveAs($path . time() . '.' . $file->getExtension());//print_r($file);exit;
Yii::$app->session->setFlash('success', '上传成功！');
$this->data_import($path . time() . '.' . $file->getExtension());
}
}
return $this->render('import',['model'=>$model]);
}
public function data_import($file)
{
require(Yii::getAlias("@vendor")."/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php");//引入读取excel的类文件
require(Yii::getAlias("@vendor")."/phpoffice/phpexcel/Classes/PHPExcel.php");
$filename=$file;//print_r($filename);exit;
$fileType=\PHPExcel_IOFactory::identify($filename);//自动获取文件的类型提供给phpexcel用
$objReader=\PHPExcel_IOFactory::createReader($fileType);//获取文件读取操作对象
$excel = $objReader->load($filename);//引入文件
$excelSheets = $excel->getAllSheets();
foreach ($excelSheets as $SheetIndex => $activeSheet) {
$sheetColumnTotal = $activeSheet->getHighestRow();//总行数
if($sheetColumnTotal == 1){
continue;
}
for($i = 2;$i < $sheetColumnTotal;$i++){
$data=array(
$activeSheet->getCell('A'.$i)->getValue(),
$activeSheet->getCell('B'.$i)->getValue(),
$activeSheet->getCell('C'.$i)->getValue(),
$activeSheet->getCell('D'.$i)->getValue(),
$activeSheet->getCell('E'.$i)->getValue(),
$activeSheet->getCell('F'.$i)->getValue(),
$activeSheet->getCell('G'.$i)->getValue(),
$activeSheet->getCell('H'.$i)->getValue(),
$activeSheet->getCell('I'.$i)->getValue(),
$activeSheet->getCell('J'.$i)->getValue(),
$activeSheet->getCell('K'.$i)->getValue(),
$activeSheet->getCell('L'.$i)->getValue(),
$activeSheet->getCell('M'.$i)->getValue(),
$activeSheet->getCell('N'.$i)->getValue(),
$activeSheet->getCell('O'.$i)->getValue(),
$activeSheet->getCell('P'.$i)->getValue(),
$activeSheet->getCell('Q'.$i)->getValue(),
$activeSheet->getCell('R'.$i)->getValue(),
$activeSheet->getCell('S'.$i)->getValue(),
$activeSheet->getCell('T'.$i)->getValue(),
$activeSheet->getCell('U'.$i)->getValue(),
$activeSheet->getCell('V'.$i)->getValue(),
$activeSheet->getCell('W'.$i)->getValue(),
$activeSheet->getCell('X'.$i)->getValue(),
$activeSheet->getCell('Y'.$i)->getValue(),
$activeSheet->getCell('Z'.$i)->getValue(),
$activeSheet->getCell('AA'.$i)->getValue(),
$activeSheet->getCell('AB'.$i)->getValue(),
$activeSheet->getCell('AC'.$i)->getValue(),
$activeSheet->getCell('AD'.$i)->getValue(),
$activeSheet->getCell('AE'.$i)->getValue(),);//print_r($data);exit;
$info=Yii::$app->db->createCommand()->insert(Information::tableName(), [
'creation_time' => $data['0'],
'click_time' => $data['1'],
'commodity' => $data['2'],
'commodity_id' => $data['3'],
'manager' => $data['4'],
'shop' => $data['5'],
'number' => $data['6'],
'unitprice' => $data['7'],
'state' => $data['8'],
'type' => $data['9'],
'income_ratio' => $data['10'],
'division_ratio' => $data['11'],
'payment' => $data['12'],
'effect_prediction' => $data['13'],
'settlement' => $data['14'],
'forecast_income' => $data['15'],
'settlement_time' => $data['16'],
'commission_rate' => $data['17'],
'commission_amount' => $data['18'],
'service_charge_ratio' => $data['19'],
'subsidy_ratio' => $data['20'],
'subsidy_amount' => $data['21'],
'subsidy_type' => $data['22'],
'transaction_platform' => $data['23'],
'service_source' => $data['24'],
'order_number' => $data['25'],
'category_name' => $data['26'],
'media_id' => $data['27'],
'media_name' => $data['28'],
'advertisement_id' => $data['29'],
'advertisement_name' => $data['30'],
])->execute();
if ($info) {
$ok = 1;
}
}
}
if ($ok == 1){
$this->redirect(array('index'));
} else{
echo "<script>alert('操作失败');window.history.back();</script>";
}
// $data = [];
// if($total_line > 1) {
// for ($row = 2; $row <= $total_line; $row++) {
// for ($column = 'A'; $column <= $total_column; $column++) {
// //var_dump($column);
// $data[$row][$column] = trim($phpExcel->getCell($column . $row)->getValue());
// }
//
// var_dump($data);
// die;
// }
// }
}
类似资料：