概述
需求：现在有20多张的配置表（后续还可能新增/修改），都是简单的增删改查导入导出，如果一张表对应数据库一张表，那后续新增/修改每次都要改代码数库。如果我们将所有配置表数据都存到一张表中，前端配置每张表内容就没这问题了。
数据库设计：
t_bs_table_row（配置表-列名）：设置配置表中有哪些列及列属性。
t_bs_table_data（配置表-数据）：存储 表中的每一行数据的唯一id（t_bs_table_data_detailed中的table_data_id相同，即是同一行的不同列数据）
t_bs_table_data_detailed（配置表-数据详情）：存储 表中每个单元格的数据
CREATE TABLE `t_bs_table_row` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`table_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '表名',
`table_key` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '表标识',
`row_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '列名',
`row_number` int(11) DEFAULT NULL COMMENT '列号',
`merge_row_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '合并列名',
`type` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '类型（默认1字符串，2数值，3数据字典，4接口，5长文本）',
`data_sources` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '数据来源',
`data_sources_key` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据来源key',
`proof` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '校验',
`is_must` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT '是否必填 0否1是',
`is_screen` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT '是否筛选 0否1是',
`formula` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '公式',
`formula_text` text COLLATE utf8_bin COMMENT '公式文本',
`creator` varchar(44) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人AD',
`creator_name` varchar(44) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人名称',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`last_updator` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人AD',
`last_updator_name` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人名称',
`last_update_time` datetime DEFAULT NULL COMMENT '修改时间',
`is_deleted` char(1) COLLATE utf8_bin DEFAULT '0' COMMENT '删除标识符0否1是',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1385410320750612482 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='配置表-列名';
CREATE TABLE `t_bs_table_data` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`table_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '表名',
`creator` varchar(44) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人AD',
`creator_name` varchar(44) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人名称',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`last_updator` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人AD',
`last_updator_name` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人名称',
`last_update_time` datetime DEFAULT NULL COMMENT '修改时间',
`is_deleted` char(1) COLLATE utf8_bin DEFAULT '0' COMMENT '删除标识符0否1是',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1385411285767688195 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='配置表-数据';
CREATE TABLE `t_bs_table_data_detailed` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`table_data_id` bigint(20) DEFAULT NULL COMMENT '配置表_数据id',
`table_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '表名',
`table_key` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '表标识',
`row_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '列名',
`row_key` text COLLATE utf8_bin COMMENT '列键（如数据id）',
`row_value` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '列值',
`row_text` text COLLATE utf8_bin COMMENT '列值-文本',
`creator` varchar(44) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人AD',
`creator_name` varchar(44) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人名称',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`last_updator` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人AD',
`last_updator_name` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人名称',
`last_update_time` datetime DEFAULT NULL COMMENT '修改时间',
`is_deleted` char(1) COLLATE utf8_bin DEFAULT '0' COMMENT '删除标识符0否1是',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1385411285977403394 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='配置表-数据详情';
页面展示：
t_bs_table_row:
t_bs_table_data：
t_bs_table_data_detailed：
查询表中数据接口：
@Override
public List<TableData> getQueryAllTableData(String tableName, Map<String, String> queryKeyMap, Map<String, String> queryMap, List<DataSourcesDTO> list) {
QueryWrapper<TableData> queryWrapper = new QueryWrapper();
queryWrapper.lambda().eq(TableData::getTableName,tableName);
if(ObjectUtils.isNotEmpty(queryKeyMap)){
this.getEqualTableDataKeySql(tableName,queryKeyMap,queryWrapper);
}
if(ObjectUtils.isNotEmpty(queryMap)){
this.getEqualTableDataSql(tableName,queryMap,queryWrapper);
}
if(ObjectUtils.isNotEmpty(list)){
this.getIntervalTableDataSql(tableName,list,queryWrapper);
}
List<TableData> tableDataList = this.list(queryWrapper);
//获取文本列名
List<String> rows = this.getRowText(tableName);
//组合数据
this.combinedData(tableDataList,rows);
//获取文本列名
return tableDataList;
}
private void getEqualTableDataKeySql(String tableName, Map<String, String> queryKeyMap, QueryWrapper<TableData> queryWrapper) {
if(ObjectUtils.isEmpty(queryKeyMap)){
return;
}
if(StringUtils.isBlank(tableName)){
throw new BusiException("表名必填");
}
List<TableRow> tableRowList = tableRowMapper.selectList(Wrappers.<TableRow>query().lambda().eq(TableRow::getTableName,tableName));
List<String> rowNameList = tableRowList.stream().map(a->a.getRowName()).collect(Collectors.toList());
List<String> interList = new ArrayList<>();
for(TableRow data : tableRowList){
if(TableRowTypeEnum.INTER.getCode().equals(data.getType())){
interList.add(data.getRowName());
}
}
for(Map.Entry<String, String> entry : queryKeyMap.entrySet()){
String mapKey = entry.getKey();
String mapValue = entry.getValue();
if(!rowNameList.contains(mapKey)){
continue;
}
if(StringUtils.isBlank(mapValue)){
continue;
}
if(interList.contains(mapKey)){
String sql = " SELECT 1 " +
" from t_bs_table_data_detailed b " +
" WHERE t_bs_table_data.id =b.table_data_id and b.is_deleted = 0 and table_name = '" +tableName+"' and ( ( row_name = '"+mapKey+"' and row_key like '%"+mapValue+"%' ) or ( row_name = '"+mapKey+"' and row_value = '')) ";
queryWrapper.exists(sql);
}else {
String sql = " SELECT 1 " +
" from t_bs_table_data_detailed b " +
" WHERE t_bs_table_data.id =b.table_data_id and b.is_deleted = 0 and table_name = '" +tableName+"' and ( ( row_name = '"+mapKey+"' and row_key = '"+mapValue+"' ) or ( row_name = '"+mapKey+"' and row_value = '')) ";
queryWrapper.exists(sql);
}
}
}
private void getEqualTableDataSql(String tableName, Map<String, String> queryMap, QueryWrapper<TableData> queryWrapper) {
if(ObjectUtils.isEmpty(queryMap)){
return;
}
if(StringUtils.isBlank(tableName)){
throw new BusiException("表名必填");
}
List<TableRow> tableRowList = tableRowMapper.selectList(Wrappers.<TableRow>query().lambda().eq(TableRow::getTableName,tableName));
List<String> rowNameList = tableRowList.stream().map(a->a.getRowName()).collect(Collectors.toList());
List<String> intervalList = new ArrayList<>();
for (TableRow data :tableRowList ){
if(TableRowTypeEnum.INTERVAL.getCode().equals(data.getType())){
intervalList.add(data.getRowName());
}
}
for(Map.Entry<String, String> entry : queryMap.entrySet()){
String mapKey = entry.getKey();
String mapValue = entry.getValue();
if(!rowNameList.contains(mapKey)){
continue;
}
if(StringUtils.isBlank(mapValue)){
continue;
}
if(intervalList.contains(mapKey)){
String sql = " SELECT 1 " +
" from t_bs_table_data_detailed b " +
" WHERE table_name = '"+tableName+"' and t_bs_table_data.id =b.table_data_id and b.is_deleted = 0 and table_name = '" +tableName+"' " +
" and ( ( row_name = '"+mapKey+"' and row_value = '' ) or ( row_name = '"+mapKey+"' and f_interval_judgment("+mapValue+",b.row_value)) ) ";
queryWrapper.exists(sql);
}else {
String sql = " SELECT 1 " +
" from t_bs_table_data_detailed b " +
" WHERE t_bs_table_data.id =b.table_data_id and b.is_deleted = 0 and table_name = '" +tableName+"' and ( ( row_name = '"+mapKey+"' and row_value = '"+mapValue+"' ) or ( row_name = '"+mapKey+"' and row_value = '') ) ";
queryWrapper.exists(sql);
}
}
}
private void getIntervalTableDataSql(String tableName, List<DataSourcesDTO> list, QueryWrapper<TableData> queryWrapper) {
if(ObjectUtils.isEmpty(list)){
return;
}
if(StringUtils.isBlank(tableName)){
throw new BusiException("表名必填");
}
List<TableRow> tableRowList = tableRowMapper.selectList(Wrappers.<TableRow>query().lambda().eq(TableRow::getTableName,tableName));
List<String> rowNameList = tableRowList.stream().map(a->a.getRowName()).collect(Collectors.toList());
for(DataSourcesDTO data : list){
String min = data.getMin();
String max = data.getMax();
String value = data.getValue();
if(StringUtils.isBlank(value)){
continue;
}
if(!StringUtils.isBlank(min) && rowNameList.contains(min)){
String sql = " SELECT 1 " +
" from t_bs_table_data_detailed b " +
" WHERE table_name = '" +tableName+"' and t_bs_table_data.id =b.table_data_id and b.is_deleted = 0 and table_name = '" +tableName+"' and (( row_name = '"+min+"' and row_value <= "+value+" ) or ( row_name = '"+min+"' and row_value = '')) ";
queryWrapper.exists(sql);
}
if(!StringUtils.isBlank(max) && rowNameList.contains(max)){
String sql1 = " SELECT 1 " +
" from t_bs_table_data_detailed b " +
" WHERE table_name = '" +tableName+"' and t_bs_table_data.id =b.table_data_id and b.is_deleted = 0 and table_name = '" +tableName+"' and (( row_name = '"+max+"' and row_value >= "+value+" ) or ( row_name = '"+max+"' and row_value = '')) ";
queryWrapper.exists(sql1);
}
}
}
导出接口：
if(StringUtils.isBlank(tableName)){
throw new BusiException("表名必填");
}
String fileName = tableName+".xls";
String sheetName = tableName+".xls";
List<TableRow> tableRowList =tableRowMapper.selectList(Wrappers.<TableRow>query().lambda()
.eq(TableRow::getTableName,tableName)
.orderByAsc(TableRow::getRowNumber)
);
if(ObjectUtils.isEmpty(tableRowList)){
throw new BusiException("表不存在");
}
try {
//list 表头
List<List<String>> list = getHeader(tableRowList);
//dataList 数据
List<List<String>> dataList = getData(tableRowList,tableName,key);
EasyExcelUtils.getMultiHeaderIo(fileName,list,sheetName,dataList,response);
} catch (Exception e){
log.debug(e.getMessage());
throw new BusiException("数据导出失败");
}
private List<List<String>> getHeader(List<TableRow> tableRowList) {
List<List<String>> list = new ArrayList<List<String>>();
for(TableRow data : tableRowList){
List<String> head = new ArrayList<String>();
if(ObjectUtils.isNotEmpty(data.getMergeRowName())){
head.add(data.getMergeRowName());
}else {
head.add(data.getRowName());
}
head.add(data.getRowName());
list.add(head);
}
return list;
}
private List<List<String>> getData(List<TableRow> tableRowList,String tableName,String key) {
List<List<String>> list = new ArrayList<>();
List<String> rows = new ArrayList<>();
for (TableRow data : tableRowList){
if(TableRowTypeEnum.TEXT.getCode().equals(data.getType())){
rows.add(data.getRowName());
}
}
QueryWrapper<TableData> queryWrapper = new QueryWrapper();
queryWrapper.lambda().eq(TableData::getTableName,tableName);
queryWrapper.lambda().orderByDesc(TableData::getCreateTime);
if(ObjectUtils.isNotEmpty(key)){
String sql = " SELECT 1 from t_bs_table_data_detailed b WHERE table_name = '" +tableName+"' and row_value like '%"+key+"%' and b.is_deleted = 0 ";
queryWrapper.exists(sql);
}
queryWrapper.lambda().orderByDesc(TableData::getId);
List<TableData> tableDataList = this.list(queryWrapper);
if(ObjectUtils.isEmpty(tableDataList)){
return list;
}
List<Long> ids = tableDataList.stream().map(a->a.getId()).collect(Collectors.toList());
List<TableDataDetailed> detailedList = tableDataDetailedService.list(Wrappers.<TableDataDetailed>query().lambda()
.in(TableDataDetailed::getTableDataId,ids).orderByDesc(TableDataDetailed::getTableDataId));
// orderByDesc(TableData::getId);
if(ObjectUtils.isEmpty(detailedList)){
return list;
}
Map<String,Map<String,String>> detailedMap = new HashMap<>();
for(TableDataDetailed data : detailedList){
String id = String.valueOf(data.getTableDataId());
Map<String,String> map = detailedMap.get(id);
if(map == null){
map = new HashMap<>();
}
if(!ObjectUtils.isEmpty(rows) && rows.contains(data.getRowName())){
map.put(data.getRowName(),data.getRowText());
}else {
map.put(data.getRowName(),data.getRowValue());
}
detailedMap.put(id,map);
}
for(TableData data : tableDataList ){
String id = String.valueOf(data.getId());
Map<String,String> value = detailedMap.get(id);
List<String> list1 = new ArrayList<>();
for(TableRow data1 : tableRowList){
list1.add(value.get(data1.getRowName()));
}
list.add(list1);
}
return list;
}
excel导出工具类：
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.zb.bonus.common.BusiException;
import com.zb.bonus.modules.give.entity.GiveBase;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
@Slf4j
public class EasyExcelUtils {
/**
* 多表头数据导出
* 将list数据导出成excel文件流提供下载
*
* @param fileName 导出的文件名
* @param list 表头
* @param sheetName sheet名
* @param dataList 数据
* @param response
* @throws IOException
*/
public static void getMultiHeaderIo(String fileName, List<List<String>> list, String sheetName, List<List<String>> dataList, HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
EasyExcel.write(response.getOutputStream()).head(list)
// .registerWriteHandler(EasyExcelUtils.getStyleStrategy())
.sheet(sheetName).doWrite(dataList);
} catch (Exception e) {
log.error(e.getMessage());
throw new BusiException("数据导出失败");
}
}
public static HorizontalCellStyleStrategy getStyleStrategy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
// 背景色, 设置为白色，也是默认颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 12);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景绿色
//contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
// 字体策略
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//设置 自动换行
contentWriteCellStyle.setWrapped(true);
//设置 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置 水平居中
// contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//设置边框样式
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
/**
* 自定义头部的 列的宽度设置 策略. .
*/
class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
// 测试为 COLUMN 宽度定制.
if (isHead && cell.getRowIndex() == 2) {
int columnWidth = cell.getStringCellValue().getBytes().length;
int cellIndex = cell.getColumnIndex();
switch (cellIndex) {
case 0:
case 2:
case 3:
columnWidth = 10;
break;
case 1:
columnWidth = 25;
break;
case 4:
columnWidth = 15;
break;
case 5:
columnWidth = 50;
break;
default:
break;
}
if (columnWidth > 255) {
columnWidth = 255;
}
writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
}
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
// 设置行高测试
int rowIndex = row.getRowNum();
System.out.println("当前行: " + rowIndex);
short height = 600;
row.setHeight(height);
}
}
/**
* 简单读取excel excel量达到万级别不要使用该方法
*
* @param inputStream
* @param clazz excel内容要转换的对应的实体类
* @param sheetNo
* @param <T>
* @return
*/
public static <T> List<T> simpleReadExcel(InputStream inputStream, Class clazz, int sheetNo, int startRowNo) {
ExcelReaderBuilder read1 = EasyExcel.read(inputStream, clazz, null);
List<T> list = read1.sheet(sheetNo).headRowNumber(startRowNo).doReadSync();
return list;
}
/**
* 导出Excel
*
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @param wb HSSFWorkbook对象
* @param rowNum 表头数据所在行
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb, Integer rowNum) {
// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
HSSFSheet sheet = null;
if (wb == null) {
wb = new HSSFWorkbook();
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
sheet = wb.createSheet(sheetName);
} else {
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
sheet = wb.getSheet(sheetName);
}
// 第三步，在sheet中添加表头第 rowNum 行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(rowNum);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
// 第五步，声明列对象
HSSFCell cell = null;
//创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//创建内容
for (int i = 0; i < values.length; i++) {
row = sheet.createRow(i + rowNum + 1);
for (int j = 0; j < values[i].length; j++) {
//将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
return wb;
}
/**
* 发送响应流方法
*
* @param response
* @param fileName
*/
public static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
log.error(ex.getMessage());
}
}
}
excel导入：
@Override
public void dataImport(MultipartFile file, String tableName, HttpServletResponse response) {
List<TableData> tableDataList = new ArrayList<>();
boolean isError = this.getDataImportInfo(file,tableName,tableDataList);
if(isError){
//有错误信息
errorExport(tableDataList,response);
//错误数据生成excel 文件流返回前端
}else {
//没有错误信息
//更新表数据
saveTableDataList(tableDataList);
}
}
private boolean getDataImportInfo(MultipartFile file, String tableName, List<TableData> tableDataList) {
boolean isError = false;
if(ObjectUtils.isEmpty(tableName)){
throw new BusiException("表名不能为空");
}
List<Map<Integer, String>> listMap = null;
byte [] byteArr= new byte[0];
try {
byteArr = file.getBytes();
InputStream inputStream = new ByteArrayInputStream(byteArr);
listMap = EasyExcel.read(inputStream).sheet().doReadSync();
} catch (Exception e) {
log.error(e.getMessage());
throw new BusiException("excel有问题");
}
if(ObjectUtils.isEmpty(listMap)){
throw new BusiException("excel为空");
}
//判断列名 是否有效
List<TableRow> tableRowList = this.tableRowMapper.selectList(Wrappers.<TableRow>query().lambda()
.eq(TableRow::getTableName,tableName));
if(ObjectUtils.isEmpty(tableRowList)){
throw new BusiException("表："+tableName+"不存在");
}
// 判断是否多了少了 列名
Map<String,TableRow> tableRowMap = new HashMap<>();
List<String> dicts = new ArrayList<>();
List<String> intervalList = new ArrayList<>();
for (TableRow data : tableRowList){
tableRowMap.put(data.getRowName(),data);
if(TableRowTypeEnum.DICT.getCode().equals(data.getType())){
dicts.add(data.getDataSourcesKey());
}
if(TableRowTypeEnum.INTERVAL.getCode().equals(data.getType())){
intervalList.add(data.getRowName());
}
}
List<TableRow> tableRows = new ArrayList<>();
Map<Integer, String> tableMap = listMap.get(0);
List<String> tableRowNames = new ArrayList<>();
for (Map.Entry<Integer, String> entry : tableMap.entrySet()) {
String value = entry.getValue();
TableRow tableRow = tableRowMap.get(value);
if(ObjectUtils.isEmpty(tableRow)){
throw new BusiException("表："+tableName+"不存在列："+value);
}
tableRows.add(tableRow);
tableRowNames.add(value);
}
for(TableRow data : tableRowList){
if(!tableRowNames.contains(data.getRowName())){
throw new BusiException("excel缺少列："+data.getRowName());
}
}
listMap.remove(0);
if(ObjectUtils.isEmpty(listMap)){
throw new BusiException("excel数据为空");
}
//数据字典
Map<String,List<DictDetails>> detailsMap = dictService.selectCodeList(dicts);
//接口数据
Map<String,List<String>> dataCodeMap = new HashMap<>();
for(Map<Integer,String> data : listMap){
for (Map.Entry<Integer, String> entry : data.entrySet()) {
Integer key = entry.getKey();
String value = entry.getValue();
TableRow tableRow = tableRows.get(key);
if(ObjectUtils.isEmpty(value)){
break;
}
if(TableRowTypeEnum.INTER.getCode().equals(tableRow.getType())){
String dataSourcesKey = tableRow.getDataSourcesKey();
List<String> list = dataCodeMap.get(dataSourcesKey);
if(list == null){
list = new ArrayList<>();
}
list.add(value);
dataCodeMap.put(dataSourcesKey,list);
}
}
}
Map<String,List<BaseMapVO>> dataMap = dataSourcesService.getDataList(dataCodeMap);
for(Map<Integer,String> data : listMap){
String excelId =String.valueOf(tableDataList.size());
TableData tableData = new TableData();
tableData.setTableName(tableName);
tableData.setExcelId(excelId);
List<TableDataDetailed> tableDataDetailedList = new ArrayList<>();
List<String> errorInfoList = new ArrayList<>();
for (Map.Entry<Integer, String> entry : data.entrySet()) {
TableDataDetailed tableDataDetailed= new TableDataDetailed();
tableDataDetailed.setExcelId(excelId);
Integer key = entry.getKey();
String value = entry.getValue();
TableRow tableRow = tableRows.get(key);
tableDataDetailed.setTableName(tableRow.getTableName());
tableDataDetailed.setTableKey(tableRow.getTableKey());
tableDataDetailed.setRowName(tableRow.getRowName());
//校验 字符串/数值类型/长文本
if(TableRowTypeEnum.STRING.getCode().equals(tableRow.getType()) || TableRowTypeEnum.INT.getCode().equals(tableRow.getType()) || TableRowTypeEnum.TEXT.getCode().equals(tableRow.getType())){
if(TableRowTypeEnum.TEXT.getCode().equals(tableRow.getType())){
tableDataDetailed.setRowText(value);
}else {
tableDataDetailed.setRowValue(value);
tableDataDetailed.setRowKey(value);
}
isError = check(isError,errorInfoList,value,tableRow);
}
//数据字典校验
if(TableRowTypeEnum.DICT.getCode().equals(tableRow.getType())){
tableDataDetailed.setRowValue(value);
isError = dictCheck(isError,errorInfoList,value, tableRow,detailsMap,tableDataDetailed);
}
// 接口校验
if(TableRowTypeEnum.INTER.getCode().equals(tableRow.getType())){
tableDataDetailed.setRowValue(value);
isError = dataCheck(isError,errorInfoList,value, tableRow,dataMap,tableDataDetailed);
}
// 区间校验
if(TableRowTypeEnum.INTERVAL.getCode().equals(tableRow.getType())){
tableDataDetailed.setRowValue(value);
isError = intervalCheck(isError,errorInfoList,value,tableRow,tableDataDetailed);
}
tableDataDetailedList.add(tableDataDetailed);
}
if(ObjectUtils.isNotEmpty(errorInfoList)){
String errorInfo =org.apache.commons.lang.StringUtils.join(errorInfoList.toArray(), ",");
tableData.setErrorInfo(errorInfo);
}
tableData.setTableDataDetailedList(tableDataDetailedList);
tableDataList.add(tableData);
}
return isError;
}
/**
* 字符串/数值类型/长文本校验
* @param isError
* @param errorInfoList
* @param value
* @param tableRow
*/
private Boolean check(Boolean isError, List<String> errorInfoList, String value, TableRow tableRow) {
if(ObjectUtils.isEmpty(value)){
if(WhetherEnum.YES.getCode().equals(tableRow.getIsMust())){
String errorInfo = tableRow.getRowName()+"必填";
errorInfoList.add(errorInfo);
return true;
}else {
return isError;
}
}else {
if(TableRowTypeEnum.STRING.getCode().equals(tableRow.getType())){
//字符串不能超过30
if(value.length() > 30){
String errorInfo = tableRow.getRowName()+"字符串类型，长度不能超过30";
errorInfoList.add(errorInfo);
return true;
}
}else if(TableRowTypeEnum.INT.getCode().equals(tableRow.getType())){
Integer proof1 = Integer.valueOf(tableRow.getProof().split(",")[0]);
Integer proof2 = Integer.valueOf(tableRow.getProof().split(",")[1]);
//数值校验
if (value.endsWith(".")) {
String errorInfo = tableRow.getRowName()+"数值类型，整数位不能超过"+proof1+",小数位不能超过"+proof2;
errorInfoList.add(errorInfo);
return true;
}
String[] strings = value.split("\.");
if(strings.length > 2){
String errorInfo = tableRow.getRowName()+"数值类型，整数位不能超过"+proof1+",小数位不能超过"+proof2;
errorInfoList.add(errorInfo);
return true;
}
String st = strings[0];
if(!StringUtils.isNumeric(st) || st.length() > proof1){
String errorInfo = tableRow.getRowName()+"数值类型，整数位不能超过"+proof1+",小数位不能超过"+proof2;
errorInfoList.add(errorInfo);
return true;
}
if(strings.length == 2){
String st1 = strings[1];
if(!StringUtils.isNumeric(st1) || st1.length() > proof2){
String errorInfo = tableRow.getRowName()+"数值类型，整数位不能超过"+proof1+",小数位不能超过"+proof2;
errorInfoList.add(errorInfo);
return true;
}
}
} else if(TableRowTypeEnum.TEXT.getCode().equals(tableRow.getType())){
if(value.length() > 500){
String errorInfo = tableRow.getRowName()+"长文本类型，长度不能超过500";
errorInfoList.add(errorInfo);
return true;
}
}
}
return isError;
}
/**
* 数据字典校验
* @param isError
* @param errorInfoList
* @param value
* @param detailsMap
*/
private Boolean dictCheck(Boolean isError, List<String> errorInfoList, String value, TableRow tableRow , Map<String,List<DictDetails>> detailsMap,TableDataDetailed tableDataDetailed) {
if(ObjectUtils.isEmpty(value)){
if(WhetherEnum.YES.getCode().equals(tableRow.getIsMust())){
String errorInfo = tableRow.getRowName()+"必填";
errorInfoList.add(errorInfo);
return true;
}else {
return isError;
}
}
if(ObjectUtils.isEmpty(tableRow.getDataSourcesKey())){
throw new BusiException("表："+tableRow.getTableName()+",列："+tableRow.getRowName()+"，数据字典信息有误");
}
List<DictDetails> detailsList = detailsMap.get(tableRow.getDataSourcesKey());
if(ObjectUtils.isEmpty(detailsList)){
String errorInfo = tableRow.getRowName()+":"+tableRow.getDataSources()+"数据字典值为空";
errorInfoList.add(errorInfo);
return true;
}
for (DictDetails data :detailsList ){
if(value.equals(data.getDictValue())){
tableDataDetailed.setRowKey(data.getDictKey());
break;
}
}
if(ObjectUtils.isEmpty(tableDataDetailed.getRowKey())){
String errorInfo = tableRow.getRowName()+":"+tableDataDetailed.getRowValue()+",在数据字典不存在";
errorInfoList.add(errorInfo);
return true;
}
return isError;
}
/**
*
* @param isError
* @param errorInfoList
* @param value
* @param tableRow
* @param dataMap
* @param tableDataDetailed
*/
private Boolean dataCheck(Boolean isError, List<String> errorInfoList, String value, TableRow tableRow, Map<String, List<BaseMapVO>> dataMap, TableDataDetailed tableDataDetailed) {
if(ObjectUtils.isEmpty(value)){
if(WhetherEnum.YES.getCode().equals(tableRow.getIsMust())){
String errorInfo = tableRow.getRowName()+"必填";
errorInfoList.add(errorInfo);
return true;
}else {
return isError;
}
}
if(ObjectUtils.isEmpty(tableRow.getDataSourcesKey())){
throw new BusiException("表："+tableRow.getTableName()+",列："+tableRow.getRowName()+"，数据来源信息有误");
}
List<BaseMapVO> baseMapVOList = dataMap.get(tableRow.getDataSourcesKey());
if(ObjectUtils.isEmpty(baseMapVOList)){
String errorInfo = tableRow.getRowName()+":"+tableRow.getDataSources()+"值为空";
errorInfoList.add(errorInfo);
return true;
}
for (BaseMapVO data :baseMapVOList ){
if(value.equals(data.getName())){
tableDataDetailed.setRowKey(data.getId());
break;
}
}
if(ObjectUtils.isEmpty(tableDataDetailed.getRowKey())){
String errorInfo = tableRow.getRowName()+":"+tableDataDetailed.getRowValue()+"不存在";
errorInfoList.add(errorInfo);
return true;
}
return isError;
}
private boolean intervalCheck(boolean isError, List<String> errorInfoList, String value,TableRow tableRow, TableDataDetailed tableDataDetailed) {
if(ObjectUtils.isEmpty(value)){
if(WhetherEnum.YES.getCode().equals(tableRow.getIsMust())){
String errorInfo = tableRow.getRowName()+"必填";
errorInfoList.add(errorInfo);
return true;
}else {
return isError;
}
}
if(!CommonUtils.isInterval(value)){
String errorInfo = tableRow.getRowName()+"格式有误，正确格式如下：(2,50]或(30,]";
errorInfoList.add(errorInfo);
return true;
}
tableDataDetailed.setRowKey(value);
return isError;
}
获取接口信息：
@Override
public Map<String, List<BaseMapVO>> getDataList(Map<String, List<String>> listMap) {
Map<String, List<BaseMapVO>> map = new HashMap<>();
for (Map.Entry<String, List<String>> entry : listMap.entrySet()) {
String key = entry.getKey();
List<String> value = entry.getValue();
List<BaseMapVO> baseMapVOList = getDataList(key, value);
map.put(key, baseMapVOList);
}
return map;
}
public List<BaseMapVO> getDataList(String code, List<String> nameList) {
List<BaseMapVO> baseMapVOList = new ArrayList<>();
if (code.equals(DataSourcesEnum.BONUS_TYPE.name())) {
baseMapVOList = this.getBonusType(nameList);
} else if (code.equals(DataSourcesEnum.BUSINESS_POSITION.name())) {
baseMapVOList = this.getBusinessPosition(nameList);
} else if (code.equals(DataSourcesEnum.PROJECT_BASE.name())) {
baseMapVOList = this.getProjectBase(nameList);
} else if (code.equals(DataSourcesEnum.ASSESS_INDEX.name())) {
baseMapVOList = this.getAssessIndex(nameList);
}
return baseMapVOList;
}
/**
* 错误信息导出
* @param tableDataList
*/
private void errorExport(List<TableData> tableDataList,HttpServletResponse response) {
if(ObjectUtils.isEmpty(tableDataList)){
throw new BusiException("数据为空");
}
String tableName = tableDataList.get(0).getTableName();
String fileName = tableName+".xls";
String sheetName = tableName+".xls";
List<TableRow> tableRowList =tableRowMapper.selectList(Wrappers.<TableRow>query().lambda()
.eq(TableRow::getTableName,tableName)
.orderByAsc(TableRow::getRowNumber)
);
if(ObjectUtils.isEmpty(tableRowList)){
throw new BusiException("表不存在");
}
//list 表头
List<List<String>> list = getHeader(tableRowList);
List<String> head = new ArrayList<String>();
head.add("错误信息");
list.add(head);
//dataList 数据
List<List<String>> dataList = getErrorData(tableDataList,tableRowList);
EasyExcelUtils.getMultiHeaderIo(fileName,list,sheetName,dataList,response);
}
判断字符串是否是区间：
/**
* 判断是否是 区间范围 (12,20]
*
* @param data
* @return
*/
public static Boolean isInterval(String data) {
if (StringUtils.isEmpty(data) || data.length() < CommonConst.THREE) {
return false;
}
String first = data.substring(0, 1);
String tail = data.substring(data.length() - 1);
if (!CommonConst.LEFT_BRACKETS.equals(first) && !CommonConst.LEFT_SQUARE_BRACKETS.equals(first)) {
return false;
}
if (!CommonConst.RIGHT_BRACKETS.equals(tail) && !CommonConst.RIGHT_SQUARE_BRACKETS.equals(tail)) {
return false;
}
String min = data.substring(1, data.indexOf(CommonConst.COMMA));
String[] mixs = min.split(CommonConst.POINT);
if (mixs[0].length() > CommonConst.TEN) {
return false;
}
if (mixs.length > CommonConst.TWO && mixs[1].length() > CommonConst.TWO) {
return false;
}
String max = data.substring(data.indexOf(CommonConst.COMMA) + 1, data.length() - 1);
String[] maxs = max.split(CommonConst.POINT);
if (maxs[0].length() > CommonConst.TEN) {
return false;
}
if (maxs.length > 1 && maxs[1].length() > CommonConst.TWO) {
return false;
}
BigDecimal minBig = null;
BigDecimal maxBig = null;
if (!StringUtils.isBlank(min)) {
try {
minBig = new BigDecimal(min);
} catch (Exception e) {
return false;
}
}
if (!StringUtils.isBlank(max)) {
try {
maxBig = new BigDecimal(max);
} catch (Exception e) {
return false;
}
}
if (!ObjectUtils.isEmpty(minBig) && !ObjectUtils.isEmpty(maxBig)) {
if (maxBig.compareTo(minBig) < 0) {
return false;
}
}
return true;
}
最后
以上就是炙热白昼为你收集整理的自定义表-配置实现的全部内容，希望文章能够帮你解决自定义表-配置实现所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复