yii phpexcel 导入数据和导出数据

在做网页的设计的同时我们不可避免的要使用这类数据型插件，至于highchart 之后也会有所介绍
闲话不多说直接上干货

1，需要用表单上传文件

<?php
namespace app\models;

use yii\base\Model;
use yii\web\UploadedFile;

/**
 * UploadForm is the model behind the upload form.
 */
class UploadForm extends Model
{
    /**
     * @var UploadedFile file attribute
     */
    public $file;

    /**
     * @return array the validation rules.
     */
    public function rules()  //定义上传文件
    {
        return [
            [['file'], 'file'],
	    //[['file'], 'file','skipOnEmpty' => false],  后面表示选择不能为空 规避一下违法的操作
        ];
    }
}

<2>一个view

<?php
use yii\widgets\ActiveForm;
?>

<?php $form = ActiveForm::begin(['options' => ['enctype' => 'multipart/form-data']]) ?>

<?= $form->field($model, 'file')->fileInput() ?>

<button>Submit</button>

<?php ActiveForm::end() ?>

<3>然后就是上传的controller 了

<?php
namespace app\controllers;

use Yii;
use yii\web\Controller;
use app\models\UploadForm;
use yii\web\UploadedFile;
use app\models\Mydata;

class UploadController extends Controller
{
    public function actionUpload()
    {
        $model = new UploadForm();  //使用一下 form吧
        $Mydata = new Mydata();
        $mydata1=new Mydata();
        if (Yii::$app->request->isPost) {
            $model->file = UploadedFile::getInstance($model, 'file'); // 文件内容映射
            $time = rand(100,999).time();               
                $name=$model->file->baseName . $time . '.'. $model->file->extension; // 文件的保存名
                /*$path=Yii::$app->basePath;
                echo $path;die;*/
                //$filePath = $this->fileExists(dirname(__FILE__).'/../');
                //echo $filePath;die;
            if ($model->validate()) {  //检测是否有错
                $result = $model->file->saveAs($name);  // 保存默认地址是 web下面            
                if($result)
                {
                    $pexcel = $Mydata->Dizhi($name);
                    $mingzhi=$model->file->baseName;
                    $geshi=$model->file->extension;
                    $luru=$mydata1->Excelto($mingzhi,$geshi,$time); // 对excel 进行处理
                }
            } 
        }
        return $this->render('upload', ['model' => $model]);
    }
 }

 <4>就是model 的函数处理了

public function Excelto($mingzhi,$geshi,$time)
  {
              
              require_once  dirname(__FILE__) . '/../PHPExcel/PHPExcel.php';
              
              $filePath = Yii::$app->basePath.'/web/'. $mingzhi . $time . '.' . $geshi; /文件的地址E:\php_developer\Apache\htdocs\xxx\web\uploads
              //echo $filePath;die;
              $PHPExcel = new \PHPExcel(); 
              /*默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/ 
              $PHPReader = new \PHPExcel_Reader_Excel2007(); 
              if(!$PHPReader->canRead($filePath)){ 
              $PHPReader = new \PHPExcel_Reader_Excel5(); 
              if(!$PHPReader->canRead($filePath)){ 
              echo 'no Excel'; 
              return ; 
              } 
              } 
              $PHPExcel = $PHPReader->load($filePath); 

              /**读取excel文件中的第一个工作表*/ 
              $currentSheet = $PHPExcel->getSheet(0); 
              /**取得最大的列号*/ 
              $allColumn = $currentSheet->getHighestColumn(); 
              /**取得一共有多少行*/ 
              $allRow = $currentSheet->getHighestRow(); 
              /**从第二行开始输出，因为excel表中第一行为列名*/
              $var=array(); 
              for($currentRow = 2;$currentRow <= $allRow;$currentRow++){ 
              /**从第A列开始输出*/ 
                 for($currentColumn= 'A';$currentColumn<= $allColumn; $currentColumn++)
                  { 
                      $val[$currentRow-2][ord($currentColumn) - 65] = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();/**ord()将字符转为十进制数 就是A转1*/     
                  } 
                  } 
              //echo "</br>";
              //var_dump($val);die;
              $results=Mydata::Insetindb($val);
              return $results;
              //补充插入成功后的画面 
    }
  // 这个就直接把excel中的数据打包成数组集了
public function Insetindb($val)
           { $flog=1;
            foreach ($val as $value) 
            {
              $xuhao=$value[0];
              $freq=$value[1];
              $temp=$value[2];
              $a=$value[3];
              $b=$value[4];
              $c=$value[5];
              $d=$value[6];
              $e=$value[7];
              $f=$value[8];
              $g=$value[9];
            $sql=<<<EOT
              INSERT INTO wenjianshuru_ceshi VALUES ("$xuhao","$freq","$temp","$a","$b","$c","$d","$e","$f","$g")
EOT;
                  //还需要改数据表的表名
                  $connection=\yii::$app->db;
                  $command = $connection->createCommand($sql);
                  $rowCount=$command->execute();
                  if(!$rowCount)
                    {$flog=2;}
           }
           return $flog;
         }

这之后就可以用迭代拿出结果内容处理了 。现在还不知道怎么处理折线图。

二 下载excel 也很简单只用把数组形式的结果集放进单元格去就行了去就很方便
    还需要phpexcel 的包