通过Excel表格批量导入数据

业务场景

批量导入用户数据

依赖

导入操作Excel表格相关依赖

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

工具类

package com.example.demo.util;


import com.example.demo.entity.dto.ImportExcelFile;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

/** * 导入Excel表格的工具类 * * @author cwt * @date 2022/5/23 11:27 */
public class ImportExcelUtil { 

    /** * @param @param excelFileName * @param @return * @param @throws IOException * @return Workbook * @throws * @Title: createWorkbook * @Description: 判断excel文件后缀名，生成不同的workbook * @param @param is */
    public Workbook createWorkbook(InputStream is, String excelFileName) throws IOException { 
        if (excelFileName.endsWith(".xls")) { 
            return new HSSFWorkbook(is);
        } else if (excelFileName.endsWith(".xlsx")) { 
            return new XSSFWorkbook(is);
        }
        return null;
    }

    /** * @param @param workbook * @param @param sheetIndex * @param @return * @return Sheet * @throws * @Title: getSheet * @Description: 根据sheet索引号获取对应的sheet */
    public Sheet getSheet(Workbook workbook, int sheetIndex) { 
        return workbook.getSheetAt(0);
    }


    public List<Object> importDataFromExcel(InputStream is, String excelFileName) { 
        List<Object> list = new ArrayList<Object>();
        try { 
            //创建工作簿
            Workbook workbook = this.createWorkbook(is, excelFileName);
            //创建工作表sheet
            Sheet sheet = this.getSheet(workbook, 0);
            //获取sheet中数据的行数
            int rows = sheet.getPhysicalNumberOfRows();
            //获取表头单元格个数
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            //第一行为标题栏，从第二行开始取数据
            for (int i = 1; i < rows; i++) { 
                //利用反射，给JavaBean的属性进行赋值
                ImportExcelFile vo = new ImportExcelFile();
                Field[] fields = vo.getClass().getDeclaredFields();

                Row row = sheet.getRow(i);
                int index = 0;
                while (index < cells) { 
                    Cell cell = row.getCell(index);
                    if (null == cell) { 
                        cell = row.createCell(index);
                    }
                    cell.setCellType(CellType.STRING);
                    String value = null == cell.getStringCellValue() ? "" : cell.getStringCellValue();

                    Field field = fields[index];
                    String fieldName = field.getName();
                    String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Method setMethod = vo.getClass().getMethod(methodName, new Class[]{ String.class});
                    setMethod.invoke(vo, new Object[]{ value});
                    index++;
                }
                //判断对象属性是否有值
                if (isHasValues(vo)) { 
                    list.add(vo);
                }

            }
        } catch (Exception e) { 
        } finally { 
            try { 
                //关闭流
                is.close();
            } catch (Exception e2) { 
            }
        }
        return list;

    }

    /** * @param @param object * @param @return * @return boolean * @throws * @Title: isHasValues * @Description: 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true */
    public boolean isHasValues(Object object) { 
        Field[] fields = object.getClass().getDeclaredFields();
        boolean flag = false;
        for (int i = 0; i < fields.length; i++) { 
            String fieldName = fields[i].getName();
            String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
            Method getMethod;
            try { 
                getMethod = object.getClass().getMethod(methodName);
                Object obj = getMethod.invoke(object);
                if (null != obj && !"".equals(obj)) { 
                    flag = true;
                    break;
                }
            } catch (Exception e) { 
            }
        }
        return flag;

    }
}

实体类

/** * Excel表格格式 * * @author cwt */
@Data
public class ImportExcelFile { 

    /** * 姓名 */
    private String name;

    /** * 账号（手机号码） */
    private String mobile;
    
    /** * 密码 */
    private String password;
}

/** * 用户实体类 * * @author cwt */
@Data
public class ImportExcelFile { 
    
    /** * id */
    private String id;

    /** * 姓名 */
    private String name;

    /** * 账号（手机号码） */
    private String mobile;
    
    /** * 密码 */
    private String password;
}

控制层

    @Resource
    private Service service;

    /** * 批量导入Excel表格数据 * @param file * @return 响应体 * @author cwt * @date */
    @RequestMapping(value = "/importExcel")
    public AjaxResult importExcel(MultipartFile file) throws IOException { 
        service.importExcel(file);
        return AjaxResult.success();
    }

服务层

public interface Service extends IService<User> { 
    /** * 通过excel导入用户 * * @param file * @return java.lang.Boolean * @author cwt * @date */
    Boolean importExcel(MultipartFile file) throws IOException;
}

服务实现类

@Service
public class ServiceImpl extends ServiceImpl<UserMapper, User> implements Service { 
    @Resource
    private UserMapper userMapper;
   
    @Override
    public Boolean importExcel(MultipartFile file) throws IOException { 
        //获取文件输入流
        InputStream is = file.getInputStream();
        //创建工作蒲用于读取文件信息
        ImportExcel importExcel = new ImportExcel();
        //调用工作蒲中的方法读取文件信息,此处使用stream流进行遍历插入操作
        List<Boolean> userList = importExcel.importDataFromExcel(is, file.getOriginalFilename()).stream.map(a -> { 
            //原始类型为Object，进行类型强转
            ImportExcelFile userDto = (ImportExcelFile) a;
            User user = new User();
            BeanUtils.copyBeanProp(user, userDto);
            user.setId(UUID.randomUUID().toString())
                userMapper.insert(user);
            return true;
        }).collect(Collectors.toList());
        
        return true;
    }
}

效果

demo

https://github.com/CWTJYY/ImportExcelFile.git