JAVA实现Excel导入

使用alibaba 的easyexcel

工具类
package com.zhada.cloud.enterprise.infrastructure.utils;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.zhada.cloud.enterprise.infrastructure.listener.EasyExcelListener;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * describe:            读取Excel工具类（导出Excel功能未实现）
 *
 * @author wangxf
 */

public class EasyExcelUtil {
    /**
     * 读取某个sheet的Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射（继承BaseRowModel的类）
     * @return
     * @throws IOException
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
        return readExcel(excel, rowModel, 1, 1);
    }

    /**
     * 读取某个sheet的Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承BaseRowModel类
     * @param sheetNo  sheet的序号，从1开始
     * @return Excel数据list
     * @throws IOException
     */
    public static Map<String, Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
        Map<String, Object> result = new HashMap<>();
        EasyExcelListener easyExcelListener = new EasyExcelListener();
        ExcelReader reader = getReader(excel, easyExcelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));
        //检验表头
        Boolean flag = false;
        if (easyExcelListener.getImportHeads().equals(easyExcelListener.getModelHeads())) {
            flag = true;
        }
        result.put("flag", flag);
        result.put("datas", easyExcelListener.getDatas());
        return result;
    }

    /**
     * 读取某个sheet的Excel
     *
     * @param excel       文件
     * @param rowModel    实体类映射，继承BaseRowModel类
     * @param sheetNo     sheet的序号 ，从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel数据List
     * @throws IOException
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
        EasyExcelListener excelListener = new EasyExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
        return excelListener.getDatas();
    }

    /**
     * 读取指定sheet的Excel(多个sheet)
     *
     * @param excel     文件
     * @param rowModel  实体类映射，继承BaseRowModel类
     * @param sheetName
     * @return
     * @throws IOException
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, String sheetName) throws IOException {
        EasyExcelListener easyExcelListener = new EasyExcelListener();
        ExcelReader reader = getReader(excel, easyExcelListener);
        if (reader == null) {
            return null;
        }
        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }

            if (sheet.getSheetName().contains(sheetName)) {
                reader.read(sheet);
                break;
            }
        }
        return easyExcelListener.getDatas();
    }


    /**
     * 返回ExcelReader
     *
     * @param excel         需要解析的excel文件
     * @param excelListener new ExcelListener()
     * @return
     * @throws IOException
     */
    private static ExcelReader getReader(MultipartFile excel, EasyExcelListener excelListener) throws IOException {
        String filename = excel.getOriginalFilename();
        if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) {
            InputStream is = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(is, null, excelListener, false);
        } else {
            return null;
        }
    }

}

具体的实现：

//Controller
 @AuthorizeConfiguration(type = ActionEnum.fileImport)
  @RequestMapping(value = "/ByExcel")
  @ApiOperation(value = "Excel导入", notes = "Excel导入", httpMethod = "POST")
  public PostRequestResult ExcelImport(MultipartFile file, String createName) {
    try {
      logger.info("Excel导入");
      return iService.ExcelImport(file, createName);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return ResponseResultFactory.createPostResult(ResponseState.error, "解析失败");
  }

@Override
  public PostRequestResult ExcelImport(MultipartFile file, String createName)
      throws IOException {
    PostRequestResult resultInfo = null;
    List<BuildEntity> entities = new ArrayList<>();
    if (file != null) {
      Map<String, Object> result = EasyExcelUtil.readExcel(file, new BuildExcelImport(), 1);
      if (result == null) {
        return ResponseResultFactory.createPostResult(ResponseState.error, "文件格式类型错误");
      }
      List<Object> list = (List<Object>) result.get("datas");
      if (list == null || list.size() == 0) {
        return ResponseResultFactory.createPostResult(ResponseState.error, "文件无数据");
      }
      List<BuildExcelImport> modelList = new ArrayList<>();
      for (int i = 0; i < list.size(); i++) {
        BuildExcelImport model = (BuildExcelImport) list.get(i);
        if (model.getAreaName() == null
            || model.getAreaCode() == null
            || model.getState() == null) {
          return ResponseResultFactory.createPostResult(
              ResponseState.error, "文件第" + (i + 2) + "行附近区域名称/区域编码/建设情况/为空");
        }
        modelList.add(model);
      }
      modelList.stream()
          .forEach(
              mode -> {
                BuildEntity build = new BuildEntity(mmode ,createName);
                entities.add(build);
              });
      if (CollectionUtil.isNotEmpty(entities)) {
        try {
          iBuildRepository.saveAll(entities);
        } catch (DataIntegrityViolationException e) {
          return ResponseResultFactory.createPostResult(ResponseState.repeat, "当前编码重复，请重新输入");
        }
      }
      return ResponseResultFactory.createPostResult(ResponseState.success, "模板导入成功");
    }

    return resultInfo;
  }

模板DTO

package com.zhada.cloud.enterprise.apis.dto.build;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.zhada.cloud.enterprise.domain.core.build.IBuildExcel;

/**
 * @author: wang.xf
 * @description: 导入
 * @create: 2021/6/2 9:54
 */
public class BuildExcelImport extends BaseRowModel implements IBuildExcel {
  @ExcelProperty(value = "*区域名称", index = 0)
  private String areaName;

  @ExcelProperty(value = "*区域编码", index = 1)
  private String areaCode;

  @ExcelProperty(value = "*建设情况(未完成;建设中;已完成)", index = 2)
  private String state;

  @Override
  public String getAreaName() {
    return areaName;
  }

  public void setAreaName(String areaName) {
    this.areaName = areaName;
  }

  @Override
  public String getAreaCode() {
    return areaCode;
  }

  public void setAreaCode(String areaCode) {
    this.areaCode = areaCode;
  }

  @Override
  public String getState() {
    return state;
  }

  public void setState(String state) {
    this.state = state;
  }
}