Java中POI的使用（处理Excel文件）

1.Maven依赖下载

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

2.前端vue代码（可以先不传参数，参数是为了后期模糊查询及筛选导出的功能）

 <el-button type="success" @click="handleExport()">导出</el-button>  

handleExport(){
    axios
        .post(
          "/api/file/daochu",
          {
            status: this.orderStatus,
            qname:this.qname,
            qpositionList: this.checkedGrades,
          },
          {
            responseType: "blob",
          }
        )
        .then((response) => {
          console.log("后台API调用成功:", response);
          let url = window.URL.createObjectURL(
            new Blob([response.data], {
              type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            })
          );
          var a = document.createElement("a");
          document.body.appendChild(a);
          a.href = url;
          a.download = "导出文件.xlsx";
          a.click();
          setTimeout(() => {
            window.URL.revokeObjectURL(url);
            document.body.removeChild(a);
          }, 0);
        })
        .catch((error) => {
          console.error("导出错误:", error);
          // 错误处理...
        });
  },

2.后端控制层代码（写在你的controller里面）

@Autowired
    private FileService fileService;

 @PostMapping("/file/daochu")
    public ResponseEntity<Integer> handleSmallDaochuRequest(@RequestBody SmallDaochuRequest request, HttpServletResponse response) {
        // 处理接收到的数据
        String status = request.getStatus();
        String qname = request.getQname();
        List<String> qpositionList = request.getQpositionList();
        excelBigDaochu(response);//在下面定义的方法
        return ResponseEntity.status(HttpStatus.OK).body(36);
        
    }
    private void excelBigDaochu(HttpServletResponse response) {
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/模板.xlsx");//定义一个模板文件，你可以在文件中预设置一些样式或标题，也可以是空的
        try {
            //获取excel对象
            XSSFWorkbook excel=new XSSFWorkbook(in);
            //定义sheet页名称和对应的status值的映射关系
            Map<String, Integer> sheetMap = new HashMap<>();
            sheetMap.put("第一页", 2);
            sheetMap.put("第二页", 3);
            //遍历映射关系，填充数据到对应的sheet页
            for (Map.Entry<String, Integer> entry : sheetMap.entrySet()) {
                String sheetName = entry.getKey();
                int status = entry.getValue();
                //获取数据
                List<CompleteEx> dataList = fileService.getByStatus(status);
                //获取sheet页
                XSSFSheet sheet = excel.getSheet(sheetName);
                //填充数据
                XSSFRow row ;
                int i=1;
                for (CompleteEx completeEx : dataList){
                    //获得某一行
                    row=sheet.getRow(i)!= null ? sheet.getRow(i) : sheet.createRow(i);
                    // 填充数据到单元格
                    createCellIfNotExists(sheet, row, 0).setCellValue(completeEx.getQname());//方法在下面定义，为了防止空值报错
                    createCellIfNotExists(sheet, row, 1).setCellValue(completeEx.getQposition());
                    createCellIfNotExists(sheet, row, 2).setCellValue(completeEx.getNumber());
                    createCellIfNotExists(sheet, row, 3).setCellValue(completeEx.getDate());
                    createCellIfNotExists(sheet, row, 4).setCellValue(completeEx.getFname());
                    createCellIfNotExists(sheet, row, 
                    i=i+1;


                }

            }

            //通过输出流将Excel下载到客户端浏览器
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);
            //关闭资源
            out.close();
            excel.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }
   private XSSFCell createCellIfNotExists(XSSFSheet sheet,XSSFRow row, int columnIndex) {
        XSSFCell cell = row.getCell(columnIndex);
        // 获取第二行的样式
        XSSFRow secondRow = sheet.getRow(1);
        XSSFCellStyle style = secondRow.getCell(columnIndex).getCellStyle();
        if (cell == null) {
            cell = row.createCell(columnIndex);
            // 应用样式到单元格
            cell.setCellStyle(style);
        }
        return cell;
    }

3.编写后端业务层代码

 public List<CompleteEx> getByStatus(Integer status) {
        List<CompleteEx> list=fileMapper.getByStatus(status);
        return list;
    }

4.mapper代码

 @Select("select * from EQ where status=#{status}")
    List<CompleteEx> getByStatus(Integer status);