From 655ba7e8220644462441b401c6d763530e1363a9 Mon Sep 17 00:00:00 2001
From: yijiusmile <253281376@qq.com>
Date: 星期四, 18 二月 2021 22:59:39 +0800
Subject: [PATCH] 增加导出员工
---
febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/controller/EmpBaseInfoController.java | 58 +
febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiExportExcel.java | 1480 ++++++++++++++++++++++++++++++++++++++++++++++
febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiImportExcel.java | 190 +++++
febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/ModelUtil.java | 163 +++++
4 files changed, 1,879 insertions(+), 12 deletions(-)
diff --git a/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/controller/EmpBaseInfoController.java b/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/controller/EmpBaseInfoController.java
index ab60640..3c11c43 100644
--- a/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/controller/EmpBaseInfoController.java
+++ b/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/controller/EmpBaseInfoController.java
@@ -1,26 +1,41 @@
package cc.mrbird.febs.server.hr.controller;
+import java.io.IOException;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+import java.util.Map;
+
+import javax.servlet.http.HttpServletResponse;
+import javax.validation.Valid;
+import javax.validation.constraints.NotBlank;
+
+import org.springframework.security.access.prepost.PreAuthorize;
+import org.springframework.validation.annotation.Validated;
+import org.springframework.web.bind.annotation.DeleteMapping;
+import org.springframework.web.bind.annotation.GetMapping;
+import org.springframework.web.bind.annotation.PathVariable;
+import org.springframework.web.bind.annotation.PostMapping;
+import org.springframework.web.bind.annotation.PutMapping;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RestController;
+
+import cc.mrbird.febs.common.core.entity.FebsResponse;
+import cc.mrbird.febs.common.core.entity.QueryRequest;
+import cc.mrbird.febs.common.core.exception.FebsException;
+import cc.mrbird.febs.common.core.utils.FebsUtil;
import cc.mrbird.febs.server.hr.entity.EmpBaseInfo;
import cc.mrbird.febs.server.hr.entity.EmpDimissionLog;
import cc.mrbird.febs.server.hr.entity.EmpJobChange;
import cc.mrbird.febs.server.hr.feign.IRemoteDeptService;
import cc.mrbird.febs.server.hr.feign.IRemoteUserService;
import cc.mrbird.febs.server.hr.service.IEmpBaseInfoService;
-import cc.mrbird.febs.common.core.entity.FebsResponse;
-import cc.mrbird.febs.common.core.entity.QueryRequest;
-import cc.mrbird.febs.common.core.exception.FebsException;
-import cc.mrbird.febs.common.core.utils.FebsUtil;
+import cc.mrbird.febs.server.hr.util.PoiExportExcel;
+import cn.hutool.core.date.DateUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
-import lombok.extern.slf4j.Slf4j;
import lombok.RequiredArgsConstructor;
-import org.springframework.security.access.prepost.PreAuthorize;
-import org.springframework.validation.annotation.Validated;
-import org.springframework.web.bind.annotation.*;
-
-import javax.validation.Valid;
-import javax.validation.constraints.NotBlank;
-import java.util.Map;
+import lombok.extern.slf4j.Slf4j;
/**
* name:EmpBaseInfo
@@ -150,4 +165,23 @@
throw new FebsException(message);
}
}
+
+ @ApiOperation(value = "导出员工")
+ @PostMapping("export")
+ public void export(QueryRequest request, HttpServletResponse response, String exportField) throws IOException {
+ EmpBaseInfo empBaseinfo = new EmpBaseInfo();
+ empBaseinfo.setDelFlag(0);
+ List<EmpBaseInfo> exportList = empBaseInfoService.findEmpBaseInfos(empBaseinfo);
+ List<Map<String, Object>> listMapDicItem = new ArrayList();
+ List<Map<String, Object>> allList = PoiExportExcel.getDataList(exportField, exportList, listMapDicItem);
+ String fileName = "在职员工列表";
+ Date currentDate = new Date();
+ String dateStr = DateUtil.formatDate(currentDate);
+ boolean result = PoiExportExcel.exportCommonExcel(response, fileName + dateStr, fileName, allList);
+ }
+
+ @ApiOperation(value = "导出员工")
+ @PostMapping("importEmp")
+ public void importEmp() throws IOException {
+ }
}
diff --git a/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/ModelUtil.java b/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/ModelUtil.java
new file mode 100644
index 0000000..859c628
--- /dev/null
+++ b/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/ModelUtil.java
@@ -0,0 +1,163 @@
+package cc.mrbird.febs.server.hr.util;
+
+import java.lang.reflect.Field;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.commons.lang3.StringUtils;
+
+import com.baomidou.mybatisplus.annotation.TableName;
+
+/**
+ * 实体的工具类
+ *
+ * @author zhangshuaipeng
+ *
+ */
+public class ModelUtil {
+ public static Map<String, Field[]> modelFields = new HashMap<String, Field[]>();
+
+ public static Map<String, String> modelJson = new HashMap<String, String>();
+
+ /**
+ * 判断实体不为空
+ *
+ * @param obj
+ * @return
+ */
+ public static Boolean isNotNull(Object obj) {
+ if (obj != null) {
+ return true;
+ } else {
+ return false;
+ }
+ }
+
+ /**
+ * 获取实体对应的数据表名
+ *
+ * @param clazz
+ * 要获取表名的实体类
+ * @param equalEntity
+ * 为true时返回实体的名称，否则返回注解的表名
+ * @return
+ */
+ public static String getTableName(Class<?> clazz, boolean equalEntity) {
+ String tableName = clazz.getSimpleName();
+
+ if (!equalEntity) {
+ TableName annotation = (TableName)clazz.getAnnotation(TableName.class);
+ if (!StringUtils.isEmpty(annotation.value())) {
+ tableName = annotation.value();
+ }
+ }
+ return tableName;
+ }
+
+ /**
+ * 获取指定字段映射的数据表的列名
+ *
+ * @param f
+ * 指定的字段
+ * @param isAnnotation
+ * 为true表示取字段使用@Column进行注解的名称，当没有注解时取字段名
+ * @return
+ */
+// public static String getColumnName(Field f, boolean isAnnotation) {
+// String columnName = f.getName();
+// if (isAnnotation) {
+// Annotation[] annotations = f.getAnnotations();
+// for (int i = 0; i < annotations.length; i++) {
+// if (annotations[i] instanceof Column) {
+// // 数据列
+// Column column = (Column) annotations[i];
+// if (StringUtils.isNotEmpty(column.name())) {
+// columnName = column.name();
+// }
+// } else if (annotations[i] instanceof JoinColumn) {
+// // 关联列
+// JoinColumn joinColumn = (JoinColumn) annotations[i];
+// if (StringUtils.isNotEmpty(joinColumn.name())) {
+// columnName = joinColumn.name();
+// }
+// }
+//
+// }
+// }
+
+// return columnName;
+// }
+
+ /**
+ * 得到类的属性集合
+ *
+ * @param c
+ * @param itself
+ * 是否是自身的字段
+ * @return
+ */
+ public static Field[] getClassFields(Class<?> c, boolean itself) {
+ if (itself) {
+ if (modelFields.get(c.getName()) != null) {
+ return modelFields.get(c.getName());
+ } else {
+ Field[] fields = c.getDeclaredFields();
+ modelFields.put(c.getName(), fields);
+ return fields;
+ }
+ } else {
+ if (modelFields.get(c.getName()) != null) {
+ return modelFields.get(c.getName());
+ } else {
+ List<Field> fields = new ArrayList<Field>();
+ getAllDeclaredFields(c, fields);
+ Field[] fies = new Field[fields.size()];
+ fields.toArray(fies);
+ modelFields.put(c.getName(), fies);
+ return fies;
+ }
+ }
+ }
+
+ /**
+ * 从c类中取得全部字段,包括父类
+ *
+ * @param c
+ * @param fields
+ */
+ public static void getAllDeclaredFields(Class<?> c, List<Field> fields) {
+ Field[] fies = c.getDeclaredFields();
+ Collections.addAll(fields, fies);
+ Class<?> parent = c.getSuperclass();
+ if (parent != Object.class) {
+ getAllDeclaredFields(parent, fields);
+ } else {
+ return;
+ }
+ }
+
+ /**
+ * 得到类的主键字段
+ *
+ * @param clazz
+ * @return
+ */
+// public static String getClassPkName(Class<?> clazz) {
+// Field[] fields = getClassFields(clazz, false);
+// String pkName = "";
+// for (Field f : fields) {
+// FieldInfo fieldInfo = f.getAnnotation(FieldInfo.class);
+// if (isNotNull(fieldInfo)) {
+// if ("ID".equals(fieldInfo.type())) {
+// pkName = f.getName();
+// break;
+// }
+// }
+// }
+// return pkName;
+// }
+
+}
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
new file mode 100644
index 0000000..80eb819
--- /dev/null
+++ b/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiExportExcel.java
@@ -0,0 +1,1480 @@
+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;
+ }
+
+
+}
diff --git a/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiImportExcel.java b/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiImportExcel.java
new file mode 100644
index 0000000..3f5b6e7
--- /dev/null
+++ b/febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiImportExcel.java
@@ -0,0 +1,190 @@
+package cc.mrbird.febs.server.hr.util;
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.text.DecimalFormat;
+import java.text.SimpleDateFormat;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+
+import org.apache.poi.hssf.usermodel.HSSFDataFormat;
+import org.apache.poi.hssf.usermodel.HSSFDateUtil;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+
+public class PoiImportExcel {
+
+ private final static String excel2003L = ".xls"; // 2003- 版本的excel
+ private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
+
+ /**
+ * 描述：获取IO流中的数据，组装成List<List<Object>>对象
+ *
+ * @param in,fileName
+ * @return
+ * @throws IOException
+ */
+ public List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception {
+ List<List<Object>> list = null;
+
+ // 创建Excel工作薄
+ Workbook work = this.getWorkbook(in, fileName);
+ if (null == work) {
+ throw new Exception("创建Excel工作薄为空！");
+ }
+ Sheet sheet = null;
+ Row row = null;
+ Cell cell = null;
+
+ list = new ArrayList<List<Object>>();
+ // 直接获取第一个sheet
+ sheet = work.getSheetAt(0);
+ if (sheet == null) {
+ return list;
+ }
+ // 取得Excel的总列数,总行数
+ int columns = 0;
+ Row firstRow = sheet.getRow((short) 0);
+ if (firstRow != null) {
+ columns = firstRow.getPhysicalNumberOfCells();
+ }
+
+ int rows = sheet.getPhysicalNumberOfRows();
+ List<Object> dataRow = null;
+
+ // 首行为定义标题的行，数据从第2行开始
+ for (int i = 1; i < rows; i++) {
+ dataRow = new ArrayList<Object>();
+ // 获取行
+ row = sheet.getRow(i);
+ if (row != null) {
+ // columns=row.getPhysicalNumberOfCells();//不在这里设置，通过firstRow来获取列数信息。
+ for (int j = 0; j < columns; j++) {
+ // 获取某行某列的某一个单元格
+ cell = row.getCell(j);
+ // 往dataRow存值
+ dataRow.add(getCellValue(cell));
+ }
+ list.add(dataRow);
+ } else {
+ list.add(new ArrayList<>());
+ }
+ }
+
+ // 遍历当前sheet中的所有行
+ /*
+ * int countCellNum = 0; for (int j = sheet.getFirstRowNum(); j <=
+ * sheet.getLastRowNum(); j++) { row = sheet.getRow(j); if (row == null
+ * || row.getFirstCellNum() == j) { if (row != null) countCellNum =
+ * row.getLastCellNum(); continue; }
+ *
+ * // 遍历所有的列 List<Object> li = new ArrayList<Object>(); for (int y = row
+ * .getFirstCellNum(); y <= countCellNum; y++) { cell = row.getCell(y);
+ * li.add(this.getCellValue(cell));
+ *
+ * } list.add(li); }
+ */
+
+ work.close();
+ return list;
+ }
+
+ /**
+ * 描述：根据文件后缀，自适应上传文件的版本
+ *
+ * @param inStr,fileName
+ * @return
+ * @throws Exception
+ */
+ public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
+ Workbook wb = null;
+ String fileType = fileName.substring(fileName.lastIndexOf("."));
+ if (excel2003L.equals(fileType)) {
+ wb = new HSSFWorkbook(inStr); // 2003-
+ } else if (excel2007U.equals(fileType)) {
+ wb = new XSSFWorkbook(inStr); // 2007+
+ } else {
+ throw new Exception("解析的文件格式有误！");
+ }
+ return wb;
+ }
+
+ /**
+ * 描述：对表格中数值进行格式化
+ *
+ * @param cell
+ * @return
+ */
+ @SuppressWarnings("deprecation")
+ public Object getCellValue(Cell cell) {
+ try {
+ Object value = null;
+
+ if (cell == null) {
+ value = "";
+ return value;
+ }
+
+ DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 日期格式化
+ DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字
+
+ switch (cell.getCellType()) {
+ case Cell.CELL_TYPE_STRING:
+ value = cell.getRichStringCellValue().getString();
+ break;
+ case Cell.CELL_TYPE_NUMERIC:
+ if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
+
+ if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")
+ || cell.getCellStyle().getDataFormat() == 176) {
+ sdf = new SimpleDateFormat("HH:mm");
+ }
+ Date date = cell.getDateCellValue();
+ value = sdf.format(date);
+
+ } else if (cell.getCellStyle().getDataFormat() == 58) {
+ // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
+ SimpleDateFormat sdf2 = new SimpleDateFormat("MM-dd");
+ double cellValue = cell.getNumericCellValue();
+ Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cellValue);
+ value = sdf2.format(date);
+ } else if (cell.getCellStyle().getDataFormat() == 183 || cell.getCellStyle().getDataFormat() == 177
+ || cell.getCellStyle().getDataFormat() == 181) {
+ // 处理自定义日期格式：y年m月d日(通过判断单元格的格式id解决，id的值是183)
+ SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
+ Date date = cell.getDateCellValue();
+ value = sdf2.format(date);
+ } else {
+ if ("General".equals(cell.getCellStyle().getDataFormatString())) {
+ value = df.format(cell.getNumericCellValue());
+ } else {
+ value = df2.format(cell.getNumericCellValue());
+ }
+ }
+
+ break;
+ case Cell.CELL_TYPE_BOOLEAN:
+ value = cell.getBooleanCellValue();
+ break;
+ case Cell.CELL_TYPE_BLANK:
+ value = "";
+ break;
+ default:
+ value = cell.getStringCellValue();
+ break;
+ }
+ return value;
+
+ } catch (Exception e) {
+ return null;
+ }
+
+ }
+
+}
\ No newline at end of file
--
Gitblit v1.8.0