Execl util

package com.ying.common;

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.Collection;
import java.util.Collections;
import java.util.List;

/**
 * 
 *
 * @author 
 * @date 2018/1/10
 */
public class ExcelUtils {

    /**
     * 获取Excel内容
     *
     * @param file 文件
     * @param maxRow 最大读取行数
     * @return Excel内容
     * @throws IOException 异常
     */
    public static List<List<JSONObject>> getExcelContent(MultipartFile file, int maxRow) throws IOException {
        List<List<JSONObject>> sheetList = Lists.newArrayList();
        InputStream fis = null;
        try {
            fis = file.getInputStream();
            //创建工作薄
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            int numberOfSheets = wb.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet st = wb.getSheetAt(i);
                int lastRow = st.getLastRowNum() <= maxRow ? st.getLastRowNum() : maxRow;

                List<String> sheetHeadList = ExcelUtils.getSheetHeadList(st.getRow(0));
                if (CollectionUtils.isEmpty(sheetHeadList)) {
                    continue;
                }

                List<JSONObject> jsonObjList = Lists.newArrayListWithCapacity(lastRow);
                // 第一行为标题，不取
                for (int rowIndex = 1; rowIndex <= lastRow; rowIndex++) {
                    XSSFRow row = st.getRow(rowIndex);
                    if (null == row) {
                        break;
                    }
                    JSONObject jsonObject = ExcelUtils.getCellContent(row, sheetHeadList);

                    if (null == jsonObject) {
                        break;
                    }
                    jsonObjList.add(jsonObject);
                }
                sheetList.add(jsonObjList);
            }
        } finally {
            IOUtils.closeQuietly(fis);
        }

        return sheetList;
    }

    /**
     * 表格头列表
     *
     * @param row 行
     * @return 表格头列表
     */
    public static List<String> getSheetHeadList(XSSFRow row) {
        if (row == null) {
            return Collections.emptyList();
        }
        short lastCellNum = row.getLastCellNum();
        List<String> headList = Lists.newArrayListWithCapacity(lastCellNum);
        for (int columnIndex = 0; columnIndex <= lastCellNum; columnIndex++) {
            XSSFCell contentCell = row.getCell(columnIndex);
            if (contentCell == null) {
                continue;
            }
            headList.add(getCellValue(contentCell).toString());
        }
        return headList;
    }

    /**
     * 表格内容
     */
    public static JSONObject getCellContent(XSSFRow row, List<String> headList) {
        JSONObject jsonObj = new JSONObject();

        int maxHeadIndex = headList.size() - 1;
        // 当前行最后一侧cell的编号 < 标题头的数目时，认为该行数据不全，跳过
        if (row.getLastCellNum() < maxHeadIndex) {
            return null;
        }
        short lastCellNum = row.getLastCellNum() > maxHeadIndex ? (short) maxHeadIndex : row.getLastCellNum();

        int emptyValueCount = 0;
        for (int columnIndex = 0; columnIndex <= lastCellNum; columnIndex++) {
            XSSFCell contentCell = row.getCell(columnIndex);
            if (contentCell == null) {
                continue;
            }
            String head = headList.get(columnIndex);
            Object cellValue = getCellValue(contentCell);
            if (null == cellValue || StringUtils.isBlank(cellValue.toString())) {
                emptyValueCount++;
            }
            jsonObj.put(head, cellValue);
        }

        // 一行中每个单元格都是空白值，就跳过读取，理解为读取完成
        if (emptyValueCount == headList.size()) {
            return null;
        }
        return jsonObj;
    }

    /**
     * 获取单元格的值
     */
    protected static Object getCellValue(XSSFCell cell) {

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_FORMULA:
                return cell.getRawValue();
            case Cell.CELL_TYPE_BLANK:
            case Cell.CELL_TYPE_BOOLEAN:
            case Cell.CELL_TYPE_ERROR:
            default:
        }
        return StringUtils.EMPTY;
    }
}