java中导入导出excel表格

一、导入maven依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>

二、excel导出

1、写个user实体类

@Data
@ToString(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {

    private String username;
    private Long id;
    private Date date;

}

2、测试excel导出

    @Test
    public void testExcel2() throws IOException {

        ArrayList<User> users = new ArrayList<>();
        for (int i = 1; i <= 15; i++) {
            User user = new User();
            user.setId((long)i);
            user.setUsername("小明"+ i);
            user.setDate(new Date());
            users.add(user);
        }

        //创建工作薄对象
        HSSFWorkbook workbook=new HSSFWorkbook();//这里也可以设置sheet的Name
        //创建工作表对象
        HSSFSheet sheet = workbook.createSheet();
        //创建工作表的行   添加表头
        HSSFRow rowT = sheet.createRow(0);
        int a = 0;
        rowT.createCell(a++).setCellValue("id");
        rowT.createCell(a++).setCellValue("用户名");
        rowT.createCell(a++).setCellValue("创建时间");
        int i = 1;
        /*遍历集合，添加到每一行*/
        for (User user : users) {
            HSSFRow row = sheet.createRow(i++);
            int j = 0;
            row.createCell(j++).setCellValue(user.getId());
            row.createCell(j++).setCellValue(user.getUsername());
            row.createCell(j++).setCellValue(user.getDate());
        }
        //文档输出
        FileOutputStream out = new FileOutputStream("C:\\Users\\admin\\Desktop\\abc\\a.xls");
        workbook.write(out);
        out.close();
    }

 

三、excel导入

@Test
    public void readExcel() {
        List<User> list = new ArrayList<User>();
        HSSFWorkbook workbook = null;

        try {
            // 读取Excel文件
            InputStream inputStream = new FileInputStream("C:\\Users\\admin\\Desktop\\abc\\a.xls");
            workbook = new HSSFWorkbook(inputStream);
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 循环工作表
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }

                // 将单元格中的内容存入集合
                User user = new User();
                int i = 0;
                HSSFCell cell = hssfRow.getCell(i++);
                if (cell != null) {
                    user.setId((long)cell.getNumericCellValue());
                }

                cell = hssfRow.getCell(i++);
                if (cell != null) {
                    user.setUsername(cell.getStringCellValue());
                }

                cell = hssfRow.getCell(i++);
                if (cell != null) {
                    user.setDate(cell.getDateCellValue());
                }
                list.add(user);
            }
        }
        System.out.println(list);
    }

运行测试