Joyce' Blog
0%

cc-develop-note-i

发表于
  1. ==vue 和 PHP 对 Excel、word 导入导出的支持==
  2. ==安装前后端项目==
  3. ==本地数据库==
Windows 系统安装指定版本的composer：

https://pkg.phpcomposer.com/#how-to-install-composer

  1. 找到并进入 PHP 的安装目录（和你在命令行中执行的 php 指令应该是同一套 PHP）。
  2. 将 composer.phar 复制到 PHP 的安装目录下面，也就是和 php.exe 在同一级目录。
  3. 在 PHP 安装目录下新建一个 composer.bat 文件，并将下列代码保存到此文件中。
1
@php "%~dp0composer.phar" %*

最后重新打开一个命令行窗口试一试执行 composer --version 看看是否正确输出版本号。

yii2 项目初始化命令：.\init

mklink 命令是将文件或目录建立双向连接, 该变任何一方都会发生变化, 其主要文件链接有三: 符号链接(软件接), 目录连接(软件接), 文本文件链接(硬连接), 可以这样理解, 软连接, 是建立快捷方式, 硬连接, 是进行复制

在 cmd 里面输入: mklink /? 来查看 mklink 命令和参数的使用

1
mklink /D cc C:\Users\pcy\Documents\DBISWork\cc-computer-backend
使用表前缀

https://www.yiiframework.com/doc/guide/2.0/zh-cn/db-dao

1
2
3
4
5
6
7
'components' => [
    // ...
    'db' => [
        // ...
        'tablePrefix' => 'tbl_',
    ],
],

src\api\http.js

1
2
// 如果出现问题，就直接 hard code 写死域名
Instance.defaults.baseURL = 'http://localhost:8081/cc-computer-homepage/backend/web/index.php/api/v1'

当前：PHP/5.6.40

Yii2 对 word、Excel 上传下载的支持——PHPOffice

PHPExcel was officially deprecated in 2017 and permanently archived in 2019. The project has not be maintained for years and must not be used anymore. All users must migrate to its direct successor PhpSpreadsheet, or another alternative.

PhpSpreadsheet

PHP 7.2+

1
2
3
4
5
6
7
8
9
10
11
12
13
<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');
Architecture

01-schematic.png

Creating a spreadsheet

Loading a Workbook from a file:

1
2
3
$inputFileName = './sampleData/example1.xls';
/** Load $inputFileName to a Spreadsheet object **/
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

Creating a new workbook:

1
2
/** Create a new Spreadsheet Object **/
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
Accessing cells

Setting a cell value by coordinate:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// Set cell A1 with a string value
$spreadsheet->getActiveSheet()->setCellValue('A1', 'PhpSpreadsheet');

// Set cell A2 with a numeric value
$spreadsheet->getActiveSheet()->setCellValue('A2', 12345.6789);

// Set cell A3 with a boolean value
$spreadsheet->getActiveSheet()->setCellValue('A3', TRUE);

// Set cell A4 with a formula
$spreadsheet->getActiveSheet()->setCellValue(
    'A4',
    '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
);

$spreadsheet->getActiveSheet()
    ->getCell('B8')
    ->setValue('Some value');

Documentation

PHPWord

The current version of PHPWord supports Microsoft Office Open XML (OOXML or OpenXML), OASIS Open Document Format for Office Applications (OpenDocument or ODF), and Rich Text Format (RTF).

PHPWord requires the following:

  • PHP 5.3.3+
  • XML Parser extension
  • Zend\Escaper component
  • Zend\Stdlib component
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
require_once 'bootstrap.php';

// Creating the new document...
$phpWord = new \PhpOffice\PhpWord\PhpWord();

/* Note: any element you append to a document must reside inside of a Section. */

// Adding an empty Section to the document...
$section = $phpWord->addSection();
// Adding Text element to the Section having font styled by default...
$section->addText(
    '"Learn from yesterday, live for today, hope for tomorrow. '
        . 'The important thing is not to stop questioning." '
        . '(Albert Einstein)'
);

// Saving the document as OOXML file...
$objWriter = \PhpOffice\PhpWord\IOFactory::createWriter($phpWord, 'Word2007');
$objWriter->save('helloWorld.docx');

前端发起ajax请求，后端生成excel并下载，同时需要在header头中，带上token验证信息，实现如下：

1
2
3
4
5
6
7
$filename = 'demo.xlsx';
$objWriter = \PHPExcel_IOFactory::createWriter($objectPHPExcel, 'Excel2007');
ob_start();
$objWriter->save("php://output");
$xlsData = ob_get_contents();
ob_end_clean();
return Api::success(['filename' => $filename, 'file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsData)]);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$('.download').click(function(){
var url = "http://xxxx.com/group/bi/export";
var params = {
    from_date: '2017-09-01',
    to_date: '2017-09-08',
    group_id: 1
    };
    $.ajax({
        type:'POST',
        url: url,
        data: params,
        beforeSend: function(request) {
            request.setRequestHeader("Authorization", "token信息，验证身份");
        },
        success: function(redata) {
            // 创建a标签，设置属性，并触发点击下载
            var $a = $("<a>");
            $a.attr("href", redata.data.file);
            $a.attr("download", redata.data.filename);
            $("body").append($a);
            $a[0].click();
            $a.remove();
        }
    });
});

PHP 操作 office 文档

vue使用FormData上传excel文件，使用blob下载文件

BLOB (binary large object) 二进制大对象

导入excel文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
inputCustomProduct(e){
                //声明一个FormDate对象
                let formData = new FormData();
                let file = e.target.files[0]
                //把文件信息放入对象中
                formData.append( "excelFile", file);
                // let params = new URLSearchParams(formData)     //当请求没有转换成formData时使用
                if(file.type == "application/vnd.ms-excel" || file.type == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"){
                    this.$ajax({
                        method: "post",
                        url: "",
                        data: formData,
                        headers: {
                            'Content-Type': 'multipart/form-data'
                        }
                    }).then( res => {
                        if(res.code === 200){
                            this.$message({
                                message: "导入成功",
                                type: "success"
                            })
                        }else {
                            this.$message({
                                message: "导入失败",
                                type: "error"
                            })
                        }
                    })
                }else {
                    this.$message({
                        message: "请选择excel文件！",
                        type: "error"
                    })
                }
                //此处必须将控制导入的input值进行置空，否则不会触发change事件，会导致同一个文件不能二次导入
                document.getElementById('up').value = null;
            },
导出Excel
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
outputCustomProduct() {
                this.$ajax({
                    method: "",
                    url: "",
                    params: {
                       
                    },
                    responseType: 'blob'
                }).then(res => {
                    console.log(res)
                    //调用成功，在html中创建一个a元素
                    let aTag = document.createElement('a');
                    //创建一个blob对象
                    let blob = new Blob([res], {
                        //Excel文件版本
                        type: "multipart/form-data;charset=utf-8",   //application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
                    });// 这个content是下载的文件内容，自己修改
                    aTag.download = '订单导入模板.xls';// 下载的文件名
                    aTag.href = window.URL.createObjectURL(blob);   //创建一个URL对象
                    aTag.click();
                    document.body.removeChild(aTag)
                    window.URL.revokeObjectURL(blob);          //释放URL对象
                })
            },

Vue Element UI 实现文件excel的上传及下载2种方式（文件流及a标签）

上传使用Element ui 里的 el-upload

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<el-form-item label="上传">
   <el-upload
     class="upload-demo"
     ref="upload"
     action="#"
     :limit="1"
     :http-request="uploadOk"
     :on-preview="handlePreview"
     :before-upload="beforeAvatarUpload"
     :on-remove="handleRemove"
     :file-list="fileList"
     :auto-upload="true">
     <el-button slot="trigger" size="small" type="primary">选取文件</el-button>
     <div slot="tip" class="el-upload__tip">只能上传xls/xlsx文件</div>
   </el-upload>
 </el-form-item>

action 必选参数，上传的地址
:limit 最大允许上传个数
:http-request 覆盖默认的上传行为，自定义上传
:on-preview 文件上传时的钩子
:before-upload 上传文件之前的钩子
:on-remove 文件列表移除文件时的钩子
:file-list 上传的文件列表
:auto-upload 是否在选取文件后立即进行上传

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 // 上传前验证
beforeAvatarUpload(file) {
  let Xls = file.name.split('.'); 
  if(Xls[1] === 'xls'||Xls[1] === 'xlsx'){
    return file
  }else {
    this.$message.error('上传文件只能是 xls/xlsx 格式!')
    return false
  }
},
handleRemove(file, fileList) {
  console.log(file, fileList);
},
handlePreview(file) {
  console.log(file);
},
// 上传成功
uploadOk(val){
  let fd = new FormData() // 上传文件必须为FormData 所以这边New FormData() ，把上传文件的File填进去。
  fd.append('upload_file',val.file)
  fd.append('dir_name','contract')
  // console.log(fd.upload_file)  错误
  // console.log(fd.get("upload_file")  正确
  this.$post('/upload/uploadFile', fd).then((result) => {
    if (result.code == 200) {
      this.$message({
        message: '上传成功',
        type: 'success'
      });
      // 接收excel文件地址
      this.addForm.addurl = result.data.file_url
    }else{
      this.message(result.msg)
    }
  })
},
下载

a标签方式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// 下载源文件
  handleDownload: function(index, row){
    if(row.contract_file_url == ''){
      this.$message({
        message: '没有源文件',
        type: 'warning'
      })
    }else{
      // 创建 a 标签 
      let link = document.createElement('a')
      // href 链接, row.contract_file_url 是从表格里拿到的下载文件地址
      link.setAttribute('href', row.contract_file_url)
      // 自执行点击事件
      link.click()
    }   
  },