buildadmin中header的导入功能

序言：根据buildadmin提供的模块数据导入管理进行改写，变成当前模块下进行当前表格导入

首先在控制器的index.vue文件中进行如下操作

目的：旨在先将header中的导入按钮显示出来

<!-- 表格顶部菜单 -->
<!-- 自定义按钮请使用插槽，甚至公共搜索也可以使用具名插槽渲染，参见文档 -->
<TableHeader
:buttons="['refresh', 'add', 'edit', 'delete', 'unfold', 'comSearch', 'quickSearch', 'columnDisplay']"
:quick-search-placeholder="t('Quick search placeholder', { fields: t('treetable.quick Search Fields') })"
>
<el-button v-blur class="table-header-operate" type="success" style="margin-left: 12px" @click="stick">
    <Icon color="#ffffff" name="el-icon-Upload" />
    <span class="table-header-operate-text">{{ t('treetable.import') }}</span>
</el-button>
</TableHeader>

第二步：点击导入显示出导入上传的弹窗

因为第一步中存在<@click="stick">点击事件

const stick = () => {
    baTable.table.extend!.showPreImport = true  //点击才显示导入弹窗
    baTable.form.loading = true                    //加载样式
    baTable.form.extend!.fileUploadStatus = 'wait'   //上传状态
    setTimeout(() => {
        baTable.form.loading = false                //定时任务：1s后关闭加载样式
    }, 1000)
    close()
}

第三步：弹窗页面完善，首先需要在index.vue注册，并且进行引入弹窗页面

//注册
import dialogCom from '/@/components/treetable/dialogCom.vue'    //导入弹窗
import preImportDialogs from '/@/components/treetable/preImportDialogs.vue' //导入后预览
/**
* 引入
* 建议放在<PopupForm />后
*/
<dialogCom />        
<preImportDialogs />

<template>
    <el-dialog class="ba-operate-dialog" v-model="baTable.table.extend!.showPreImport" width="50%">
        <template #header>
            <div class="title" v-drag="['.ba-operate-dialog', '.el-dialog__header']" v-zoom="'.ba-operate-dialog'">导入</div>
        </template>
        <el-scrollbar v-loading="baTable.form.loading" class="ba-table-form-scrollbar">
            <div
                class="ba-operate-form"
                :class="'ba-' + baTable.form.operate + '-form'"
                :style="'width: calc(100% - ' + baTable.form.labelWidth! / 2 + 'px)'"
            >
                <el-form
                    v-if="!baTable.form.loading"
                    ref="formRef"
                    @submit.prevent=""
                    @keyup.enter="baTable.onSubmit(formRef)"
                    :model="baTable.form.items"
                    label-position="right"
                    :label-width="baTable.form.labelWidth + 'px'"
                >
                    <el-form-item label="导入数据" prop="file">
                        <el-upload class="upload-xls" :show-file-list="false" accept=".xlsx,.xls" drag :auto-upload="false" @change="uploadXls">
                            <div v-if="baTable.form.extend!.fileUploadStatus == 'wait'" class="upload-file-box">
                                <Icon size="50px" color="#909399" name="el-icon-UploadFilled" />
                                <div class="el-upload__text">拖拽 .xls[x] 文件至此处 <em>或点击我上传</em></div>
                            </div>
                            <div v-if="baTable.form.extend!.fileUploadStatus == 'uploading'" class="upload-file-box">
                                <Icon size="50px" color="#ffffff" v-loading="true" name="el-icon-UploadFilled" />
                                <div class="el-upload__text">上传中...</div>
                            </div>
                            <div v-if="baTable.form.extend!.fileUploadStatus == 'success'" class="upload-file-box">
                                <Icon size="50px" color="#ffffff" v-loading="true" name="el-icon-UploadFilled" />
                                <div class="el-upload__text">文件上传成功，正在处理...</div>
                            </div>
                        </el-upload>
                    </el-form-item>
                    <el-form-item>
                        <el-alert title="提示" class="import-tips" type="success">
                            <p>1、导入数据内无`主键`字段或`主键留空`则可以使用主键自动递增</p>
                            <p>2、若数据表有设计`create_time`、`update_time`字段且导入数据内未设定这两个字段的值，则自动填充</p>
                            <p>
                                3、所有已设定值的导入数据，将原样导入，比如：`create_time`字段，数据表设计为时间戳则请导入时间戳，`status:0=隐藏,1=开启`，请导入`0`或`1`
                            </p>
                        </el-alert>
                    </el-form-item>
                </el-form>
            </div>
        </el-scrollbar>
        <template #footer>
            <div :style="'width: calc(100% - ' + baTable.form.labelWidth! / 1.8 + 'px)'">
                <el-button @click="baTable.table.extend!.showPreImport = false">{{ t('Cancel') }}</el-button>
            </div>
        </template>
    </el-dialog>
</template>

<script setup lang="ts">
import {ref, inject, watch } from 'vue'
import type baTableClass from '/@/utils/baTable'
import type { ElForm, UploadFile } from 'element-plus'
import { useI18n } from 'vue-i18n'
import { handleXls } from '/@/api/backend/treetable'
import { fileUpload } from '/@/api/common'
import NProgress from 'nprogress'
import 'nprogress/nprogress.css'

const formRef = ref<InstanceType<typeof ElForm>>()
const baTable = inject('baTable') as baTableClass
const { t } = useI18n()


const uploadXls = (file: UploadFile) => {
    if (!file || !file.raw) return

    NProgress.configure({ showSpinner: false })
    NProgress.start()

    baTable.form.extend!.fileUploadStatus = 'uploading'

    let fd = new FormData()
    fd.append('file', file.raw!)
    console.log(1111)
    fileUpload(fd, {}, true, {
        onUploadProgress: (evt) => {
            NProgress.set(evt.progress!)
        },
    })
        .then((res) => {
            console.log(2222)
            if (res.code == 1) {
                handleXls('treetable', res.data.file.url)
                    .then((handleRes) => {                  
                        baTable.table.extend!.showPreImports = true
                        baTable.table.extend!.fields = handleRes.data.fields
                        baTable.table.extend!.rowCount = handleRes.data.rowCount
                        baTable.table.extend!.data = handleRes.data.data
                        baTable.table.extend!.file_url = res.data.file.url
                        baTable.form.extend!.fileUploadStatus = 'success'
                    })
                    .catch(() => {
                        baTable.form.extend!.fileUploadStatus = 'wait'
                    })
            }
        })
        .catch(() => {
            baTable.form.extend!.fileUploadStatus = 'wait'
        })
        .finally(() => {
            NProgress.done()
        })
}


watch(
    () => baTable.table.extend!.showPreImports,
    (newVal) => {
        if (newVal === false) {
            baTable.form.extend!.fileUploadStatus = 'wait'
        }
    }
)
</script>

<style scoped lang="scss">
.template-text-success {
    color: var(--el-color-success);
    cursor: pointer;
    user-select: none;
}
.template-text-info {
    color: var(--el-color-info);
}
.upload-xls {
    width: 100%;
}
.import-tips {
    line-height: 16px;
}
</style>

<template>
    <div>
        <el-dialog title="导入预览" width="80%" v-model="baTable.table.extend!.showPreImports" class="pre-import-dialog" top="5vh">
            <el-alert
                :title="'总计 ' + baTable.table.extend!.rowCount + ' 条数据' + (baTable.table.extend!.rowCount > 101 ? '，请目检导入数据的前面50条及最后50条~':'。')"
                class="import-tips"
                :closable="false"
                type="success"
            ></el-alert>
            <el-table :data="baTable.table.extend!.data" style="width: 100%" height="600">
                <el-table-column
                    v-for="(item,idx) in baTable.table.extend!.fields"
                    :key="idx"
                    :label="item.COLUMN_COMMENT ? item.COLUMN_COMMENT : ''"
                >
                    <el-table-column :prop="item.COLUMN_NAME" :label="item.COLUMN_NAME" />
                </el-table-column>
            </el-table>
            <template #footer>
                <div :style="'width: calc(100% - ' + baTable.form.labelWidth! / 1.8 + 'px)'">
                    <el-button @click="baTable.table.extend!.showPreImports = false">{{ $t('Cancel') }}</el-button>
                    <el-button v-blur :loading="state.importBtnLoading" type="primary" @click="onImport">导入</el-button>
                </div>
            </template>
        </el-dialog>
    </div>
</template>

<script setup lang="ts">
import { inject, reactive } from 'vue'
import type baTableClass from '/@/utils/baTable'
import { importXls } from '/@/api/backend/treetable'

const baTable = inject('baTable') as baTableClass

const state = reactive({
    importBtnLoading: false,
})

const onImport = () => {
    state.importBtnLoading = true
    importXls('treetable', baTable.table.extend!.file_url)
        .then(() => {
            baTable.table.extend!.showPreImports = false
            baTable.toggleForm()
            baTable.onTableHeaderAction('refresh', {})
        })
        .finally(() => {
            state.importBtnLoading = false
        })
}
</script>

<style scoped lang="scss">
:deep(.pre-import-dialog) .el-dialog__body {
    padding: 10px 20px;
}
.import-tips {
    margin-bottom: 10px;
}
</style>

 创建当前控制的treetable.ts文件，供页面进行调用

import createAxios, { getUrl } from '/@/utils/axios'
const controllerUrl = '/admin/treetable/'

export function handleXls(table: string, url: string) {
    return createAxios({
        url: controllerUrl + 'handleXls',
        method: 'get',
        params: {
            file: url,
            table: table,
        },
    })
}

export function importXls(table: string, url: string) {
    return createAxios(
        {
            url: controllerUrl + 'handleXls',
            method: 'post',
            params: {
                file: url,
                table: table,
            },
        },
        {
            showSuccessMessage: true,
        }
    )
}

最后：重点控制器中编写关于导入的方法

 

/**
     * 导入方法
     * @return void
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function handleXls()
    {
        $file  = $this->request->request('file', '');
        $table = $this->request->request('table', '');
        if (!$table) {
            $this->error('请选择数据表！');
        }
        if (!$file) {
            $this->error('请上传导入数据！');
        }
        

        // 读取xls文件内容
        $filePath    = Filesystem::fsFit(public_path() . $file);
        $spreadsheet = IOFactory::load($filePath);
        $sheet       = $spreadsheet->getSheet(0);
        $rowCount    = $sheet->getHighestRow();
        $data        = $sheet->toArray();
        $fields      = get_table_fields($table);
        


        // 寻找字段对应数据所在的 key 值
        // 字段名、字段备注、字段名(字段备注)、字段名(字段备注+字段字典)
        $importKeys = [];
        foreach ($fields as $key => $field) {
            $title    = $field['COLUMN_NAME'] . ($field['COLUMN_COMMENT'] ? '(' . $field['COLUMN_COMMENT'] . ')' : '');
            $titleKey = array_search($title, $data[0]);
            if ($titleKey !== false) {
                $importKeys[$field['COLUMN_NAME']] = $titleKey;
                continue;
            }

            $nameKey = array_search($field['COLUMN_NAME'], $data[0]);
            if ($nameKey !== false) {
                $importKeys[$field['COLUMN_NAME']] = $nameKey;
                continue;
            }

            if ($field['COLUMN_COMMENT']) {
                $commentKey = array_search($field['COLUMN_COMMENT'], $data[0]);
                if ($commentKey !== false) {
                    $importKeys[$field['COLUMN_NAME']] = $commentKey;
                    continue;
                }

                if (strpos($field['COLUMN_COMMENT'], ':')) {
                    $comment      = explode(':', $field['COLUMN_COMMENT']);
                    $cleanComment = $comment[0];
                    $commentKey   = array_search($cleanComment, $data[0]);
                    if ($commentKey !== false) {
                        $importKeys[$field['COLUMN_NAME']] = $commentKey;
                        continue;
                    }

                    $titleCleanComment = $field['COLUMN_NAME'] . '(' . $cleanComment . ')';
                    $commentKey        = array_search($titleCleanComment, $data[0]);
                    if ($commentKey !== false) {
                        $importKeys[$field['COLUMN_NAME']] = $commentKey;
                    }
                    $fields[$key]['COLUMN_COMMENT'] = $cleanComment;
                }
            }
        }

        $importPre = [];
        foreach ($data as $key => $item) {
            if ($key == 0) continue;
            $importPreItem = [];
            foreach ($importKeys as $importKey => $importValueKey) {
                $importPreItem[$importKey] = $item[$importValueKey];
            }
            $importPre[] = $importPreItem;
        }
        
        if ($this->request->isPost()) {
            // 导入到表
            $nowTime    = time();
            $nowYmdHis  = date('Y-m-d H:i:s');
            $timeFields = ['createtime', 'create_time', 'updatetime', 'update_time'];
            foreach ($importPre as &$item) {
                foreach ($timeFields as $timeField) {
                    if (array_key_exists($timeField, $fields) && (!isset($item[$timeField]) || !$item[$timeField])) {
                        if ($fields[$timeField]['DATA_TYPE'] == 'int' || $fields[$timeField]['DATA_TYPE'] == 'bigint') {
                            $item[$timeField] = $nowTime;
                        } elseif ($fields[$timeField]['DATA_TYPE'] == 'datetime') {
                            $item[$timeField] = $nowYmdHis;
                        }
                    }
                }
            }
            Db::startTrans();
            $res = 0;
            try {
                $res = Db::name($table)->strict(false)->limit(500)->insertAll($importPre);
                // dump(1111);
                Db::name('dataimport')->insert([
                    'data_table'             => $table,
                    'admin_id'               => $this->auth->id,
                    'file'                   => $file,
                    'records'                => $rowCount - 1,
                    'import_success_records' => $res,
                    'radio'                  => 'import',
                    'create_time'            => $nowTime,
                ]);
                Db::commit();
                @unlink($filePath);
            } catch (Throwable $e) {
                Db::rollback();
                $this->error($e->getMessage());
            }
            $this->success('总计' . ($rowCount - 1) . '行数据，成功导入' . $res . '条！', [
                'data' => $importPre,
            ]);
        }

        if ($rowCount > 101) {
            $importPre = array_merge(array_slice($importPre, 0, 50), array_slice($importPre, $rowCount - 51, $rowCount));
        }

        $this->success('', [
            'fields'   => $fields,
            'rowCount' => ($rowCount - 1),
            'data'     => $importPre,
        ]);
    }