public class POI2ExcelTest { SimpleDateFormat df = new SimpleDateFormat("yyyy.MM.dd");// 设置日期格式 @SuppressWarnings("deprecation") @Transactional public InputStream exportExcel() { // 要导出的数据,这里我造的假数据来的。 List<Map<String, String>> datas = toMap(madeData()); HSSFWorkbook wb = new HSSFWorkbook(); // 创建excel HSSFSheet sheet = wb.createSheet(); // 创建子表名称 HSSFRow row = null; HSSFCell cell = null; // 表头样式 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 HSSFFont font = wb.createFont(); font.setFontName("宋体");// 设置字体名称 font.setFontHeightInPoints((short) 24);// 设置字号 font.setBold(true);// 加粗 style.setFont(font); // 筛选条件样式 HSSFCellStyle style_s = wb.createCellStyle(); style_s.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 右对齐 style_s.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 HSSFFont font_s = wb.createFont(); font_s.setFontName("宋体");// 设置字体名称 font_s.setFontHeightInPoints((short) 12);// 设置字号 font_s.setBold(true);// 加粗 style_s.setFont(font_s); // 筛选条件值样式 HSSFCellStyle style_V = wb.createCellStyle(); style_V.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左对齐 style_V.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 HSSFFont font_V = wb.createFont(); font_V.setFontName("宋体");// 设置字体名称 font_V.setFontHeightInPoints((short) 12);// 设置字号 font_V.setBold(true);// 加粗 style_V.setFont(font_V); // 字段样式 HSSFCellStyle style_C = wb.createCellStyle(); style_C.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 style_C.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框 style_C.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 style_C.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 style_C.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中 style_C.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 HSSFFont font_C = wb.createFont(); font_C.setFontName("宋体");// 设置字体名称 font_C.setFontHeightInPoints((short) 11);// 设置字号 font_C.setBold(true);// 加粗 style_C.setFont(font_C); // 数据样式 HSSFCellStyle style_D = wb.createCellStyle(); HSSFDataFormat format = wb.createDataFormat(); style_D.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中 style_D.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 style_D.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 style_D.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框 style_D.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 style_D.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 style_D.setDataFormat(format.getFormat("@")); // 设置单元格为文本格式 HSSFFont font_D = wb.createFont(); font_D.setFontName("宋体");// 设置字体名称 font_D.setFontHeightInPoints((short) 11);// 设置字号 style_D.setFont(font_D); // 创建表头标题 int rowIndex = 0; row = sheet.createRow(rowIndex); cell = row.createCell(0); // 合并单元格:起始行,终止行,起始列,终止列。 sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 14)); // 表标题 cell.setCellValue("匹配交易明细导出"); cell.setCellStyle(style); rowIndex += 3; // 创建筛选数据 row = sheet.createRow(rowIndex); rowIndex++; cell = row.createCell(1); cell.setCellValue("站点:"); cell.setCellStyle(style_s); cell = row.createCell(2); cell.setCellValue(stationAddress != null ? getStationName(stationAddress) : ""); cell.setCellStyle(style_V); cell = row.createCell(5); cell.setCellValue("交易模式:"); cell.setCellStyle(style_s); cell = row.createCell(6); cell.setCellValue(payType != null ? ExchangUtil.ex_payType(payType) : ""); cell.setCellStyle(style_V); cell = row.createCell(9); cell.setCellValue("交易类型:"); cell.setCellStyle(style_s); HSSFCell matchTypeCellV = row.createCell(10); matchTypeCellV.setCellValue(matchType != null ? ExchangUtil.ex_matchType(matchType) : ""); matchTypeCellV.setCellStyle(style_V); row = sheet.createRow(rowIndex); rowIndex += 2; cell = row.createCell(1); cell.setCellValue("卡号:"); cell.setCellStyle(style_s); cell = row.createCell(2); cell.setCellValue(cardId != null ? cardId : ""); cell.setCellStyle(style_V); cell = row.createCell(5); cell.setCellValue("交易状态:"); cell.setCellStyle(style_s); cell = row.createCell(6); cell.setCellValue(payStatus != null ? ExchangUtil.ex_payStatus_S(payStatus) : ""); cell.setCellStyle(style_V); cell = row.createCell(9); cell.setCellValue("运营日期:"); cell.setCellStyle(style_s); cell = row.createCell(10); cell.setCellValue(sTtradeDate != null ? sTtradeDate : ""); cell.setCellStyle(style_V); cell = row.createCell(11); cell.setCellValue(eTtradeDate != null ? eTtradeDate : ""); cell.setCellStyle(style_V); // 创建字段数据 row = sheet.createRow(rowIndex); row.setRowStyle(style); String[] columns = { "卡号", "交易模式", "匹配时间", "交易类型", "交易金额(元)", "交易状态", "进站站点", "进站设备", "进站时间", "出站站点", "出站设备", "出站时间", "BOM站点", "BOM设备", "BOM时间" }; int colum_i = 0; for (String columName : columns) { cell = row.createCell(colum_i); cell.setCellValue(columName); cell.setCellStyle(style_C); colum_i++; } rowIndex++; // 创建数据 for (Map<String, String> data : datas) { row = sheet.createRow(rowIndex); int data_i = 0; for (String colum : columns) { cell = row.createCell(data_i); String val = data.get(colum); cell.setCellValue(val != null ? val : ""); cell.setCellStyle(style_D); data_i++; } rowIndex++; } // 设置列宽 sheet.setColumnWidth(0, 16 * 256); sheet.setColumnWidth(1, 6 * 256); sheet.setColumnWidth(2, 22 * 256); sheet.setColumnWidth(3, 18 * 256); sheet.setColumnWidth(4, 10 * 256); sheet.setColumnWidth(5, 4 * 256); ByteArrayOutputStream os = new ByteArrayOutputStream(); try { wb.write(os); os.close(); wb.close(); InputStream is = new ByteArrayInputStream(os.toByteArray()); return is; } catch (Exception e) { e.printStackTrace(); } return null; } private List<Map<String, String>> toMap(List<Student> students) { List<Map<String, String>> list = new ArrayList<>(); for (Student student : students) { Map<String, String> map = new HashMap<>(); map.put("学号", student.getNumber() != null ? student.getNumber() : ""); map.put("姓名", student.getName() != null ? student.getName() : ""); map.put("学院", student.getCollege() != null ? student.getCollege() : ""); map.put("专业", student.getMajor() != null ? student.getMajor() : ""); map.put("班级", student.getClasses() != null ? student.getClasses() : ""); map.put("性别", student.getSex() != null ? student.getSex() : ""); list.add(map); } return list; } private List<Student> madeData() { List<Student> list = new ArrayList<>(); list.add(new Student("2014041743301", "saly", "计算机科学与工程学院", "计算机科学与技术", "计科143", "女")); list.add(new Student("2014041743301", "张三", "计算机科学与工程学院", "计算机科学与技术", "计科143", "男")); list.add(new Student("2014041743301", "李四", "计算机科学与工程学院", "计算机科学与技术", "计科143", "男")); list.add(new Student("2014041743301", "王五", "计算机科学与工程学院", "计算机科学与技术", "计科143", "男")); list.add(new Student("2014041743301", "赵六", "计算机科学与工程学院", "计算机科学与技术", "计科143", "男")); return list; } } |