springboot中批量导入excel中数据到数据库

1、在pom.xml中添加依赖:

2、添加元注解:

@Target({TYPE, ElementType.FIELD})
@Retention(RUNTIME)
public @interface Description {

String showName() default "";
}

3、添加一个exceldata的对象:

@Description(showName = "编号")
private String id;

@Description(showName = "用户名称")
private String name;

@Description(showName = "用户年龄")
private String age;

 

4、编写一个ExcelUtil:

public static final String XLS = ".xls";
public static final String XLSX = ".xlsx";

public <T> List<T> readExcelFileToDTO(MultipartFile file, Class<T> clazz) throws IOException {
return readExcelFileToDTO(file, clazz, 0);
}

public <T> List<T> readExcelFileToDTO(MultipartFile file, Class<T> clazz, Integer sheetId) throws IOException {
//将文件转成workbook类型
Workbook workbook = buildWorkbook(file);
//第一个表
return readSheetToDTO(workbook.getSheetAt(sheetId), clazz);
}

public <T> List<T> readSheetToDTO(Sheet sheet, Class<T> clazz) throws IOException {
List<T> result = new ArrayList<>();
List<Map<String, String>> sheetValue = changeSheetToMapList(sheet);
for (Map<String, String> valueMap : sheetValue) {
T dto = buildDTOByClass(clazz, valueMap);
if (dto != null) {
result.add(dto);
}
}
return result;
}

//类型转换
private Workbook buildWorkbook(MultipartFile file) throws IOException {
String filename = file.getOriginalFilename();
if (filename.endsWith(XLS)) {
return new HSSFWorkbook(file.getInputStream());
} else if (filename.endsWith(XLSX)) {
return new XSSFWorkbook(file.getInputStream());
} else {
throw new IOException("unknown file format: " + filename);
}
}

private List<Map<String, String>> changeSheetToMapList(Sheet sheet) {
List<Map<String, String>> result = new ArrayList<>();
int rowNumber = sheet.getPhysicalNumberOfRows();
String[] titles = getSheetRowValues(sheet.getRow(0)); // 第一行作为表头
for (int i = 1; i < rowNumber; i++) {
String[] values = getSheetRowValues(sheet.getRow(i));
Map<String, String> valueMap = new HashMap<>();
for (int j = 0; j < titles.length; j++) {
valueMap.put(titles[j], values[j]);
}
result.add(valueMap);
}
return result;
}

private <T> T buildDTOByClass(Class<T> clazz, Map<String, String> valueMap) {
try {
T dto = clazz.newInstance();
for (Field field : clazz.getDeclaredFields()) {
Description desc = field.getAnnotation(Description.class);
String value = valueMap.get(desc.showName());
if (value != null) {
Method method = clazz.getMethod(getSetMethodName(field.getName()), field.getType());
method.invoke(dto, value);
}
}

return dto ;
} catch (Exception e) {
e.getStackTrace();
}
return null;
}

private String getSetMethodName(String name) {
String firstChar = name.substring(0, 1);
return "set" + firstChar.toUpperCase() + name.substring(1);
}

private String[] getSheetRowValues(Row row) {
if (row == null) {
return new String[]{};
} else {
int cellNumber = row.getLastCellNum();
List<String> cellValueList = new ArrayList<>();
for (int i = 0; i < cellNumber; i++) {
cellValueList.add(getValueOnCell(row.getCell(i)));
}
return cellValueList.toArray(new String[0]);
}
}

private String getValueOnCell(Cell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellTypeEnum()) {
case STRING: return cell.getStringCellValue();
case NUMERIC: return String.format("%.2f", cell.getNumericCellValue());
case BOOLEAN: return cell.getBooleanCellValue() ? "true" : "false";
case FORMULA:
try {
return cell.getStringCellValue();
} catch (Exception e) {
return String.format("%.2f", cell.getNumericCellValue());
}
default: return "";
}
}

5、导入的方法:

public void uploadUserInfo(MultipartFile file) throws Exception {
List<UserDTO> dtoList = excelService.readExcelFileToDTO(file, UserDTO.class);
System.out.println("长度:"+dtoList.size());
List<User> userList = dtoList.stream().map(dto -> {
User user = userRepository.findUserByName(dto.getName()).orElse(null);
if(user == null) {
user = new User();
}
user.setId(dto.getId());
user.setName(dto.getName());
user.setAge(dto.getAge());
return user;
}).collect(Collectors.toList());
userRepository.saveAll(userList);
}

 

posted on   mshfx  阅读(4908)  评论(0编辑  收藏  举报

编辑推荐:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
阅读排行:
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 推荐几款开源且免费的 .NET MAUI 组件库
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· 一个费力不讨好的项目,让我损失了近一半的绩效!
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

导航

统计

点击右上角即可分享
微信分享提示