通用Excel导入

1、引入maven

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

2、ExcelUtil工具类

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

/**
 * 通用excel导入数据库
 * @author wanglu
 * @since 1.0, 2021/9/8 15:57
 */
public class ExcelUtil {

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

    /**
     * 公共的导入excel方法
     * @param sheet excel第一个工作簿
     * @param obj 实体类
     * @return
     * @throws IOException
     */
    public static List<Map<String,Object>> importBaseExcel(Sheet sheet,Object obj) throws IOException{
        try {
            //获取sheet中第一行行号
            int firstRowNum = sheet.getFirstRowNum();
            //获取sheet中最后一行行号
            int lastRowNum = sheet.getLastRowNum();
            //获取该实体所有定义的属性 返回Field数组
            java.lang.reflect.Field[] entityName = findEntityAllTypeName(obj);
            String classname =  obj.getClass().getName();
            Class<?> clazz = Class.forName(classname);
            List<Map<String,Object>> list = new ArrayList<>();
            //循环插入数据
            for(int i=firstRowNum+1;i<=lastRowNum;i++){
                Row row = sheet.getRow(i);
                //可以根据该类名生成Java对象
                Object pojo =  clazz.newInstance();
                //实体字段匹配sheet列
                for(int j = 0;j < entityName.length;j++){
                    //获取属性的名字,将属性的首字符大写，方便构造set方法
                    String name = "set"+entityName[j].getName().substring(0, 1).toUpperCase().concat(entityName[j].getName().substring(1))+"";
                    //获取属性的类型
                    String type = entityName[j].getGenericType().toString();
                    Method m = null;
                    //getMethod只能调用public声明的方法，而getDeclaredMethod基本可以调用任何类型声明的方法
                    m = obj.getClass().getDeclaredMethod(name,entityName[j].getType());
                    Cell pname = row.getCell(j);
                    //根据属性类型装入值
                    switch (type) {
                        case "char":
                        case "java.lang.Character":
                        case "class java.lang.String":
                            m.invoke(pojo,getVal(pname));
                            break;
                        case "int":
                        case "class java.lang.Integer":
                            m.invoke(pojo,Integer.valueOf(getVal(pname)));
                            break;
                        case "class java.util.Date":
                            m.invoke(pojo,getVal(pname));
                            break;
                        case "float":
                        case "double":
                        case "java.lang.Double":
                        case "class java.lang.Double":
                        case "java.lang.Float":
                        case "class java.lang.Float":
                        case "java.lang.Long":
                        case "java.lang.Short":
                        case "java.math.BigDecimal":
                            m.invoke(pojo,Double.valueOf(getVal(pname)));
                            break;
                        default:
                            break;
                    }
                }
                Map<String, Object> inmap = transBean2Map(pojo);
                list.add(inmap);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取实体对象返回属性名称
     * @param obj 实体对象
     * @return
     * @throws Exception
     */
    public static java.lang.reflect.Field[] findEntityAllTypeName(Object obj)throws Exception{

        Class<? extends Object> cls = obj.getClass();

        return cls.getDeclaredFields();
    }

    /**
     * 根据文件选择excel版本
     * @return
     * @throws Exception
     */
    public Workbook chooseWorkbook(MultipartFile file) throws Exception{

        Workbook workbook = null;

        //把MultipartFile转化为File
        CommonsMultipartFile cmf = (CommonsMultipartFile)file;
        DiskFileItem dfi = (DiskFileItem) cmf.getFileItem();
        File fo = dfi.getStoreLocation();

        String filename = file.getOriginalFilename();
        String fileType = (filename.substring(filename.lastIndexOf("."), filename.length())).toLowerCase();

        if(excel2003L.equals(fileType)){
            workbook = new HSSFWorkbook(FileUtils.openInputStream(fo));  //2003-
        }else if(excel2007U.equals(fileType)){
            workbook = new XSSFWorkbook(FileUtils.openInputStream(fo));  //2007+
        }else{
            throw new Exception("解析的文件格式有误！");
        }
        return workbook;
    }

    /**
     * 处理类型
     * @param cell
     * @return
     */
    public static String getVal(Cell cell) {
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }

    /**
     * 利用Introspector和PropertyDescriptor将Bean转为Map
     * @param: obj
     * @return Map<String,Object>
     * @author wanglu
     * @Date  2021/9/8
     */
    public static Map<String, Object> transBean2Map(Object obj) throws Exception{
        if(obj == null){
            return null;
        }
        Map<String, Object> map = new HashMap<String, Object>();
        BeanInfo beanInfo = Introspector.getBeanInfo(obj.getClass());
        PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
        for (PropertyDescriptor property : propertyDescriptors) {
            String key = property.getName();
            // 过滤class属性
            if (!key.equals("class")) {
                // 得到property对应的getter方法
                Method getter = property.getReadMethod();
                Object value = getter.invoke(obj);
                map.put(key, value);
            }

        }
        return map;
    }
}

3、Controller代码

@Operation(summary = "excel表格导入")
    @RequestMapping(value = "/ledger/importChildLedgerDetailExcel", method = RequestMethod.POST)
    @ApiResponses(value = { @ApiResponse(responseCode = "200", description = "操作成功") , @ApiResponse(responseCode = "400", description = "参数验证错误；以及业务异常"), @ApiResponse(responseCode = "500", description = "程序处理内部报错") })
    public Map<String,Object> importChildLedgerDetailExcel(@RequestParam(value = "file") MultipartFile file) {
        Assert.notNull(file, "参数file不允许为空！");
        return oaOctChildLedgerService.importChildLedgerDetailExcel(file);
    }

4、service实现类调用

 @Override
    public Map<String, Object> importChildLedgerDetailExcel(MultipartFile file) {
        Map<String,Object> map = new HashMap<>();
        map.put("success",true);
        map.put("message","导入成功");
        Workbook workbook = null;
        try {
            String fileName = file.getOriginalFilename();
            if (fileName.endsWith("xls")) {
                POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
                workbook = new HSSFWorkbook(pois);
            } else if (fileName.endsWith("xlsx")) {
                workbook = new XSSFWorkbook(file.getInputStream());
            }
           // HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);//获取文档中的第一个sheet
            OctChildLedgerDetailExcelBean excelBean = new OctChildLedgerDetailExcelBean();
            List<Map<String,Object>> list = ExcelUtil.importBaseExcel(sheet,excelBean);
            map.put("list",list);
            workbook.close();
        }catch (Exception e){
            e.printStackTrace();
            map.put("success",false);
            map.put("message","导入失败");
            return map;
        }finally {
            try {
                workbook.close();
            }catch (Exception e){
                logger.error("关闭workbook失败"+e.getMessage());
            }

        }
        return map;
    }