excel上传工具类

首先感谢网络上愿意分享的大佬，让我们这些小菜鸟能够很快的学习。
介于这两天碰到的业务好几个都是excel上传方面的，就萌生了找一个比较通用的上传工具类的想法。
不过网上的大佬们分享的，都不太符合我的业务需求，于是俺自己结合了几位大佬的工具类，再加上自己的一点东西，合成了一个。
本着分享的精神，放出来献献丑，还请大佬们尽情批评。

这是工具类

/*
#          .,:,,,                                        .::,,,::.
#        .::::,,;;,                                  .,;;:,,....:i:
#        :i,.::::,;i:.      ....,,:::::::::,....   .;i:,.  ......;i.
#        :;..:::;::::i;,,:::;:,,,,,,,,,,..,.,,:::iri:. .,:irsr:,.;i.
#        ;;..,::::;;;;ri,,,.                    ..,,:;s1s1ssrr;,.;r,
#        :;. ,::;ii;:,     . ...................     .;iirri;;;,,;i,
#        ,i. .;ri:.   ... ............................  .,,:;:,,,;i:
#        :s,.;r:... ....................................... .::;::s;
#        ,1r::. .............,,,.,,:,,........................,;iir;
#        ,s;...........     ..::.,;:,,.          ...............,;1s
#       :i,..,.              .,:,,::,.          .......... .......;1,
#      ir,....:rrssr;:,       ,,.,::.     .r5S9989398G95hr;. ....,.:s,
#     ;r,..,s9855513XHAG3i   .,,,,,,,.  ,S931,.,,.;s;s&BHHA8s.,..,..:r:
#    :r;..rGGh,  :SAG;;G@BS:.,,,,,,,,,.r83:      hHH1sXMBHHHM3..,,,,.ir.
#   ,si,.1GS,   sBMAAX&MBMB5,,,,,,:,,.:&8       3@HXHBMBHBBH#X,.,,,,,,rr
#   ;1:,,SH:   .A@&&B#&8H#BS,,,,,,,,,.,5XS,     3@MHABM&59M#As..,,,,:,is,
#  .rr,,,;9&1   hBHHBB&8AMGr,,,,,,,,,,,:h&&9s;   r9&BMHBHMB9:  . .,,,,;ri.
#  :1:....:5&XSi;r8BMBHHA9r:,......,,,,:ii19GG88899XHHH&GSr.      ...,:rs.
#  ;s.     .:sS8G8GG889hi.        ....,,:;:,.:irssrriii:,.        ...,,i1,
#  ;1,         ..,....,,isssi;,        .,,.                      ....,.i1,
#  ;h:               i9HHBMBBHAX9:         .                     ...,,,rs,
#  ,1i..            :A#MBBBBMHB #  .r1,..                     ....,,,,,i1;
#   :h;..       .,..;,1XBMMMMBXs,.,, .. :: ,.               ....,,,,,,ss.
#    ih: ..    .;;;, ;;:s58A3i,..    ,. ,.:,,.             ...,,,,,:,s1,
#    .s1,....   .,;sh,  ,iSAXs;.    ,.  ,,.i85            ...,,,,,,:i1;
#     .rh: ...     rXG9XBBM#M#MHAX3hss13&&HHXr         .....,,,,,,,ih;
#      .s5: .....    i598X&&A&AAAAAA&XG851r:       ........,,,,:,,sh;
#      . ihr, ...  .         ..                    ........,,,,,;11:.
#         ,s1i. ...  ..,,,..,,,.,,.,,.,..       ........,,.,,.;s5i.
#          .:s1r,......................       ..............;shs,
#          . .:shr:.  ....                 ..............,ishs.
#              .,issr;,... ...........................,is1s;.
#                 .,is1si;:,....................,:;ir1sr;,
#                    ..:isssssrrii;::::::;;iirsssssr;:..
#                         .,::iiirsssssssssrri;;:.
*/
package com.see.emergency.util;

import com.alibaba.fastjson.JSON;
import com.see.common.core.exception.SeeException;
import com.see.common.core.utils.SpringUtils;
import lombok.extern.slf4j.Slf4j;
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;
import org.springframework.util.StringUtils;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @description: excel上传工具类
 * @author: syc
 * @version: 1.0
 * @createDate:
 */
@Slf4j
public class ImportExcelUtil {

    private final static String excel2003L = ".xls"; // 2003- 版本的excel
    private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel

    /**
     * 将流中的Excel数据转成List<Map>
     * 
     * @param in
     *            输入流
     * @param fileName
     *            文件名（判断Excel版本）
     * @param mapping
     *            字段名称映射(key为对应列，value为对应字段)
     * @param columnStart
     *            表头在哪列
     * @param valueStart
     *            值在哪列开始
     * @param notNull
     *            哪些列不能为空
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> parseExcel(InputStream in, String fileName,
                                                       Map<String, String> mapping,
                                                       int columnStart,
                                                       int valueStart,
                                                       List<String> notNull) throws Exception {
        // 根据文件名来创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        List<String> errList = new ArrayList<>();
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        // 返回数据
        List<Map<String, Object>> ls = new ArrayList<Map<String, Object>>();

        // 遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null)
                continue;

            // 取第一行所有列值的名称（表头），对应的表行不同，取的行开始索引不同
            row = sheet.getRow(columnStart);
            String title[] = null;
            if (row != null) {
                title = new String[row.getLastCellNum()];
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    title[y] = (String) getCellValue(cell);
                }
                Set<String> strings = mapping.keySet();
                //判断上传的表格 表头是否符合规范
                Boolean aBoolean = setAndArrayIsSame(title,strings);
                if (!aBoolean){
                    throw new Exception("上传的表格表头不符合规范");
                }
            } else
                continue;
            log.info(JSON.toJSONString(title));

            // 遍历当前sheet中的所有行，对应的表行不同，取的行开始索引不同
            for (int j = valueStart; j < sheet.getLastRowNum() + 1; j++) {
                row = sheet.getRow(j);
                Map<String, Object> m = new HashMap<String, Object>();
                // 遍历所有的列
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    String key = title[y];
                    // log.info(JSON.toJSONString(key));
                    Object cellValue = getCellValue(cell);
                    String column = mapping.get(key);
                    if (!notNull.isEmpty()){
                        for (String s : notNull) {
                            if (s.equals(column)){
                                if (StringUtils.isEmpty(cellValue)){
                                    errList.add("导入失败，第" +row.getRowNum() + "行，"
                                            + column + "列未填写完毕<br>");
                                }
                            }
                        }
                    }
                    m.put(mapping.get(key), getCellValue(cell));
                }
                ls.add(m);
            }
            if (!errList.isEmpty()){
                if (errList.size() > 10){
                    throw new NullPointerException("导入失败，必填项为空");
                }else {
                    StringBuilder stringBuilder = new StringBuilder();
                    for (String s : errList) {
                        stringBuilder.append(s + "<br>");
                    }
                    throw new NullPointerException("导入失败<br>" + stringBuilder);
                }
            }
        }
        work.close();
        return ls;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     * 
     * @param inStr,fileName
     * @return
     * @throws SeeException
     */
    public static 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
     */
    public static Object getCellValue(Cell cell) {
        Object value = null;
        DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
        DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字

        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
            } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                value = sdf.format(cell.getDateCellValue());
            } else {
                value = df2.format(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            value = "";
            break;
        default:
            break;
        }
        return value;
    }

    /**
     * 判断set和string数组是否相等
     * 判断上传的表格 表头是否符合规范
     * @param strings
     * @param set
     * @return
     */
    private static Boolean setAndArrayIsSame(String[] strings, Set set){
        if (strings == null && set == null) {
            return true; // Both are null
        }

        if (strings == null || set == null || strings.length != set.size()
                || strings.length == 0 || set.size() == 0) {
            return false;
        }
        boolean isFullEqual = true;

        for (String string : strings) {
            if (!set.contains(string)) {
                isFullEqual = false;
            }
        }
        return isFullEqual;
    }
}

这是业务层

@Override
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    public void importxxx(String fileName, MultipartFile file){
        //构建一个与上传的excel表头相同的map;
        HashMap<String, String> stringStringHashMap = getStringStringHashMap();
        //不能为空的列
        List<String> canNotNullList = canNotNullCoulmn();
        //excel上传工具类，map需要按照表头自己构建
        List<Map<String, Object>> maps = null;
        try {
            maps = ImportExcelUtil.parseExcel(file.getInputStream(), fileName,stringStringHashMap,1,2,canNotNullList);
        } catch (Exception e) {
            e.printStackTrace();
            //捕获异常信息返回
            //StringWriter stringWriter= new StringWriter();
            //PrintWriter writer= new PrintWriter(stringWriter);
            //e.printStackTrace(writer);
            //StringBuffer buffer= stringWriter.getBuffer();
            throw new SeeException(e.getMessage());
        }
        log.info("集合长度：{}",maps.size());
        log.info("集合内容：{}",maps);
       
    }

    /**
     * 设置不能为空的列
     * @return
     */
    private List<String> canNotNullCoulmn() {
        List<String> list = new ArrayList<String>();
        list.add("xx字段名");
        list.add("xx字段名");
        return list;
    }

    /**
     * 构建一个与上传的excel表头相同的map
     * key为对应列，value为对应字段
     * @return
     */
    private HashMap<String, String> getStringStringHashMap() {
        HashMap<String, String> stringStringHashMap = new HashMap<String, String>();
        stringStringHashMap.put("xx系统","xxCode");
        stringStringHashMap.put("xx代码","xxCode");
        stringStringHashMap.put("xx名称","xxName");
        stringStringHashMap.put("xx级别","xxLevel");
        stringStringHashMap.put("xx来源","xxSource");
        stringStringHashMap.put("xx条件","xxCondition");
        stringStringHashMap.put("xx推送","xxIs");
        return stringStringHashMap;
    }