Java通过POI将表数据导出为Excel

青旅半醒 2021-12-15 00:21 302阅读 0赞

前段时间项目需要将用户操作日志表导出为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>3.17</version>
    </dependency>

controller层：

@GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException { 
        operationLogService.export(response);
    }

service层：

public void export(HttpServletResponse response) throws IOException { 
        HSSFWorkbook workbook = new HSSFWorkbook();
        //sheet名称
        HSSFSheet sheet = workbook.createSheet("用户操作日志表");
        //获取表数据，根据自己实际情况获取
        List<OperationLog> logList = operationLogDao.listAll();
        //设置要导出的文件的名字
        String fileName = "用户操作日志" + ".xls";
        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        String[] headers = { "id", "模块", "姓名", "访问时间", "IP地址", "操作内容"};
        //headers表示excel表中第一行的表头
        HSSFRow row = sheet.createRow(0);
        //在excel表中添加表头
        for (int i = 0; i < headers.length; i++) { 
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //在表中存放查询到的数据放入对应的列
        for (OperationLog operationLog : logList) { 
            HSSFRow row1 = sheet.createRow(rowNum);
            row1.createCell(0).setCellValue(operationLog.getId());
            row1.createCell(1).setCellValue(operationLog.getModule());
            row1.createCell(2).setCellValue(operationLog.getUserName());
            row1.createCell(3).setCellValue(format.format(operationLog.getLogTime()));
            row1.createCell(4).setCellValue(operationLog.getIp());
            row1.createCell(5).setCellValue(operationLog.getContent());
            rowNum++;
        }
        //设置自动列宽
        for (int i = 0; i < headers.length; i++) { 
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 13 / 10);
        }
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        workbook.write(response.getOutputStream());
    }

最后直接用浏览器调即可。