springboot 读写excel

添加两个坐标:

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

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

<!-- https://mvnrepository.com/artifact/dom4j/dom4j -->
<dependency>
    <groupId>dom4j</groupId>
    <artifactId>dom4j</artifactId>
    <version>1.6.1</version>
</dependency>

 response.setContentType("application/excel");

        List<User> list = userService.selecetAll();

        String fileName = "user.xls";
        // 设置响应头
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8"));
        // 创建用户表
        HSSFWorkbook excel = new HSSFWorkbook();
        HSSFSheet sheet = excel.createSheet();
        // 声明每0行的索引
        int rowNum = 0;
        // 创建标题
        String[] headers = {"用户名", "年龄", "邮箱"};
        // 创建第一行
        HSSFRow row = sheet.createRow(rowNum);
        // 将标题放入进去
        for (int i = 0; i < headers.length; i++) {
            // 创建一个单元格
            HSSFCell cell = row.createCell(i);
            // 将数据转换为字符串
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            // 将数据放入进去
            cell.setCellValue(text);
        }
        /**
         * 将结果返回
         */

        // 将用户数据放入进去
        for (User u : list) {
            ++rowNum;
            HSSFRow row1 = sheet.createRow(rowNum);
            row1.createCell(0).setCellValue(u.getUsername());
            row1.createCell(1).setCellValue(u.getAge());
            row1.createCell(2).setCellValue(u.getEmail());
        }
        excel.write(response.getOutputStream());

读excel

@RequestMapping("readfile")
public String readFile() {
    try {
        FileInputStream fileInputStream = new FileInputStream(new File("D:\\work_place.xlsx"));
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
        if (xssfWorkbook != null) {
            // 获取sheet个数
            // 获取第一个Sheet 索引为０
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
            // 获取总共有多少行
            List<User> list = new ArrayList<>();
            for (int i = 0; i < sheet.getLastRowNum(); i++) {
                XSSFRow row = sheet.getRow(i);
                if (row != null) {
                    User user = new User();
                    for (int k = 0; k < row.getLastCellNum(); k++) {
                        if (row.getCell(0) != null) {
                            user.setUsername(row.getCell(0).toString());
                        }

                        if (row.getCell(1) != null) {
                            user.setAge(10);
                        }

                        if (row.getCell(2) != null) {
                            user.setEmail(row.getCell(2).toString());
                        }
                    }
                    list.add(user);
                } else {
                    System.out.println("null");
                }

            }
            for (User user : list) {
                System.out.println(user);
            }
        }
        return "test";
    } catch (IOException e) {
        e.printStackTrace();
    }
    return "test";
}

　　

转载于:https://www.cnblogs.com/leigepython/p/10150823.html