Excel 读取解析存入数据库完整版

1.应用到的pom

  <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.18</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>
    </dependencies>

2.使用的模板 表头要是和javabean的一样 必须是英文

3.javaBean

@Getter
@Setter
public class CourtStandardData {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)//主键和自增长标记
    private Long id;

    @Column(name = "court_id")
    private Long courtId;

    @Column(name = "court_name")
    private String courtName;

    @Column(name = "parent_id")
    private Long parentId;

    @Column(name = "operation_flag")
    private Integer operationFlag;

    @Column(name = "region_name")
    private String regionName;

    @Column(name = "sort_number")
    private Integer sortNumber;

    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "create_date")
    private Date createDate;

    public Date getCreateDate() {
        return DateUtils.cloneDate(createDate);
    }

    public void setCreateDate(Date createDate) {
        this.createDate = DateUtils.cloneDate(createDate);
    }

4. DateUtils

package com.echoquant.portal.util;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

public class DateUtils {

    /**
     * 时间格式.
     */
    public static final String TIME_FORMAT = "yyyy-MM-dd HH:mm:ss.SSS";
    /**
     * 日期格式.
     */
    public static final String DATE_FORMAT = "yyyy-MM-dd";

    /**
     * Make a copy of date, null-safe.
     *
     * @param date date to copy
     * @return copy of date
     */
    public static Date cloneDate(Date date) {
        return date == null ? null : new Date(date.getTime());
    }

    /**
     * 获取当前时间.
     *
     * @return
     */
    public static String getTime() {
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat(TIME_FORMAT);

        return sdf.format(date);
    }

    /**
     * 字符串转时间.
     *
     * @param time
     * @return
     *
     * @throws ParseException if has error
     */
    public static Date toDate(String time) throws ParseException {

        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
        return sdf.parse(time);

    }

    /**
     * 日期加一天.
     *
     * @param date
     * @return
     */
    public static Date dateAddOne(Date date) {
        Calendar calendar = new GregorianCalendar();
        calendar.setTime(date);
        calendar.add(calendar.DATE, 1); //把日期往后增加一天,整数  往后推,负数往前移动
        date = calendar.getTime(); //这个时间就是日期往后推一天的结果
        return date;
    }

    /**
     * 日期加一秒.
     *
     * @param date
     * @return
     */
    public static Date dateAddOneSecond(Date date) {
        Calendar calendar = new GregorianCalendar();
        calendar.setTime(date);
        calendar.add(calendar.SECOND, 1); //把日期往后增加一天,整数  往后推,负数往前移动
        date = calendar.getTime(); //这个时间就是日期往后推一天的结果
        return date;
    }

    /**
     * 字符串格式日期加一天.
     *
     * @param time
     * @return
     *
     * @throws ParseException if has error
     */
    public static String dateAddOne(String time) throws ParseException {
        return dateToString(dateAddOne(toDate(time)));
    }

    /**
     * 字符串格式日期加一天.
     *
     * @return
     *
     * @throws ParseException if has error
     */
    public static String timeAddOneSecond(Date date) {
        return dateToStringBYFormat(dateAddOneSecond(date), TIME_FORMAT);
    }

    /**
     * 日期转字符串.
     *
     * @param date
     * @return
     */
    public static String dateToString(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);

        return sdf.format(date);
    }

    /**
     * 日期转字符串.
     *
     * @param date
     * @return
     */
    public static String dateToStringBYFormat(Date date, String strDateFormat) {
        SimpleDateFormat sdf = new SimpleDateFormat(strDateFormat);

        return sdf.format(date);
    }

    /**
     * 日期转为8位字符串.
     *
     * @param date
     * @return
     */
    public static String dateToStringByEightFormat(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
        String eighteFormat = sdf.format(date);
        //字符串时间转为8位字符串
        eighteFormat = eighteFormat.replace("-", "");
        return eighteFormat;
    }
}

5.返回值自定义

// Checkstyle OFF: EmptyLineSeparator
// Checkstyle OFF: SummaryJavadoc
// Checkstyle OFF: JavadocMethod
// Checkstyle OFF: MagicNumber
// Checkstyle OFF: JavadocMethod
// Checkstyle OFF: OperatorWrap
// Checkstyle OFF: JavadocVariable
// Checkstyle OFF: WhitespaceAround
// Checkstyle OFF: FileTabCharacter
public class JsonWrapperLog<T> {
    private T data;
    private int code;
    private String msg;

    public JsonWrapperLog() {
        this.code = 200;
        this.msg = "Success";
    }

    public JsonWrapperLog(int code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    public JsonWrapperLog(T data) {
        this.data = data;
        this.code = 200;
        this.msg = "Success";
    }

    public JsonWrapperLog(T data, String msg) {
        this.data = data;
        this.code = 200;
        this.msg = msg;
    }

    public T getData() {
        return data;
    }

    public void setData(T data) {
        this.data = data;
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    @Override
    public String toString() {
        return "JsonUtil{"
            + "data=" + data
            + ", code='" + code + '\''
            + ", msg='" + msg + '\''
            + '}';
    }
}

6. Controller 上传文件进行解析

    /**
     * 导入excel文件解析文件
     *
     * @param file
     * @return
     * @throws IOException 异常处理
     */
    @PostMapping("/import")
    public JsonWrapperLog importExcelCourtStandardData(@RequestPart(value = "file")
                                                       MultipartFile file, @RequestParam(value = "type") String type) throws IOException {
        return courtStandardDataService.dataComparison(file, type);
    }

2.ServiceImple

 @Override
    public JsonWrapperLog dataComparison(MultipartFile file, String type) throws IOException {
      JsonWrapperLog jsonWrapperLog = new JsonWrapperLog();
           //获取文件流
            InputStream inputStream = file.getInputStream();
            // hutool.poi.excel工具类 读取excel内容
            ExcelReader reader = ExcelUtil.getReader(inputStream, "Sheet1");
            //表头要是和javabean的一样 必须是英文 此处读取完成转为了list<实体>
            List<CourtStandardData> CourtStandardDataList =   reader.readAll(CourtStandardData.class);
              //已经把excel数据赋值到CourtStandardData上
            if (ObjectUtils.isNotEmpty(CourtStandardDataList)) {
                //如果CourtStandardData不等于null 插入数据库
                //jpa方式批量保存到数据库中
                courtStandardData = courtStandardDataRepository.saveAll(CourtStandardDataList);
            }
            jsonWrapperLog.setCode(200);
            jsonWrapperLog.setMsg("保存成功");
        return jsonWrapperLog;

}