您现在的位置是：首页 > java技术交流java技术交流

springboot 使用Apache POI Excel文件读取和写入

上善若水2021-01-16 11:29:49【java技术交流】 3969人已围观

简介Excel作为常用的办公文件,存储数据十分便捷,工作中也是大范围使用.在搜索找到最广泛的处理excel文件的包还Apache的工具包!试试excel在web项目中的基本使用吧! 基本工作准备一个基本

Excel作为常用的办公文件,存储数据十分便捷,工作中也是大范围使用.在搜索找到最广泛的处理excel文件的包还Apache的工具包!
试试excel在web项目中的基本使用吧!

基本工作准备一个基本的springboot项目略

导包

导入最新org.apache.poi 目前最新的是4.1.2

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

编写简单页excel.html面测试上传和下载

<!DOCTYPE html>
<html lang="zh-cn">
<head>
    <meta charset="UTF-8">
    <title>测试上传demo</title>
</head>
<body>
<form action="/uploadExcel" method="post" enctype="multipart/form-data">
    <input type="file" name="file">
    <input type="submit" value="提交">
</form>
<a href="/downExcel">导出excel</a>
</body>
</html>

编写控制器方法

@GetMapping("/excel")
    public String excel() {
        return "excel";
    }


    @GetMapping("/downExcel")
    public String downExcel(HttpServletResponse response) throws IOException {
        String[] head = {"头1", "头1", "头3"};
        List<String[]> values = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            values.add(new String[]{"php最好的语言" + i, "java天下第一" + i, "python我年轻我骄傲" + i});
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue(head[0]);
        row.createCell(1).setCellValue(head[1]);
        row.createCell(2).setCellValue(head[2]);
        for (int i = 1; i <= values.size(); i++) {
            row = sheet.createRow(i);
            for (int j = 0; j < values.get(i - 1).length; j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(values.get(i - 1)[j]);
            }
        }
        workbook.setActiveSheet(0);
        response.setCharacterEncoding("utf-8");
        response.setContentType("multipart/form-data");
        response.setHeader("Content-Disposition", "attachment;fileName=temp.xlsx");
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        return null;
    }

    @PostMapping("/uploadExcel")
    @ResponseBody
    public List uploadExcel(@RequestParam(value = "file") MultipartFile file) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
        //获取工作表
        HSSFSheet sheet = workbook.getSheetAt(0);
        List<List<String>> data=new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i < lastRowNum; i++) {
            //获取指定行
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<String> rowList=new ArrayList<>();
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                HSSFCell cell = row.getCell(j);
                rowList.add(cell.getStringCellValue());
            }
            data.add(rowList);
        }
        workbook.close();
        return data;
    }