写了一个上传excel到解析到数据库的通用类，只能作为参考
先在本文件中判断有没有规定的列重复的，然后判断数据库有没有重复的，没有就insert 有就提示，次例为自定义orm框架中的一个写法
// An highlighted block
protected String saveList(RestTemplate restTemplate, String pass, String addr, Integer port, Class<?> type, MultipartFile multipartFile) throws Exception {
String moduleandDate = RedisUtil.getTable_DsModule(addr, port, pass, type);
List<String> dbList = Arrays.asList(moduleandDate.split(","));
String module = dbList.get(0);
String datasouce = dbList.get(1);
SaveOrUpdateResult saveOrUpdateResult = new SaveOrUpdateResult();
RowCheck rowCheck = new RowCheck();
if (multipartFile == null || multipartFile.getSize() == 0) {
rowCheck.setCheck(false);
saveOrUpdateResult.setData(rowCheck);
rowCheck.getRowMessageList().add("文件上传错误，重新上传");
return objectMapper.writeValueAsString(saveOrUpdateResult);
}
String filename = multipartFile.getOriginalFilename();
if (!(filename.endsWith(".xls") || filename.endsWith(".xlsx"))) {
rowCheck.setCheck(false);
saveOrUpdateResult.setData(rowCheck);
rowCheck.getRowMessageList().add("文件上传格式错误，请重新上传");
return objectMapper.writeValueAsString(saveOrUpdateResult);
}
List<Map<String, Object>> accountMapList = new ArrayList<>();
List<Object> objectList = new ArrayList<>();
if (filename.endsWith(".xls")) {
InputStream inputStream = multipartFile.getInputStream();
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
for(int s = 0; s <workbook.getNumberOfSheets(); s++){
HSSFSheet sheet = workbook.getSheetAt(s);
if(sheet.getLastRowNum()!=0){
String tablename = sheet.getSheetName();
int lastRowNum = sheet.getLastRowNum();//:读取总行数
//从第二行开始，第一行为标题
HSSFRow fieldName = sheet.getRow(0);//第一行的所有信息 隐藏行的字段信息
HashMap<Integer,Object> fieldNameset = new HashMap<>();
// 获取所有的字段 获取要去去重的列
List<Map<Integer, String>> ints = new ArrayList<Map<Integer, String>>();
for (int i = 0; i < fieldName.getPhysicalNumberOfCells(); i++) {
fieldName.getCell(i).getStringCellValue(); //各列的字段名称
List<String> logicIdFieldList = addLogicalJudgmentList(); //获取要去重的字段
if (logicIdFieldList != null && logicIdFieldList.size() > 0) {
// 吧要去重的字段放到一个listmap中
for (int c = 0; c < logicIdFieldList.size(); c++) {
if (fieldName.getCell(i).getStringCellValue().equals(logicIdFieldList.get(c))) {
Map<Integer, String> map = new HashMap<>();
map.put(i, fieldName.getCell(i).getStringCellValue());
ints.add(map);
}
}
}
// 存入所有的字段名
fieldNameset.put(i,fieldName.getCell(i).getStringCellValue());
}
//从第二行开始，第一行为标题
for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
HSSFRow row = sheet.getRow(rowNum);//第一行的所有信息
if (row != null) {
// 获取列数
row.getPhysicalNumberOfCells();
Object object = type.newInstance();
Field field = object.getClass().getDeclaredField("db_id");
field.setAccessible(true);
field.set(object, IdUtil.genSnowflakeId());
Field[] fields = object.getClass().getDeclaredFields();
for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
for (Field fie : fields) {
if ( row.getCell(i)==null){
rowCheck.setCheck(false);
saveOrUpdateResult.setData(rowCheck);
rowNum=rowNum+1;
i=i+1;
rowCheck.getRowMessageList().add("第"+rowNum+"行，第"+i+"列数据为空，请检查");
return objectMapper.writeValueAsString(saveOrUpdateResult);
}
//如果有创建时间就在这加上当前时间
if ("db_createtime".equals(fie.getName())) {
fie.setAccessible(true);
fie.set(object, new Date());
}
if ("db_intime".equals(fie.getName())) {
fie.setAccessible(true);
fie.set(object, new Date());
}
//循环判断要去重的列
for(Map vmap:ints){
Iterator<Integer> it = vmap.keySet().iterator();
while (it.hasNext()) {
Integer value = Integer.valueOf(it.next());
if(value.equals(i)){ //每一行的这列需要进行判断
for(Object objects:objectList){
Field ofield = objects.getClass().getDeclaredField(String.valueOf(vmap.get(value)));
ofield.setAccessible(true);
if(ofield.get(objects).equals(row.getCell(i).getStringCellValue())){
rowCheck.setCheck(false);
saveOrUpdateResult.setData(rowCheck);
rowNum=rowNum+1;
i=i+1;
rowCheck.getRowMessageList().add("第"+rowNum+"行，第"+i+"列数据重复");
return objectMapper.writeValueAsString(saveOrUpdateResult);
}
}
}
}
}
//判断如果字段名相等就赋值
Iterator<Integer> fieldNames = fieldNameset.keySet().iterator(); //遍历所有的字段名
while (fieldNames.hasNext()) {
Integer fieldValue = fieldNames.next();
if (fieldNameset.get(fieldValue).equals(fie.getName())) {
row.getCell(fieldValue).setCellType(CellType.STRING);
fie.setAccessible(true);
fie.set(object, row.getCell(fieldValue).getStringCellValue());
}
}
}
}
//判断数据库中是否有这个数据，如果有提醒
List<String> logicIdFieldList = addLogicalJudgmentList();
if (logicIdFieldList != null && logicIdFieldList.size() > 0) {
String sql = RedisUtil.getTable_SelectCount(addr, port, pass, type);
for (int i = 0; i < logicIdFieldList.size(); i++) {
String fieldss = logicIdFieldList.get(i);
sql += " and " + fieldss + " = ? ";
}
Map<String, String> dataMap = new LinkedHashMap<>();
for (int i = 0; i < logicIdFieldList.size(); i++) {
Object value = ReflectUtil.getFieldValue(object, logicIdFieldList.get(i));
String name = object.getClass().getDeclaredField(logicIdFieldList.get(i)).getType().getName();
Object types = name.substring(name.lastIndexOf(".") + 1);
//获取字段类型
dataMap.put(i + "_" + types.toString().toLowerCase(), String.valueOf(value));
}
String url = "";
ResponseEntity<CommonResult> responseEntity = restTemplate.getForEntity(url, CommonResult.class);
CommonResult commonResult = responseEntity.getBody();
List<Map> results = new ArrayList<>();
if (commonResult.getData() instanceof ArrayList<?>) {
for (Object o : (List<?>) commonResult.getData()) {
results.add(Map.class.cast(o));
}
results.get(0).get("count");
}
if ((int) results.get(0).get("count") > 0) {
rowCheck.getRowMessageList().add("逻辑主键重复");
rowCheck.setCheck(false);
saveOrUpdateResult.setData(rowCheck);
return objectMapper.writeValueAsString(saveOrUpdateResult);
}
}
//吧entity转换成map
objectList.add(object); //redis存入格式
Map<String, Object> accountMap = RedisUtil.getInsertParamMap(addr, port, pass, object);
accountMapList.add(accountMap);
}
}
}
}
} else {
InputStream inputStream = multipartFile.getInputStream();
XSSFWorkbook Workbook = new XSSFWorkbook(inputStream);
// 遍历所有的sheet
for (int s = 0; s < Workbook.getNumberOfSheets(); s++) {
XSSFSheet sheet = Workbook.getSheetAt(s);
if(sheet.getLastRowNum()!=0){
String tablename = sheet.getSheetName();
XSSFRow fieldName = sheet.getRow(0);//第一行的所有信息
HashMap<Integer,Object> fieldNameset = new HashMap<>();
// 获取所有的字段 获取要去去重的列
List<Map<Integer, String>> ints = new ArrayList<Map<Integer, String>>();
for (int i = 0; i < fieldName.getPhysicalNumberOfCells(); i++) {
fieldName.getCell(i).getStringCellValue(); //各列的字段名称
List<String> logicIdFieldList = addLogicalJudgmentList(); //获取要去重的字段
if (logicIdFieldList != null && logicIdFieldList.size() > 0) {
// 吧要去重的字段放到一个listmap中
for (int c = 0; c < logicIdFieldList.size(); c++) {
if (fieldName.getCell(i).getStringCellValue().equals(logicIdFieldList.get(c))) {
Map<Integer, String> map = new HashMap<>();
map.put(i, fieldName.getCell(i).getStringCellValue());
ints.add(map);
}
}
}
// 存入所有的字段名
fieldNameset.put(i,fieldName.getCell(i).getStringCellValue());
}
//从第二行开始，第一行为标题
for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
XSSFRow row = sheet.getRow(rowNum);//第一行的所有信息
if (row != null) {
// 获取列数
row.getPhysicalNumberOfCells();
Object object = type.newInstance();
Field field = object.getClass().getDeclaredField("db_id");
field.setAccessible(true);
field.set(object, IdUtil.genSnowflakeId());
Field[] fields = object.getClass().getDeclaredFields();
for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
for (Field fie : fields) {
if ( row.getCell(i)==null){
rowCheck.setCheck(false);
saveOrUpdateResult.setData(rowCheck);
rowNum=rowNum+1;
i=i+1;
rowCheck.getRowMessageList().add("第"+rowNum+"行，第"+i+"列数据为空，请检查");
return objectMapper.writeValueAsString(saveOrUpdateResult);
}
//如果有创建时间就在这加上当前时间
if ("db_createtime".equals(fie.getName())) {
fie.setAccessible(true);
fie.set(object, new Date());
}
if ("db_intime".equals(fie.getName())) {
fie.setAccessible(true);
fie.set(object, new Date());
}
//循环判断要去重的列
for(Map vmap:ints){
Iterator<Integer> it = vmap.keySet().iterator();
while (it.hasNext()) {
Integer value = Integer.valueOf(it.next());
if(value.equals(i)){ //每一行的这列需要进行判断
for(Object objects:objectList){
Field ofield = objects.getClass().getDeclaredField(String.valueOf(vmap.get(value)));
ofield.setAccessible(true);
if(ofield.get(objects).equals(getCellValue(row.getCell(i)))){
rowCheck.setCheck(false);
saveOrUpdateResult.setData(rowCheck);
rowNum=rowNum+1;
i=i+1;
rowCheck.getRowMessageList().add("第"+rowNum+"行，第"+i+"列数据重复");
return objectMapper.writeValueAsString(saveOrUpdateResult);
}
}
}
}
}
//判断如果字段名相等就赋值
Iterator<Integer> fieldNames = fieldNameset.keySet().iterator(); //遍历所有的字段名
while (fieldNames.hasNext()) {
Integer fieldValue = fieldNames.next();
if (fieldNameset.get(fieldValue).equals(fie.getName())) {
row.getCell(fieldValue).setCellType(CellType.STRING);
fie.setAccessible(true);
fie.set(object, getCellValue(row.getCell(fieldValue)));
}
}
}
}
//判断数据库中是否有这个数据，如果有提醒
List<String> logicIdFieldList = addLogicalJudgmentList();
if (logicIdFieldList != null && logicIdFieldList.size() > 0) {
String sql = RedisUtil.getTable_SelectCount(addr, port, pass, type);
for (int i = 0; i < logicIdFieldList.size(); i++) {
String fieldss = logicIdFieldList.get(i);
sql += " and " + fieldss + " = ? ";
}
Map<String, String> dataMap = new LinkedHashMap<>();
for (int i = 0; i < logicIdFieldList.size(); i++) {
Object value = ReflectUtil.getFieldValue(object, logicIdFieldList.get(i));
String name = object.getClass().getDeclaredField(logicIdFieldList.get(i)).getType().getName();
Object types = name.substring(name.lastIndexOf(".") + 1);
//获取字段类型
dataMap.put(i + "_" + types.toString().toLowerCase(), String.valueOf(value));
}
String url = "";
ResponseEntity<CommonResult> responseEntity = restTemplate.getForEntity(url, CommonResult.class);
CommonResult commonResult = responseEntity.getBody();
List<Map> results = new ArrayList<>();
if (commonResult.getData() instanceof ArrayList<?>) {
for (Object o : (List<?>) commonResult.getData()) {
results.add(Map.class.cast(o));
}
results.get(0).get("count");
}
if ((int) results.get(0).get("count") > 0) {
rowCheck.getRowMessageList().add("逻辑主键重复");
rowCheck.setCheck(false);
saveOrUpdateResult.setData(rowCheck);
return objectMapper.writeValueAsString(saveOrUpdateResult);
}
}
//吧entity转换成map
objectList.add(object); //redis存入格式
Map<String, Object> accountMap = RedisUtil.getInsertParamMap(addr, port, pass, object);
accountMapList.add(accountMap);
}
}
}
}
}
//在这添加
String sql = RedisUtil.getTable_Insert(addr, port, pass, type);
String inserturl = CURDURL + "/curd/ijsondata/updateSql?module=" + module + "&datasource=" + datasouce +
"&sql=" + sql + "&jsonData=" + URLEncoder.encode(objectMapper.writeValueAsString(accountMapList), "utf-8");
ResponseEntity<CommonResult> cresponseEntity = restTemplate.getForEntity(inserturl, CommonResult.class);
CommonResult commonResult = cresponseEntity.getBody();
if (commonResult.isSuccess()) {
RedisUtil.setTableDatas(addr, port, pass, objectList);
return objectMapper.writeValueAsString(saveOrUpdateResult);
} else {
saveOrUpdateResult.setSuccess(false);
saveOrUpdateResult.setMessage(commonResult.getMessage());
return objectMapper.writeValueAsString(saveOrUpdateResult);
}
//return null;
}
1775
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
