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));
									if (field.getName().toLowerCase().indexOf("date")!=-1) {
										value = field.get(exportList.get(i)) != null
												? DateUtil.formatDate(time).toString()
												: "";
									}else {
										value = field.get(exportList.get(i)) != null
												? DateUtil.formatDateTime(time).toString()
												: "";
									}
								} else if (field.getGenericType().toString().equals("class java.lang.Boolean")) {
									Boolean bool = (Boolean) field.get(exportList.get(i));
									value = bool != null && bool == true ? "是" : "否";
								} 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;
	}

}