From 4e52cb2253af9a22571c7d0c239ee283178d2fd4 Mon Sep 17 00:00:00 2001
From: 孔祥富 <kongxf@daryun.com>
Date: 星期一, 08 三月 2021 10:05:18 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master'
---
febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiExportExcel.java | 209 ++++++++++++++++++++++++++++++++++++++++++++++++++--
1 files changed, 201 insertions(+), 8 deletions(-)
diff --git a/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiExportExcel.java b/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiExportExcel.java
index 80eb819..e60e2f8 100644
--- a/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiExportExcel.java
+++ b/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiExportExcel.java
@@ -1360,7 +1360,7 @@
}
return result;
}
-
+
/*
* 获取导出数据的方法
*
@@ -1427,9 +1427,15 @@
try {
if (field.getGenericType().toString().equals("class java.util.Date")) {
Date time = (Date) field.get(exportList.get(i));
- value = field.get(exportList.get(i)) != null
- ? DateUtil.formatDateTime(time).toString()
- : "";
+ if (field.getName().toLowerCase().indexOf("date")!=-1) {
+ value = field.get(exportList.get(i)) != null
+ ? DateUtil.formatDate(time).toString()
+ : "";
+ }else {
+ value = field.get(exportList.get(i)) != null
+ ? DateUtil.formatDateTime(time).toString()
+ : "";
+ }
} else if (field.getGenericType().toString().equals("class java.lang.Boolean")) {
Boolean bool = (Boolean) field.get(exportList.get(i));
value = bool != null && bool == true ? "是" : "否";
@@ -1448,9 +1454,9 @@
}
if (null != listMapDicItem) {
for (int ss = 0; ss < listMapDicItem.size(); ss++) {
- if (null != listMapDicItem.get(ss).get(s[j])) {
- Map<String, String> mapDicItem = (Map<String, String>) listMapDicItem.get(ss)
- .get(s[j]);
+ //因为数据字典查出来之后改成了全小写，这里也改成全小些
+ if (null != listMapDicItem.get(ss).get(s[j].toLowerCase())) {
+ Map<String, String> mapDicItem = (Map<String, String>) listMapDicItem.get(ss).get(s[j].toLowerCase());
value = mapDicItem.get(value);
}
}
@@ -1476,5 +1482,192 @@
return allList;
}
-
+ public final static boolean exportCommonExcelMultiSheet(HttpServletResponse response, String fileName, List<Map<String, Object>> listContent,List<String> sheetTitle) throws IOException {
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ boolean result = false;
+ OutputStream fileOutputStream = null;
+ response.reset();// 清空输出流
+ response.setHeader("Content-disposition",
+ "attachment; filename=" + new String((fileName + ".xls").getBytes("GB2312"), "ISO8859-1"));
+ response.setContentType("application/msexcel");
+
+ if (null != listContent && !listContent.isEmpty()) {
+ try {
+ // 处理数据
+ for (int k = 0; k < listContent.size(); k++) {
+ Sheet sheet = workbook.createSheet(sheetTitle.get(k));
+ // 创建基本的样式
+ CellStyle titleStyle = getCellStyle(workbook, "", (short) 20, true, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle headStyle = getCellStyle(workbook, "", (short) 9, true, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleCenter = getCellStyle(workbook, "", (short) 8, false, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleLeft = getCellStyle(workbook, "", (short) 8, false, HorizontalAlignment.LEFT,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleRight = getCellStyle(workbook, "", (short) 8, false, HorizontalAlignment.RIGHT,
+ VerticalAlignment.CENTER, true);
+
+ int rowNum = 0; // 初始化第一行为0开始计数
+ int colCount = ((String[]) listContent.get(k).get("head")).length;// 表头的列数
+
+ // 第一行先创建一个大标题(当不为null的时候，设置这一行)
+ if (sheetTitle != null) {
+ titleStyle.setBorderTop(BorderStyle.NONE);
+ titleStyle.setBorderBottom(BorderStyle.NONE);
+ titleStyle.setBorderLeft(BorderStyle.NONE);
+ titleStyle.setBorderRight(BorderStyle.NONE);
+
+ Row sheetTitleRow = sheet.createRow(rowNum); // 创建标题行
+ sheetTitleRow.setHeight((short) 0x300); // 设置行高
+ Cell sheetTitleCell = sheetTitleRow.createCell(0); // 创建第一个单元格
+ sheetTitleCell.setCellStyle(titleStyle); // 设置标题的样式
+ sheetTitleCell.setCellValue(sheetTitle.get(k)); // 给标题格设定值
+ if (colCount != 1) {
+ sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
+ }
+ // sheet.addMergedRegion(new CellRangeAddress(rowNum,
+ // rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
+ rowNum++;
+ }
+
+ // 获取数据
+ List<Map<String, String>> currentData = (List<Map<String, String>>) listContent.get(k).get("data");
+ String title = (String) listContent.get(k).get("title");
+ String[] headArray = (String[]) listContent.get(k).get("head");
+ Map<String, Integer> columnWidthArray = (Map<String, Integer>) listContent.get(k).get("columnWidth");
+ String[] pageHeadArray = (String[]) listContent.get(k).get("pageHead");
+ String[] pageFooterArray = (String[]) listContent.get(k).get("pageFooter");
+
+ // 设置标题栏内容(当不为null的时候，设置这一行)
+ if (title != null) {
+ if (rowNum > 1) { // 除了第一个表格的时候，后续表格和之前表格空三行
+ rowNum += 3;
+ }
+ Row titleRow = sheet.createRow(rowNum); // 标题行
+ titleRow.setHeight((short) 0x248); // 标题行高
+
+ for (int i = 0; i < headArray.length; i++) {
+ Cell titleCell = titleRow.createCell(i);
+ titleCell.setCellStyle(headStyle);
+ titleCell.setCellValue(title);
+ }
+ sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, headArray.length - 1));
+ rowNum++;
+ }
+
+ // 设置页头内容（标题下面的一排小文字信息）
+ if (pageHeadArray != null && pageHeadArray.length > 0) {
+ CellStyle pageHeadStyle = getCellStyle(workbook, "", (short) 9, false, HorizontalAlignment.LEFT,
+ VerticalAlignment.CENTER, false);
+ pageHeadStyle.setBorderTop(BorderStyle.NONE);
+ pageHeadStyle.setBorderBottom(BorderStyle.NONE);
+ pageHeadStyle.setBorderLeft(BorderStyle.NONE);
+ pageHeadStyle.setBorderRight(BorderStyle.NONE);
+ for (int i = 0; i < pageHeadArray.length; i++) {
+ Row headRow = sheet.createRow(rowNum); // 表头行
+ headRow.setHeight((short) 0x150); // 表头行高
+ for (int j = 0; j < headArray.length; j++) {
+ Cell titleCell = headRow.createCell(j);
+ titleCell.setCellStyle(pageHeadStyle);
+ titleCell.setCellValue(pageHeadArray[i]);
+ }
+ sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, headArray.length - 1));
+ rowNum++;
+ }
+ }
+
+ // 设置表头内容
+ Row headRow = sheet.createRow(rowNum); // 表头行
+ headRow.setHeight((short) 0x200); // 表头行高
+ for (int i = 0; i < headArray.length; i++) {
+ Cell cell = headRow.createCell(i);
+ cell.setCellValue(headArray[i]);
+ cell.setCellStyle(headStyle);
+ if (sheetTitle != null && headArray.length == 1) {// 只有一列
+ sheet.setColumnWidth(i, sheetTitle.get(k).getBytes().length * 3 * 256);
+ } else {
+ if (null == columnWidthArray) {
+ sheet.setColumnWidth(i, headArray[i].getBytes().length * 2 * 256);
+ } else {
+ sheet.setColumnWidth(i, columnWidthArray.get(headArray[i]) * 256);
+ }
+ }
+
+ }
+ rowNum++;
+
+ for (int i = 0; i < currentData.size(); i++) {
+ Row textRow = sheet.createRow(rowNum);
+ Map<String, String> map = currentData.get(i);
+ int j = 0, maxTextHeight = (short) 0X170;// 0X250; //
+ // 默认行高，可以放2行数据
+ for (String s : map.keySet()) {
+ Object val = map.get(s);
+ if (val == null) {
+ val = "";
+ }
+
+ Cell cell = textRow.createCell(j);
+ cell.setCellValue(String.valueOf(val));
+ cell.setCellStyle(textStyleCenter);
+
+ // 计算最大的高度值
+ // int len = String.valueOf(val).getBytes().length;
+ // if (len > columnWidthArray[j] + 1) {
+ // int tempHeight = (len / (columnWidthArray[j] - 1)
+ // + 1) * 0X125; // 加入了边框，所以一行放入的字节数会少一个
+ // if (tempHeight > maxTextHeight)
+ // maxTextHeight = tempHeight;
+ // }
+ j++;
+ }
+ // 设置行高
+ textRow.setHeight((short) maxTextHeight);
+ rowNum++;
+ }
+
+ // 设置页脚内容
+ if (pageFooterArray != null && pageFooterArray.length > 0) {
+ CellStyle pageFooterStyle = getCellStyle(workbook, "", (short) 9, false,
+ HorizontalAlignment.CENTER, VerticalAlignment.CENTER, false);
+ pageFooterStyle.setBorderTop(BorderStyle.NONE);
+ for (int i = 0; i < pageFooterArray.length; i++) {
+ Row footerRow = sheet.createRow(rowNum); // 表头行
+ footerRow.setHeight((short) 0x180); // 表头行高
+ for (int j = 0; j < headArray.length; j++) {
+ Cell titleCell = footerRow.createCell(j);
+ titleCell.setCellStyle(pageFooterStyle);
+ titleCell.setCellValue(pageFooterArray[i]);
+ }
+ sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, headArray.length - 1));
+ rowNum++;
+ }
+ }
+
+ }
+
+ fileOutputStream = response.getOutputStream();
+
+ workbook.write(fileOutputStream);
+ } catch (IOException e) {
+ System.out.println(e.getMessage());
+ return false;
+ } catch (Exception e) {
+ System.out.println(e.getMessage());
+ return false;
+ } finally {
+ if (null != fileOutputStream) {
+ try {
+ fileOutputStream.close();
+ } catch (IOException e) {
+ System.out.println(e.getMessage());
+ }
+ }
+ }
+ result = true;
+ }
+ return result;
+ }
+
}
--
Gitblit v1.8.0