用poi-ooxml导出/读取Excel文件

拼搏现实的明天。 2024-03-22 19:26 37阅读 0赞

### 使用依赖 ###

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.2</version>
    </dependency>

### 自定义注解 ###

注解使用在实体类上，表示哪些字段的值需要到处到 excel 表格中

@Target(ElementType.FIELD)
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    public @interface ExcelHeader {
        
        /**
         * 表头
         * @return
         */
        String value() default "";
        /**
         * 列索引
         * @return
         */
        int columnIndex() default 0;
    }

### 实体类 ###

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class User {
        
    
        @ExcelHeader(value = "账号")
        private String username;
    
        @ExcelHeader(value = "密码")
        private String password;
    
        @ExcelHeader(value = "生日")
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        private LocalDateTime birthday;
    
        // 该字段没有添加注解, Excel不导出
        private Integer age;
    
        public User(String username, String password, LocalDateTime birthday) {
        
            this.username = username;
            this.password = password;
            this.birthday = birthday;
        }
    }

### 导出代码 ###

@Slf4j
    class IdcPoiApplicationTests {
        
    
        @Test
        void contextLoads() throws NoSuchFieldException, IllegalAccessException, IOException {
        
            // 创建模拟数据
            User user1 = new User("admin", "123456", LocalDateTime.now());
            User user2 = new User("test", "123456", LocalDateTime.now());
            List<User> users = Arrays.asList(user1, user2);
    
            // 文件路径
            String basePath =  "/Users/cuishiying/Documents/spring-cloud-learning/idc-poi/oss/";
            String fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls";
    
            // 将Excel写入文件
            HSSFWorkbook workbook = exportExcel(users, User.class);
            workbook.setSheetName(0,"sheetName");//设置sheet的Name
    
            // 无论是通过HttpServletResponse导出还是导出到本地磁盘,本质都是IO操作，所以这里将IO操作提取到外层。
            workbook.write(new File(basePath + File.separator + fileName));
        }
    
        /**
         *
         * @param data 需要导出的数据
         * @param clz 数据对应的实体类
         * @param <T> 泛型
         * @return Excel文件
         * @throws NoSuchFieldException
         * @throws IllegalAccessException
         */
        public <T> HSSFWorkbook exportExcel(List<T> data, Class<T> clz) throws NoSuchFieldException, IllegalAccessException {
        
    
            Field[] fields = clz.getDeclaredFields();
            List<String> headers = new LinkedList<>();
            List<String> variables = new LinkedList<>();
    
            // 创建工作薄对象
            HSSFWorkbook workbook=new HSSFWorkbook();//这里也可以设置sheet的Name
            // 创建工作表对象
            HSSFSheet sheet = workbook.createSheet();
            // 创建表头
            Row rowHeader = sheet.createRow(0);
    
            // 表头处理
            //根据实体类的自定义注解来创建表头值
            for (int h = 0; h < fields.length; h++) {
        
                Field field = fields[h];
                if (field.isAnnotationPresent(ExcelHeader.class)) {
        
                    // 表头
                    ExcelHeader annotation = field.getAnnotation(ExcelHeader.class);
                    headers.add(annotation.value());
                    rowHeader.createCell(h).setCellValue(annotation.value());
    
                    // 每个字段名
                    variables.add(field.getName());
                }
            }
    
            // 数据处理
            for (int i = 0; i < data.size() ; i++) {
        
    
                //创建工作表的行(表头占用1行, 这里从第二行开始)
                HSSFRow row = sheet.createRow(i + 1);
                // 获取一行数据
                T t = data.get(i);
                Class<?> aClass = t.getClass();
                // 填充列数据
                for (int j = 0; j < variables.size(); j++) {
        
    
                    Field declaredField = aClass.getDeclaredField(variables.get(j));
                    declaredField.setAccessible(true);
    
                    String key = declaredField.getName();
                    Object value = declaredField.get(t);
    
                    row.createCell(j).setCellValue(value.toString());
                }
            }
            log.info("Excel文件创建成功");
            return workbook;
        }
    
    }

### 读取 excel ###

File file = new File("C:\\user\\desktop\\文件.xlsx");
        XSSFWorkbook sheets= new XSSFWorkbook(new FileInputStream());
        //System.out.println(sheets.getNumberOfSheets());  获取总sheet表个数
        for(int i = 0; i < sheets.getNumberOfSheets(); i ++){
        
        	//获取单个sheet
        	XSSFSheet sheet = sheets.getSheetAt(i);
        	//获取首行
        	XSSFRow title = sheet.getRow(0);
        	
        	for(int j = 1; j < sheet.getPhysicalNumberOfRows(); j ++){
        
        		//获取单行
        		XSSFRow row = sheet.getRow(j);
        		//获取单个单元格
        		XSSFCell cell= row.getCell(0);
        		//设置单元格格式
        		cell.setCellType(CellType.STRING);
        		
        		...对单元格进行操作
    	    }
        }