读取导入excel到List中

package com.hanboard.educloud.utils;
import com.hanboard.educloud.client.OperationPlatformServiceClient;
import com.hanboard.educloud.framework.web.message.SimpleMessage;
import com.rabbitmq.tools.json.JSONUtil;
import net.sf.json.JSONObject;
import net.sf.json.util.JSONUtils;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.PrintWriter;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by wj on 2017/7/12 0012.
 */
@Component
public class ImportExcelUtil {

    private final static String excel2003L = ".xls";    //2003- 版本的excel
    private final static String excel2007U = ".xlsx";   //2007+ 版本的excel
    private static final Logger logger = LoggerFactory.getLogger(ImportExcelUtil.class);

    @Autowired
    private OperationPlatformServiceClient operationPlatformServiceClient;
    private static ImportExcelUtil importExcelUtil;

    @PostConstruct
    public void init() {
        importExcelUtil = this;
        importExcelUtil.operationPlatformServiceClient = this.operationPlatformServiceClient;
    }




    /**
     * 导入数据获取集合
     * @param request  请求头
     * @param claz     对象class
     * @param fileds   字段属性集合["userId","userName"]
     * @param types    字段类型集合[String.class,String.class]
     * @param response 响应头
     * @param <T>
     * @return
     */
    public static <T> SimpleMessage getListByExcel(
        HttpServletRequest request, Class<T> claz, String[] fileds, Class[] types,int startRow, HttpServletResponse response) {
        /*Integer startRowNum,*/
        try {
            List<T> list = new ArrayList<>();
            T t;
            MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
            /*输入流*/
            InputStream in = null;
            MultipartFile file = multipartRequest.getFile("files");
            if (file.isEmpty()) {
                responseMessage(response, "未获取到文件，请重试!",false);
                return SimpleMessage.warn("未获取到文件，请重试!");
            } else {
                in = file.getInputStream();
                String originalFilename = file.getOriginalFilename();
                //创建Excel工作薄
                Workbook work = ImportExcelUtil.getWorkbook(in, originalFilename);
                if (null == work) {
                    responseMessage(response, "没有获取到工作薄对象!",false);
                    return SimpleMessage.warn("未获取到文件，请重试!");
                }
                Sheet sheet = null;
                Row row = null;
                Cell cell = null;
                String cellValue;
                //遍历Excel中所有的sheet
                int numberOfSheets = work.getNumberOfSheets();
                for (int i = 0; i < numberOfSheets; i++) {
                    sheet = work.getSheetAt(i);
                    if (sheet == null) {
                        continue;
                    }
                    //遍历当前sheet中的所有行
                    int firstRowNum = sheet.getFirstRowNum();
                    int lastRowNum = sheet.getLastRowNum();
                    for (int j = firstRowNum; j <= lastRowNum; j++) {
                        //设置属相
                        t = claz.newInstance();
                        row = sheet.getRow(j);
                        //没有数据或者是第一行 那么不导入
                        if (row == null || j < startRow-1) {
                            continue;
                        }


                        int length = fileds.length;
                        for (int m = 0; m < length; m++) {
                            cell = row.getCell(m);
                            if(cell!=null){
                                Method setMethod = getSetMethod(claz, fileds[m], types[m]);
                                Class classItem = types[m];
                                Object value = getCellValue(cell);
                                if(value!=null){
                                    setMethodValue(t, setMethod, classItem, value);
                                }
                            }

                        }
                        list.add(t);
                    }
                }
            }
            return SimpleMessage.info(list);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("导入数据报错:{}",e);
            return SimpleMessage.warn("报错");
        }
    }

    /**
     * 设置当前属性值
     *
     * @param t
     * @param setMethod
     * @param classItem
     * @param value
     * @param <T>
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    public static <T> void setMethodValue(T t, Method setMethod, Class classItem, Object value) throws InvocationTargetException, IllegalAccessException {
        String valueString = (String) value;
        if (classItem == Byte.class) {
            Byte value_byte = Byte.parseByte(valueString);
            setMethod.invoke(t, value_byte);
        } else if (classItem == Short.class) {
            Short value_short = Short.parseShort(valueString);
            setMethod.invoke(t, value_short);
        } else if (classItem == Integer.class) {
            Integer value_int = Integer.parseInt(valueString);
            setMethod.invoke(t, value_int);
        } else if (classItem == Long.class) {
            Long value_long = Long.parseLong(valueString);
            setMethod.invoke(t, value_long);
        } else if (classItem == Double.class) {
            Double value_double = Double.parseDouble(valueString);
            setMethod.invoke(t, value_double);
        } else if (classItem == Float.class) {
            Float value_float = Float.parseFloat(valueString);
            setMethod.invoke(t, value_float);
        } else if (classItem == Boolean.class) {
            Boolean value_boolean = Boolean.parseBoolean(valueString);
            setMethod.invoke(t, value_boolean);
        } else {
            String value_string = (String) value;
            setMethod.invoke(t, value_string);
        }
    }

    /**
     * 获取set方法
     *
     * @param tCls
     * @param courseNameFild
     * @return
     * @throws Exception
     */
    private static Method getSetMethod(Class tCls, String courseNameFild, Class argClass) throws Exception {
        String setNameMethodName = "set" + courseNameFild.substring(0, 1).toUpperCase() + courseNameFild.substring(1);
        Method setNameMethod = tCls.getMethod(setNameMethodName, argClass);
        return setNameMethod;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     *
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    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.00");  //格式化数字

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


    /**
     * 描述：获取excel数据
     *
     * @return 包含多有cell内容的List<Map>
     */
    public List<Map> getExcelData(InputStream in, String fileName) throws Exception {
        List<Map> list = new ArrayList<>();
        Object value = null;

        //创建Excel工作薄
        Workbook work = this.getWorkbook(in, fileName);
        if (null == work) {
            return null;
        }
        XSSFSheet sheet = null;
        Row row = null;
        Cell cell = null;
        //遍历Excel中所有的sheet
        int numberOfSheets = work.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            sheet = (XSSFSheet) work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                //没有数据或者是第一行标题行 那么不导入
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }
                //遍历所有的cell,组装cell到Map
                Map map = new HashMap();
                for (int a = row.getFirstCellNum(); a < row.getLastCellNum(); a++) {
                    cell = row.getCell(a);
                    map.put(a, cell);
                }
                list.add(map);
            }
            //把有值的那一页sheet读取后就不读取其他的sheet了
            if (list.size() > 0) {
                break;
            }
        }
        return list;
    }


    /**
     * 设置响应头数据
     *
     * @param response
     * @param message
     */
    public static void responseMessage(HttpServletResponse response, String message, Boolean info) {
        try {
            response.setHeader("Content-type", "text/html;charset=UTF-8");
            response.setCharacterEncoding("utf-8");  //防止ajax接受到的中文信息乱码
            PrintWriter out = response.getWriter();
            SimpleMessage simpleMessage;
            if (info) {
                simpleMessage = SimpleMessage.info(message);
            } else {
                simpleMessage = SimpleMessage.warn(message);
            }
            JSONObject json = JSONObject.fromObject(simpleMessage);//将java对象转换为json对象
            String jsonString = json.toString();//将json对象转换为字符串
            out.print(jsonString);
            out.flush();
            out.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 返回简单的数据
     * @param response
     * @param message
     * @param info
     */
    public static void responseMessageStr(HttpServletResponse response, String message, Boolean info) {
        try {
            response.setHeader("Content-type", "text/html;charset=UTF-8");
            response.setCharacterEncoding("utf-8");  //防止ajax接受到的中文信息乱码
            PrintWriter out = response.getWriter();
            out.print(message);
            out.flush();
            out.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}