java导出Excel文件

• Controller

/**
     * 导出数据到excel表格 
     * @param request
     * @param response
     */
    @RequestMapping("export")
    @ResponseBody
    public void export(HttpServletRequest request, HttpServletResponse response) {
        String tokenEmp = request.getHeader("tokenEmp");
        UserAccount userInfo = userAccountService.getByUserNo(tokenEmp);
        if (null != userInfo && StringUtils.isNotEmpty(userInfo.getButton())) {
            String button = userInfo.getButton();
            if (button.equals("0")) {
                String role = userInfo.getRole();
                RulePropertiesDTO propertiesDTO = new RulePropertiesDTO();
                if (role.equals("pm")) { //设置多个项目 
                    String[] split = ProjectUtil.getString(userInfo);
                    Long userId = userInfo.getId();
                    List<RelPrjUsr> relPrjUsrs = relPrjUsrService.selectByProjectIdAndUserId(null, Long.toString(userId));
                    String projectId = "";
                    for (RelPrjUsr relPrjUsr : relPrjUsrs) {
                        if (StringUtils.isNotEmpty(relPrjUsr.getProjectId())) {
                            projectId = projectId + "," + relPrjUsr.getProjectId();
                        }
                    }
                    projectId = StringUtils.substring(projectId, 1, projectId.length());
                    String[] split = projectId.split(",");
                    propertiesDTO.setProjectIdList(split);
                } else if (role.equals("user")) {
                    propertiesDTO.setCreateBy(tokenEmp);
                }
                List<Map> ruleProperties = iRulePropertiesService.selectExportRuleProperties(propertiesDTO);
                if (CollectionUtil.isNotEmpty(ruleProperties)) {
                    for (Map map : ruleProperties) {
                        String starTime = map.get("start_at_hour").toString() + ":" + map.get("start_at_minute").toString() + ":" + map.get("start_at_second").toString();
                        map.put("startTime", starTime);
                    }
                }
                String fileName = "配置信息记录表-" + System.currentTimeMillis() + ".xls";
                ServletOutputStream servletOutputStream;
                try {
                    HSSFWorkbook wb = ExportExcel.getTurntableRecordNewHSSFWorkbook(ruleProperties);
                    ExportExcel.setResponseHeader(response, fileName);
                    OutputStream os = response.getOutputStream();
                    wb.write(os);
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

import sun.management.resources.agent;
import sun.misc.BASE64Encoder;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

@Slf4j

public class ExportExcel {
    /**
     * @param sheetName 工作表的名字 * @param column 列名 * @param data 需要导出的数据 ( map的键定义为列的名字 一定要和column中的列明保持一致 ) * @param response
     */
    public static void exportExcel(String sheetName, List<String> column, List<Map<String, Object>> data, HttpServletRequest request, HttpServletResponse response) {
        //创建工作薄
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //创建sheet
        HSSFSheet sheet = hssfWorkbook.createSheet(sheetName);
        //表头 HSSFRow headRow = sheet.createRow(0);
        for (int i = 0; i < column.size(); i++) {
            headRow.createCell(i).setCellValue(column.get(i));
        }
        for (int i = 0; i < data.size(); i++) {
            HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            for (int x = 0; x < column.size(); x++) {
                dataRow.createCell(x).setCellValue(data.get(i).get(column.get(x)) == null ? "" : data.get(i).get(column.get(x)).toString());
            }
        }
        response.setContentType("application/vnd.ms-excel");
        try {
            //获取浏览器名称
            String agent = request.getHeader("user-agent");
            String filename = sheetName + ".xls";
            //不同浏览器需要对文件名做特殊处理
            if (agent.contains("Firefox")) {
                //火狐浏览器
                filename = "=?UTF-8?B?" + new BASE64Encoder().encode(filename.getBytes("utf-8")) + "?=";
                filename = filename.replaceAll("\r\n", "");
            } else {
                //IE及其他浏览器
                filename = URLEncoder.encode(filename, "utf-8");
                filename = filename.replace("+", " ");
            }
            //推送浏览器
            response.setHeader("Content-Disposition", "attachment;filename=" + filename);
            hssfWorkbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 发送响应流方法
     *
     * @param response
     * @param fileName
     */
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static HSSFWorkbook getTurntableRecordNewHSSFWorkbook(List<Map> values) {
        String sheetName = "Sheet";
        String[] title = new String[]{"序号", "应用名称", "应用类型", "规则组编号", "检测频度", "检测时间范围（开始）", "检测时间范围（结束）", "时间间隔", "检测执行次数", "告警次数", "通知人员", "mail", "tel", "时效性检测时间维度", "时效性检测SQL", "质量检测时间维度", "质量检测含义", "质量检测SQL", "下边界", "上边界"};
        //第一步，创建一个HSSFWorkbook，对应一个Excel文件 
        HSSFWorkbook wb = new HSSFWorkbook();
        //第二步，在workbook中添加一个sheet, 对应Excel文件中的sheet 
        HSSFSheet sheet = wb.createSheet(sheetName);
        //第三步，在sheet中添加表头第0行, 注意老版本poi对Excel的行数列数有限制 
        HSSFRow row = sheet.createRow(0);
        //第四步，创建单元格，并设置值表头 设置表头居中 
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //创建一个居中格式 声明列对象 
        HSSFCell cell = null;
        //创建标题 
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        int index = 1;
        //记录额外创建的sheet数量 
        int sheetNo = 0;
        //创建内容
        Map entity = null;
        for (int i = 0; i < values.size(); i++) {
            if ((i + 1) % 65535 == 0) {
                sheet = wb.createSheet("Sheet" + sheetNo);
                row = sheet.createRow(0);
                //创建标题 
                for (int j = 0; j < title.length; j++) {
                    cell = row.createCell(j);
                    cell.setCellValue(title[j]);
                    cell.setCellStyle(style);
                }
                sheetNo++;
            }
            entity = values.get(i);
            row = sheet.createRow((i + 1) - (sheetNo * 65535));
            row.createCell(0).setCellValue(index);
            row.createCell(1).setCellValue((String) entity.get("app_info"));
            row.createCell(2).setCellValue((String) entity.get("app_type"));
            row.createCell(3).setCellValue((String) entity.get("rule_group_no"));
            row.createCell(4).setCellValue((String) entity.get("execute_frequency"));
            row.createCell(5).setCellValue((String) entity.get("startTime"));
            row.createCell(6).setCellValue((String) entity.get("end_time"));
            row.createCell(7).setCellValue(entity.get("interval_check_time").toString());
            row.createCell(8).setCellValue((String) entity.get("max_execute_count").toString());
            Map<String, Object> map = SpringUtils.getBean(ICheckPlanService.class).getWarningCountByRuleNo((String) entity.get("rule_no"));
            row.createCell(9).setCellValue(map.get("warningCount") + "");
            List<NoticeMember> noticeMembers = new ArrayList<>();
            try {
                noticeMembers = JSONUtil.toList((String) entity.get("notice_member"), NoticeMember.class);
            } catch (Exception ex) {
                log.error("noticeMember 字段格式错误，解析失败，请检查格式");
            }
            List<String> nameList = new ArrayList<>();
            List<String> recipientsList = new ArrayList<>();
            List<String> telList = new ArrayList<>();
            noticeMembers.forEach(item -> {
                nameList.add(item.getName());
                recipientsList.add(item.getEmail());
                telList.add(item.getTel());
            });
            String noticeMembersName = Joiner.on(", \r\n").join(nameList);
            String noticeMembersTel = Joiner.on(", \r\n").join(telList);
            String recipients = Joiner.on(", \r\n").join(recipientsList);
            row.createCell(10).setCellValue(noticeMembersName);
            row.createCell(11).setCellValue(recipients);
            row.createCell(12).setCellValue(noticeMembersTel);
            row.createCell(13).setCellValue(entity.get("date_latitude").equals("0") ? "昨天" : "当天");
            row.createCell(14).setCellValue(entity.get("check_sql") + "");
            row.createCell(15).setCellValue(entity.get("sdatelatitude") + "");
            row.createCell(16).setCellValue(entity.get("check_comment") + "");
            row.createCell(17).setCellValue(entity.get("schecksql") + "");
            row.createCell(18).setCellValue(entity.get("lower_boundary") + "");
            row.createCell(19).setCellValue(entity.get("upper_boundary") + "");
            index++;
        }
        return wb;
    }
}