将数据集合按某个字段分组,分别导出excel
Map<String, List<Map<String, Object>>> listGroupMao = listGroup(list,"QXDMKDDM"); String[] headers = new String[]{"区县代码", "区县名称", "考点代码", "考点简称", "考试时间", "课程代码", "课程简称", "考场号", "屏幕标识"};//在excel中的第2行每列的参数 String[] headersKey = new String[]{"QXDM", "QXMC", "KDDM", "KDJC", "KSSJ", "KCDM", "KCMC", "KCH", "PMBS"};//在excel中的第2行每列的参数 exportExcel(response, listGroupMao,headers,headersKey);
package org.jeecg.common.util; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; public class ExcelGroupUtil { public static Map<String, List<Map<String,Object>>> listGroup(List<Map<String,Object>> list,String key) throws Exception { Map<String, List<Map<String,Object>>> resultMap = new HashMap<>(); try { for (Map map : list) { String mapKey =String.valueOf(map.get(key)); // map中key已存在,将该数据存放到同一个key(key存放的是一级品种code + 交货地code)的map中 if (resultMap.containsKey(mapKey)) { resultMap.get(mapKey).add(map); } else { // map中不存在,新建key,用来存放数据 List<Map<String,Object>> tmpList = new ArrayList<>(); tmpList.add(map); resultMap.put(mapKey, tmpList); } } } catch (Exception e) { throw new Exception("按照一级品种code加交货地code进行分组时出现异常", e); } return resultMap; } public static void exportExcel(HttpServletResponse response, Map<String, List<Map<String, Object>>> map, String[] headers, String[] headersKey) throws IOException { ServletOutputStream outputStream = response.getOutputStream(); ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream); try { map.forEach((k, v) -> { //新建一个Excel 并设置下sheet头 HSSFWorkbook workbook = createExcelAndSetHeaders(headers, k); //向sheet中 继续填充对象的数据 setSheetCellValue(workbook.getSheet(k), v,headersKey); try { //重点开始,创建压缩文件 ZipEntry zipEntry = new ZipEntry(k + ".xls"); zipOutputStream.putNextEntry(zipEntry); } catch (IOException e) { try { throw new Exception("向XXX压缩包中添加Excel失败"); } catch (Exception e1) { e1.printStackTrace(); } } try { //写入一个压缩文件 workbook.write(zipOutputStream); } catch (IOException e) { try { throw new Exception("向zipOutputStream中写入流数据失败"); } catch (Exception e1) { e1.printStackTrace(); } } }); zipOutputStream.flush(); } catch (Exception e) { e.printStackTrace(); } finally { //关闭数据流,注意关闭的顺序 zipOutputStream.close(); outputStream.close(); } } private static HSSFWorkbook createExcelAndSetHeaders(String[] headers, String sheetName) { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); HSSFCellStyle headstyle = createCellStyle(hssfWorkbook, (short) 18); HSSFCellStyle style1 = createCellStyle(hssfWorkbook, (short) 13); HSSFSheet hssfSheet = hssfWorkbook.createSheet(sheetName); hssfSheet.setDefaultColumnWidth(15); hssfSheet.setDefaultRowHeightInPoints(15); hssfSheet.autoSizeColumn(1, true); HSSFRow row0 = hssfSheet.createRow(0); row0.setHeightInPoints(50); HSSFCell cell0 = row0.createCell(0); // 加载单元格样式 cell0.setCellStyle(headstyle); String titleName = "考场标识 ("+sheetName.substring(sheetName.lastIndexOf("_")+1)+")"; cell0.setCellValue(titleName); hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1)); HSSFRow row1 = hssfSheet.createRow(1); for (int i = 0; i < headers.length; i++) { HSSFCell cellHeader = row1.createCell(i); cellHeader.setCellStyle(style1); cellHeader.setCellValue(headers[i]); } return hssfWorkbook; } private static void setSheetCellValue(HSSFSheet hssfSheet, List<Map<String,Object>> dtos,String[] headersKey) { for (Map map : dtos) { //从当前sheet页的最后一行后新增一行,开始填充数据 HSSFRow row = hssfSheet.createRow(hssfSheet.getLastRowNum() + 1); int count = -1; for (int i = 0; i < headersKey.length; i++) { row.createCell(++count).setCellValue(String.valueOf(map.get(headersKey[i]))); } } } /** * 创建单元格样式 * * @param workbook 工作簿 * @param fontSize 字体大小 * @return 单元格样式 */ private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) { HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 // 创建字体 HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗字体 font.setFontHeightInPoints(fontSize); // 加载字体 style.setFont(font); return style; } }