初识Vue2.* - 与PhpSpreadsheet的小demo

Vue.js 一个流行的MVVM前端框架，数据驱动思想使得前端开发易于理解和维护。
PHPSpreadsheet 提起已经不再维护的PHPExcel，可能知道的人更多一些。PHPSpreadsheet就是PHPExcel的新项目。

刚接触Vue，结合PHPSpreadsheet做了excel和表格互转的小demo。

1、excel文件表格展示

前端代码

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <!--<meta name="viewport" content="width=device-width,initial-scale=1.0">-->
    <!-- Bootstrap 核心 CSS 文件 -->
    <link rel="stylesheet" href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
    <script src="https://cdn.bootcss.com/jquery/3.3.1/jquery.min.js"></script>
    <!--vue.js-->
    <script src="https://cdn.jsdelivr.net/npm/vue@2.5.16/dist/vue.js"></script>
    <title>vue-excel</title>
</head>
<body>
<div class="container" style="padding-top: 60px;">
    <div class="row" id="fileForm" style="text-align: center;display:flex; display: -webkit-flex; align-items:center;">
        <div class="col-md-2 col-md-offset-2">
            <div  class="btn btn-default" v-on:click="selectFile">选择文件</div>
        </div>
        <div class="col-md-4">
            {{ fileData ? '当前选择：'+(fileData[0].name) :'未选择文件' }}
            <span v-show="upStatus" style="margin-left: 32px;color: #00CC00;">【 {{ upStatus }} 】</span>
        </div>
        <div class="col-md-2">
            <div v-on:click="upload" class="btn btn-default">提交</div>
        </div>
        <input type="file" id="upfile" name="file" @change="tirggerFile($event)" v-show="false">
    </div>

    <div class="row" v-if="load" id="table">
        <div style="padding:36px 0;text-align:center;font-size: 22px;">
            {{ name }}
        </div>
        <table class="table">
            <tr>
                <th v-for="title in titles">{{ title }}</th>
            </tr>
            <tr v-for="row in excel">
                <td v-for="val in row">{{ val }}</td>
            </tr>
        </table>
    </div>
</div>
</body>
</html>
<script>
    var ExcelFile = new Vue({
        el: "#fileForm",
        data: {
            upStatus: false,
            fileData: false,
            text: 'nihao '
        },
        methods:{
            selectFile: function () {
                upfile.click();
            },
            tirggerFile : function (event) {
                this.fileData = event.target.files;
                this.upStatus = '未上传';
            },
            upload:function () {
                let formData = new FormData();
                let docName = this.fileData[0].name;
                formData.append("file", this.fileData[0]);
                $.ajax({
                    url: "./index.php",
                    type: "POST",
                    data: formData,
                    async: true,
                    processData: false,
                    contentType: false,
                    dataType: "json",
                    beforeSend:function(){
                        ExcelFile.upStatus = '正在上传...';
                    },
                    success: function (data) {
                        if (data.code != 200){
                            ExcelFile.upStatus = data.msg;
                            alert(data.msg);
                            return;
                        }
                        ExcelFile.upStatus = '已上传';
                        // PHPSpreadsheet返回的是关联数组，JSON后为对象
                        table.titles = data.result[1];
                        delete data.result[1];
                        table.excel = data.result;
                        table.name = docName;
                        table.load = true;
                        console.log('success');
                    },
                    error: function (data) {
                        ExcelFile.upStatus = '上传失败';
                        alert('上传失败');
                    }
                });
                return false;
            }
        }
    });

    var table = new Vue({
        el:'#table',
        data:{
            name: '',
            load:false,
            titles:null,
            excel:null
        },
        methods:{
        }
    });
</script>

后端代码

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;

function reJson($code=200,$msg='',$result=[]){
    $data['code'] = $code;
    $data['msg'] = $msg;
    $data['result'] = $result;
    echo json_encode($data);
    die;
}

if (!isset($_FILES["file"]))  reJson(204,'未上传文件');
if ($_FILES["file"]["error"] > 0) reJson(204,'文件上传出错');
// 这里只接收 xsl 和 xsls 文档，PhpSpreadsheet支持更多格式，自行处理
if (!($_FILES["file"]["type"] == 'application/vnd.ms-excel' || $_FILES["file"]["type"] == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')) reJson(204,'文件类型不符');

$inputFileName = $_FILES["file"]["tmp_name"];
// PhpSpreadsheet能够自动识别文档类型
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
reJson(200,'ok',$sheetData);

2、填写表单生成excel文件

前端代码

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <!--<meta name="viewport" content="width=device-width,initial-scale=1.0">-->
    <!-- Bootstrap 核心 CSS 文件 -->
    <link rel="stylesheet" href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/vue@2.5.16/dist/vue.js"></script>
    <title>vue-excel</title>
</head>
<body>
<div class="container" style="padding-top: 60px;">
    <div class="table-responsive" id="tableDiv">
        <form action="./output.php" method="post" id="infoForm">
        <table class="table table-striped table-bordered table-hover">
            <tr>
                <th>姓名</th>
                <th>性别</th>
                <th>身高</th>
                <th>体重</th>
                <th>体积</th>
                <th>表面积</th>
            </tr>
            <tr v-for="info in infos">
                <td><input class="form-control" type="text" name="name[]" v-model="info.name"></td>
                <td><input class="form-control" type="text" name="sex[]" v-model="info.sex"></td>
                <td><input class="form-control" type="text" name="height[]" v-model="info.height"></td>
                <td><input class="form-control" type="text" name="weight[]" v-model="info.weight"></td>
                <td><input class="form-control" type="text" name="volume[]" v-model="info.volume"></td>
                <td><input class="form-control" type="text" name="proportion[]" v-model="info.proportion"></td>
            </tr>
        </table>
        </form>
        <div style="margin: 22px auto;text-align: center;" >
            <div class="btn btn-success btn-lg" @click="add" style="margin: 0 16px;">新增</div>
            <div class="btn btn-primary btn-lg" @click="submit" style="margin: 0 16px;">提交</div>
        </div>
    </div>
</div>
</body>
</html>
<script>
    var table = new Vue({
        el: "#tableDiv",
        data: {
            // 表单模型
            // 直接写对象的话，用的时候要做深拷贝处理
            model: function(){
                return {
                    name:"李四",
                    sex:1,
                    height: 176,
                    weight: 66,
                    volume: 128,
                    proportion: 90
                };
            },
            infos : []
        },
        methods: {
            add: function () {
                this.infos.push(this.model());
            },
            submit:function () {
                infoForm.submit()
            }
        },
        mounted:function() {
            this.infos.push(this.model());
        }
    });
</script>

后端代码

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
$spreadsheet = new Spreadsheet();
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setTitle('infos');
$titles = [
        'name' =>'姓名',
        'sex' =>'性别',
        'height' =>'身高',
        'weight' =>'体重',
        'volume' =>'体积',
        'proportion' =>'表面积'
    ];
$azs = range('A','Z');
$datas = $_POST;
// 写入表格数据
$i = 0;
foreach ($titles as $name => $title) {
    $spreadsheet->getActiveSheet()->setCellValue(($azs[$i]).'1', $title);
    foreach ($datas[$name] as $n => $val) {
        $spreadsheet->getActiveSheet()->setCellValue(($azs[$i]).($n+2), $val);
    }
    ++$i;
}

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="test.xlsx"');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');

JQ写习惯了，在写新增行的时候，惯性思维想写个模板追加过去。后来发现这样就违背了MVVM的思想，失去了扩展性和对数据的管理。在数据驱动下，我们只要对数据对象进行操作(如:排序)，视图就会响应数据。可以使前端开发更为专注高效。
PHPSpreadsheet 可以去下载或者使用composer安装

//composer.json
{
    "require": {
        "phpoffice/phpspreadsheet": "^1.2"
    }
}