效果图:
生成如图所示的excel文档,合并第一行单元格,动态生成第二行时间点单元格数据,不多说,上干货:
/** * 导出行业信用利差历史范围数据 * * @param studyType 研究类型 5 * @param industry 行业 1,2 行业为一个值,数组取最后一个值,3.4为多个二级行业值数组 * @param rateType 评级类型,1: 内部评级,2:外部评级,3:隐含评级 * @param rate 评级数组,以逗号隔开,直接放到in里面 * @param bondType 债券类型 * @param termtomaturity 债券到期期限,开始值和结束值用逗号相连成字符串 * @param isWeight 是否含权 0 不含权, 1 含权 , 2 所有 * @param time 取样时间 * @param startTime 开始时间 只有1有 * @param endTime 结束时间 只有1有 * @param exchangecode 交易市场, 银行间:113 , 交易市场:101,105 * @return java.util.Map<java.lang.String,java.lang.Object> * @author xj * @date 2017/10/24 18:27 */ private Map<String, Object> exportHandleDataChart5(Integer studyType, String[] industry, Integer rateType, String[] rate, String[] bondType, String termtomaturity, Integer isWeight, String time, String startTime, String endTime, String[] exchangecode) { // 返回结果集 Map<String, Object> resultMap = new HashMap<>(); // 时间参数间隔日期 Integer intervalDays = 7; // 行业参数异常抛出 if (industry == null || industry.length == 0) { throw new ServiceException(ResultEnum.INDUSTRY_PARAMETER_ERROR); } // 指定日期内所有时间集合 List<String> days = Util.getPerDaysByStartAndEndDate(startTime, endTime, "yyyy-MM-dd"); // 时间参数异常抛出 if (days == null) { throw new ServiceException(ResultEnum.DATE_PARAMETER_ERROR); } // excel对象 XSSFWorkbook book = new XSSFWorkbook(); XSSFSheet sheet = book.createSheet("导出结果"); // 设置单元格风格,居中对齐. XSSFCellStyle cs = book.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 下边框 cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 左边框 cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 上边框 cs.setBorderTop(HSSFCellStyle.BORDER_THIN); // 右边框 cs.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置字体: XSSFFont font = book.createFont(); font.setFontName("黑体"); // 设置字体大小 font.setFontHeightInPoints((short) 12); // 粗体显示 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 要用到的字体格式 cs.setFont(font); // 添加excel数据 for (int i = 0; i < industry.length; i++) { // 行业数组 String[] currentIndustry = new String[]{industry[i]}; // 行业到期收益率数组 List<Double> evalutedyieldList = new ArrayList<Double>(); // 行业信用利差数组 国债 List<Double> evalutedyieldNationList = new ArrayList<Double>(); // 行业信用利差数组 国开债 List<Double> evalutedyieldDevelopList = new ArrayList<Double>(); // 时间点数组 List<String> dayList = new ArrayList<String>(); Map<String, List> listMap = null; // 获取利差值 listMap = getMap(days, intervalDays, studyType, currentIndustry, rateType, rate, bondType, termtomaturity, isWeight, time, startTime, endTime, exchangecode, evalutedyieldList, evalutedyieldNationList, evalutedyieldDevelopList, dayList); evalutedyieldNationList = listMap.get("evalutedyieldNationList"); evalutedyieldDevelopList = listMap.get("evalutedyieldDevelopList"); dayList = listMap.get("dayList"); // 时间判断 if (dayList == null || dayList.isEmpty()) { resultMap.put("excel", book); return resultMap; } // 时间点 Integer titleLen = dayList.size(); XSSFRow row = null; if (i == 0) { // excel标题 String[] title = new String[titleLen + 4]; title[0] = "行业"; title[1] = "国债"; for (int k = 2; k < title.length; k++) { title[k] = "国开债"; } // 第一行 row = sheet.createRow(0); XSSFCell cell = null; for (int k = 0; k < title.length; k++) { cell = row.createCell(k); cell.setCellValue(title[k]); cell.setCellStyle(cs); sheet.setColumnWidth(k, 10 * 512); } // 横向:合并第一行的第2列到第时间点加2列 sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, titleLen + 2)); // 横向:合并第一行的第时间点加3列到第2*(时间点加2)列 sheet.addMergedRegion(new CellRangeAddress(0, 0, titleLen + 3, (titleLen + 2) * 2)); //纵向:合并第一列的第1行和第2行第 sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0)); // 设置对应的合并单元格标题 row = sheet.createRow(1); for (int k = 1; k <= (titleLen + 2) * 2; k++) { cell = row.createCell((short) k); if (k <= titleLen) { cell.setCellValue(dayList.get(k - 1)); } else if (k == titleLen + 1 || k == titleLen * 2 + 3) { cell.setCellValue("最小值"); } else if (k == titleLen + 2 || k == titleLen * 2 + 4) { cell.setCellValue("最大值"); } else if (k > titleLen + 2) { cell.setCellValue(dayList.get(k - titleLen - 3)); } cell.setCellStyle(cs); sheet.setColumnWidth(k, 10 * 512); } } // 数据值判断 if (listMap == null || evalutedyieldDevelopList.isEmpty() || evalutedyieldNationList.isEmpty()) { resultMap.put("excel", book); return resultMap; } // excel数据添加 int k = 0; row = sheet.createRow(i + 2); row.createCell(k).setCellValue(industry[i]); for (int j = 0; j < evalutedyieldNationList.size(); j++) { row.createCell(++k).setCellValue(Util.nullTrans(evalutedyieldNationList.get(j))); } evalutedyieldNationList.removeAll(Collections.singleton(null)); row.createCell(++k).setCellValue(Collections.min(evalutedyieldNationList)); row.createCell(++k).setCellValue(Collections.max(evalutedyieldNationList)); for (int j = 0; j < evalutedyieldDevelopList.size(); j++) { row.createCell(++k).setCellValue(Util.nullTrans(evalutedyieldDevelopList.get(j))); } evalutedyieldDevelopList.removeAll(Collections.singleton(null)); row.createCell(++k).setCellValue(Collections.min(evalutedyieldDevelopList)); row.createCell(++k).setCellValue(Collections.max(evalutedyieldDevelopList)); } // 添加返回值 resultMap.put("excel", book); return resultMap; }