excel导入

在jeecgframework框架里面实现excel导入，读取excel的内容，指定sheet，通过excel的前第一个行字段，第二行字段描述，解析出结果

工具类：

package org.jeecg.modules.fsmImport.util;

import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.functions.T;
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.jeecg.common.fsmutil.BeanUtil;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecgframework.poi.excel.entity.ImportParams;

import java.beans.IntrospectionException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel导入工具类
 */
public class ExcelImportUitl {
    /**
     * 获取Excel中数据，只读取第一个sheet页
     *
     * @param inputStream
     * @return
     */
    public static <T> List<T> getExcelData(InputStream inputStream, Class<T> tClass, ImportParams params) {
        List<T> results = new ArrayList<>();

        List<Map<String, Object>> list = new ArrayList<>();
        String sheetName = "";
        Workbook book = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            if (POIFSFileSystem.hasPOIFSHeader((InputStream) inputStream)) {
                book = new HSSFWorkbook((InputStream) inputStream);
            } else if (POIXMLDocument.hasOOXMLHeader((InputStream) inputStream)) {
                book = new XSSFWorkbook(OPCPackage.open((InputStream) inputStream));
            }
            Sheet sheet = book.getSheetAt(0);

            // 获取表头字段配置信息
            Map<Integer, String> headerMap = new HashMap<>();
            Row headerRow = sheet.getRow(params.getTitleRows());
            int lastCellNum = headerRow.getLastCellNum();
            for (int i = 0; i < lastCellNum; i++) {
                String title = headerRow.getCell(i).getStringCellValue();
                if (StringUtils.isEmpty(title)) {
                    continue;
                }
                // 替换回车
                title = title.replace("\n", " ");
                headerMap.put(i, title);
            }

            // 获取Excel数据
            int lastRowNum = sheet.getLastRowNum() + 1;
            int startRow = params.getStartRows();
            for (int i = startRow; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    break;
                }

                // 每一行的第一个单元格为空直接返回
                Object vsl = getCellValue(row, 0);
                if (vsl == null || StringUtils.isEmpty(vsl.toString())) {
                    break;
                }

                Map<String, Object> map = new HashMap<>();
                for (int ci : headerMap.keySet()) {
                    map.put(headerMap.get(ci), getCellValue(row, ci));
                }
                list.add(map);
            }
            // 取值为空，直接返回
            if (list.isEmpty()) {
                return results;
            }

            // 进行字段匹配
            Map<String, ExcelFieldConfig> configMap = getFieldConfig(tClass);
            if (configMap.isEmpty()) {
                return results;
            }
            List<String> keys = new ArrayList<>(headerMap.values());
            for (Map<String, Object> one : list) {
                T bean = tClass.newInstance();
                for (String key : keys) {
                    Object val = one.get(key);
                    ExcelFieldConfig config = configMap.get(key);
                    if (val != null && StringUtils.isNotEmpty(val.toString()) && config != null) {
                        if (config.isDate()) {
                            // 日期
                            String valStr = val.toString();
                            if (val.getClass() == Double.class || val.getClass() == Integer.class) {
                                Double dateD = Double.valueOf(valStr);
                                Date date = HSSFDateUtil.getJavaDate(dateD);
                                valStr = sdf.format(date);
                            }
                            BeanUtil.setBeanValue(bean, tClass, config.getFieldName(), valStr);
                        } else {
                            if (config.getType() == String.class) {
                                BeanUtil.setBeanValue(bean, tClass, config.getFieldName(), val.toString());
                                continue;
                            }
                            if (config.getType() == val.getClass()) {
                                BeanUtil.setBeanValue(bean, tClass, config.getFieldName(), val);
                                continue;
                            }
                            if (config.getType() == Double.class && (val.getClass() == Integer.class || val.getClass() == String.class)) {
                                BeanUtil.setBeanValue(bean, tClass, config.getFieldName(), Double.valueOf(val.toString()));
                                continue;
                            }
                            if (config.getType() == Integer.class && (val.getClass() == Double.class || val.getClass() == String.class)) {
                                BeanUtil.setBeanValue(bean, tClass, config.getFieldName(), Double.valueOf(val.toString()).intValue());
                                continue;
                            }
                        }
                    }
                }
                results.add(bean);
            }
        } catch (IOException | InvalidFormatException | InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        } catch (IntrospectionException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return results;
    }

    /**
     * 获取Excel中数据，指定读取sheet页
     *
     * @param inputStream
     * @return
     */
    public static JSONObject getExcelData(InputStream inputStream, ImportParams params, int sheetIndex) {
        JSONObject result = new JSONObject();
        List header = new ArrayList();
        List headerDesc = new ArrayList();
        List<T> results = new ArrayList<>();

        List<Map<String, Object>> list = new ArrayList<>();
        String sheetName = "";
        Workbook book = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            if (POIFSFileSystem.hasPOIFSHeader((InputStream) inputStream)) {
                book = new HSSFWorkbook((InputStream) inputStream);
            } else if (POIXMLDocument.hasOOXMLHeader((InputStream) inputStream)) {
                book = new XSSFWorkbook(OPCPackage.open((InputStream) inputStream));
            }
            Sheet sheet = book.getSheetAt(sheetIndex);

            // 获取表头字段配置信息
            Map<Integer, String> headerMap = new HashMap<>();
            Row headerRow = sheet.getRow(params.getTitleRows());//头一行是字段
            int lastCellNum = headerRow.getLastCellNum();
            for (int i = 0; i < lastCellNum; i++) {
                String title = headerRow.getCell(i).getStringCellValue();
                if (StringUtils.isEmpty(title)) {
                    continue;
                }
                // 替换回车
                title = title.replace("\n", " ").trim();//去除列字段的空格
                headerMap.put(i, title);
                header.add(title);
            }
            // 获取表头字段配置信息-描述
            Row headerDescRow = sheet.getRow(params.getTitleRows() + 1);//第二行是字段描述
            //int lastCellNum = headerDescRow.getLastCellNum();
            for (int i = 0; i < lastCellNum; i++) {
                String title = headerDescRow.getCell(i).getStringCellValue();
                if (StringUtils.isEmpty(title)) {
                    continue;
                }
                // 替换回车
                title = title.replace("\n", " ").trim();//去除列字段的空格
                headerDesc.add(title);
            }

            // 获取Excel数据
            int lastRowNum = sheet.getLastRowNum() + 1;
            int startRow = params.getStartRows();
            for (int i = startRow; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    break;
                }

                // 每一行的第一个单元格为空直接返回
                Object vsl = getCellValue(row, 0);
                if (vsl == null || StringUtils.isEmpty(vsl.toString())) {
                    break;
                }

                Map<String, Object> map = new HashMap<>();
                for (int ci : headerMap.keySet()) {
                    map.put(headerMap.get(ci), getCellValue(row, ci));
                }
                list.add(map);
            }

            // 取值为空，直接返回
            /*if (list.isEmpty()) {
                return results;
            }*/

            result.put("headerDesc", headerDesc);
            result.put("header", header);
            result.put("headerMap", headerMap);
            result.put("item", list);

        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
//        return results;
//        return list;
        return result;
    }


    /**
     * 获取单元格数据
     *
     * @param row
     * @param i
     * @return
     */
    private static Object getCellValue(Row row, int i) {
        if (row == null) {
            return null;
        }
        Cell cell = row.getCell(i);
        if (cell == null) {
            return null;
        }
        if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
            Double doubleVal = cell.getNumericCellValue();
            if (doubleVal == null) {
                return null;
            }
            DecimalFormat formatCell = (DecimalFormat) NumberFormat.getPercentInstance();
            formatCell.applyPattern("0");
            String value = formatCell.format(doubleVal);
            if (Double.parseDouble(value) != doubleVal) {
                formatCell.applyPattern(Double.toString(doubleVal));
                value = formatCell.format(doubleVal);
            }
            Double dVal = Double.valueOf(value);
            if (dVal.doubleValue() == 1.0 * dVal.intValue()) {
                return dVal.intValue();
            }
            return dVal;
        } else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
            return cell.getBooleanCellValue();
        } else {
            return cell.getStringCellValue();
        }
    }

    /**
     * 获取Excel标题字段配置关系,实体字段需要带有@Excel注解
     *
     * @param tClass
     * @return
     */
    private static <T> Map<String, ExcelFieldConfig> getFieldConfig(Class<T> tClass) {
        Map<String, ExcelFieldConfig> configMap = new HashMap<>();
        Field[] fields = BeanUtil.getAllFields(tClass);
        for (Field field : fields) {
            if (field.getAnnotation(Excel.class) != null) {
                ExcelFieldConfig config = new ExcelFieldConfig();
                String excelField = field.getAnnotation(Excel.class).name();
                int type = field.getAnnotation(Excel.class).type();
                if (StringUtils.isEmpty(excelField)) {
                    excelField = field.getName();
                }
                config.setFieldName(field.getName());
                config.setExcelField(excelField);
                config.setType(field.getType());
                config.setField(field);
                config.setDate(type == 2);
                configMap.put(excelField, config);
            }
        }
        return configMap;
    }
}

调用方法

            MultipartFile file = entity.getValue();// 获取上传文件对象

            ImportParams params = new ImportParams();
            params.setTitleRows(0);
            params.setHeadRows(2);
            params.setStartRows(2);//所在行索引
            params.setNeedSave(true);

            JSONObject list = ExcelImportUitl.getExcelData(file.getInputStream(), params, 0);//当前指定读取第一个sheet内容