yii2 使用 PhpSpreadsheet 对excel进行导入导出操作

最新推荐文章于 2024-09-02 08:12:02 发布

_野草

最新推荐文章于 2024-09-02 08:12:02 发布

阅读量1.8k

点赞数 1

分类专栏： PHP

本文链接：https://blog.csdn.net/dl1456074580/article/details/105946155

版权

PHP 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

安装PhpSpreadsheet

composer require phpoffice/phpspreadsheet

安装完成后我们写一个工具类

Excel.php 工具类

<?php
namespace common\tools;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;


class Excel
{

    # 导入excel
    static function importExecl($file = '', $sheet = 0, $columnCnt = 0, &$options = [])
    {
        try {
            /* 转码 */
            $file = iconv("utf-8", "gb2312", $file);

            if (empty($file) OR !file_exists($file)) {
                throw new \Exception('文件不存在!');
            }

            /** @var Xlsx $objRead */
            $objRead = IOFactory::createReader('Xlsx');

            if (!$objRead->canRead($file)) {
                /** @var Xls $objRead */
                $objRead = IOFactory::createReader('Xls');

                if (!$objRead->canRead($file)) {
                    throw new \Exception('只支持导入Excel文件！');
                }
            }

            /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
            empty($options) && $objRead->setReadDataOnly(true);
            /* 建立excel对象 */
            $obj = $objRead->load($file);
            /* 获取指定的sheet表 */
            $currSheet = $obj->getSheet($sheet);

            if (isset($options['mergeCells'])) {
                /* 读取合并行列 */
                $options['mergeCells'] = $currSheet->getMergeCells();
            }

            if (0 == $columnCnt) {
                /* 取得最大的列号 */
                $columnH = $currSheet->getHighestColumn();
                /* 兼容原逻辑，循环时使用的是小于等于 */
                $columnCnt = Coordinate::columnIndexFromString($columnH);
            }

            /* 获取总行数 */
            $rowCnt = $currSheet->getHighestRow();
            $data   = [];

            /* 读取内容 */
            for ($_row = 1; $_row <= $rowCnt; $_row++) {
                $isNull = true;

                for ($_column = 1; $_column <= $columnCnt; $_column++) {
                    $cellName = Coordinate::stringFromColumnIndex($_column);
                    $cellId   = $cellName . $_row;
                    $cell     = $currSheet->getCell($cellId);

                    if (isset($options['format'])) {
                        /* 获取格式 */
                        $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                        /* 记录格式 */
                        $options['format'][$_row][$cellName] = $format;
                    }

                    if (isset($options['formula'])) {
                        /* 获取公式，公式均为=号开头数据 */
                        $formula = $currSheet->getCell($cellId)->getValue();

                        if (0 === strpos($formula, '=')) {
                            $options['formula'][$cellName . $_row] = $formula;
                        }
                    }

                    if (isset($format) && 'm/d/yyyy' == $format) {
                        /* 日期格式翻转处理 */
                        $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                    }

                    $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());

                    if (!empty($data[$_row][$cellName])) {
                        $isNull = false;
                    }
                }

                /* 判断是否整行数据为空，是的话删除该行数据 */
                if ($isNull) {
                    unset($data[$_row]);
                }
            }

            return $data;
        } catch (\Exception $e) {
            throw $e;
        }
    }
   # 导出
    static function exportExcel($data,$title)
    {
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();

        //设置工作表标题名称
        $worksheet->setTitle('工作表格1');

        //表头 设置单元格内容
        foreach ($title as $key => $value) {
            $worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
        }

        $row = 2; //从第二行开始
        foreach ($data as $item) {
            $column = 1; //从第一列设置并初始化
            foreach ($item as $value) {
                $worksheet->setCellValueByColumnAndRow($column, $row, $value); //哪一列哪一行设置哪个值
                $column++; //列数加1
            }
            $row++; //行数加1
        }

        //输出到浏览器
        $fileName = date('YmdHis',time());
        $fileType = 'Xlsx';
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        self::excelBrowserExport($fileName, $fileType);
        $writer->save('php://output');
    }
    static public function excelBrowserExport($fileName, $fileType)
    {
        //文件名称校验
        if (!$fileName) {
            throw new Exception('文件名不能为空');
        }

        //Excel文件类型校验
        $type = ['Excel2007', 'Xlsx', 'Excel5', 'xls'];
        if (!in_array($fileType, $type)) {
            throw new Exception('未知文件类型');
        }

        if ($fileType == 'Excel2007' || $fileType == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
            header('Cache-Control: max-age=0');
        } else {
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
            header('Cache-Control: max-age=0');
        }
    }

}

写一个测试的控制器

<?php
namespace frontend\controllers;

use app\models\UploadForm;
use common\tools\Excel;
use Yii;
use yii\web\Controller;
use yii\web\UploadedFile;

class TestController extends Controller
{

    # 用来测试的index页面 
    public function actionIndex()
    {

        # 文件上传的model
        $file = new UploadForm();
        return $this->render('index', [
            'file' => $file
        ]);

    }



    # 导入表格
    public function actionImport_excel_data(){
        $model = new UploadForm();
        if (Yii::$app->request->isPost) {
            $model->file = UploadedFile::getInstance($model, 'file');
            if ($model->file && $model->validate()) {
                $path = 'uploads/';
                if (!file_exists($path)) {
                    mkdir($path, 0755, true);
                }
                $time = time();
                $rand = rand(100,999);
                $basename = $model->file->baseName;
                $filename = md5($basename.$time.$rand);
                $fname = $path . $filename . '.' . $model->file->extension;
                if ($model->file && $model->validate()) {
                    $model->file->saveAs($fname);
                }
                $fpath = Yii::$app->BasePath.'/web/'.$fname;
//                echo $fpath;
//                exit;
                $data = Excel::importExecl($fpath);
                # 得到表头
                $biaotou = $data[1];
//                $date = $biaotou['日期'];
//                $productname = $biaotou['损坏产品名称'];
//                $people = $biaotou['产品使用人'];
//                $ment = $biaotou['产品所属部门'];
//                $reason = $biaotou['损坏原因'];
                $count  = count($data);
//                var_dump($data);
//                exit;
                if ($biaotou['A'] != '日期'||$biaotou['B'] != '损坏产品名称'||$biaotou['C'] != '产品使用人'
                ||$biaotou['D'] != '产品所属部门'||$biaotou['E'] != '损坏原因'){
                    return "表格表头不匹配";
                }
                # 这里要从第二行开始读 因为第一行是表头
                # 拿到数据后写入数据里面 建议这里用批量写入的方式而不是再循环里面写入数据库
                for ($i = 2; $i<$count+1; $i++){
                   # var_dump($data[$i]);
                    echo '日期'.$data[$i]['A']."</br>";
                    echo '损坏产品名称'.$data[$i]['B']."</br>";
                    echo '产品使用人'.$data[$i]['C']."</br>";
                    echo '产品所属部门'.$data[$i]['D']."</br>";
                    echo '损坏原因'.$data[$i]['E']."</br>";
                }
            }
        }

        return "";
    }
    # 导出表格
    public function actionExport_excel_data(){
        $data = [
            ['2020-04-29', '测试', '张三','测试部门','没得原因'],
            ['2020-04-29', '测试', '张三','测试部门','没得原因'],
            ['2020-04-29', '测试', '张三','测试部门','没得原因'],
            ['2020-04-29', '测试', '张三','测试部门','没得原因'],
            ['2020-04-29', '测试', '张三','测试部门','没得原因'],
            ['2020-04-29', '测试', '张三','测试部门','没得原因']
        ];
        $title = ['日期', '损坏产品名称', '产品使用人','产品所属部门','损坏原因'];
        Excel::exportExcel($data,$title);
    }
}

UploadForm.php

<?php

namespace app\models;


use yii\base\Model;

class UploadForm extends Model
{
    /**
     * @var UploadedFile|Null file attribute
     */
    public $file;

    /**
     * @return array the validation rules.
     */
    public function rules()
    {
        return [
            [['file'], 'file'],
        ];
    }

}

view里面的index.php

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

<!--导入表格-->
<h2>导入表格</h2>
<?php $form = ActiveForm::begin(['options' => ['enctype' => 'multipart/form-data'],'action' => ['import_excel_data'],'method'=>'post']) ?>

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

<button>导入表格</button>

<?php ActiveForm::end() ?>
<!--下载表格-->
<a  href="export_excel_data">下载表格</a>

以上就是全部代码，均测试过，流程跑通了的。可以根据自己需要进行调整。