Springboot Excel导入导出

1. 引入依赖

添加hutools工具和poi

<!--pom添加-->  
<dependency>  
    <groupId>cn.hutool</groupId>  
    <artifactId>hutool-all</artifactId>  
    <version>5.8.7</version>  
</dependency>  
  
<!-- excel工具 -->  
<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi-ooxml</artifactId>  
    <version>4.1.2</version>  
</dependency>

2. controller

  
@RestController  
@RequestMapping("/user")  
public class ImportController {  
    @Autowired  
    private ImportService importService;  

    @PostMapping("/import")  
    public String importExcel(MultipartFile file){  
        importService.importExcel(file);  
        return "请求成功";  
    } 
    @GetMapping("/download")  
    public void download(HttpServletResponse response){  
        importService.download(response);  
    } 
}

3.简单注解(用来标注字段在Excel的第几行,从0开始)

import java.lang.annotation.*;  
  
@Target({ElementType.PARAMETER, ElementType.FIELD})  
@Retention(RetentionPolicy.RUNTIME)  
@Documented  
public @interface ExcelColumn {  
    public int col();  
}

4.实体类

  
@Data  
public class UserEntity {  
    @ExcelColumn(col = 0)  
    private String name;  
    @ExcelColumn(col = 1)  
    private String sex;  
    @ExcelColumn(col = 2)  
    private int age;  
    @ExcelColumn(col = 3)  
    private Float height;  
    @ExcelColumn(col = 4)  
    private Date birth;
    }

5.service

  
@Service  
public class UserServiceImpl implements UserService {  
    @Autowired  
    private UserEntityMapper mapper;  

    @Override  
    @SneakyThrows  
    public void importExcel(MultipartFile multipartFile) {  
        ExcelReader reader = ExcelUtil.getReader(multipartFile.getInputStream(),0);  
        // 去掉2行标题  
        List<List<Object>> dataList = reader.read(2);  
        List<UserEntity> userList = PHExcelUtil.excelToList(dataList,UserEntity.class);  
        // 获取excel的每行数据  
        userList.forEach(user->{  
            mapper.insert(user);  
        });  
    }  
   
   @SneakyThrows  
    public void download(HttpServletResponse response) {  
        String filePath = Objects.requireNonNull(Thread.currentThread().getContextClassLoader().getResource("template")).getPath()+"/header.xlsx";  
        File file = ObjectUtil.cloneByStream(new File(filePath));  
        ExcelWriter writer = ExcelUtil.getWriter(file);  
        writer.setSheet(1);  
        // 跳过2行 
        writer.passRows(2);  
        List<UserEntity> list = mapper.list();  
        List<List<String>> rows = PHExcelUtil.getRowsList(list,80, UserEntity.class);  
        writer.write(rows);  
        // 下载  
        response.setContentType("application/octet-stream");  
        String fileName = URLEncoder.encode("文件名称.xlsx", "UTF8");  
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);  
        OutputStream out= response.getOutputStream();  
        writer.flush(out,true);  
        writer.close();  
        IoUtil.close(out);  
    }
}

6.工具类

public class PHExcelUtil {  
  
    @SneakyThrows  
    public static <T> List<T> excelToList(List<List<Object>> dataList, Class<T> clazz){  
        List<T> list = new ArrayList<>();  
        // 遍历每行数据  
        for (List<Object> columns : dataList) {  
            // 创建接收泛型  
            T obj = clazz.newInstance();  
            for (Field field : clazz.getDeclaredFields()) {  
                if (field.isAnnotationPresent(ExcelColumn.class)) {  
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);  
                int col = annotation.col();  
                if (col<columns.size()) {  
                    String method = "set"+ Character.toUpperCase(field.getName().charAt(0))+field.getName().substring(1);  
                    clazz.getMethod(method,field.getType()).invoke(obj, convertProperties(field.getType(),columns.get(col)));  
                    }  
                }  
            }  
            list.add(obj);  
        }  
        return list;  
    }  
    private static Object convertProperties(Class<?> type, Object obj) {  
        if (ObjectUtil.isEmpty(obj)) {  
            return null;  
        }  
        if (type == int.class || type == Integer.class) {  
            return Integer.parseInt(obj.toString());  
        }  
        if (type == float.class || type == Float.class){  
            return Float.parseFloat(obj.toString());  
        }  
        if (type == double.class || type ==Double.class){  
            return Double.parseDouble(obj.toString());  
        }  
        if (type == Date.class){  
            return DateUtil.parse(obj.toString());  
        } 
        return obj.string;  
    }  
    
    @SneakyThrows  
    public static <T> List<List<String>> getRowsList(List<T> dataList,Integer columnSize, Class<T> clazz){  
        List<List<String>> rows = new ArrayList<>();  
        for (T obj : dataList) {  
            List<String> daList = new ArrayList<>(Arrays.asList(new String[columnSize]));  
            clazz.newInstance();  
            for (Field field : clazz.getDeclaredFields()) {  
                if (field.isAnnotationPresent(ExcelColumn.class)) {  
                    int col = field.getAnnotation(ExcelColumn.class).col();  
                    if (col<daList.size()) {  
                        String methodName = "get"+Character.toUpperCase(field.getName().charAt(0))+field.getName().substring(1);  
                        Object invoke = clazz.getMethod(methodName).invoke(obj);  
                        if (ObjectUtil.isNotEmpty(invoke)) {  
                            daList.set(col,invoke.toString());  
                        }  
                    }  
                }  
            }  
            rows.add(daList);  
        }  
        return rows;  
    }
}

7.测试的excel