ThinkCMF结合PhpOffile 实现导入导出

安装PhpOffile

composer require phpoffice/phpspreadsheet

导出excel文件到本地

/**
 * 导出文件到本地
 */
public function output(){
    $model = new GraveModel();
    $data = $model->field('id,sn,open_name,mobile,status,dead_name1,dead_name2,type,temp_id')->all();

    $newData = [];
    foreach ($data as $key=>$val){
        $newData[] = [
            'id' => $val['id'],
            'sn' => $val['sn'],
            'type' => $val['type'] == 1 ? '单墓' : '双墓',
            'open_name' => $val['open_name'],
            'mobile' => $val['mobile'],
            'dead_name' => $val['dead_name1'].' '.$val['dead_name2'],
            'status' => $val['status'] == 1 ? '已领取' : '未领取'
        ];
    }
   
    $title = ['id','墓穴编号','类型','墓穴开通者','手机号码','亡者姓名','状态'];

    $filename = '墓穴数据';

    FileProcess::downFile($title,$newData,$filename);
}

导入Excel数据到数据库

/**
* 导入墓穴数据【编号，亡者名称】
*/
public function import(){
   //先把文件上传到本地
   $file = $this->request->file('myFile');
   $info = $file->move('./upload/xsl');
   if(!$info){
       $this->error($file->getError());
   }

   $fileurl =  WEB_ROOT.'upload/xsl/'.$info->getSaveName();
   $data = FileProcess::toArray($fileurl);

   //检查编号有没有重复的，有的话就返回错误
   $snArr = [];
   $dataArr = []; //数据库的数据
   foreach ($data as $key=>$val){
       $snArr[] = $val[0];
       $dataArr[] = [
           'sn' => $val[0],
           'dead_name1' => $val[1]
       ];
   }

   $res = GraveModel::where('sn','in',$snArr)->find();
   if($res){
       $this->error('有重复的编号，不可导入');
   }

   //导入所有的数据到数据库
   $res = GraveModel::insertAll($dataArr);

   if(!$res){
       $this->error('导入失败');
   }
   $this->success('导入成功');
}

FileProcess 类

<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2020/8/10
 * Time: 11:01
 */

namespace app\portal\service;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class FileProcess
{
    /**
     * 文件 转 Array
     * @param string $filePath 文件路径
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public static function toArray($filePath)
    {

        $spreadsheet = IOFactory::load($filePath);// 载入excel表格

        $worksheet = $spreadsheet->getActiveSheet();

        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestColumn(); // 总列数

        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);

        $data = [];
        for ($row = 2; $row <= $highestRow; ++$row) { // 从第二行开始
            $row_data = [];
            for ($column = 1; $column <= $highestColumnIndex; $column++) {
                $row_data[] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
            }
            $data[] = $row_data;
        }

        return $data;
    }

    /**
     * Array 转 文件
     * @param array $title 标题 格式['title1', 'title2']
     * @param array $data 导出数据 ['A1' => '***', 'B2' => '***']
     * @param string $fileName 导出文件名称
     * @param array $options 配置 [ 'fileType' => 'xls',    导出文件后缀 默认 xls
     *                             'savePath' => '/web'    自定义保存地址
     *                           ]
     * @return bool
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function toFile($title, $data, $fileName, $options = [])
    {

        /** 设置转义格式 */
        if (isset($options['fileType']) && !in_array($options['fileType'], ['xls', 'xlsx', 'ods', 'csv', 'html', 'tcpdf', 'dompdf', 'mpdf'])) {
            return false;
        }

        $spreadsheet = new Spreadsheet();

        $worksheet = $spreadsheet->getActiveSheet();
        //设置标题名称
        $worksheet->setTitle($fileName);

        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++;
            }
            $row++;
        }

        /** 设置文件后缀名 */
        if (!isset($options['fileType'])) {
            $fileName = $fileName . '.xls';
        } else {
            $fileName = $fileName . '.' . $options['fileType'];
        }

        /** 设置文件路径 */
        if (!isset($options['savePath'])) {
            //$savePath = \Yii::$app->basePath . '/web/' . $fileName;
        } else {
            $savePath = $options['savePath'] . $fileName;
        }

        /** @var string $writerType 转义成PhpSpreadsheet能识别的后缀 */
        $writerType = ucfirst($options['fileType']);

        $writer = IOFactory::createWriter($spreadsheet, $writerType);

        $writer->save($savePath);

        /* 释放内存 */
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();

        return true;
    }

    /**
     * 导出文件下载到本地
     * @param $title ['title1','title2']
     * @param $data array 对应的数据
     * @param $filename string 导出的文件名
     */
    public static function downFile($title,$data,$filename){

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //设置工作表标题名称
        $sheet->setTitle($filename);
        $sheet->getDefaultColumnDimension()->setWidth(18);
        $sheet->getDefaultRowDimension()->setRowHeight(25);
        //设置单元格内容 表头
        foreach($title as $key => $value){
            $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
        }
        $row = 2; //从第二行开始
        //设置单元格内容
        foreach ($data as $item) {
            $column = 1;
            foreach ($item as $value) {
                $sheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }
        $fileType = 'Xlsx';

        // 输出到浏览器下载
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$filename.'.'.$fileType.'');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
    }

    /**
     * 导入文件插入数据库
     * @param string $tableName 表名
     * @param array $filed 字段名 [ 'create_id', 'create_time' ]
     * @param string $filePath 文件路径
     * @return bool|int
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \yii\db\Exception
     */
    public static function toSql($tableName, $filed, $filePath)
    {
        $params = self::toArray($filePath);

        //$result = \Yii::$app->db->createCommand()->batchInsert($tableName, $filed, $params)->execute();

        //return $result;
    }
}

前端文件

//导入
<input type="file" id="xslFile" style="display:none">
<button type="button" class="btn btn-info btn-sm" onclick="F_Open_dialog()">导入</button>

<script>
    function F_Open_dialog()
    {
        document.getElementById("xslFile").click();
    }

    $(function(){
        $('#xslFile').change(function(res){
            var formData = new FormData();
            formData.append('myFile', $('#xslFile')[0].files[0]);
            $.ajax({
                url:"{:url('import')}",
                type:"POST",
                data:formData,
                processData: false,
                contentType: false,
                success:function(res){
                    if(res.code == 1){
                        noty({
                            text: "导入成功",
                            type: 'success',
                            layout: 'center',
                            callback: {
                                afterClose: function () {
                                    reloadPage(window);
                                }
                            }
                        });
                    }else{
                        noty({
                            text: res.msg,
                            type: 'error',
                            layout: 'center',
                            callback: {
                                afterClose: function () {
                                    reloadPage(window);
                                }
                            }
                        });
                    }
                },
                fail:function(res){
                    noty({
                        text: '导入失败',
                        type: 'error',
                        layout: 'center',
                        callback: {
                            afterClose: function () {
                                reloadPage(window);
                            }
                        }
                    });
                }
            });
        })
    })
</script>


//导出
<a class="btn btn-default btn-sm " href="{:url('output')}" data-subcheck="true">导出
</a>