Excel导出功能实现

本是古典何须时尚 2023-01-05 12:15 236阅读 0赞

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

pom添加依赖

<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][http_localhost_8080_exportExcel]  导出结果如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMDM3NDc4_size_16_color_FFFFFF_t_70][]

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

[http_localhost_8080_exportExcel]: http://localhost:8080/exportExcel
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMDM3NDc4_size_16_color_FFFFFF_t_70]: /images/20221119/e13b1700ba724235aaf4de88063da02c.png