From 6ca0f85f69b5a3d52d7d9cd755e032ae338f5a60 Mon Sep 17 00:00:00 2001
From: luoyb <412940104@qq.com>
Date: 星期五, 07 六月 2024 05:52:02 +0800
Subject: [PATCH] fix: 问题修复 1.员工档案中修改合同时性别不能自动带入问题
---
febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiExportExcel.java | 3153 +++++++++++++++++++++++++++++++---------------------------
1 files changed, 1,673 insertions(+), 1,480 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..232b194 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
@@ -1,1480 +1,1673 @@
-package cc.mrbird.febs.server.hr.util;
-
-import java.io.File;
-import java.io.FileOutputStream;
-import java.io.IOException;
-import java.io.OutputStream;
-import java.lang.reflect.Field;
-import java.util.ArrayList;
-import java.util.Collection;
-import java.util.Date;
-import java.util.HashMap;
-import java.util.LinkedHashMap;
-import java.util.List;
-import java.util.Map;
-import java.util.TreeMap;
-
-import javax.servlet.http.HttpServletResponse;
-
-import org.apache.commons.lang3.StringUtils;
-import org.apache.poi.hssf.usermodel.HSSFFont;
-import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-import org.apache.poi.ss.usermodel.BorderStyle;
-import org.apache.poi.ss.usermodel.Cell;
-import org.apache.poi.ss.usermodel.CellStyle;
-import org.apache.poi.ss.usermodel.HorizontalAlignment;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
-import org.apache.poi.ss.usermodel.VerticalAlignment;
-import org.apache.poi.ss.util.CellRangeAddress;
-
-import cc.mrbird.febs.common.core.annotation.FieldInfo;
-import cn.hutool.core.date.DateUtil;
-
-public class PoiExportExcel {
-
- /**
- * 导出EXCEL文件（单个sheet，可以多个表格）
- *
- * @param response
- * @param fileName 文件名称
- * @param sheetTitle sheet中的大标题（第一行）
- * @param listContent sheet中的数据集（list中每个map数据中，存放一个表格的数据；在每个map中又细分为多个不同的Object数据）
- * 如：（详见导出班级代码） List<Map<String, Object>> listContent = new
- * ArrayList<>(); //数据集
- *
- * Map<String, Object> roomAllMap = new LinkedHashMap<>();
- * //一个map中，代表一个表格 roomAllMap.put("data", roomList);
- * //此表格中的具体遍历数据 roomAllMap.put("title", "班级学员宿舍信息表");
- * //表格标题，若为null，且数据集中存在其他map，则下一个map不会空出3行（空出3行，用于划分各个表格）。
- * roomAllMap.put("head", new String[] { "姓名", "性别", "是否午休",
- * "是否晚宿" });
- * //若存在名字相同的，则名字相同且相邻的head合并（当head名字相同，并且某行中的对应的列值也相同，则合并它们）
- * roomAllMap.put("columnWidth", new Integer[] { 15, 15, 15,
- * 20 }); //
- * 20代表20个字节，10个字符（整个sheet中，只能存在一个columnWidth,因为列宽是针对整个sheet的）
- * roomAllMap.put("columnAlignment", new Integer[] { 0, 0, 0,
- * 0 }); // 0代表居中，1代表居左，2代表居右
- * roomAllMap.put("mergeCondition", null); //
- * 跨行合行列需要的条件，条件优先级按顺序决定，NULL表示不合并,空数组表示无条件
- * listContent.add(roomAllMap); //加入此map到数据集中
- *
- * @return
- * @throws IOException
- */
- public final static boolean exportExcel(HttpServletResponse response, String fileName, String sheetTitle,
- List<Map<String, Object>> listContent) 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 {
- Sheet sheet = workbook.createSheet(fileName);
-
- // 创建基本的样式
- CellStyle titleStyle = getCellStyle(workbook, "", (short) 26, true, HorizontalAlignment.CENTER,
- VerticalAlignment.CENTER, true);
- CellStyle headStyle = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.CENTER,
- VerticalAlignment.CENTER, true);
- CellStyle textStyleCenter = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.CENTER,
- VerticalAlignment.CENTER, true);
- CellStyle textStyleLeft = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.LEFT,
- VerticalAlignment.CENTER, true);
- CellStyle textStyleRight = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.RIGHT,
- VerticalAlignment.CENTER, true);
-
- int rowNum = 0; // 初始化第一行为0开始计数
- int colCount = ((String[]) listContent.get(0).get("head")).length;// 表头的列数
-
- // 第一行先创建一个大标题(当不为null的时候，设置这一行)
- if (sheetTitle != null) {
- Row sheetTitleRow = sheet.createRow(rowNum); // 创建标题行
- sheetTitleRow.setHeight((short) 0x300); // 设置行高
- Cell sheetTitleCell = sheetTitleRow.createCell(0); // 创建第一个单元格
- sheetTitleCell.setCellStyle(titleStyle); // 设置标题的样式
- sheetTitleCell.setCellValue(sheetTitle); // 给标题格设定值
- sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
- rowNum++;
- }
-
- // 处理数据
- for (Map<String, Object> dataList : listContent) {
-
- // 获取数据
- List<Map<String, String>> currentData = (List<Map<String, String>>) dataList.get("data");
- String title = (String) dataList.get("title");
- String[] headArray = (String[]) dataList.get("head");
- Integer[] columnWidthArray = (Integer[]) dataList.get("columnWidth");
- Integer[] columnWidthAlignment = (Integer[]) dataList.get("columnAlignment");
- String[] columnMergeCondition = (String[]) dataList.get("mergeCondition");
-
- // 设置标题栏内容(当不为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++;
- }
-
- // 设置表头内容
- Row headRow = sheet.createRow(rowNum); // 表头行
- headRow.setHeight((short) 0x200); // 表头行高
- Object[] headMergeObj = null; // （因为2个行或列合并了之后，就必须要先移除合并，才能重新合并更多的行）
- for (int i = 0; i < headArray.length; i++) {
- Cell cell = headRow.createCell(i);
- cell.setCellValue(headArray[i]);
- cell.setCellStyle(headStyle);
- sheet.setColumnWidth(i, columnWidthArray[i] * 256);
-
- // 如果当前列名和上一列的名字一致，则合并
- if (i > 0 && headArray[i - 1].equals(headArray[i])) {
- if (headMergeObj == null) {
- int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
- headMergeObj = new Object[] { index, headArray[i], i - 1 }; // 合并的index值，表头值，行号。。
- } else {
- if (headMergeObj[1].equals(headArray[i])) {
- sheet.removeMergedRegion((int) headMergeObj[0]);
- int index = sheet.addMergedRegion(
- new CellRangeAddress(rowNum, rowNum, (int) headMergeObj[2], i));
- headMergeObj[0] = index;
- } else {
- int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
- headMergeObj = new Object[] { index, headArray[i], i - 1 }; // 合并的index值，表头值，行号。
- }
- }
- }
-
- }
- rowNum++;
-
- // 保存上一个map
- Map<String, String> preMap = null;
- // 保存某一列上一个合并的数据，并用来判断是否再合并（因为2个列合并了之后，就必须要先移除合并，才能合并）
- Map<Integer, Object[]> recordMap = new HashMap<>();
- // 保存某一行的上一个合并的数据，并用来判断是否再合并
- Object[] rowMergeObj = null;
- 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) 0X250; // 默认行高，可以放2行数据
- String preVal = null; // 保存某一行中，上一列的值
- for (String s : map.keySet()) {
- Object val = map.get(s);
- if (val == null) {
- val = "";
- }
-
- Cell cell = textRow.createCell(j);
- cell.setCellValue(String.valueOf(val));
-
- if (columnWidthAlignment[j] == 0) {
- cell.setCellStyle(textStyleCenter);
- } else if (columnWidthAlignment[j] == 1) {
- cell.setCellStyle(textStyleLeft);
- } else if (columnWidthAlignment[j] == 2) {
- cell.setCellStyle(textStyleRight);
- } else {
- 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;
- }
-
- // 判断是否需要进行列合并
- // 如果当前列名和上一列的名字一致，并且行的值也一致，则合并
- if (j > 0 && headArray[j - 1].equals(headArray[j]) && preVal != null
- && preVal.equals(val)) {
- if (rowMergeObj == null) {
- int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
- rowMergeObj = new Object[] { index, headArray[j - 1], preVal, i, j - 1 }; // 合并的index值，表头值，数据值，行号,列号。
- } else {
- // 当上一个合并的数据值、表头值、行号 与
- // 当前处理的单元格的数据一致时，才继续合并，否则单独合并两列
- if (rowMergeObj[2].equals(val) && rowMergeObj[1].equals(headArray[j])
- && i == (int) rowMergeObj[3]) {
- sheet.removeMergedRegion((int) rowMergeObj[0]);
- int index = sheet.addMergedRegion(
- new CellRangeAddress(rowNum, rowNum, (int) rowMergeObj[4], j));
- rowMergeObj[0] = index;
- } else {
- int index = sheet
- .addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
- rowMergeObj = new Object[] { index, headArray[j - 1], preVal, i, j - 1 }; // 合并的index值，表头值，数据值，行号,列号。
- }
- }
- }
-
- // 判断是否需要进行行合并
- if (i > 0 && preMap.get(s) != null && !preMap.get(s).equals("") && preMap.get(s).equals(val)
- && columnMergeCondition != null) { // 当前后的值都一致，才能满足最基本的合并条件
-
- boolean isMerge = true;
- String tempStr = "";
- // 当需要合并的列，满足必要的合并条件后，才允许合并
- for (int k = 0; k < columnMergeCondition.length; k++) {
- tempStr = columnMergeCondition[k];
-
- // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
- if (s.equals(tempStr)) {
- isMerge = true;
- break;
- } else if (!preMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
- isMerge = false;
- break;
- }
- }
-
- if (isMerge == true) {
- // 若不存在值，表明还未合并
- Object[] recordObj = recordMap.get(j);
- if (recordObj == null) {
- int index = sheet
- .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
- recordObj = new Object[] { index, currentData.get(i), rowNum - 1 }; // 合并的index值，MAP，行号。
- recordMap.put(j, recordObj);
- }
- // 若存在值，则判断，值是否一致，一致则合并，否则重新保存新的数据
- else {
- boolean isTempMerger = false;
- Map<String, String> tempMap = (Map) recordObj[1];
- // 当需要合并的列，满足必要的合并条件后，才允许合并
- for (int k = 0; k < columnMergeCondition.length; k++) {
- tempStr = columnMergeCondition[k];
-
- // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
- if (s.equals(tempStr) && tempMap.get(s).equals(val)) {
- isTempMerger = true;
- break;
- } else if (!tempMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
- isTempMerger = false;
- break;
- }
- }
- if (isTempMerger == true) {
- sheet.removeMergedRegion((int) recordObj[0]); // 先移除
- int index = sheet.addMergedRegion(
- new CellRangeAddress((int) recordObj[2], rowNum, j, j)); // 再合并
- recordObj[0] = index;
- recordMap.put(j, recordObj);
- } else { // 否则，重新保存此列的合并数据
- int index = sheet
- .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
- recordObj = new Object[] { index, currentData.get(i), rowNum - 1 }; // 保存新的合并的index值，列值，行号。
- recordMap.put(j, recordObj);
- }
-
- }
- }
- }
- preVal = (String) val;
- j++;
- }
-
- // 设置行高
- textRow.setHeight((short) maxTextHeight);
- // 保存上一个map
- preMap = currentData.get(i);
- 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;
- }
-
- public final static boolean exportExcelFile(HttpServletResponse response, String fileName, String sheetTitle,
- List<Map<String, Object>> listContent, String fileUrl) 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 {
- Sheet sheet = workbook.createSheet(fileName);
-
- // 创建基本的样式
- CellStyle titleStyle = getCellStyle(workbook, "", (short) 26, true, HorizontalAlignment.CENTER,
- VerticalAlignment.CENTER, true);
- CellStyle headStyle = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.CENTER,
- VerticalAlignment.CENTER, true);
- CellStyle textStyleCenter = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.CENTER,
- VerticalAlignment.CENTER, true);
- CellStyle textStyleLeft = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.LEFT,
- VerticalAlignment.CENTER, true);
- CellStyle textStyleRight = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.RIGHT,
- VerticalAlignment.CENTER, true);
-
- int rowNum = 0; // 初始化第一行为0开始计数
- int colCount = ((String[]) listContent.get(0).get("head")).length;// 表头的列数
-
- // 第一行先创建一个大标题(当不为null的时候，设置这一行)
- if (sheetTitle != null) {
- Row sheetTitleRow = sheet.createRow(rowNum); // 创建标题行
- sheetTitleRow.setHeight((short) 0x300); // 设置行高
- Cell sheetTitleCell = sheetTitleRow.createCell(0); // 创建第一个单元格
- sheetTitleCell.setCellStyle(titleStyle); // 设置标题的样式
- sheetTitleCell.setCellValue(sheetTitle); // 给标题格设定值
- sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
- rowNum++;
- }
-
- // 处理数据
- for (Map<String, Object> dataList : listContent) {
-
- // 获取数据
- List<Map<String, String>> currentData = (List<Map<String, String>>) dataList.get("data");
- String title = (String) dataList.get("title");
- String[] headArray = (String[]) dataList.get("head");
- Integer[] columnWidthArray = (Integer[]) dataList.get("columnWidth");
- Integer[] columnWidthAlignment = (Integer[]) dataList.get("columnAlignment");
- String[] columnMergeCondition = (String[]) dataList.get("mergeCondition");
-
- // 设置标题栏内容(当不为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++;
- }
-
- // 设置表头内容
- Row headRow = sheet.createRow(rowNum); // 表头行
- headRow.setHeight((short) 0x200); // 表头行高
- Object[] headMergeObj = null; // （因为2个行或列合并了之后，就必须要先移除合并，才能重新合并更多的行）
- for (int i = 0; i < headArray.length; i++) {
- Cell cell = headRow.createCell(i);
- cell.setCellValue(headArray[i]);
- cell.setCellStyle(headStyle);
- sheet.setColumnWidth(i, columnWidthArray[i] * 256);
-
- // 如果当前列名和上一列的名字一致，则合并
- if (i > 0 && headArray[i - 1].equals(headArray[i])) {
- if (headMergeObj == null) {
- int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
- headMergeObj = new Object[] { index, headArray[i], i - 1 }; // 合并的index值，表头值，行号。。
- } else {
- if (headMergeObj[1].equals(headArray[i])) {
- sheet.removeMergedRegion((int) headMergeObj[0]);
- int index = sheet.addMergedRegion(
- new CellRangeAddress(rowNum, rowNum, (int) headMergeObj[2], i));
- headMergeObj[0] = index;
- } else {
- int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
- headMergeObj = new Object[] { index, headArray[i], i - 1 }; // 合并的index值，表头值，行号。
- }
- }
- }
-
- }
- rowNum++;
-
- // 保存上一个map
- Map<String, String> preMap = null;
- // 保存某一列上一个合并的数据，并用来判断是否再合并（因为2个列合并了之后，就必须要先移除合并，才能合并）
- Map<Integer, Object[]> recordMap = new HashMap<>();
- // 保存某一行的上一个合并的数据，并用来判断是否再合并
- Object[] rowMergeObj = null;
- 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) 0X250; // 默认行高，可以放2行数据
- String preVal = null; // 保存某一行中，上一列的值
- for (String s : map.keySet()) {
- Object val = map.get(s);
- if (val == null) {
- val = "";
- }
-
- Cell cell = textRow.createCell(j);
- cell.setCellValue(String.valueOf(val));
-
- if (columnWidthAlignment[j] == 0) {
- cell.setCellStyle(textStyleCenter);
- } else if (columnWidthAlignment[j] == 1) {
- cell.setCellStyle(textStyleLeft);
- } else if (columnWidthAlignment[j] == 2) {
- cell.setCellStyle(textStyleRight);
- } else {
- 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;
- }
-
- // 判断是否需要进行列合并
- // 如果当前列名和上一列的名字一致，并且行的值也一致，则合并
- if (j > 0 && headArray[j - 1].equals(headArray[j]) && preVal != null
- && preVal.equals(val)) {
- if (rowMergeObj == null) {
- int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
- rowMergeObj = new Object[] { index, headArray[j - 1], preVal, i, j - 1 }; // 合并的index值，表头值，数据值，行号,列号。
- } else {
- // 当上一个合并的数据值、表头值、行号 与
- // 当前处理的单元格的数据一致时，才继续合并，否则单独合并两列
- if (rowMergeObj[2].equals(val) && rowMergeObj[1].equals(headArray[j])
- && i == (int) rowMergeObj[3]) {
- sheet.removeMergedRegion((int) rowMergeObj[0]);
- int index = sheet.addMergedRegion(
- new CellRangeAddress(rowNum, rowNum, (int) rowMergeObj[4], j));
- rowMergeObj[0] = index;
- } else {
- int index = sheet
- .addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
- rowMergeObj = new Object[] { index, headArray[j - 1], preVal, i, j - 1 }; // 合并的index值，表头值，数据值，行号,列号。
- }
- }
- }
-
- // 判断是否需要进行行合并
- if (i > 0 && preMap.get(s) != null && !preMap.get(s).equals("") && preMap.get(s).equals(val)
- && columnMergeCondition != null) { // 当前后的值都一致，才能满足最基本的合并条件
-
- boolean isMerge = true;
- String tempStr = "";
- // 当需要合并的列，满足必要的合并条件后，才允许合并
- for (int k = 0; k < columnMergeCondition.length; k++) {
- tempStr = columnMergeCondition[k];
-
- // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
- if (s.equals(tempStr)) {
- isMerge = true;
- break;
- } else if (!preMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
- isMerge = false;
- break;
- }
- }
-
- if (isMerge == true) {
- // 若不存在值，表明还未合并
- Object[] recordObj = recordMap.get(j);
- if (recordObj == null) {
- int index = sheet
- .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
- recordObj = new Object[] { index, currentData.get(i), rowNum - 1 }; // 合并的index值，MAP，行号。
- recordMap.put(j, recordObj);
- }
- // 若存在值，则判断，值是否一致，一致则合并，否则重新保存新的数据
- else {
- boolean isTempMerger = false;
- Map<String, String> tempMap = (Map) recordObj[1];
- // 当需要合并的列，满足必要的合并条件后，才允许合并
- for (int k = 0; k < columnMergeCondition.length; k++) {
- tempStr = columnMergeCondition[k];
-
- // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
- if (s.equals(tempStr) && tempMap.get(s).equals(val)) {
- isTempMerger = true;
- break;
- } else if (!tempMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
- isTempMerger = false;
- break;
- }
- }
- if (isTempMerger == true) {
- sheet.removeMergedRegion((int) recordObj[0]); // 先移除
- int index = sheet.addMergedRegion(
- new CellRangeAddress((int) recordObj[2], rowNum, j, j)); // 再合并
- recordObj[0] = index;
- recordMap.put(j, recordObj);
- } else { // 否则，重新保存此列的合并数据
- int index = sheet
- .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
- recordObj = new Object[] { index, currentData.get(i), rowNum - 1 }; // 保存新的合并的index值，列值，行号。
- recordMap.put(j, recordObj);
- }
-
- }
- }
- }
- preVal = (String) val;
- j++;
- }
-
- // 设置行高
- textRow.setHeight((short) maxTextHeight);
- // 保存上一个map
- preMap = currentData.get(i);
- rowNum++;
- }
-
- }
-
- File localFile = new File(fileUrl);
- if (!localFile.exists()) {
- // 先得到文件的上级目录，并创建上级目录，在创建文件
- localFile.getParentFile().mkdirs();
- localFile.createNewFile();
- }
- fileOutputStream = new FileOutputStream(localFile);
- fileOutputStream.flush();
-
- 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;
- }
-
- /*
- * 获取样式格式的方法
- *
- * @param workbook Excel文件
- *
- * @param fontName 字体名称
- *
- * @param fontSize 字体大小
- *
- * @param isBold 是否加粗
- *
- * @param horizontalAlignment (左，右，居中)对齐
- *
- * @param verticalAlignment (上，下，居中)对齐
- *
- * @param isWrapText 是否换行
- *
- */
- public static CellStyle getCellStyle(HSSFWorkbook workbook, String fontName, Short fontSize, Boolean isBold,
- HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, Boolean isWrapText) {
-
- HSSFFont font = workbook.createFont();
- // 默认方正黑体
- if (StringUtils.isBlank(fontName)) {
- font.setFontName("方正黑体简体");
- } else {
- font.setFontName(fontName);
- }
- font.setFontHeightInPoints(fontSize);// 字体大小
- font.setBold(isBold); // 是否加粗
-
- CellStyle style = workbook.createCellStyle();
- style.setFont(font);
- style.setAlignment(horizontalAlignment);// 左右对齐
- style.setVerticalAlignment(verticalAlignment);// 上下对齐
- style.setBorderBottom(BorderStyle.THIN);
- style.setBorderLeft(BorderStyle.THIN);
- style.setBorderTop(BorderStyle.THIN);
- style.setBorderRight(BorderStyle.THIN);
- style.setWrapText(isWrapText);
-
- return style;
- }
-
- /*
- * 导出的数据是多个sheet表的
- */
- public final static boolean exportSheetsExcel(HttpServletResponse response, String fileName,
- List<String> sheetNames, String sheetTitle, List<Map<String, Object>> listContent) 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 {
- // 创建基本的样式
- CellStyle headStyle = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.CENTER,
- VerticalAlignment.CENTER, true);
- CellStyle textStyleCenter = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.CENTER,
- VerticalAlignment.CENTER, true);
- CellStyle textStyleLeft = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.LEFT,
- VerticalAlignment.CENTER, true);
- CellStyle textStyleRight = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.RIGHT,
- VerticalAlignment.CENTER, true);
-
- int rowNum = 0; // 初始化第一行为0开始计数
- // 处理数据
- for (Map<String, Object> dataList : listContent) {
-
- int sheetNumber = 0;
- // 获取数据
- List<Map<String, String>> currentData = (List<Map<String, String>>) dataList.get("data");
- String title = (String) dataList.get("title");
- String[] headArray = (String[]) dataList.get("head");
- Integer[] columnWidthArray = (Integer[]) dataList.get("columnWidth");
- Integer[] columnWidthAlignment = (Integer[]) dataList.get("columnAlignment");
- String[] columnMergeCondition = (String[]) dataList.get("mergeCondition");
-
- Sheet sheet = workbook.createSheet(sheetNames.get(sheetNumber));
- sheetNumber++;
-
- // 设置标题栏内容(当不为null的时候，设置这一行)
- if (title != null) {
- 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++;
- }
-
- // 设置表头内容
- Row headRow = sheet.createRow(rowNum); // 表头行
- headRow.setHeight((short) 0x200); // 表头行高
- Object[] headMergeObj = null; // （因为2个行或列合并了之后，就必须要先移除合并，才能重新合并更多的行）
- for (int i = 0; i < headArray.length; i++) {
- Cell cell = headRow.createCell(i);
- cell.setCellValue(headArray[i]);
- cell.setCellStyle(headStyle);
- sheet.setColumnWidth(i, columnWidthArray[i] * 256);
-
- // 如果当前列名和上一列的名字一致，则合并
- if (i > 0 && headArray[i - 1].equals(headArray[i])) {
- if (headMergeObj == null) {
- int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
- headMergeObj = new Object[] { index, headArray[i], i - 1 }; // 合并的index值，表头值，行号。。
- } else {
- if (headMergeObj[1].equals(headArray[i])) {
- sheet.removeMergedRegion((int) headMergeObj[0]);
- int index = sheet.addMergedRegion(
- new CellRangeAddress(rowNum, rowNum, (int) headMergeObj[2], i));
- headMergeObj[0] = index;
- } else {
- int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
- headMergeObj = new Object[] { index, headArray[i], i - 1 }; // 合并的index值，表头值，行号。
- }
- }
- }
-
- }
- rowNum++;
-
- // 保存上一个map
- Map<String, String> preMap = null;
- // 保存某一列上一个合并的数据，并用来判断是否再合并（因为2个列合并了之后，就必须要先移除合并，才能合并）
- Map<Integer, Object[]> recordMap = new HashMap<>();
- // 保存某一行的上一个合并的数据，并用来判断是否再合并
- Object[] rowMergeObj = null;
- 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) 0X250; // 默认行高，可以放2行数据
- String preVal = null; // 保存某一行中，上一列的值
- for (String s : map.keySet()) {
- Object val = map.get(s);
- if (val == null) {
- val = "";
- }
-
- Cell cell = textRow.createCell(j);
- cell.setCellValue(String.valueOf(val));
-
- if (columnWidthAlignment[j] == 0) {
- cell.setCellStyle(textStyleCenter);
- } else if (columnWidthAlignment[j] == 1) {
- cell.setCellStyle(textStyleLeft);
- } else if (columnWidthAlignment[j] == 2) {
- cell.setCellStyle(textStyleRight);
- } else {
- 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;
- }
-
- // 判断是否需要进行列合并
- // 如果当前列名和上一列的名字一致，并且行的值也一致，则合并
- if (j > 0 && headArray[j - 1].equals(headArray[j]) && preVal != null
- && preVal.equals(val)) {
- if (rowMergeObj == null) {
- int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
- rowMergeObj = new Object[] { index, headArray[j - 1], preVal, i, j - 1 }; // 合并的index值，表头值，数据值，行号,列号。
- } else {
- // 当上一个合并的数据值、表头值、行号 与
- // 当前处理的单元格的数据一致时，才继续合并，否则单独合并两列
- if (rowMergeObj[2].equals(val) && rowMergeObj[1].equals(headArray[j])
- && i == (int) rowMergeObj[3]) {
- sheet.removeMergedRegion((int) rowMergeObj[0]);
- int index = sheet.addMergedRegion(
- new CellRangeAddress(rowNum, rowNum, (int) rowMergeObj[4], j));
- rowMergeObj[0] = index;
- } else {
- int index = sheet
- .addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
- rowMergeObj = new Object[] { index, headArray[j - 1], preVal, i, j - 1 }; // 合并的index值，表头值，数据值，行号,列号。
- }
- }
- }
-
- // 判断是否需要进行行合并
- if (i > 0 && preMap.get(s) != null && !preMap.get(s).equals("") && preMap.get(s).equals(val)
- && columnMergeCondition != null) { // 当前后的值都一致，才能满足最基本的合并条件
-
- boolean isMerge = true;
- String tempStr = "";
- // 当需要合并的列，满足必要的合并条件后，才允许合并
- for (int k = 0; k < columnMergeCondition.length; k++) {
- tempStr = columnMergeCondition[k];
-
- // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
- if (s.equals(tempStr)) {
- isMerge = true;
- break;
- } else if (!preMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
- isMerge = false;
- break;
- }
- }
-
- if (isMerge == true) {
- // 若不存在值，表明还未合并
- Object[] recordObj = recordMap.get(j);
- if (recordObj == null) {
- int index = sheet
- .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
- recordObj = new Object[] { index, currentData.get(i), rowNum - 1 }; // 合并的index值，MAP，行号。
- recordMap.put(j, recordObj);
- }
- // 若存在值，则判断，值是否一致，一致则合并，否则重新保存新的数据
- else {
- boolean isTempMerger = false;
- Map<String, String> tempMap = (Map) recordObj[1];
- // 当需要合并的列，满足必要的合并条件后，才允许合并
- for (int k = 0; k < columnMergeCondition.length; k++) {
- tempStr = columnMergeCondition[k];
-
- // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
- if (s.equals(tempStr) && tempMap.get(s).equals(val)) {
- isTempMerger = true;
- break;
- } else if (!tempMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
- isTempMerger = false;
- break;
- }
- }
- if (isTempMerger == true) {
- sheet.removeMergedRegion((int) recordObj[0]); // 先移除
- int index = sheet.addMergedRegion(
- new CellRangeAddress((int) recordObj[2], rowNum, j, j)); // 再合并
- recordObj[0] = index;
- recordMap.put(j, recordObj);
- } else { // 否则，重新保存此列的合并数据
- int index = sheet
- .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
- recordObj = new Object[] { index, currentData.get(i), rowNum - 1 }; // 保存新的合并的index值，列值，行号。
- recordMap.put(j, recordObj);
- }
-
- }
- }
- }
- preVal = (String) val;
- j++;
- }
-
- // 设置行高
- textRow.setHeight((short) maxTextHeight);
- // 保存上一个map
- preMap = currentData.get(i);
- 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;
- }
-
- /**
- * 导出EXCEL文件（单个sheet，可以多个表格）
- *
- * @param response
- * @param fileName 文件名称
- * @param sheetTitle sheet中的大标题（第一行）
- * @param listContent sheet中的数据集（list中每个map数据中，存放一个表格的数据；在每个map中又细分为多个不同的Object数据）
- * 如：（详见导出班级代码） List<Map<String, Object>> listContent = new
- * ArrayList<>(); //数据集
- *
- * Map<String, Object> roomAllMap = new LinkedHashMap<>();
- * //一个map中，代表一个表格 roomAllMap.put("data", roomList);
- * //此表格中的具体遍历数据 roomAllMap.put("title", "班级学员宿舍信息表");
- * //表格标题，若为null，且数据集中存在其他map，则下一个map不会空出3行（空出3行，用于划分各个表格）。
- * roomAllMap.put("head", new String[] { "姓名", "性别", "是否午休",
- * "是否晚宿" });
- * //若存在名字相同的，则名字相同且相邻的head合并（当head名字相同，并且某行中的对应的列值也相同，则合并它们）
- * roomAllMap.put("columnWidth", new Integer[] { 15, 15, 15,
- * 20 }); //
- * 20代表20个字节，10个字符（整个sheet中，只能存在一个columnWidth,因为列宽是针对整个sheet的）
- * roomAllMap.put("columnAlignment", new Integer[] { 0, 0, 0,
- * 0 }); // 0代表居中，1代表居左，2代表居右
- * roomAllMap.put("mergeCondition", null); //
- * 跨行合行列需要的条件，条件优先级按顺序决定，NULL表示不合并,空数组表示无条件
- * listContent.add(roomAllMap); //加入此map到数据集中
- *
- * @return
- * @throws IOException
- */
- public final static boolean exportCommonExcel(HttpServletResponse response, String fileName, String sheetTitle,
- List<Map<String, Object>> listContent) throws IOException {
- return exportCommonExcel(response, fileName, sheetTitle, listContent, null);
- }
-
- public final static boolean exportCommonExcel(HttpServletResponse response, String fileName, String sheetTitle,
- List<Map<String, Object>> listContent, String fileUrl) 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 {
- Sheet sheet = workbook.createSheet(fileName);
-
- // 创建基本的样式
- 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(0).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); // 给标题格设定值
- if (colCount != 1) {
- sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
- }
- // sheet.addMergedRegion(new CellRangeAddress(rowNum,
- // rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
- rowNum++;
- }
-
- // 处理数据
- for (Map<String, Object> dataList : listContent) {
-
- // 获取数据
- List<Map<String, String>> currentData = (List<Map<String, String>>) dataList.get("data");
- String title = (String) dataList.get("title");
- String[] headArray = (String[]) dataList.get("head");
- Map<String, Integer> columnWidthArray = (Map<String, Integer>) dataList.get("columnWidth");
- String[] pageHeadArray = (String[]) dataList.get("pageHead");
- String[] pageFooterArray = (String[]) dataList.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.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++;
- }
- }
-
- }
-
- if (fileUrl != null) {
- File localFile = new File(fileUrl);
- if (!localFile.exists()) {
- // 先得到文件的上级目录，并创建上级目录，在创建文件
- localFile.getParentFile().mkdirs();
- localFile.createNewFile();
- }
- fileOutputStream = new FileOutputStream(localFile);
- fileOutputStream.flush();
- } else {
- 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;
- }
-
- /**
- * 导出EXCEL文件（多个sheet，单个表格）
- *
- * @param response
- * @param fileName 文件名称
- * @param sheetTitle sheet中的大标题（第一行）
- * @param dataMap sheet中的数据集（每个map中细分为多个不同的Object数据） 如：（详见导出班级代码） Map<String,
- * Object> roomAllMap = new LinkedHashMap<>(); //一个map中，代表一个表格
- * roomAllMap.put("data", roomList); //此表格中的具体遍历数据
- * roomAllMap.put("title", "班级学员宿舍信息表");
- * //表格标题，若为null，且数据集中存在其他map，则下一个map不会空出3行（空出3行，用于划分各个表格）。
- * roomAllMap.put("head", new String[] { "姓名", "性别", "是否午休",
- * "是否晚宿" });
- * //若存在名字相同的，则名字相同且相邻的head合并（当head名字相同，并且某行中的对应的列值也相同，则合并它们）
- * roomAllMap.put("columnWidth", new Integer[] { 15, 15, 15,
- * 20 }); //
- * 20代表20个字节，10个字符（整个sheet中，只能存在一个columnWidth,因为列宽是针对整个sheet的）
- * roomAllMap.put("columnAlignment", new Integer[] { 0, 0, 0,
- * 0 }); // 0代表居中，1代表居左，2代表居右 roomAllMap.put("mergeCondition",
- * null); // 跨行合行列需要的条件，条件优先级按顺序决定，NULL表示不合并,空数组表示无条件
- * @param pageSize 每个sheet页的数据量
- * @return
- * @throws IOException
- */
- public final static boolean exportCommonExcelMultiSheet(HttpServletResponse response, String fileName,
- String sheetTitle, Map<String, Object> dataMap, int pageSize) 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 != dataMap) {
-
- try {
- // 计算总页数
- List<Map<String, String>> currentData = (List<Map<String, String>>) dataMap.get("data");
-
- // 创建基本的样式
- 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);
- CellStyle pageSizeStyle = getCellStyle(workbook, "", (short) 9, false, HorizontalAlignment.RIGHT,
- VerticalAlignment.CENTER, false);
- pageSizeStyle.setBorderLeft(BorderStyle.NONE);
- pageSizeStyle.setBorderRight(BorderStyle.NONE);
- pageSizeStyle.setBorderBottom(BorderStyle.NONE);
- pageSizeStyle.setBorderTop(BorderStyle.NONE);
-
- int page = (currentData.size() + pageSize - 1) / pageSize;
- for (int pageI = 0; pageI < page; pageI++) {
-
- Sheet sheet = workbook.createSheet("第" + (pageI + 1) + "页");
-
- int rowNum = 0; // 初始化第一行为0开始计数
- int colCount = ((String[]) dataMap.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); // 给标题格设定值
- if (colCount != 1) {
- sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
- }
- // sheet.addMergedRegion(new CellRangeAddress(rowNum,
- // rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
- rowNum++;
- }
-
- String title = (String) dataMap.get("title");
- String[] headArray = (String[]) dataMap.get("head");
- Map<String, Integer> columnWidthArray = (Map<String, Integer>) dataMap.get("columnWidth");
- String[] pageHeadArray = (String[]) dataMap.get("pageHead");
- String[] pageFooterArray = (String[]) dataMap.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.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 pageJ = pageSize * pageI; pageJ < (pageI + 1) * pageSize
- && pageJ < currentData.size(); pageJ++) {
-
- Row textRow = sheet.createRow(rowNum);
- Map<String, String> map = currentData.get(pageJ);
- 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) {
-
- // 当最后一页时，才输出统计信息
- if (pageI + 1 == page) {
- CellStyle pageFooterStyle = getCellStyle(workbook, "", (short) 9, false,
- HorizontalAlignment.LEFT, 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++;
- }
- }
- }
-
- // 显示页数
- Row footerRow = sheet.createRow(rowNum); // 页脚行
- footerRow.setHeight((short) 0x180); // 行高
- Cell pageCell = footerRow.createCell(0); // 创建单元格
- pageCell.setCellStyle(pageSizeStyle); // 样式
- pageCell.setCellValue("第" + (pageI + 1) + "页 - 共" + page + "页 "); // 值
- if (colCount != 1) {
- 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;
- }
-
- /*
- * 获取导出数据的方法
- *
- * @param exportField 需要导出的字段
- *
- * @param exportList 查询的结果集
- *
- */
- public static List<Map<String, Object>> getDataList(String exportField, List<?> exportList,
- List<Map<String, Object>> listMapDicItem) {
-
- // 最终导出的数据
- List<Map<String, Object>> allList = new ArrayList<>();
-
- String[] exportFields = exportField.split(",");
-
- Map<Integer, String> filedTitleMap = new TreeMap<Integer, String>();// 存放字段
- Map<Integer, String> titleMap = new TreeMap<Integer, String>();// 存放名字
- Map<String, Integer> widthMap = new TreeMap<String, Integer>();// 存放列宽
- for (int i = 0; i < exportFields.length; i++) {
- filedTitleMap.put(i, exportFields[i]);
- }
- Collection<String> values = filedTitleMap.values();
- String[] s = new String[values.size()];
- values.toArray(s);
-
- // 处理基本数据
- Map<String, String> dataMap = null;
- List<Map<String, String>> dataList = new ArrayList<>();
- if (null != exportList && !exportList.isEmpty()) {
- Object test = exportList.get(0);
- Map<String, Field> fieldMap = new HashMap<String, Field>();
- Map<String, String> titleNameMap = new HashMap<String, String>();
- Map<String, Integer> widthNameMap = new TreeMap<String, Integer>();// 存放列宽
- // Field[] fields = test.getClass().getDeclaredFields();
- Field[] fields = ModelUtil.getClassFields(test.getClass(), false);
- int k = 0;
- for (Field field : fields) {
- if (field.isAnnotationPresent(FieldInfo.class)) {
- FieldInfo mapperCell = field.getAnnotation(FieldInfo.class);
- fieldMap.put(field.getName(), field);
- filedTitleMap.put(k, field.getName());
- titleNameMap.put(field.getName(), mapperCell.explain());
- // widthMap.put(mapperCell.cellName(),
- // mapperCell.cellWidth());
- widthNameMap.put(mapperCell.explain(), 10);
- k++;
- }
- }
-
- // 存放基础数据
- for (int i = 0; i < exportList.size(); i++) {
- dataMap = new LinkedHashMap<>();
- for (int j = 0; j < s.length; j++) {
- for (Map.Entry<String, Field> data : fieldMap.entrySet()) {
- if (data.getKey().equals(s[j])) {
- Field field = data.getValue();
- field.setAccessible(true);
- titleMap.put(j, titleNameMap.get(s[j]));
- String str = titleNameMap.get(s[j]);
- widthMap.put(titleNameMap.get(s[j]), widthNameMap.get(titleNameMap.get(s[j])));
- String value = "";
-
- 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()
- : "";
- } else if (field.getGenericType().toString().equals("class java.lang.Boolean")) {
- Boolean bool = (Boolean) field.get(exportList.get(i));
- value = bool != null && bool == true ? "是" : "否";
- } else {
- value = field.get(exportList.get(i)) != null
- ? field.get(exportList.get(i)).toString()
- : "";
- }
- //
- } catch (IllegalArgumentException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } catch (IllegalAccessException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- 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]);
- value = mapDicItem.get(value);
- }
- }
- }
-
- dataMap.put(s[j], value);
- }
- }
- }
- dataList.add(dataMap);
- }
- }
-
- values = titleMap.values();
- s = new String[values.size()];
- values.toArray(s);
-
- Map<String, Object> dataAllMap = new LinkedHashMap<>();
- dataAllMap.put("data", dataList);
- dataAllMap.put("head", s);
- dataAllMap.put("columnWidth", widthMap);
- allList.add(dataAllMap);
- return allList;
- }
-
-
-}
+package cc.mrbird.febs.server.hr.util;
+
+import java.io.File;
+import java.io.FileOutputStream;
+import java.io.IOException;
+import java.io.OutputStream;
+import java.lang.reflect.Field;
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Date;
+import java.util.HashMap;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.TreeMap;
+
+import javax.servlet.http.HttpServletResponse;
+
+import org.apache.commons.lang3.StringUtils;
+import org.apache.poi.hssf.usermodel.HSSFFont;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.BorderStyle;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellStyle;
+import org.apache.poi.ss.usermodel.HorizontalAlignment;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.VerticalAlignment;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+import cc.mrbird.febs.common.core.annotation.FieldInfo;
+import cn.hutool.core.date.DateUtil;
+
+public class PoiExportExcel {
+
+ /**
+ * 导出EXCEL文件（单个sheet，可以多个表格）
+ *
+ * @param response
+ * @param fileName 文件名称
+ * @param sheetTitle sheet中的大标题（第一行）
+ * @param listContent sheet中的数据集（list中每个map数据中，存放一个表格的数据；在每个map中又细分为多个不同的Object数据）
+ * 如：（详见导出班级代码） List<Map<String, Object>> listContent = new
+ * ArrayList<>(); //数据集
+ * <p>
+ * Map<String, Object> roomAllMap = new LinkedHashMap<>();
+ * //一个map中，代表一个表格 roomAllMap.put("data", roomList);
+ * //此表格中的具体遍历数据 roomAllMap.put("title", "班级学员宿舍信息表");
+ * //表格标题，若为null，且数据集中存在其他map，则下一个map不会空出3行（空出3行，用于划分各个表格）。
+ * roomAllMap.put("head", new String[] { "姓名", "性别", "是否午休",
+ * "是否晚宿" });
+ * //若存在名字相同的，则名字相同且相邻的head合并（当head名字相同，并且某行中的对应的列值也相同，则合并它们）
+ * roomAllMap.put("columnWidth", new Integer[] { 15, 15, 15,
+ * 20 }); //
+ * 20代表20个字节，10个字符（整个sheet中，只能存在一个columnWidth,因为列宽是针对整个sheet的）
+ * roomAllMap.put("columnAlignment", new Integer[] { 0, 0, 0,
+ * 0 }); // 0代表居中，1代表居左，2代表居右
+ * roomAllMap.put("mergeCondition", null); //
+ * 跨行合行列需要的条件，条件优先级按顺序决定，NULL表示不合并,空数组表示无条件
+ * listContent.add(roomAllMap); //加入此map到数据集中
+ * @return
+ * @throws IOException
+ */
+ public final static boolean exportExcel(HttpServletResponse response, String fileName, String sheetTitle,
+ List<Map<String, Object>> listContent) 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 {
+ Sheet sheet = workbook.createSheet(fileName);
+
+ // 创建基本的样式
+ CellStyle titleStyle = getCellStyle(workbook, "", (short) 26, true, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle headStyle = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleCenter = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleLeft = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.LEFT,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleRight = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.RIGHT,
+ VerticalAlignment.CENTER, true);
+
+ int rowNum = 0; // 初始化第一行为0开始计数
+ int colCount = ((String[]) listContent.get(0).get("head")).length;// 表头的列数
+
+ // 第一行先创建一个大标题(当不为null的时候，设置这一行)
+ if (sheetTitle != null) {
+ Row sheetTitleRow = sheet.createRow(rowNum); // 创建标题行
+ sheetTitleRow.setHeight((short) 0x300); // 设置行高
+ Cell sheetTitleCell = sheetTitleRow.createCell(0); // 创建第一个单元格
+ sheetTitleCell.setCellStyle(titleStyle); // 设置标题的样式
+ sheetTitleCell.setCellValue(sheetTitle); // 给标题格设定值
+ sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
+ rowNum++;
+ }
+
+ // 处理数据
+ for (Map<String, Object> dataList : listContent) {
+
+ // 获取数据
+ List<Map<String, String>> currentData = (List<Map<String, String>>) dataList.get("data");
+ String title = (String) dataList.get("title");
+ String[] headArray = (String[]) dataList.get("head");
+ Integer[] columnWidthArray = (Integer[]) dataList.get("columnWidth");
+ Integer[] columnWidthAlignment = (Integer[]) dataList.get("columnAlignment");
+ String[] columnMergeCondition = (String[]) dataList.get("mergeCondition");
+
+ // 设置标题栏内容(当不为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++;
+ }
+
+ // 设置表头内容
+ Row headRow = sheet.createRow(rowNum); // 表头行
+ headRow.setHeight((short) 0x200); // 表头行高
+ Object[] headMergeObj = null; // （因为2个行或列合并了之后，就必须要先移除合并，才能重新合并更多的行）
+ for (int i = 0; i < headArray.length; i++) {
+ Cell cell = headRow.createCell(i);
+ cell.setCellValue(headArray[i]);
+ cell.setCellStyle(headStyle);
+ sheet.setColumnWidth(i, columnWidthArray[i] * 256);
+
+ // 如果当前列名和上一列的名字一致，则合并
+ if (i > 0 && headArray[i - 1].equals(headArray[i])) {
+ if (headMergeObj == null) {
+ int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
+ headMergeObj = new Object[]{index, headArray[i], i - 1}; // 合并的index值，表头值，行号。。
+ } else {
+ if (headMergeObj[1].equals(headArray[i])) {
+ sheet.removeMergedRegion((int) headMergeObj[0]);
+ int index = sheet.addMergedRegion(
+ new CellRangeAddress(rowNum, rowNum, (int) headMergeObj[2], i));
+ headMergeObj[0] = index;
+ } else {
+ int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
+ headMergeObj = new Object[]{index, headArray[i], i - 1}; // 合并的index值，表头值，行号。
+ }
+ }
+ }
+
+ }
+ rowNum++;
+
+ // 保存上一个map
+ Map<String, String> preMap = null;
+ // 保存某一列上一个合并的数据，并用来判断是否再合并（因为2个列合并了之后，就必须要先移除合并，才能合并）
+ Map<Integer, Object[]> recordMap = new HashMap<>();
+ // 保存某一行的上一个合并的数据，并用来判断是否再合并
+ Object[] rowMergeObj = null;
+ 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) 0X250; // 默认行高，可以放2行数据
+ String preVal = null; // 保存某一行中，上一列的值
+ for (String s : map.keySet()) {
+ Object val = map.get(s);
+ if (val == null) {
+ val = "";
+ }
+
+ Cell cell = textRow.createCell(j);
+ cell.setCellValue(String.valueOf(val));
+
+ if (columnWidthAlignment[j] == 0) {
+ cell.setCellStyle(textStyleCenter);
+ } else if (columnWidthAlignment[j] == 1) {
+ cell.setCellStyle(textStyleLeft);
+ } else if (columnWidthAlignment[j] == 2) {
+ cell.setCellStyle(textStyleRight);
+ } else {
+ 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;
+ }
+
+ // 判断是否需要进行列合并
+ // 如果当前列名和上一列的名字一致，并且行的值也一致，则合并
+ if (j > 0 && headArray[j - 1].equals(headArray[j]) && preVal != null
+ && preVal.equals(val)) {
+ if (rowMergeObj == null) {
+ int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
+ rowMergeObj = new Object[]{index, headArray[j - 1], preVal, i, j - 1}; // 合并的index值，表头值，数据值，行号,列号。
+ } else {
+ // 当上一个合并的数据值、表头值、行号 与
+ // 当前处理的单元格的数据一致时，才继续合并，否则单独合并两列
+ if (rowMergeObj[2].equals(val) && rowMergeObj[1].equals(headArray[j])
+ && i == (int) rowMergeObj[3]) {
+ sheet.removeMergedRegion((int) rowMergeObj[0]);
+ int index = sheet.addMergedRegion(
+ new CellRangeAddress(rowNum, rowNum, (int) rowMergeObj[4], j));
+ rowMergeObj[0] = index;
+ } else {
+ int index = sheet
+ .addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
+ rowMergeObj = new Object[]{index, headArray[j - 1], preVal, i, j - 1}; // 合并的index值，表头值，数据值，行号,列号。
+ }
+ }
+ }
+
+ // 判断是否需要进行行合并
+ if (i > 0 && preMap.get(s) != null && !preMap.get(s).equals("") && preMap.get(s).equals(val)
+ && columnMergeCondition != null) { // 当前后的值都一致，才能满足最基本的合并条件
+
+ boolean isMerge = true;
+ String tempStr = "";
+ // 当需要合并的列，满足必要的合并条件后，才允许合并
+ for (int k = 0; k < columnMergeCondition.length; k++) {
+ tempStr = columnMergeCondition[k];
+
+ // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
+ if (s.equals(tempStr)) {
+ isMerge = true;
+ break;
+ } else if (!preMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
+ isMerge = false;
+ break;
+ }
+ }
+
+ if (isMerge == true) {
+ // 若不存在值，表明还未合并
+ Object[] recordObj = recordMap.get(j);
+ if (recordObj == null) {
+ int index = sheet
+ .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
+ recordObj = new Object[]{index, currentData.get(i), rowNum - 1}; // 合并的index值，MAP，行号。
+ recordMap.put(j, recordObj);
+ }
+ // 若存在值，则判断，值是否一致，一致则合并，否则重新保存新的数据
+ else {
+ boolean isTempMerger = false;
+ Map<String, String> tempMap = (Map) recordObj[1];
+ // 当需要合并的列，满足必要的合并条件后，才允许合并
+ for (int k = 0; k < columnMergeCondition.length; k++) {
+ tempStr = columnMergeCondition[k];
+
+ // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
+ if (s.equals(tempStr) && tempMap.get(s).equals(val)) {
+ isTempMerger = true;
+ break;
+ } else if (!tempMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
+ isTempMerger = false;
+ break;
+ }
+ }
+ if (isTempMerger == true) {
+ sheet.removeMergedRegion((int) recordObj[0]); // 先移除
+ int index = sheet.addMergedRegion(
+ new CellRangeAddress((int) recordObj[2], rowNum, j, j)); // 再合并
+ recordObj[0] = index;
+ recordMap.put(j, recordObj);
+ } else { // 否则，重新保存此列的合并数据
+ int index = sheet
+ .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
+ recordObj = new Object[]{index, currentData.get(i), rowNum - 1}; // 保存新的合并的index值，列值，行号。
+ recordMap.put(j, recordObj);
+ }
+
+ }
+ }
+ }
+ preVal = (String) val;
+ j++;
+ }
+
+ // 设置行高
+ textRow.setHeight((short) maxTextHeight);
+ // 保存上一个map
+ preMap = currentData.get(i);
+ 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;
+ }
+
+ public final static boolean exportExcelFile(HttpServletResponse response, String fileName, String sheetTitle,
+ List<Map<String, Object>> listContent, String fileUrl) 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 {
+ Sheet sheet = workbook.createSheet(fileName);
+
+ // 创建基本的样式
+ CellStyle titleStyle = getCellStyle(workbook, "", (short) 26, true, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle headStyle = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleCenter = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleLeft = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.LEFT,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleRight = getCellStyle(workbook, "", (short) 11, false, HorizontalAlignment.RIGHT,
+ VerticalAlignment.CENTER, true);
+
+ int rowNum = 0; // 初始化第一行为0开始计数
+ int colCount = ((String[]) listContent.get(0).get("head")).length;// 表头的列数
+
+ // 第一行先创建一个大标题(当不为null的时候，设置这一行)
+ if (sheetTitle != null) {
+ Row sheetTitleRow = sheet.createRow(rowNum); // 创建标题行
+ sheetTitleRow.setHeight((short) 0x300); // 设置行高
+ Cell sheetTitleCell = sheetTitleRow.createCell(0); // 创建第一个单元格
+ sheetTitleCell.setCellStyle(titleStyle); // 设置标题的样式
+ sheetTitleCell.setCellValue(sheetTitle); // 给标题格设定值
+ sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
+ rowNum++;
+ }
+
+ // 处理数据
+ for (Map<String, Object> dataList : listContent) {
+
+ // 获取数据
+ List<Map<String, String>> currentData = (List<Map<String, String>>) dataList.get("data");
+ String title = (String) dataList.get("title");
+ String[] headArray = (String[]) dataList.get("head");
+ Integer[] columnWidthArray = (Integer[]) dataList.get("columnWidth");
+ Integer[] columnWidthAlignment = (Integer[]) dataList.get("columnAlignment");
+ String[] columnMergeCondition = (String[]) dataList.get("mergeCondition");
+
+ // 设置标题栏内容(当不为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++;
+ }
+
+ // 设置表头内容
+ Row headRow = sheet.createRow(rowNum); // 表头行
+ headRow.setHeight((short) 0x200); // 表头行高
+ Object[] headMergeObj = null; // （因为2个行或列合并了之后，就必须要先移除合并，才能重新合并更多的行）
+ for (int i = 0; i < headArray.length; i++) {
+ Cell cell = headRow.createCell(i);
+ cell.setCellValue(headArray[i]);
+ cell.setCellStyle(headStyle);
+ sheet.setColumnWidth(i, columnWidthArray[i] * 256);
+
+ // 如果当前列名和上一列的名字一致，则合并
+ if (i > 0 && headArray[i - 1].equals(headArray[i])) {
+ if (headMergeObj == null) {
+ int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
+ headMergeObj = new Object[]{index, headArray[i], i - 1}; // 合并的index值，表头值，行号。。
+ } else {
+ if (headMergeObj[1].equals(headArray[i])) {
+ sheet.removeMergedRegion((int) headMergeObj[0]);
+ int index = sheet.addMergedRegion(
+ new CellRangeAddress(rowNum, rowNum, (int) headMergeObj[2], i));
+ headMergeObj[0] = index;
+ } else {
+ int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
+ headMergeObj = new Object[]{index, headArray[i], i - 1}; // 合并的index值，表头值，行号。
+ }
+ }
+ }
+
+ }
+ rowNum++;
+
+ // 保存上一个map
+ Map<String, String> preMap = null;
+ // 保存某一列上一个合并的数据，并用来判断是否再合并（因为2个列合并了之后，就必须要先移除合并，才能合并）
+ Map<Integer, Object[]> recordMap = new HashMap<>();
+ // 保存某一行的上一个合并的数据，并用来判断是否再合并
+ Object[] rowMergeObj = null;
+ 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) 0X250; // 默认行高，可以放2行数据
+ String preVal = null; // 保存某一行中，上一列的值
+ for (String s : map.keySet()) {
+ Object val = map.get(s);
+ if (val == null) {
+ val = "";
+ }
+
+ Cell cell = textRow.createCell(j);
+ cell.setCellValue(String.valueOf(val));
+
+ if (columnWidthAlignment[j] == 0) {
+ cell.setCellStyle(textStyleCenter);
+ } else if (columnWidthAlignment[j] == 1) {
+ cell.setCellStyle(textStyleLeft);
+ } else if (columnWidthAlignment[j] == 2) {
+ cell.setCellStyle(textStyleRight);
+ } else {
+ 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;
+ }
+
+ // 判断是否需要进行列合并
+ // 如果当前列名和上一列的名字一致，并且行的值也一致，则合并
+ if (j > 0 && headArray[j - 1].equals(headArray[j]) && preVal != null
+ && preVal.equals(val)) {
+ if (rowMergeObj == null) {
+ int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
+ rowMergeObj = new Object[]{index, headArray[j - 1], preVal, i, j - 1}; // 合并的index值，表头值，数据值，行号,列号。
+ } else {
+ // 当上一个合并的数据值、表头值、行号 与
+ // 当前处理的单元格的数据一致时，才继续合并，否则单独合并两列
+ if (rowMergeObj[2].equals(val) && rowMergeObj[1].equals(headArray[j])
+ && i == (int) rowMergeObj[3]) {
+ sheet.removeMergedRegion((int) rowMergeObj[0]);
+ int index = sheet.addMergedRegion(
+ new CellRangeAddress(rowNum, rowNum, (int) rowMergeObj[4], j));
+ rowMergeObj[0] = index;
+ } else {
+ int index = sheet
+ .addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
+ rowMergeObj = new Object[]{index, headArray[j - 1], preVal, i, j - 1}; // 合并的index值，表头值，数据值，行号,列号。
+ }
+ }
+ }
+
+ // 判断是否需要进行行合并
+ if (i > 0 && preMap.get(s) != null && !preMap.get(s).equals("") && preMap.get(s).equals(val)
+ && columnMergeCondition != null) { // 当前后的值都一致，才能满足最基本的合并条件
+
+ boolean isMerge = true;
+ String tempStr = "";
+ // 当需要合并的列，满足必要的合并条件后，才允许合并
+ for (int k = 0; k < columnMergeCondition.length; k++) {
+ tempStr = columnMergeCondition[k];
+
+ // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
+ if (s.equals(tempStr)) {
+ isMerge = true;
+ break;
+ } else if (!preMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
+ isMerge = false;
+ break;
+ }
+ }
+
+ if (isMerge == true) {
+ // 若不存在值，表明还未合并
+ Object[] recordObj = recordMap.get(j);
+ if (recordObj == null) {
+ int index = sheet
+ .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
+ recordObj = new Object[]{index, currentData.get(i), rowNum - 1}; // 合并的index值，MAP，行号。
+ recordMap.put(j, recordObj);
+ }
+ // 若存在值，则判断，值是否一致，一致则合并，否则重新保存新的数据
+ else {
+ boolean isTempMerger = false;
+ Map<String, String> tempMap = (Map) recordObj[1];
+ // 当需要合并的列，满足必要的合并条件后，才允许合并
+ for (int k = 0; k < columnMergeCondition.length; k++) {
+ tempStr = columnMergeCondition[k];
+
+ // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
+ if (s.equals(tempStr) && tempMap.get(s).equals(val)) {
+ isTempMerger = true;
+ break;
+ } else if (!tempMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
+ isTempMerger = false;
+ break;
+ }
+ }
+ if (isTempMerger == true) {
+ sheet.removeMergedRegion((int) recordObj[0]); // 先移除
+ int index = sheet.addMergedRegion(
+ new CellRangeAddress((int) recordObj[2], rowNum, j, j)); // 再合并
+ recordObj[0] = index;
+ recordMap.put(j, recordObj);
+ } else { // 否则，重新保存此列的合并数据
+ int index = sheet
+ .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
+ recordObj = new Object[]{index, currentData.get(i), rowNum - 1}; // 保存新的合并的index值，列值，行号。
+ recordMap.put(j, recordObj);
+ }
+
+ }
+ }
+ }
+ preVal = (String) val;
+ j++;
+ }
+
+ // 设置行高
+ textRow.setHeight((short) maxTextHeight);
+ // 保存上一个map
+ preMap = currentData.get(i);
+ rowNum++;
+ }
+
+ }
+
+ File localFile = new File(fileUrl);
+ if (!localFile.exists()) {
+ // 先得到文件的上级目录，并创建上级目录，在创建文件
+ localFile.getParentFile().mkdirs();
+ localFile.createNewFile();
+ }
+ fileOutputStream = new FileOutputStream(localFile);
+ fileOutputStream.flush();
+
+ 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;
+ }
+
+ /*
+ * 获取样式格式的方法
+ *
+ * @param workbook Excel文件
+ *
+ * @param fontName 字体名称
+ *
+ * @param fontSize 字体大小
+ *
+ * @param isBold 是否加粗
+ *
+ * @param horizontalAlignment (左，右，居中)对齐
+ *
+ * @param verticalAlignment (上，下，居中)对齐
+ *
+ * @param isWrapText 是否换行
+ *
+ */
+ public static CellStyle getCellStyle(HSSFWorkbook workbook, String fontName, Short fontSize, Boolean isBold,
+ HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, Boolean isWrapText) {
+
+ HSSFFont font = workbook.createFont();
+ // 默认方正黑体
+ if (StringUtils.isBlank(fontName)) {
+ font.setFontName("方正黑体简体");
+ } else {
+ font.setFontName(fontName);
+ }
+ font.setFontHeightInPoints(fontSize);// 字体大小
+ font.setBold(isBold); // 是否加粗
+
+ CellStyle style = workbook.createCellStyle();
+ style.setFont(font);
+ style.setAlignment(horizontalAlignment);// 左右对齐
+ style.setVerticalAlignment(verticalAlignment);// 上下对齐
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setBorderTop(BorderStyle.THIN);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setWrapText(isWrapText);
+
+ return style;
+ }
+
+ /*
+ * 导出的数据是多个sheet表的
+ */
+ public final static boolean exportSheetsExcel(HttpServletResponse response, String fileName,
+ List<String> sheetNames, String sheetTitle, List<Map<String, Object>> listContent) 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 {
+ // 创建基本的样式
+ CellStyle headStyle = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleCenter = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.CENTER,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleLeft = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.LEFT,
+ VerticalAlignment.CENTER, true);
+ CellStyle textStyleRight = getCellStyle(workbook, "", (short) 11, true, HorizontalAlignment.RIGHT,
+ VerticalAlignment.CENTER, true);
+
+ int rowNum = 0; // 初始化第一行为0开始计数
+ // 处理数据
+ for (Map<String, Object> dataList : listContent) {
+
+ int sheetNumber = 0;
+ // 获取数据
+ List<Map<String, String>> currentData = (List<Map<String, String>>) dataList.get("data");
+ String title = (String) dataList.get("title");
+ String[] headArray = (String[]) dataList.get("head");
+ Integer[] columnWidthArray = (Integer[]) dataList.get("columnWidth");
+ Integer[] columnWidthAlignment = (Integer[]) dataList.get("columnAlignment");
+ String[] columnMergeCondition = (String[]) dataList.get("mergeCondition");
+
+ Sheet sheet = workbook.createSheet(sheetNames.get(sheetNumber));
+ sheetNumber++;
+
+ // 设置标题栏内容(当不为null的时候，设置这一行)
+ if (title != null) {
+ 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++;
+ }
+
+ // 设置表头内容
+ Row headRow = sheet.createRow(rowNum); // 表头行
+ headRow.setHeight((short) 0x200); // 表头行高
+ Object[] headMergeObj = null; // （因为2个行或列合并了之后，就必须要先移除合并，才能重新合并更多的行）
+ for (int i = 0; i < headArray.length; i++) {
+ Cell cell = headRow.createCell(i);
+ cell.setCellValue(headArray[i]);
+ cell.setCellStyle(headStyle);
+ sheet.setColumnWidth(i, columnWidthArray[i] * 256);
+
+ // 如果当前列名和上一列的名字一致，则合并
+ if (i > 0 && headArray[i - 1].equals(headArray[i])) {
+ if (headMergeObj == null) {
+ int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
+ headMergeObj = new Object[]{index, headArray[i], i - 1}; // 合并的index值，表头值，行号。。
+ } else {
+ if (headMergeObj[1].equals(headArray[i])) {
+ sheet.removeMergedRegion((int) headMergeObj[0]);
+ int index = sheet.addMergedRegion(
+ new CellRangeAddress(rowNum, rowNum, (int) headMergeObj[2], i));
+ headMergeObj[0] = index;
+ } else {
+ int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, i - 1, i));
+ headMergeObj = new Object[]{index, headArray[i], i - 1}; // 合并的index值，表头值，行号。
+ }
+ }
+ }
+
+ }
+ rowNum++;
+
+ // 保存上一个map
+ Map<String, String> preMap = null;
+ // 保存某一列上一个合并的数据，并用来判断是否再合并（因为2个列合并了之后，就必须要先移除合并，才能合并）
+ Map<Integer, Object[]> recordMap = new HashMap<>();
+ // 保存某一行的上一个合并的数据，并用来判断是否再合并
+ Object[] rowMergeObj = null;
+ 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) 0X250; // 默认行高，可以放2行数据
+ String preVal = null; // 保存某一行中，上一列的值
+ for (String s : map.keySet()) {
+ Object val = map.get(s);
+ if (val == null) {
+ val = "";
+ }
+
+ Cell cell = textRow.createCell(j);
+ cell.setCellValue(String.valueOf(val));
+
+ if (columnWidthAlignment[j] == 0) {
+ cell.setCellStyle(textStyleCenter);
+ } else if (columnWidthAlignment[j] == 1) {
+ cell.setCellStyle(textStyleLeft);
+ } else if (columnWidthAlignment[j] == 2) {
+ cell.setCellStyle(textStyleRight);
+ } else {
+ 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;
+ }
+
+ // 判断是否需要进行列合并
+ // 如果当前列名和上一列的名字一致，并且行的值也一致，则合并
+ if (j > 0 && headArray[j - 1].equals(headArray[j]) && preVal != null
+ && preVal.equals(val)) {
+ if (rowMergeObj == null) {
+ int index = sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
+ rowMergeObj = new Object[]{index, headArray[j - 1], preVal, i, j - 1}; // 合并的index值，表头值，数据值，行号,列号。
+ } else {
+ // 当上一个合并的数据值、表头值、行号 与
+ // 当前处理的单元格的数据一致时，才继续合并，否则单独合并两列
+ if (rowMergeObj[2].equals(val) && rowMergeObj[1].equals(headArray[j])
+ && i == (int) rowMergeObj[3]) {
+ sheet.removeMergedRegion((int) rowMergeObj[0]);
+ int index = sheet.addMergedRegion(
+ new CellRangeAddress(rowNum, rowNum, (int) rowMergeObj[4], j));
+ rowMergeObj[0] = index;
+ } else {
+ int index = sheet
+ .addMergedRegion(new CellRangeAddress(rowNum, rowNum, j - 1, j));
+ rowMergeObj = new Object[]{index, headArray[j - 1], preVal, i, j - 1}; // 合并的index值，表头值，数据值，行号,列号。
+ }
+ }
+ }
+
+ // 判断是否需要进行行合并
+ if (i > 0 && preMap.get(s) != null && !preMap.get(s).equals("") && preMap.get(s).equals(val)
+ && columnMergeCondition != null) { // 当前后的值都一致，才能满足最基本的合并条件
+
+ boolean isMerge = true;
+ String tempStr = "";
+ // 当需要合并的列，满足必要的合并条件后，才允许合并
+ for (int k = 0; k < columnMergeCondition.length; k++) {
+ tempStr = columnMergeCondition[k];
+
+ // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
+ if (s.equals(tempStr)) {
+ isMerge = true;
+ break;
+ } else if (!preMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
+ isMerge = false;
+ break;
+ }
+ }
+
+ if (isMerge == true) {
+ // 若不存在值，表明还未合并
+ Object[] recordObj = recordMap.get(j);
+ if (recordObj == null) {
+ int index = sheet
+ .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
+ recordObj = new Object[]{index, currentData.get(i), rowNum - 1}; // 合并的index值，MAP，行号。
+ recordMap.put(j, recordObj);
+ }
+ // 若存在值，则判断，值是否一致，一致则合并，否则重新保存新的数据
+ else {
+ boolean isTempMerger = false;
+ Map<String, String> tempMap = (Map) recordObj[1];
+ // 当需要合并的列，满足必要的合并条件后，才允许合并
+ for (int k = 0; k < columnMergeCondition.length; k++) {
+ tempStr = columnMergeCondition[k];
+
+ // 当前判断的列为条件中的列时，可以直接合并（因为列是有顺序的，所以当判断到当前列的时候，表明前面的列条件都判断完毕）
+ if (s.equals(tempStr) && tempMap.get(s).equals(val)) {
+ isTempMerger = true;
+ break;
+ } else if (!tempMap.get(tempStr).equals(map.get(tempStr))) { // 当其中一个条件不满足，则不允许合并
+ isTempMerger = false;
+ break;
+ }
+ }
+ if (isTempMerger == true) {
+ sheet.removeMergedRegion((int) recordObj[0]); // 先移除
+ int index = sheet.addMergedRegion(
+ new CellRangeAddress((int) recordObj[2], rowNum, j, j)); // 再合并
+ recordObj[0] = index;
+ recordMap.put(j, recordObj);
+ } else { // 否则，重新保存此列的合并数据
+ int index = sheet
+ .addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, j, j));
+ recordObj = new Object[]{index, currentData.get(i), rowNum - 1}; // 保存新的合并的index值，列值，行号。
+ recordMap.put(j, recordObj);
+ }
+
+ }
+ }
+ }
+ preVal = (String) val;
+ j++;
+ }
+
+ // 设置行高
+ textRow.setHeight((short) maxTextHeight);
+ // 保存上一个map
+ preMap = currentData.get(i);
+ 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;
+ }
+
+ /**
+ * 导出EXCEL文件（单个sheet，可以多个表格）
+ *
+ * @param response
+ * @param fileName 文件名称
+ * @param sheetTitle sheet中的大标题（第一行）
+ * @param listContent sheet中的数据集（list中每个map数据中，存放一个表格的数据；在每个map中又细分为多个不同的Object数据）
+ * 如：（详见导出班级代码） List<Map<String, Object>> listContent = new
+ * ArrayList<>(); //数据集
+ * <p>
+ * Map<String, Object> roomAllMap = new LinkedHashMap<>();
+ * //一个map中，代表一个表格 roomAllMap.put("data", roomList);
+ * //此表格中的具体遍历数据 roomAllMap.put("title", "班级学员宿舍信息表");
+ * //表格标题，若为null，且数据集中存在其他map，则下一个map不会空出3行（空出3行，用于划分各个表格）。
+ * roomAllMap.put("head", new String[] { "姓名", "性别", "是否午休",
+ * "是否晚宿" });
+ * //若存在名字相同的，则名字相同且相邻的head合并（当head名字相同，并且某行中的对应的列值也相同，则合并它们）
+ * roomAllMap.put("columnWidth", new Integer[] { 15, 15, 15,
+ * 20 }); //
+ * 20代表20个字节，10个字符（整个sheet中，只能存在一个columnWidth,因为列宽是针对整个sheet的）
+ * roomAllMap.put("columnAlignment", new Integer[] { 0, 0, 0,
+ * 0 }); // 0代表居中，1代表居左，2代表居右
+ * roomAllMap.put("mergeCondition", null); //
+ * 跨行合行列需要的条件，条件优先级按顺序决定，NULL表示不合并,空数组表示无条件
+ * listContent.add(roomAllMap); //加入此map到数据集中
+ * @return
+ * @throws IOException
+ */
+ public final static boolean exportCommonExcel(HttpServletResponse response, String fileName, String sheetTitle,
+ List<Map<String, Object>> listContent) throws IOException {
+ return exportCommonExcel(response, fileName, sheetTitle, listContent, null);
+ }
+
+ public final static boolean exportCommonExcel(HttpServletResponse response, String fileName, String sheetTitle,
+ List<Map<String, Object>> listContent, String fileUrl) 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 {
+ Sheet sheet = workbook.createSheet(fileName);
+
+ // 创建基本的样式
+ 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(0).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); // 给标题格设定值
+ if (colCount != 1) {
+ sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
+ }
+ // sheet.addMergedRegion(new CellRangeAddress(rowNum,
+ // rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
+ rowNum++;
+ }
+
+ // 处理数据
+ for (Map<String, Object> dataList : listContent) {
+
+ // 获取数据
+ List<Map<String, String>> currentData = (List<Map<String, String>>) dataList.get("data");
+ String title = (String) dataList.get("title");
+ String[] headArray = (String[]) dataList.get("head");
+ Map<String, Integer> columnWidthArray = (Map<String, Integer>) dataList.get("columnWidth");
+ String[] pageHeadArray = (String[]) dataList.get("pageHead");
+ String[] pageFooterArray = (String[]) dataList.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.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++;
+ }
+ }
+
+ }
+
+ if (fileUrl != null) {
+ File localFile = new File(fileUrl);
+ if (!localFile.exists()) {
+ // 先得到文件的上级目录，并创建上级目录，在创建文件
+ localFile.getParentFile().mkdirs();
+ localFile.createNewFile();
+ }
+ fileOutputStream = new FileOutputStream(localFile);
+ fileOutputStream.flush();
+ } else {
+ 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;
+ }
+
+ /**
+ * 导出EXCEL文件（多个sheet，单个表格）
+ *
+ * @param response
+ * @param fileName 文件名称
+ * @param sheetTitle sheet中的大标题（第一行）
+ * @param dataMap sheet中的数据集（每个map中细分为多个不同的Object数据） 如：（详见导出班级代码） Map<String,
+ * Object> roomAllMap = new LinkedHashMap<>(); //一个map中，代表一个表格
+ * roomAllMap.put("data", roomList); //此表格中的具体遍历数据
+ * roomAllMap.put("title", "班级学员宿舍信息表");
+ * //表格标题，若为null，且数据集中存在其他map，则下一个map不会空出3行（空出3行，用于划分各个表格）。
+ * roomAllMap.put("head", new String[] { "姓名", "性别", "是否午休",
+ * "是否晚宿" });
+ * //若存在名字相同的，则名字相同且相邻的head合并（当head名字相同，并且某行中的对应的列值也相同，则合并它们）
+ * roomAllMap.put("columnWidth", new Integer[] { 15, 15, 15,
+ * 20 }); //
+ * 20代表20个字节，10个字符（整个sheet中，只能存在一个columnWidth,因为列宽是针对整个sheet的）
+ * roomAllMap.put("columnAlignment", new Integer[] { 0, 0, 0,
+ * 0 }); // 0代表居中，1代表居左，2代表居右 roomAllMap.put("mergeCondition",
+ * null); // 跨行合行列需要的条件，条件优先级按顺序决定，NULL表示不合并,空数组表示无条件
+ * @param pageSize 每个sheet页的数据量
+ * @return
+ * @throws IOException
+ */
+ public final static boolean exportCommonExcelMultiSheet(HttpServletResponse response, String fileName,
+ String sheetTitle, Map<String, Object> dataMap, int pageSize) 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 != dataMap) {
+
+ try {
+ // 计算总页数
+ List<Map<String, String>> currentData = (List<Map<String, String>>) dataMap.get("data");
+
+ // 创建基本的样式
+ 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);
+ CellStyle pageSizeStyle = getCellStyle(workbook, "", (short) 9, false, HorizontalAlignment.RIGHT,
+ VerticalAlignment.CENTER, false);
+ pageSizeStyle.setBorderLeft(BorderStyle.NONE);
+ pageSizeStyle.setBorderRight(BorderStyle.NONE);
+ pageSizeStyle.setBorderBottom(BorderStyle.NONE);
+ pageSizeStyle.setBorderTop(BorderStyle.NONE);
+
+ int page = (currentData.size() + pageSize - 1) / pageSize;
+ for (int pageI = 0; pageI < page; pageI++) {
+
+ Sheet sheet = workbook.createSheet("第" + (pageI + 1) + "页");
+
+ int rowNum = 0; // 初始化第一行为0开始计数
+ int colCount = ((String[]) dataMap.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); // 给标题格设定值
+ if (colCount != 1) {
+ sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
+ }
+ // sheet.addMergedRegion(new CellRangeAddress(rowNum,
+ // rowNum, 0, colCount - 1)); // 合并单元格(起始行，结束行，起始列，结束列)
+ rowNum++;
+ }
+
+ String title = (String) dataMap.get("title");
+ String[] headArray = (String[]) dataMap.get("head");
+ Map<String, Integer> columnWidthArray = (Map<String, Integer>) dataMap.get("columnWidth");
+ String[] pageHeadArray = (String[]) dataMap.get("pageHead");
+ String[] pageFooterArray = (String[]) dataMap.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.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 pageJ = pageSize * pageI; pageJ < (pageI + 1) * pageSize
+ && pageJ < currentData.size(); pageJ++) {
+
+ Row textRow = sheet.createRow(rowNum);
+ Map<String, String> map = currentData.get(pageJ);
+ 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) {
+
+ // 当最后一页时，才输出统计信息
+ if (pageI + 1 == page) {
+ CellStyle pageFooterStyle = getCellStyle(workbook, "", (short) 9, false,
+ HorizontalAlignment.LEFT, 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++;
+ }
+ }
+ }
+
+ // 显示页数
+ Row footerRow = sheet.createRow(rowNum); // 页脚行
+ footerRow.setHeight((short) 0x180); // 行高
+ Cell pageCell = footerRow.createCell(0); // 创建单元格
+ pageCell.setCellStyle(pageSizeStyle); // 样式
+ pageCell.setCellValue("第" + (pageI + 1) + "页 - 共" + page + "页 "); // 值
+ if (colCount != 1) {
+ 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;
+ }
+
+ /*
+ * 获取导出数据的方法
+ *
+ * @param exportField 需要导出的字段
+ *
+ * @param exportList 查询的结果集
+ *
+ */
+ public static List<Map<String, Object>> getDataList(String exportField, List<?> exportList,
+ List<Map<String, Object>> listMapDicItem) {
+
+ // 最终导出的数据
+ List<Map<String, Object>> allList = new ArrayList<>();
+
+ String[] exportFields = exportField.split(",");
+
+ Map<Integer, String> filedTitleMap = new TreeMap<Integer, String>();// 存放字段
+ Map<Integer, String> titleMap = new TreeMap<Integer, String>();// 存放名字
+ Map<String, Integer> widthMap = new TreeMap<String, Integer>();// 存放列宽
+ for (int i = 0; i < exportFields.length; i++) {
+ filedTitleMap.put(i, exportFields[i]);
+ }
+ Collection<String> values = filedTitleMap.values();
+ String[] s = new String[values.size()];
+ values.toArray(s);
+
+ // 处理基本数据
+ Map<String, String> dataMap = null;
+ List<Map<String, String>> dataList = new ArrayList<>();
+ if (null != exportList && !exportList.isEmpty()) {
+ Object test = exportList.get(0);
+ Map<String, Field> fieldMap = new HashMap<String, Field>();
+ Map<String, String> titleNameMap = new HashMap<String, String>();
+ Map<String, Integer> widthNameMap = new TreeMap<String, Integer>();// 存放列宽
+ // Field[] fields = test.getClass().getDeclaredFields();
+ Field[] fields = ModelUtil.getClassFields(test.getClass(), false);
+ int k = 0;
+ for (Field field : fields) {
+ if (field.isAnnotationPresent(FieldInfo.class)) {
+ FieldInfo mapperCell = field.getAnnotation(FieldInfo.class);
+ fieldMap.put(field.getName(), field);
+ filedTitleMap.put(k, field.getName());
+ titleNameMap.put(field.getName(), mapperCell.explain());
+ // widthMap.put(mapperCell.cellName(),
+ // mapperCell.cellWidth());
+ widthNameMap.put(mapperCell.explain(), 10);
+ k++;
+ }
+ }
+
+ // 存放基础数据
+ for (int i = 0; i < exportList.size(); i++) {
+ dataMap = new LinkedHashMap<>();
+ for (int j = 0; j < s.length; j++) {
+ for (Map.Entry<String, Field> data : fieldMap.entrySet()) {
+ if (data.getKey().equals(s[j])) {
+ Field field = data.getValue();
+ field.setAccessible(true);
+ titleMap.put(j, titleNameMap.get(s[j]));
+ String str = titleNameMap.get(s[j]);
+ widthMap.put(titleNameMap.get(s[j]), widthNameMap.get(titleNameMap.get(s[j])));
+ String value = "";
+
+ try {
+ if (field.getGenericType().toString().equals("class java.util.Date")) {
+ Date time = (Date) field.get(exportList.get(i));
+ if (field.getName().toLowerCase().indexOf("date") != -1) {
+ value = field.get(exportList.get(i)) != null
+ ? DateUtil.formatDate(time).toString()
+ : "";
+ value = "1900-01-01".equals(value) ? "" : value;
+ } else {
+ value = field.get(exportList.get(i)) != null
+ ? DateUtil.formatDateTime(time).toString()
+ : "";
+ value = "1900-01-01".equals(value) ? "" : value;
+ }
+ } else if (field.getGenericType().toString().equals("class java.lang.Boolean")) {
+ Boolean bool = (Boolean) field.get(exportList.get(i));
+ value = bool != null && bool == true ? "是" : "否";
+ } else {
+ value = field.get(exportList.get(i)) != null
+ ? field.get(exportList.get(i)).toString()
+ : "";
+ }
+ //
+ } catch (IllegalArgumentException e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ } catch (IllegalAccessException e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ }
+ if (null != listMapDicItem) {
+ for (int ss = 0; ss < listMapDicItem.size(); ss++) {
+ //因为数据字典查出来之后改成了全小写，这里也改成全小写
+ 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);
+ }
+ }
+ }
+
+ dataMap.put(s[j], value);
+ }
+ }
+ }
+ dataList.add(dataMap);
+ }
+ }
+
+ values = titleMap.values();
+ s = new String[values.size()];
+ values.toArray(s);
+
+ Map<String, Object> dataAllMap = new LinkedHashMap<>();
+ dataAllMap.put("data", dataList);
+ dataAllMap.put("head", s);
+ dataAllMap.put("columnWidth", widthMap);
+ allList.add(dataAllMap);
+ 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) {
+ e.printStackTrace();
+ 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