jeebiz-cloud-extras-imexport（导入导出）
导入导出组件（jeebiz-cloud-extras-imexport）是基于Fastxls组件实现的业务数据导入导出组件，基于该组件可快速的实现单表、多表的数据导入或者导出！
组件包结构
表结果设计
表结构SQL
-- ----------------------------
-- Table structure for SYS_EXTRAS_IMEXPORT_TABLES
-- ----------------------------
DROP TABLE SYS_EXTRAS_IMEXPORT_TABLES;
CREATE TABLE SYS_EXTRAS_IMEXPORT_TABLES (
TAB_ID VARCHAR2(32) default sys_guid() NOT NULL,
TAB_CODE VARCHAR2(100) NOT NULL,
TAB_NAME VARCHAR2(500) NOT NULL,
TAB_LABEL VARCHAR2(500) NOT NULL,
TAB_INTRO CLOB,
TAB_FK VARCHAR2(32),
TAB_FK_COL VARCHAR2(32),
TAB_STATUS NUMBER(1) default 1 NOT NULL,
TAB_ORDER NUMBER(3) default 1 NOT NULL,
TIME24 VARCHAR2(34) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
CONSTRAINT IMEXPORT_TABLE_UK UNIQUE(TAB_CODE),
CONSTRAINT IMEXPORT_TABLE_PK PRIMARY KEY(TAB_ID)
);
COMMENT ON TABLE SYS_EXTRAS_IMEXPORT_TABLES IS '表信息配置表';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TABLES.TAB_ID IS '表信息ID';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TABLES.TAB_CODE IS '表编号（用于时的识别编码）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TABLES.TAB_NAME IS '表名称（实体表名称）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TABLES.TAB_LABEL IS '表名称（页面显示名称）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TABLES.TAB_INTRO IS '表简介信息';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TABLES.TAB_FK IS '表外键表编号（用于时的识别编码）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TABLES.TAB_FK_COL IS '表外字段名称（实体表字段列名称）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TABLES.TAB_STATUS is '可用状态:（0:不可用|1：可用）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TABLES.TAB_ORDER IS '表排序';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TABLES.TIME24 is '数据添加时间';
-- ----------------------------
-- Table structure for SYS_EXTRAS_IMEXPORT_COLUMNS
-- ----------------------------
DROP TABLE SYS_EXTRAS_IMEXPORT_COLUMNS;
CREATE TABLE SYS_EXTRAS_IMEXPORT_COLUMNS (
COL_ID VARCHAR2(32) default sys_guid() NOT NULL,
TAB_CODE VARCHAR2(100) NOT NULL,
COL_NAME VARCHAR2(100) NOT NULL,
COL_LABEL VARCHAR2(100) NOT NULL,
COL_TYPE VARCHAR2(20) default 'text' NOT NULL,
COL_PRIMARY NUMBER(1) default 0 NOT NULL,
COL_REQUISITE NUMBER(1) default 0 NOT NULL,
COL_UNIQUE NUMBER(1) default 1 NOT NULL,
COL_EXPORTABLE NUMBER(1) default 0 NOT NULL,
COL_MAXLENGTH NUMBER(3) default 1 NOT NULL,
COL_UNIT VARCHAR2(30) default null,
COL_ELEMENT VARCHAR2(20) default 'text' NOT NULL,
COL_RULES VARCHAR2(255),
COL_ERROR VARCHAR2(500),
COL_PLACEHOLDER VARCHAR2(255),
COL_SOURCE VARCHAR2(2000),
COL_STATUS NUMBER(1) default 1 NOT NULL,
COL_ORDER NUMBER(3) default 1 NOT NULL,
CONSTRAINT IMEXPORT_COLUMN_UK UNIQUE(TAB_CODE, COL_NAME),
CONSTRAINT IMEXPORT_COLUMN_PK PRIMARY KEY(COL_ID)
);
COMMENT ON TABLE SYS_EXTRAS_IMEXPORT_COLUMNS IS '表字段配置表';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_ID IS '表字段ID';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.TAB_CODE IS '表编号（用于表时的识别编码）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_NAME IS '表字段名称（实体表字段列名称）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_LABEL IS '表字段名称（页面显示名称）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_TYPE is '表字段类型：（binary_double,binary_float,blob,clob,char,date,long,number,nvarchar2,timestamp）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_PRIMARY IS '字段是否主键(唯一);0:否 1:是 默认0';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_EXPORTABLE IS '字段是否可导出;0:否 1:是 默认1';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_REQUISITE IS '字段是否必填;0:否 1:是 默认0';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_UNIQUE IS '字段是否唯一;0:否 1:是 默认0';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_MAXLENGTH IS '字段最大长度';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_UNIT is '表字段单位:如 KB';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_ELEMENT is '表字段展示类型：（ text,textarea,password,checkbox,radio,file,image,color,date,datetime,email,month,number,range,select,switch,tel,time,week,url）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_RULES is '表字段验证规则：如（required|range:[0,100] (多个用|隔开)）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_ERROR is '表字段格式数据错误提示信息';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_PLACEHOLDER is '表字段提示信息';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_SOURCE is '表字段来源： 格式如 [{"key":"1","value":"选项一"},{"key":"2","value":"选项二"},...]';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_STATUS is '表字段状态:（0:不可用|1：可用）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_COLUMNS.COL_ORDER is '表字段显示顺序';
-- ----------------------------
-- Table structure for SYS_EXTRAS_IMEXPORT_TRANSFORMS
-- ----------------------------
DROP TABLE SYS_EXTRAS_IMEXPORT_TRANSFORMS;
CREATE TABLE SYS_EXTRAS_IMEXPORT_TRANSFORMS (
TRANS_ID VARCHAR2(32) default sys_guid() NOT NULL,
TRANS_ENV NUMBER(1) default 1 NOT NULL,
TRANS_SQL VARCHAR2(500) NOT NULL,
TAB_CODE VARCHAR2(100) NOT NULL,
COL_NAME VARCHAR2(100) NOT NULL,
CONSTRAINT IMEXPORT_TRANSFORM_UK UNIQUE(COL_ID, TRANS_ENV),
CONSTRAINT IMEXPORT_TRANSFORM_PK PRIMARY KEY(TRANS_ID)
);
COMMENT ON TABLE SYS_EXTRAS_IMEXPORT_TRANSFORMS IS '表字段转换配置表';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TRANSFORMS.TRANS_ID IS '表字段转换配置ID';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TRANSFORMS.TRANS_ENV IS '表字段转换适用环境（0:数据导入,1:数据导出）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TRANSFORMS.TRANS_COL IS '表字段ID';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TRANSFORMS.TAB_CODE IS '表编号（用于表时的识别编码）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TRANSFORMS.COL_NAME IS '表字段名称（实体表字段列名称）';
-- ----------------------------
-- Table structure for SYS_EXTRAS_IMEXPORT_TEMPLATES
-- ----------------------------
DROP TABLE SYS_EXTRAS_IMEXPORT_TEMPLATES;
CREATE TABLE SYS_EXTRAS_IMEXPORT_TEMPLATES (
TPL_ID VARCHAR2(32) default sys_guid() NOT NULL,
TPL_CODE VARCHAR2(100) NOT NULL,
TPL_NAME VARCHAR2(100) NOT NULL,
TPL_TYPE NUMBER(1) default 1 NOT NULL,
TPL_CONTENT CLOB,
TPL_STREAM BLOB,
CONSTRAINT IMPORT_TEMPLATE_UK UNIQUE(TPL_CODE),
CONSTRAINT IMPORT_TEMPLATE_PK PRIMARY KEY(TPL_ID)
);
COMMENT ON TABLE SYS_EXTRAS_IMEXPORT_TEMPLATES IS '导出模板配置表';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TEMPLATES.TPL_ID IS '导出模板ID';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TEMPLATES.TPL_CODE IS '模板编号';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TEMPLATES.TPL_NAME IS '模板名称';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TEMPLATES.TPL_TYPE IS '模板类型:（1:字符|2：二进制流）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TEMPLATES.TPL_CONTENT IS '字符型模板内容（如：基于Freemarker、Velocity 等模板技术导出时使用该字段）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_TEMPLATES.TPL_STREAM IS '二进制流模板（如：基于Jxls的模板文件导出时使用该字段）';
-- ----------------------------
-- Table structure for SYS_EXTRAS_IMEXPORT_CUSTOMIZES
-- ----------------------------
DROP TABLE SYS_EXTRAS_IMEXPORT_CUSTOMIZES;
CREATE TABLE SYS_EXTRAS_IMEXPORT_CUSTOMIZES (
CUS_ID VARCHAR2(32) default sys_guid() NOT NULL,
CUS_NAME VARCHAR2(100) NOT NULL,
TAB_CODE VARCHAR2(100) NOT NULL,
USER_ID VARCHAR2(32) NOT NULL,
TIME24 VARCHAR2(34) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
CONSTRAINT EXPORT_CUSTOMIZE_UK UNIQUE(TAB_CODE, COL_NAME),
CONSTRAINT EXPORT_CUSTOMIZE_PK PRIMARY KEY(CUS_ID)
);
COMMENT ON TABLE SYS_EXTRAS_IMEXPORT_CUSTOMIZES IS '导出自定义配置表';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_CUSTOMIZES.CUS_ID IS '导出自定义ID';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_CUSTOMIZES.CUS_NAME IS '导出自定义名称';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_CUSTOMIZES.TAB_CODE IS '导出表编号（用于导出时的识别编码）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_CUSTOMIZES.USER_ID IS '创建自定义导出的用户ID';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_CUSTOMIZES.TIME24 is '自定义导出时间';
-- ----------------------------
-- Table structure for SYS_EXTRAS_IMEXPORT_MY_COLUMNS
-- ----------------------------
DROP TABLE SYS_EXTRAS_IMEXPORT_MY_COLUMNS;
CREATE TABLE SYS_EXTRAS_IMEXPORT_MY_COLUMNS (
CUS_ID VARCHAR2(32) NOT NULL,
COL_ID VARCHAR2(32) NOT NULL,
COL_NAME VARCHAR2(100) NOT NULL,
COL_LABEL VARCHAR2(100) NOT NULL,
COL_ORDER NUMBER(3) default 1 NOT NULL,
CONSTRAINT IMEXPORT_CUSTOMIZE_COLUMNS_UK UNIQUE(CUS_ID, COL_ID)
);
COMMENT ON TABLE SYS_EXTRAS_IMEXPORT_MY_COLUMNS IS '表字段配置表';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_MY_COLUMNS.CUS_ID IS '表字段ID';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_MY_COLUMNS.COL_ID IS '表字段ID';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_MY_COLUMNS.COL_NAME IS '表字段名称（实体表字段列名称）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_MY_COLUMNS.COL_LABEL IS '表字段名称（页面显示名称）';
COMMENT ON COLUMN SYS_EXTRAS_IMEXPORT_MY_COLUMNS.COL_ORDER is '表字段显示顺序';使用指南
主要初始化表
select t.*, t.rowid from SYS_EXTRAS_IMEXPORT_TABLES t;
select t.*, t.rowid from SYS_EXTRAS_IMEXPORT_COLUMNS t;
select t.*, t.rowid from SYS_EXTRAS_IMEXPORT_TRANSFORMS t;
select t.TPL_ID,t.TPL_CODE,t.TPL_NAME,t.TPL_TYPE,t.TPL_CONTENT from SYS_EXTRAS_IMEXPORT_TEMPLATES t;辅助查询：了解初始化信息的来源
select *
from user_tab_columns
where Table_Name='GXXS_CJXXB'
order by column_name;
select *
from user_tab_columns
where Table_Name='GXXS_CJXXKZB'
order by column_name;
select *
from user_col_comments
where Table_Name='GXXS_CJXXB'
order by column_name;
select distinct a.table_name, a.column_name, b.constraint_type
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
--and b.constraint_type = 'P'
and a.table_name = upper('GXXS_CJXXB');导入导出配置初始化
-- 清除数据
delete from SYS_EXTRAS_IMEXPORT_COLUMNS where TAB_CODE = 'TAB_XXX';
commit;
-- 新增记录
insert into SYS_EXTRAS_IMEXPORT_TABLES(TAB_CODE,TAB_NAME,TAB_LABEL,TAB_INTRO,TAB_FK,TAB_FK_COL,TAB_STATUS,TAB_ORDER)
values('TAB_XXX','GXXX_XYXXB','学校信息表','简介信息','','','1','11'})
insert into SYS_EXTRAS_IMEXPORT_COLUMNS(TAB_CODE,COL_NAME,COL_LABEL,COL_TYPE,COL_PRIMARY,COL_REQUISITE,COL_UNIQUE,COL_EXPORTABLE,COL_MAXLENGTH,
COL_UNIT,COL_ELEMENT,COL_RULES,COL_ERROR,COL_PLACEHOLDER,COL_SOURCE,COL_STATUS,COL_ORDER)
select distinct
'TAB_XXX' as TAB_CODE,
t1.COLUMN_NAME as COL_NAME,
NVL(t2.COMMENTS, t1.COLUMN_NAME) as COL_LABEL,
t1.DATA_TYPE as COL_TYPE,
(case when (select count(1)
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and upper(t2.TABLE_NAME) = upper(a.table_name)
and upper(t2.COLUMN_NAME) = upper(a.column_name)
and b.constraint_type = 'P'
) > 0 then '1' else '0' end) as COL_PRIMARY,
(case when t1.NULLABLE = 'Y' then '1' else '0' end) as COL_REQUISITE,
(case when (select count(1)
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and upper(t2.TABLE_NAME) = upper(a.table_name)
and upper(t2.COLUMN_NAME) = upper(a.column_name)
and b.constraint_type = 'U'
) > 0 then '1' else '0' end) as COL_UNIQUE,
'0' as COL_EXPORTABLE ,
t1.DATA_LENGTH as COL_MAXLENGTH,
'' as COL_UNIT,
'input' as COL_ELEMENT,
'' as COL_RULES,
'' as COL_ERROR,
'' as COL_PLACEHOLDER,
'' as COL_SOURCE,
'1' as COL_STATUS,
rownum
from user_tab_columns t1,
user_col_comments t2
where upper(t1.TABLE_NAME) = upper(t2.TABLE_NAME)
and upper(t1.COLUMN_NAME) = upper(t2.COLUMN_NAME)
and upper(t1.TABLE_NAME) = 'GXXX_XYXXB';
commit;
-- 转换语句示例
select (case when #{value} = '是' then '1' else '0' end) from dual
select (case when #{value} = '1' then '是' else '否' end)from dual
select t.id from GXXS_XSXXB t where t.XH=#{value}
select t.D_KEY from SYS_EXTRAS_PAIRVALUE t where t.D_GROUP ='JZGLBM' and t.D_TEXT=#{value}
select t.D_KEY from SYS_EXTRAS_PAIRVALUE t where t.D_GROUP ='DWBBM' and t.D_TEXT=#{value}
select t.D_TEXT from SYS_EXTRAS_PAIRVALUE t where t.D_GROUP ='DWBBM'and t.D_KEY=#{key}
select t.DWMC from GXXX_XYXXB t where t.ID=#{value}
5、代码示例
public class DataImexportApiController extends BaseApiController {
@Autowired
private IDataImportService dataImportService;
@Autowired
private IDataExportService dataExportService;
@ApiOperation(value = "快速导出", notes = "根据前端用户选择的列信息，快速导出code对应表的数据")
@ApiImplicitParams({
@ApiImplicitParam(paramType = "body", name = "requestVo", value = "数据导出请求参数", dataType = "DataExportRequestVo")
})
@BusinessLog(module = Constants.EXTRAS_EXPORT, business = "新增用户-名称：${name}", opt = BusinessType.INSERT)
@PostMapping("fast")
@PreAuthorize("authenticated and (hasAuthority('data-export:fast') or hasAuthority('*'))")
public ResponseEntity<byte[]> fast(@Valid @RequestBody DataExportRequestVo requestVo) throws Exception {
return getDataExportService().dataExportFast(requestVo);
}
@ApiOperation(value = "模板化导出", notes = "根据code对应模板以及表数据实现基于模板的导出")
@ApiImplicitParams({
@ApiImplicitParam(paramType = "query", name = "code", value = "表编号（用于时的识别编码）", required = true, dataType = "String")
})
@BusinessLog(module = Constants.EXTRAS_EXPORT, business = "新增用户-名称：${name}", opt = BusinessType.INSERT)
@PostMapping("tpl")
@PreAuthorize("authenticated and (hasAuthority('data-export:tpl') or hasAuthority('*'))")
public ResponseEntity<byte[]> template(@Valid @RequestParam(name = "code") @NotNull(message = "表编号不能为空") String code) throws Exception {
return getDataExportService().dataExportTpl(code);
}
@ApiOperation(value = "模板化导出", notes = "根据code对应模板以及表数据实现基于模板的导出")
@ApiImplicitParams({
@ApiImplicitParam(paramType = "query", name = "code", value = "表编号（用于时的识别编码）", required = true, dataType = "String")
})
@BusinessLog(module = Constants.EXTRAS_EXPORT, business = "新增用户-名称：${name}", opt = BusinessType.INSERT)
@PostMapping("tpl")
@PreAuthorize("authenticated and (hasAuthority('data-export:tpl') or hasAuthority('*'))")
public ResponseEntity<byte[]> template() throws Exception {
Map<String, Object> variables = new HashMap<String, Object>();
variables.put("list", "查询数据");
return getDataExportService().dataExportTpl("TEA_XYXX", variables);
}
@ApiOperation(value="Excel导入", httpMethod = "POST")
@BusinessLog(module = Constants.EXTRAS_IMPORT, business = "Excel导入", opt = BusinessType.UPLOAD)
@PostMapping(value = "upload",headers = "content-type=multipart/form-data")
@PreAuthorize("authenticated and hasAnyAuthority('dept:import','*')")
@Transactional
public ApiRestResponse<String> upload(MultipartFile file) throws Exception {
String fileName = file.getOriginalFilename();
//String userId = SubjectUtils.getPrincipal(SecurityPrincipal.class).getUserid();
if (!(fileName.endsWith(".xls") || fileName.endsWith(".xlsx"))){
return ApiRestResponse.fail("上传文件格式错误");
}
if (file.isEmpty()) {
return ApiRestResponse.fail("上传文件为空");
}
ConstraintViolationResult<RowMap> result = getDataImportService().dataImport("TEA_XYXX", file);
if(!result.accept()) {
throw new ConstraintViolationException(result.getConstraintViolations());
}
return ApiRestResponse.of(ApiCode.SC_SUCCESS.getCode(), getMessage("scores.upload.success"));
}
public IDataExportService getDataExportService() {
return dataExportService;
}
public IDataImportService getDataImportService() {
return dataImportService;
}
}作者：Jeebiz 创建时间：2019-12-24 11:43
更新时间：2023-12-20 16:58
更新时间：2023-12-20 16:58
