Excel导出功能实现

应业务需求，需要添加用户导出功能，功能测试通过后这里做个记录。

pom添加依赖

        <!-- excel导入相关jar包 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10.1</version>
        </dependency>

实体类

public class TUser {
    private String userName;
    private String loginName;
    private String password;
    private String iphone;
    private String remark;
    private String userEmail;

    // 这里省略get、set方法
}

Controller方法

    /**
     * 导出
     */
    @RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
    @ResponseBody
    public void exportExcel(HttpServletResponse response, HttpServletRequest request) {
        Calendar now = new GregorianCalendar();
        int year = now.get(Calendar.YEAR);
        int month = now.get(Calendar.MONTH) + 1;
        int day = now.get(Calendar.DAY_OF_MONTH);
        String auctionDay = year + "年" + month + "月" + day + "日";
        try {
            OutputStream os = response.getOutputStream();// 取得输出流
            response.reset();// 清空输出流
            response.setContentType("application/msexcel");// 定义输出类型
            // 导出文件名称
            String filename = "导出用户目录_" + auctionDay + ".xlsx";
            response.setHeader("Content-disposition",
                    "attachment; filename=" + new String(filename.getBytes("GB2312"), "ISO_8859_1"));
            Workbook wb = new XSSFWorkbook();
            this.export(wb, auctionDay);
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void export(Workbook wb, String auctionDay) {
        String title = "用户目录-" + auctionDay;
        // Excel左下角的Sheet菜单
        Sheet sheet = wb.createSheet("用户目录");
        CellStyle style = wb.createCellStyle();
        // 设置边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        // 设置居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        // 加粗样式
        CellStyle styleBold = wb.createCellStyle();
        styleBold.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        styleBold.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        styleBold.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        styleBold.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        styleBold.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleBold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font font = wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);// 加粗字体
        styleBold.setFont(font);

        short rowIndex = 0;
        short cellIndex = 0;
        Row row = sheet.createRow(rowIndex);
        row.setHeightInPoints(24);
        // 第一行内容（title）
        Cell cell = row.createCell(cellIndex);cell.setCellStyle(styleBold);cell.setCellValue(title);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
        cell.getSheet().setColumnWidth(1, 10 * 256);
        cell.getSheet().setColumnWidth(2, 16 * 256);
        cell.getSheet().setColumnWidth(3, 20 * 256);
        cell.getSheet().setColumnWidth(4, 15 * 256);
        cell.getSheet().setColumnWidth(5, 23 * 256);
        cell.getSheet().setColumnWidth(6, 25 * 256);
        rowIndex++;
        cellIndex = 0;
        row = sheet.createRow(rowIndex);
        row.setHeightInPoints(24);
        cell = row.createCell(cellIndex++);cell.setCellStyle(styleBold);cell.setCellValue("登录名");
        cell = row.createCell(cellIndex++);cell.setCellStyle(styleBold);cell.setCellValue("用户名");
        cell = row.createCell(cellIndex++);cell.setCellStyle(styleBold);cell.setCellValue("密码");
        cell = row.createCell(cellIndex++);cell.setCellStyle(styleBold);cell.setCellValue("手机号");
        cell = row.createCell(cellIndex++);cell.setCellStyle(styleBold);cell.setCellValue("电子邮件");
        cell = row.createCell(cellIndex++);cell.setCellStyle(styleBold);cell.setCellValue("备注");

        List<TUser> tUsers = new ArrayList<>();
        TUser tUser = new TUser();
        tUser.setLoginName("223");
        tUser.setUserName("234234");
        tUser.setPassword("2344444444443");
        tUser.setUserEmail("234242342QQEEE");
        tUser.setIphone("137445433453");
        tUser.setRemark("shfhaskfhasjhfasjkhfsklddg");
        tUsers.add(tUser);
        TUser tUser1 = new TUser();
        tUser1.setLoginName("223");
        tUser1.setUserName("234234");
        tUser1.setPassword("2344444444443");
        tUser1.setUserEmail("234242342QQEEE");
        tUser1.setIphone("137445433453");
        tUser1.setRemark("shfhaskfhasjhfasjkhf33333sklddg");
        tUsers.add(tUser1);
        if (tUsers != null && tUsers.size() > 0) {
            for (TUser vo : tUsers) {
                rowIndex++;
                cellIndex = 0;
                row = sheet.createRow(rowIndex);
                row.setHeightInPoints(24);
                cell = row.createCell(cellIndex++);cell.setCellStyle(style);cell.setCellValue(vo.getLoginName());
                cell = row.createCell(cellIndex++);cell.setCellStyle(style);cell.setCellValue(vo.getUserName());
                cell = row.createCell(cellIndex++);cell.setCellStyle(style);cell.setCellValue(vo.getPassword());
                cell = row.createCell(cellIndex++);cell.setCellStyle(style);cell.setCellValue(vo.getIphone());
                cell = row.createCell(cellIndex++);cell.setCellStyle(style);cell.setCellValue(vo.getUserEmail());
                cell = row.createCell(cellIndex++);cell.setCellStyle(style);cell.setCellValue(vo.getRemark());
            }
        }
    }

请求导出地址：http://localhost:8080/exportExcel  导出结果如下

至此，用户导出功能就完成了！