最简单的Excel的导入导出（拿去就能用）

还是基于SpringBoot + mybatis 开发：jdk1.8 数据库mysql

先搭个框架：让项目跑起来先

 框架不会搭：请参考这一篇：springboot章节_sdgames的博客-CSDN博客

响应类：

**
 *
 *
 * @author zigao
 * @date 2020年08月01日 11:48
 */
public class ResponseMessage implements Serializable {
    int status;
    Object message;
    String errmsg;

    public int getStatus() {
        return this.status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public Object getMessage() {
        return this.message;
    }

    public void setMessage(Object message) {
        this.message = message;
    }

    public String getErrmsg() {
        return this.errmsg;
    }

    public void setErrmsg(String errmsg) {
        this.errmsg = errmsg;
    }

    public ResponseMessage() {
    }

    public ResponseMessage(int status) {
        this.status = 0;
    }

    public ResponseMessage(Object data) {
        this.status = 0;
        this.message = data;
    }

    public ResponseMessage(int status, Object data) {
        this.status = status;
        this.message = data;
    }

    public ResponseMessage(int status, String err) {
        this.status = status;
        this.errmsg = err;
    }
}

 数据字段映射：一定要和excel表头属性一  一 对应，一定要和excel表头属性一  一 对应，一定要和excel表头属性一  一 对应，重要的事说三遍

/**
 * @Author: zigao
 * @Date: 2022/3/2 15:37
 */
public class ListConfig {

    public final static List<String> strList = Arrays.asList("id", "name","age", "dept", "education","competent");


   }

/**
 * 人员信息
 *
 * @author zigao
 * @date 2020年08月01日 21:48
 */

@Data
public class User implements Serializable {

    /**
     * 用户编码(自增序列)
     */
    private  String id;

    /**
     * 名字
     */
    private String name;

    /**
     * 年龄
     */
    private Integer age;


    /**
     *职务
     */
    private String dept;

    /**
     * 学历
     */
   private String education;

    /**
     * 职称
     */
   private String competent;


}

接口：

/**
 *  人员信息接口
 *
 * @author zigao
 * @date 2020年08月06日 21:25
 */

public interface UserService{

    /**
     * 批量添加人员信息
     * @param usersList
     */
    void addListUser(List<User> usersList);
    
}

实现类：

/**
 * @Author: zigao
 * @Date: 2022/3/4 11:23
 */
@Service
public class UserServiceImpl  implements UserService{


    @Autowired
    private UserMapper userMapper;

    @Override
    public void addListUser(List<User> usersList) {
        userMapper.addListUser(usersList);
    }


}

  excel解析工具类：注意startRow这个参数是Excel具体数据对应的那个行号 减一

比如数据从第四行开始：那么就是 4 - 1 = 3，这个参数就传3

/**
 * excel 解析工具类
 */
public class ExcelAnalysisUtils {

    /**
     * Date包名
     */
    private static final String TYPE_DATE = "java.util.Date";
    /**
     * Double包名
     */
    private static final String TYPE_DOUBLE = "java.lang.Double";
    /**
     * Integer包名
     */
    private static final String TYPE_INTEGER = "java.lang.Integer";
    /**
     * String包名
     */
    private static final String TYPE_STRING = "java.lang.String";

    /**
     * Excel导出为实体
     *
     * @param is          is流
     * @param clazz       class
     * @param columnNames 列名list
     * @param startRow    起始行(索引从0开始)
     * @return
     * @throws Exception
     */
    public static <T> List<T> excelToEntity(InputStream is, Class<T> clazz, List<String> columnNames, int startRow) throws Exception {
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        List<T> list = null;
        wb = WorkbookFactory.create(is);
        if (wb != null) {
            //用来存放表中数据
            list = new ArrayList<T>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            for (int i = startRow; i < rownum; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    list.add((T) getFieldValueByName(columnNames, row, clazz));
                }
            }
        }
        return list;
    }


    /**
     * @param colNames 字段名
     * @param row      表行
     * @param clazz    class
     * @return 字段值
     * @MethodName : getFieldValueByName
     * @Description : 根据字段名获取字段值
     */
    private static <T> T getFieldValueByName(List<String> colNames, Row row, Class<T> clazz) throws Exception {
        T t = clazz.newInstance();
        //获取全部字段
        Field[] fields = clazz.getDeclaredFields();

        //遍历字段
        for (Field field : fields) {
            //判断字段是否存在colnames中
            if (colNames.contains(field.getName())) {

                //开启强制赋值
                field.setAccessible(true);

                //获取值
                Object val = getCellFormatValue(row.getCell(colNames.indexOf(field.getName())));

                //判断字段对应类型，然后转换赋值
                switch (field.getType().getName()) {
                    case TYPE_DATE:
                        //getCellFormatValue(row.getCell(colNames.indexOf(field.getName()))).toString()
                        field.set(t, StringUtils.isEmpty(val) ? null : TimeUtils.formatDate(val.toString(),null));
                        break;
                    case TYPE_DOUBLE:
                        //getCellFormatValue(row.getCell(colnames.indexOf(field.getName()))).toString()
                        field.set(t, StringUtils.isEmpty(val) ? null : Double.valueOf(val.toString()));
                        break;
                    case TYPE_INTEGER:
                        if (val instanceof Double) {
                            field.set(t, StringUtils.isEmpty(val) ? null : ((Double) val).intValue());
                        } else {
                            field.set(t, StringUtils.isEmpty(val) ? null : Integer.valueOf(val.toString()));
                        }
                        break;
                    default:
                        //去除小数
                        if (val instanceof Double) {
                            field.set(t, String.valueOf(((Double) val).intValue()));
                        } else {
                            field.set(t, val);
                        }
                }
            }
        }

        return t;
    }


    //读取excel
    private static Workbook readExcel(InputStream is, String fileName) throws Exception {
        if (fileName == null) {
            return null;
        }
        try {
           return WorkbookFactory.create(is);
        } catch (FileNotFoundException e) {
            throw new Exception("文件未找到");
        } catch (IOException e) {
            throw new Exception("IO错误");
        }
    }

    private static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellTypeEnum()) {
                case NUMERIC: {
                    cellValue = cell.getNumericCellValue();
                    break;
                }
                case FORMULA: {
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        //数字
                        cellValue = cell.getNumericCellValue();
                    }
                    break;
                }
                case STRING: {
                    cellValue = cell.getStringCellValue();
                    break;
                }
                default:
            }
        }
        return cellValue;
    }
}

/**
 * 〈时间工具类〉
 *
 * @author zigao
 * @create 2022/3/11
 */
public class TimeUtils {
    private TimeUtils(){

    }

    public static Date formatDate(String dateStr, String format) throws ParseException {
        //设置sdf默认yyyy-MM-dd
        SimpleDateFormat sdf;

        //如果strDate长度>0(带时分秒) 则改为yyyy-MM-dd hh:mm:ss
        if (StringUtils.isEmpty(format)) {
            if (dateStr.length() > 10) {
                sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            } else {
                sdf = new SimpleDateFormat("yyyy-MM-dd");
            }
        } else {
            sdf = new SimpleDateFormat(format);
        }
        return sdf.parse(dateStr);
    }

    public static String formatLongDateStr(Date dateStr){
        //设置sdf默认yyyy-MM-dd
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return sdf.format(dateStr);
    }
    /**
     * 获取前后x小时时间
     *
     * @return {@link Date}
     */
    public static Date getAroundHoursDate(Date date, int hours) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.add(Calendar.HOUR, hours);
        return c.getTime();
    }

    /**
     * 获取前后x分钟时间
     *
     * @return {@link Date}
     */
    public static Date getAroundMinuteDate(Date date, int minute) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.add(Calendar.MINUTE, minute);
        return c.getTime();
    }

    /**
     * 比对时间年月日
     *
     * @param d1
     * @param d2
     * @return {@link boolean}
     * @author fengwt
     * @date 2021/5/10 11:31
     */
    public static boolean sameDate(Date d1, Date d2) {
        if (null == d1 || null == d2) {
            return false;
        }
        Calendar cal1 = Calendar.getInstance();
        cal1.setTime(d1);
        Calendar cal2 = Calendar.getInstance();
        cal2.setTime(d2);
        return cal1.get(Calendar.ERA) == cal2.get(Calendar.ERA) &&
                cal1.get(Calendar.YEAR) == cal2.get(Calendar.YEAR) &&
                cal1.get(Calendar.DAY_OF_YEAR) == cal2.get(Calendar.DAY_OF_YEAR);
    }

    /**
     * 获取月天数
     *
     * @param date date
     * @return {@link int}
     * @author fengwt
     * @date 2021/5/10 11:18
     */
    public static int getDaysOfMonth(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
    }


    /**
     * 获取前后x天时间
     *
     * @return {@link Date}
     */
    public static Date getAroundDayDate(Date date, int day) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.add(Calendar.DATE, day);
        return c.getTime();
    }

    /**
     * 获取前后x年时间
     *
     * @return {@link Date}
     */
    public static Date getAroundYearDate(Date date, int year) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.add(Calendar.YEAR, year);
        return c.getTime();
    }

    /**
     * 获取某天开始时间 (xxxx年xx月xx日 00:00:00)
     *
     * @return Date
     */
    public static Date getDayBegin(Date date) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.set(Calendar.HOUR_OF_DAY, 0);
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        c.set(Calendar.MILLISECOND, 0);
        return c.getTime();
    }

    /**
     * 获取某天结束时间 (xxxx年xx月xx日 00:00:00)
     *
     * @return Date
     */
    public static Date getDayEnd(Date date) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.set(Calendar.HOUR_OF_DAY, 23);
        c.set(Calendar.MINUTE, 59);
        c.set(Calendar.SECOND, 59);
        c.set(Calendar.MILLISECOND, 999);
        return c.getTime();
    }

    /**
     * 获取某月开始时间 (xxxx年xx月1日 00:00:00)
     *
     * @return Date
     */
    public static Date getMonthBegin(Date date) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.set(Calendar.DAY_OF_MONTH, 1);
        c.set(Calendar.HOUR_OF_DAY, 0);
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        c.set(Calendar.MILLISECOND, 0);
        return c.getTime();
    }

    /**
     * 获取某年结束时间 (xxxx年xx月1日 00:00:00)
     *
     * @return Date
     */
    public static Date getYearEnd(Date date) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.set(Calendar.MONTH, 11);
        c.set(Calendar.DAY_OF_MONTH,31);
        c.set(Calendar.HOUR_OF_DAY, 23);
        c.set(Calendar.MINUTE, 59);
        c.set(Calendar.SECOND, 59);
        c.set(Calendar.MILLISECOND, 59);
        return c.getTime();
    }

    /**
     * 获取某年某月开始时间 (xxxx年xx月1日 00:00:00)
     *
     * @return Date
     */
    public static Date getMonthBegin(int year,int month) {
        Calendar c = Calendar.getInstance();
        c.set(Calendar.YEAR,year);
        c.set(Calendar.MONTH,month-1);
        c.set(Calendar.DAY_OF_MONTH, 1);
        c.set(Calendar.HOUR_OF_DAY, 0);
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        c.set(Calendar.MILLISECOND, 0);
        return c.getTime();
    }

    /**
     * 获取某年某月结束时间
     *
     * @return Date
     */
    public static Date getMonthEnd(int year,int month) {
        Calendar c = Calendar.getInstance();
        c.set(Calendar.YEAR,year);
        c.set(Calendar.MONTH, month);
        c.set(Calendar.DAY_OF_MONTH, 0);
        c.set(Calendar.HOUR_OF_DAY, 23);
        c.set(Calendar.MINUTE, 59);
        c.set(Calendar.SECOND, 59);
        c.set(Calendar.MILLISECOND, 999);
        return c.getTime();
    }

    /**
     * 获取某月结束时间 (xxxx年xx月1日 00:00:00)
     *
     * @return Date
     */
    public static Date getMonthEnd(Date date) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.add(Calendar.MONTH, 1);
        c.set(Calendar.DAY_OF_MONTH, 0);
        c.set(Calendar.HOUR_OF_DAY, 23);
        c.set(Calendar.MINUTE, 59);
        c.set(Calendar.SECOND, 59);
        c.set(Calendar.MILLISECOND, 999);
        return c.getTime();
    }

    /**
     * 获取当年的某月开始时间
     * @author fengwt
     * @date 2021/11/16 16:19
     * @param month 月份
     * @return {@link Date}
     */
    public static Date getThisYearMonthStart(int month){
        Calendar c = Calendar.getInstance();
        c.set(Calendar.MONTH, month-1);
        c.set(Calendar.DAY_OF_MONTH, 1);
        c.set(Calendar.HOUR_OF_DAY, 0);
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        c.set(Calendar.MILLISECOND, 0);
        return c.getTime();
    }
    /**
     * 获取当年的某月结束时间
     * @author fengwt
     * @date 2021/11/16 16:19
     * @param month 月份
     * @return {@link Date}
     */
    public static Date getThisYearMonthEnd(int month){
        Calendar c = Calendar.getInstance();
        c.set(Calendar.MONTH, month);
        c.set(Calendar.DAY_OF_MONTH, 0);
        c.set(Calendar.HOUR_OF_DAY, 23);
        c.set(Calendar.MINUTE, 59);
        c.set(Calendar.SECOND, 59);
        c.set(Calendar.MILLISECOND, 999);
        return c.getTime();
    }
    public static Map<String, Integer> getDateMap(Date date) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);

        Map<String, Integer> map = new HashMap<>(6);
        map.put("yyyy", c.get(Calendar.YEAR));
        map.put("MM", c.get(Calendar.MONTH) + 1);
        map.put("dd", c.get(Calendar.DATE));
        map.put("HH", c.get(Calendar.HOUR_OF_DAY));
        map.put("mm", c.get(Calendar.MINUTE));
        map.put("ss", c.get(Calendar.SECOND));
        return map;
    }

    public static Map<String, String> getDateMapStr(Date date) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);

        Map<String, String> map = new HashMap<>(10);
        map.put("yyyy", String.valueOf(c.get(Calendar.YEAR)));
        map.put("MM", String.format("%02d", c.get(Calendar.MONTH) + 1));
        map.put("dd", String.format("%02d", c.get(Calendar.DATE)));
        map.put("HH", String.format("%02d", c.get(Calendar.HOUR_OF_DAY)));
        map.put("mm", String.format("%02d", c.get(Calendar.MINUTE)));
        map.put("ss", String.format("%02d", c.get(Calendar.SECOND)));
        return map;
    }

    public static int getDateMonth(Date date){
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        return c.get(Calendar.MONTH) + 1;
    }

    public static int getDateYear(Date date){
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        return c.get(Calendar.YEAR);
    }
}

/**
 * 
 * 启动类
 * @author zigao
 * @date 2020年08月01日 11:46
 */

@SpringBootApplication
@MapperScan("com.zigao.com.dao.**")
public class zigaoApplication {
    public static void main(String[] args) {
        SpringApplication.run(zigaoApplication.class, args);
        System.out.println("===============================已启动================================");
    }
}

  <insert id="addListUser">
        INSERT INTO `USER`(`id`,`name`,`age`,`dept`,`education`,`competent`) VALUES
        <foreach collection="usersList" separator="," item="item">
            (#{item.id},#{item.name},#{item.age},#{item.dept},#{item.education},#{item.competent})
        </foreach>
    </insert>

server:
  port: 8888
  servlet:
    # 上下文路径
    context-path: /api
  tomcat:
    uri-encoding: UTF-8

pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  pageSizeZero: false #pageSize=0


spring:
  application:
    #  项目名称
    name: zigao
  profiles:
    # 环境
    active: dev
  jackson:
    # json 序列化配置
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT
  http:
    #  编码
    encoding:
      charset: UTF-8
      enabled: true
      force: true

spring:
  datasource:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test3?useUnicode=true&characterEncoding=utf-8
    username: root
    password: ********
  #设置文件大小
  servlet:
    multipart:
      max-file-size: 50MB
      max-request-size: 50MB
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: com.zigao.com.entity
  configuration:
    call-setters-on-nulls: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

代码基本完了：我们启动项目测试下：

启动成功! ! ! 

我要把以下数据录入进去：

 

 执行成功：

 已录入到数据库：

 

完美！ ！ ！