增加导出员工

yijiusmile

2021-02-18 655ba7e8220644462441b401c6d763530e1363a9

增加导出员工

 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 {
    }
}

 febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/ModelUtil.java

New file
@@ -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;
//    }

}

 febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiExportExcel.java

New file
@@ -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;

    }



	
}


 febs-server/febs-server-hr/src/main/java/cc/mrbird/febs/server/hr/util/PoiImportExcel.java

New file
@@ -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;
        }

    }

}