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);
}