使用easyexcel导入数据到数据库

导入依赖

<!-- 阿里开源EXCEL 数据表导出-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.0-beta2</version>
</dependency>

实体类

package com.ph.rfwg.entity;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.io.Serializable;
import java.util.Date;

@Data
@TableName("xj_zg_proj") //MP表名
@ApiModel("直管工程") //swagger注释
@HeadRowHeight(23)//表头行高
public class ZgProj{
    @ExcelIgnore
    @ApiModelProperty("工程id")
    private String uuid;

    @ExcelProperty(value = {"直管工程台账","工程名称"}, index = 2)
    @ColumnWidth(35)
    @ApiModelProperty("工程名称")
    private String gcmc;

    @ExcelIgnore
    @ApiModelProperty("地区id")
    private String distId;

    @ExcelProperty(value = {"直管工程台账","地址"}, index = 3)
    @ApiModelProperty("地址")
    @ColumnWidth(35)
    private String address;

    @ExcelIgnore
    @ApiModelProperty("用户id")
    private String userId;

    @TableField(exist = false)
    @ExcelIgnore
    @ApiModelProperty("用户名字")
    private String realName;

    @TableField(exist = false)
    @ExcelProperty(value = {"直管工程台账","所属市/区"}, index = 1)
    @ColumnWidth(15)
    private String distname;

    @ExcelProperty(value = {"直管工程台账","创建时间"}, index = 4)
    @ColumnWidth(22)
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;  // 单位id

    @TableField(exist = false)
    @ExcelProperty(value = {"直管工程台账","序号"}, index = 0)
    private Integer number;  // 单位id

}

service

   // 导入数据并存到数据库
    List<String> readExcel(HttpServletRequest request);

实现类

@Override
    public List<String> readExcel(HttpServletRequest request) {
        List<MultipartFile> exceldata = ((MultipartHttpServletRequest) request).getFiles("exceldata");
        MultipartFile file = exceldata.get(0);
        List<String> problems = new ArrayList<>();
        //保存InfoColl的list
        List<ZgProj> projList = new ArrayList<>();
        int flag = 0;
        try {
            InputStream inputStream = file.getInputStream();
            String fName = file.getOriginalFilename();
            //截取文件后缀
            String suffixName = fName.substring(fName.lastIndexOf("."));
            Workbook workbook;
            if (".xls".equals(suffixName)){
                workbook = new HSSFWorkbook(inputStream);
            } else {
                workbook = new XSSFWorkbook(inputStream);
            }
            Sheet sheetAt = workbook.getSheetAt(0);
            int rowCount = sheetAt.getPhysicalNumberOfRows();
            // 从第三行开始,取决于你的列名
            for(int i = 2;i < rowCount;i++){
                //每一行一个对象
                ZgProj proj=new ZgProj();
                for(int j = 0;j < 2;j++) {
                    Row row = sheetAt.getRow(i);
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        if(j==0){
                            if(cellType!=3){
                                if (cellType != 1){  // 如果不是数值就存文本
                                    proj.setGcmc(String.valueOf((int) cell.getNumericCellValue()));
                                } else {
                                    proj.setGcmc(cell.getStringCellValue());
                                }
                            }
                        }
                       if(j==1){
                           if(cellType!=3){
                               if (cellType != 1){  // 如果不是数值就存文本
                                   proj.setAddress(String.valueOf((int) cell.getNumericCellValue()));
                               } else {
                                   proj.setAddress(cell.getStringCellValue());
                               }
                           }
                       }
                    }
                }
                proj.setUuid(CommUtils.getUUID());
                proj.setCreateTime(Tools.getTimestamp(null));
                projList.add(proj);
            }
            if (flag == 1){
                for (int i = 0; i < problems.size(); i++) {
                    problems.set(i,(i+1) + "." + problems.get(i));
                }
            } else {
                if (rowCount > 1 ) {
                    for (int i = 0; i < projList.size(); i++) {
                        ZgProj proj = projList.get(i);
                        projMapper.insert(proj);
                    }
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        return problems;
    }

controller

 @RequestMapping(value = "/daoru",method = {RequestMethod.GET})
    @ApiOperation(value = "导入")
    public AjaxObj daoru(HttpServletRequest request) throws IOException {
        System.err.println("进来导入方法");
        List<String> strings = projService.readExcel(request);
        return new AjaxObj(ReturnValCode.RTN_VAL_CODE_SUCCESS, "请求成功",strings);
    }