历史版本

1.0.0

最初版本

1.0.0-test

1.新增和编辑模板时不同操作系统下路径的斜杠问题

1.0.0.1-test

1.导入数据超过100条可能出发入库时主键冲突的以及锁表问题

导入功能

说明

导入组件是一款用于提取 Excel 表格内容存储到临时表，并且通过用户设置的入正式库的 API，将数据存储到正式库的工具组件。省去了开发人员用于文件上传，读取，数据验证，数据入临时库的过程。开发人员只需要设置数据源，模版信息，以及 Excel 表格中的列与数据库中表的对应关系，即可。在实际使用过程中，运维人员可以修改模版中的单元格描述等达到适用于不同公司使用不同的模版，实现快速复用。客户在导入功能时，只需选择相应的模版文件，上传系统，即可完成整个导入流程。

初始化

初始化附录里面的数据库脚本

直接复制对应业务系统数据库类型的脚本执行

模板新增

1. 访问地址：新增模板 (opens new window)

使用

后端引用及配置

1. application.properties 配置

spring.datasource.url: jdbc:mysql://192.168.100.105:3308/ECI_TG?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=CONVERT_TO_NULL
后面增加`&rewriteBatchedStatements=true`利于导入的批处理操作

//静态资源路径
spring
	mvc:
		static-path-pattern: /files/**
  	resources:
      static-locations:
        - file:${tgExcel.excel-dir-path}

//排除token
project:
  token:
    exclude: /fileInfo/chunk,/UEditor/**,/files/**

//与spring同级
tgExcel:
	  #框架接口地址
	  serverUrl: http://localhost:9527
	  #后端接口地址是否允许跨域
	  #false:无法跨域访问  true: 可以跨域访问  如果配置网关需要设置为false,如果通过IP：端口访问设置为true
	  openCors: true
	  #excel文件夹
	  excel-dir-path: C:\TgExcel
	  #每次读取excel的行数
	  excel-read-limit: 1000

2. POM.xml 的引用,dependencies 节点下增加如下代码

<dependency>
		<groupId>com.eci</groupId>
		<artifactId>tiangong-excel</artifactId>
		<version>1.0.0</version>
	</dependency>

前端配置

1. window.website 中增加如下代码

serverConfig: {
        importConfig: {
            server: "http://localhost:9012", //替换成当前服务地址
        },
    },

2.index.html 中增加如下代码

<link rel='stylesheet' href='/tiangongPublic/lib/luckysheet/pluginsCss.css' />
<link rel='stylesheet' href='/tiangongPublic/lib/luckysheet/plugins.css' />
<link rel='stylesheet' href='/tiangongPublic/lib/luckysheet/luckysheet.css' />
<link rel='stylesheet' href='/tiangongPublic/lib/luckysheet/iconfont.css' />
<script  src="/tiangongPublic/lib/luckysheet/plugin.js" ></script>
<script  src="/tiangongPublic/lib/luckysheet/luckysheet.umd.js"></script>
<script  type="module" src="/tiangongPublic/config/config.js"></script>

前端方法调用

function importTest() {
	  let tempUploadData = {
		templateId: "1401500083920896", //导入模板的ID
		uploadModel: 2,	//导入模式，1-极速模式，2-全文模式
		companyCode: "123456789", //企业代码
		dialogTitle: "列表模板导入测试",	//导入弹窗标题
		uploadParam: "bizNo=qyq",	//其他参数
	  };
	  page.tgImport(tempUploadData);
	}

附录

初始化表结构, 如果已有则无需新建, 这里只提供mysql, 其他数据库请自行查看

数据映射 code-name

主表 (opens new window) | 明细表 (opens new window)

达梦

表初始化下载地址 (opens new window) | 导入前置表初始化下载地址 (opens new window)

人大金仓

表初始化下载地址 (opens new window) | 导入前置表初始化下载地址 (opens new window)

Oracle

表初始化下载地址 (opens new window) | 导入前置表初始化下载地址 (opens new window)

SQLServer

表初始化下载地址 (opens new window) | 导入前置表初始化下载地址 (opens new window)

MySQL

表初始化下载地址 (opens new window) | 导入前置表初始化下载地址 (opens new window)

导入前置表需要新建的表字段-mysql

ALTER TABLE %s
ADD COLUMN `tg_batch_no` bigint(20) NULL DEFAULT 0 COMMENT 'tg批次号',
ADD COLUMN `tg_main_data_id` bigint(20) NULL DEFAULT 0 COMMENT 'tg主id' AFTER `tg_batch_no`,
ADD COLUMN `tg_template_log_id` bigint(20) NULL DEFAULT 0 COMMENT 'tg记录id' AFTER `tg_main_data_id`,
ADD COLUMN `tg_contact_data_id` bigint(20) NULL DEFAULT 0 COMMENT 'tg关联id' AFTER `tg_template_log_id`,
ADD COLUMN `tg_insert_status` int(11) NULL DEFAULT 0 COMMENT 'tg入库状态0-待处理1-入库成功2-入库失败 -1处理中' AFTER `tg_contact_data_id`,
ADD COLUMN `tg_insert_remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'tg入库结果' AFTER `tg_insert_status`,
ADD INDEX `idx_bi`(`tg_batch_no` ASC, `tg_contact_data_id` ASC, `tg_insert_status` ASC) USING BTREE;

导入配置表信息-mysql

表名	说明
tg_excel_datasource	数据源管理
tg_excel_template	模版
tg_excel_template_log	上传记录
tg_excel_template_log_detail	上传记录明细
tg_excel_template_company	企业模版关系
tg_excel_template_use	使用记录
tg_excel_table	模版表结构
tg_excel_table_column	表字段
tg_excel_field_mapping	字段映射
tg_excel_field_mapping_detail	字段映射详情表
tg_excel_import_0	上传内容-1

-- ECI_TG.tg_excel_datasource definition
DROP TABLE IF EXISTS `tg_excel_datasource`;

CREATE TABLE `tg_excel_datasource` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`db_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '数据库类型',
`dept_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '部门名称',
`project_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '系统名称',
`conn_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '连接url',
`user_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
`user_password` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '密码',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`status` int(1) DEFAULT '0' COMMENT '状态0-无效1-有效',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫-excel组件-数据源管理';

-- ECI_TG.tg_excel_template definition
DROP TABLE IF EXISTS `tg_excel_template`;

CREATE TABLE `tg_excel_template` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`datasource_id` bigint(20) DEFAULT NULL COMMENT '数据源id',
`module_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '模块名称',
`template_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '模版名称',
`template_remark` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '备注',
`template_type` int(1) DEFAULT '1' COMMENT '模版类型1-导入2-导出',
`template_is_complete` int(1) DEFAULT '0' COMMENT '事务性导入1-是0-否',
`template_insert_api` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '入库api',
`template_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '模版配置json',
`template_json_origin` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '模版配置json_前端',
`template_table_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '模版配置表结构json',
`template_file_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '模版文件储存地址',
`template_image_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '模版缩略图地址',
`company_code_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '适用公司编码集合',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_user_id` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人id',
`create_user_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人名称',
`create_company_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建公司id',
`create_company_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建公司名称',
`update_user_id` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新人id',
`update_user_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新人名称',
`update_company_id` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新公司id',
`update_company_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新公司名称',
`data_flag` int(1) DEFAULT '1' COMMENT '状态1-正常0-删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫-excel组件-模版';


-- ECI_TG.tg_excel_template_log definition
DROP TABLE IF EXISTS `tg_excel_template_log`;

CREATE TABLE `tg_excel_template_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`datasource_id` bigint(20) DEFAULT NULL COMMENT '数据源id',
`template_id` bigint(20) DEFAULT NULL COMMENT '模版id',
`template_scene` int(2) DEFAULT NULL COMMENT '模版场景1-普通模版2-区域模版 3-列表模版',
`template_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '模版配置json',
`template_table_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '模版配置表结构json',
`upload_file_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '上传文件名',
`upload_file_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '上传文件地址',
`down_file_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '错误文件地址',
`upload_model` int(1) DEFAULT NULL COMMENT '上传模式1-极速模式2-全文模式',
`upload_param` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '上传附带参数',
`upload_token` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '上传附带token',
`batch_no` bigint(20) DEFAULT NULL COMMENT '批次号',
`total_row_num` bigint(20) DEFAULT '0' COMMENT '总行数',
`total_row_num_success` bigint(20) DEFAULT '0' COMMENT '成功行数',
`total_row_num_fail` bigint(20) DEFAULT '0' COMMENT '失败行数',
`total_row_num_validate_success` bigint(20) DEFAULT NULL COMMENT '验证成功数',
`total_seconds` int(11) DEFAULT '0' COMMENT '上传耗时/秒',
`deal_step` int(11) DEFAULT NULL COMMENT '处理步骤0-未处理 1-读取文件 2-数据处理 3-数据验证 4-数据入库 5-调用API 6-完成',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`uploaded_time` datetime DEFAULT NULL COMMENT '上传完成时间',
`validate_time` datetime DEFAULT NULL COMMENT '数据验证完成时间',
`finish_time` datetime DEFAULT NULL COMMENT '入库完成时间',
`company_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '上传公司code',
`company_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '上传公司name',
`deal_type` int(1) DEFAULT NULL COMMENT '处理方式1-立即处理2-任务处理',
`deal_status` int(2) DEFAULT '1' COMMENT '处理状态1-待处理2-处理中 3-已完成4-已完成有失败5-上传失败 -1任务挂起',
`deal_remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '处理结果',
`deal_log` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '处理过程耗时记录',
`data_flag` int(1) DEFAULT '1' COMMENT '状态1-正常0-删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫-excel组件-上传记录';

-- ECI_TG.tg_excel_template_log_detail definition
DROP TABLE IF EXISTS `tg_excel_template_log_detail`;

CREATE TABLE `tg_excel_template_log_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`datasource_id` bigint(20) DEFAULT NULL COMMENT '数据源id',
`template_id` bigint(20) DEFAULT NULL COMMENT '模版id',
`template_log_id` bigint(20) DEFAULT NULL COMMENT '上传记录id',
`sheet_index` int(11) DEFAULT '0' COMMENT 'sheet序号',
`sheet_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'sheet名称',
`template_scene` int(2) DEFAULT NULL COMMENT '模版场景1-普通模版2-区域模版 3-列表模版',
`import_type` int(2) DEFAULT NULL COMMENT '导入类别1-全部成功在导入2-部分成功也导入',
`template_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '模版配置json',
`template_main_table` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '模版主表',
`template_table_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '模版配置表结构json',
`insert_api` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '入库api',
`upload_model` int(1) DEFAULT NULL COMMENT '上传模式1-极速模式2-全文模式',
`batch_no` bigint(20) DEFAULT NULL COMMENT '批次号',
`total_row_num` bigint(20) DEFAULT '0' COMMENT '总行数',
`total_row_num_success` bigint(20) DEFAULT '0' COMMENT '成功行数',
`total_row_num_fail` bigint(20) DEFAULT '0' COMMENT '失败行数',
`total_row_num_validate_success` bigint(20) DEFAULT NULL COMMENT '验证成功数',
`total_seconds` int(11) DEFAULT '0' COMMENT '上传耗时/秒',
`deal_step` int(11) DEFAULT NULL COMMENT '处理步骤0-未处理 1-读取文件 2-数据处理 3-数据验证 4-数据入库 5-调用API 6-完成',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`uploaded_time` datetime DEFAULT NULL COMMENT '上传完成时间',
`validate_time` datetime DEFAULT NULL COMMENT '数据验证完成时间',
`finish_time` datetime DEFAULT NULL COMMENT '入库完成时间',
`deal_status` int(2) DEFAULT '1' COMMENT '处理状态1-待处理2-处理中 3-已完成4-已完成有失败5-上传失败 -1任务挂起',
`deal_remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '处理结果',
`deal_log` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '处理过程耗时记录',
`data_flag` int(1) DEFAULT '1' COMMENT '状态1-正常0-删除',
`valid_api` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '逻辑验证api',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1385229718978561 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫-excel组件-上传记录';

-- ECI_TG.tg_excel_template_company definition
DROP TABLE IF EXISTS `tg_excel_template_company`;

CREATE TABLE `tg_excel_template_company` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`template_id` bigint(20) DEFAULT NULL COMMENT '模版id',
`company_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '公司编码',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫-excel组件-企业模版关系';

-- ECI_TG.tg_excel_template_use definition
DROP TABLE IF EXISTS `tg_excel_template_use`;

CREATE TABLE `tg_excel_template_use` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`template_id` bigint(20) DEFAULT NULL COMMENT '模版id',
`template_log_id` bigint(20) DEFAULT NULL COMMENT '模版上传id',
`company_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '公司编码',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '使用时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫-excel组件-使用记录';

-- ECI_TG.tg_excel_table definition
DROP TABLE IF EXISTS `tg_excel_table`;

CREATE TABLE `tg_excel_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`parent_id` bigint(20) DEFAULT NULL COMMENT '父表id',
`database_id` bigint(20) DEFAULT NULL COMMENT '数据源id',
`template_id` bigint(20) DEFAULT NULL COMMENT '模版id',
`table_name` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '表名称-模版名称',
`table_code` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '表名称-表名',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`data_flag` int(1) DEFAULT '1' COMMENT '有效性1-是0-否',
PRIMARY KEY (`id`),
KEY `idx_tid` (`template_id`, `data_flag`) USING BTREE,
KEY `idx_parent` (`parent_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫-excel组件-模版表结构';

-- ECI_TG.tg_excel_table_column definition
DROP TABLE IF EXISTS `tg_excel_table_column`;

CREATE TABLE `tg_excel_table_column` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`table_id` bigint(20) DEFAULT NULL COMMENT '表id',
`column_name` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列名-中文',
`column_code` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列名',
`column_col_code` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列存储位置',
`column_type` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '字段类型',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`data_flag` int(1) DEFAULT '1' COMMENT '有效性1-有效0-无效',
`column_remark` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '批注',
`column_location` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '坐标',
`column_get_model` int(1) DEFAULT NULL COMMENT '模版导入字段取值类型-1-单个字段 2-组合字段主字段 3-组合字段从字段',
`column_length_min` int(11) DEFAULT '0' COMMENT '长度最小值',
`column_length_max` int(11) DEFAULT '0' COMMENT '长度最大值',
`column_is_nullable` int(1) DEFAULT '0' COMMENT '是否可以为空1-是0-否',
`key_position` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列标题坐标',
`cell_span` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '单元格合并信息',
PRIMARY KEY (`id`),
KEY `idx_table` (`table_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫-excel组件-表字段';

-- ECI_TG.tg_excel_field_mapping definition
DROP TABLE IF EXISTS `tg_excel_field_mapping`;

CREATE TABLE `tg_excel_field_mapping` (
`id` bigint(20) NOT NULL COMMENT '主键',
`mapping_type` int(1) DEFAULT NULL COMMENT '映射类型 0.字段映射 1.放大镜',
`code_type` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '参数所属',
`user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户id',
`user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名称',
`company_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '企业代码',
`company_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '企业名称',
`code_title_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '参数类型字段代码',
`code_title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '参数类型',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫- excel组件-字段映射';

-- ECI_TG.tg_excel_field_mapping_detail definition
DROP TABLE IF EXISTS `tg_excel_field_mapping_detail`;

CREATE TABLE `tg_excel_field_mapping_detail` (
`id` bigint(20) NOT NULL COMMENT '主键',
`parent_id` bigint(20) DEFAULT NULL COMMENT '父级id',
`code` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '代码',
`name` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称',
`remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫-excel组件-字段映射详情表';

-- ECI_TG.tg_excel_import_0 definition
DROP TABLE IF EXISTS `tg_excel_import_0`;

CREATE TABLE `tg_excel_import_0` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`template_id` bigint(20) DEFAULT '0' COMMENT '模版id',
`template_log_id` bigint(20) DEFAULT '0' COMMENT '上传记录id',
`template_log_detail_id` bigint(20) DEFAULT '0' COMMENT '上传记录明细id',
`table_id` bigint(20) DEFAULT NULL COMMENT '表名',
`batch_no` bigint(20) DEFAULT NULL COMMENT '批次号',
`row_index` int(11) DEFAULT '0' COMMENT '行号',
`main_data_id` bigint(20) DEFAULT '0' COMMENT '主表id 数据对应',
`contact_data_id` bigint(20) DEFAULT '0' COMMENT '关联id 主从对应',
`validate_result` int(1) DEFAULT NULL COMMENT '数据验证结果1-验证通过2-验证失败',
`validate_remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '数据验证备注',
`insert_status` int(1) DEFAULT NULL COMMENT '入库状态0-待处理1-调用通过2-调用失败-1处理中',
`insert_remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '入库备注',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`col_1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列1',
`col_2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列2',
`col_3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列3',
`col_4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列4',
`col_5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列5',
`col_6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列6',
`col_7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列7',
`col_8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列8',
`col_9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列9',
`col_10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列10',
`col_11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列11',
`col_12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列12',
`col_13` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列13',
`col_14` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列14',
`col_15` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列15',
`col_16` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列16',
`col_17` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列17',
`col_18` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列18',
`col_19` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列19',
`col_20` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列20',
`col_21` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列21',
`col_22` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列22',
`col_23` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列23',
`col_24` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列24',
`col_25` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列25',
`col_26` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列26',
`col_27` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列27',
`col_28` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列28',
`col_29` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列29',
`col_30` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列30',
`col_31` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列31',
`col_32` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列32',
`col_33` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列33',
`col_34` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列34',
`col_35` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列35',
`col_36` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列36',
`col_37` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列37',
`col_38` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列38',
`col_39` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列39',
`col_40` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列40',
`col_41` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列41',
`col_42` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列42',
`col_43` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列43',
`col_44` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列44',
`col_45` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列45',
`col_46` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列46',
`col_47` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列47',
`col_48` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列48',
`col_49` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列49',
`col_50` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '列50',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_ltc` (`template_log_id`,`table_id`,`contact_data_id`) USING BTREE,
KEY `idx_ldid_instatus`(`template_log_detail_id`, `insert_status`) USING BTREE,
KEY `idx_main` (`main_data_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1384806758301738 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='天宫-excel组件-上传内容-表1';