快捷導(dǎo)航

java springboot poi 從controller 接收不同類型excel 文件處理

更新時(shí)間：2019年10月30日 10:17:47 作者：愛睡懶覺的派大星

這篇文章主要介紹了java springboot poi 從controller 接收不同類型excel 文件處理,本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

根據(jù)poi接收controller層的excel文件導(dǎo)入

可使用后綴名xls或xlsx格式的excel。

1.pom引入

    <!-- poi 操作Excel -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>

2.ExcelImportUtil 工具類創(chuàng)建　

import com.guard.biz.common.util.excel.ExcelIn;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * @author Wei
 * @time 2019/10/29
 * @Description excel 導(dǎo)入工具類
 */
public class ExcelImportUtil<T> {
  private static final Logger log = LoggerFactory.getLogger(ExcelImportUtil.class);
  private static BeanUtilsBean beanUtilsBean = new BeanUtilsBean();
  static {
    beanUtilsBean.getConvertUtils().register(new org.apache.commons.beanutils.converters.DateConverter(null), java.util.Date.class);
  }
  /**
   * 表頭名字和對應(yīng)所在第幾列的下標(biāo)，用于根據(jù)title取到對應(yīng)的值
   */
  private final Map<String, Integer> title_to_index = new HashMap<>();
  /**
   * 所有帶有ExcelIn注解的字段
   */
  private final List<Field> fields = new ArrayList<>();
  /**
   * 統(tǒng)計(jì)表格的行和列數(shù)量用來遍歷表格
   */
  private int firstCellNum = 0;
  private int lastCellNum = 0;
  private int firstRowNum = 0;
  private int lastRowNum = 0;
  private String sheetName;
  private Sheet sheet;
  public List<T> read(InputStream in, Class clazz) throws Exception {
    gatherAnnotationFields(clazz);
    configSheet(in);
    configHeader();
    List rList = null;
    try {
      rList = readContent(clazz);
    } catch (IllegalAccessException e) {
      throw new Exception(e);
    } catch (InstantiationException e) {
      throw new Exception(e);
    } catch (InvocationTargetException e) {
      throw new Exception(e);
    }
    return rList;
  }
  private List readContent(Class clazz) throws IllegalAccessException, InstantiationException, InvocationTargetException {
    Object o = null;
    Row row = null;
    List<Object> rsList = new ArrayList<>();
    Object value = null;
    for (int i = (firstRowNum + 1); i <= lastRowNum; i++) {
      o = clazz.newInstance();
      row = sheet.getRow(i);
      Cell cell = null;
      for (Field field : fields) {
        //根據(jù)注解中的title，取到表格中該列所對應(yīng)的的值
        Integer column = title_to_index.get(field.getAnnotation(ExcelIn.class).title());
        if (column == null) {
          continue;
        }
        cell = row.getCell(column);
        value = getCellValue(cell);
        if (null != value && StringUtils.isNotBlank(value.toString())) {
          beanUtilsBean.setProperty(o, field.getName(), value);
        }
      }
      rsList.add(o);
    }
    return rsList;
  }
  private void configSheet(InputStream in) throws Exception {
    // 根據(jù)文件類型來分別創(chuàng)建合適的Workbook對象
    try (Workbook wb = WorkbookFactory.create(in)) {
      getSheetByName(wb);
    } catch (FileNotFoundException e) {
      throw new Exception(e);
    } catch (IOException e) {
      throw new Exception(e);
    }
  }
  /**
   * 根據(jù)sheet獲取對應(yīng)的行列值，和表頭對應(yīng)的列值映射
   */
  private void configHeader() {
    this.firstRowNum = sheet.getFirstRowNum();
    this.lastRowNum = sheet.getLastRowNum();
    //第一行為表頭，拿到表頭對應(yīng)的列值
    Row row = sheet.getRow(firstRowNum);
    this.firstCellNum = row.getFirstCellNum();
    this.lastCellNum = row.getLastCellNum();
    for (int i = firstCellNum; i < lastCellNum; i++) {
      title_to_index.put(row.getCell(i).getStringCellValue(), i);
    }
  }
  /**
   * 根據(jù)sheet名稱獲取sheet
   *
   * @param workbook
   * @return
   * @throws Exception
   */
  private void getSheetByName(Workbook workbook) throws Exception { 
    int sheetNumber = workbook.getNumberOfSheets();
    for (int i = 0; i < sheetNumber; i++) {
      String name = workbook.getSheetName(i);
      if (StringUtils.equals(this.sheetName, name)) {
        this.sheet = workbook.getSheetAt(i);
        return;
      }
    }
    throw new Exception("excel中未找到名稱為" + this.sheetName + "的sheet");
  }
  /**
   * 根據(jù)自定義注解，獲取所要導(dǎo)入表格的sheet名稱和需要導(dǎo)入的字段名稱
   *
   * @param clazz
   * @throws Exception
   */
  private void gatherAnnotationFields(Class clazz) throws Exception {
    if (!clazz.isAnnotationPresent(ExcelIn.class)) {
      throw new Exception(clazz.getName() + "類上沒有ExcelIn注解");
    }
    ExcelIn excelIn = (ExcelIn) clazz.getAnnotation(ExcelIn.class);
    this.sheetName = excelIn.sheetName();
    // 得到所有定義字段
    Field[] allFields = FieldUtils.getAllFields(clazz);
    // 得到所有field并存放到一個(gè)list中
    for (Field field : allFields) {
      if (field.isAnnotationPresent(ExcelIn.class)) {
        fields.add(field);
      }
    }
    if (fields.isEmpty()) {
      throw new Exception(clazz.getName() + "中沒有ExcelIn注解字段");
    }
  }
  private Object getCellValue(Cell cell) {
    if (cell == null) {
      return "";
    }
    Object obj = null;
    switch (cell.getCellTypeEnum()) {
      case BOOLEAN:
        obj = cell.getBooleanCellValue();
        break;
      case ERROR:
        obj = cell.getErrorCellValue();
        break;
      case FORMULA:
        try {
          obj = String.valueOf(cell.getStringCellValue());
        } catch (IllegalStateException e) {
          obj = numericToBigDecimal(cell);
        }
        break;
      case NUMERIC:
        obj = getNumericValue(cell);
        break;
      case STRING:
        String value = String.valueOf(cell.getStringCellValue());
        value = value.replace(" ", "");
        value = value.replace("\n", "");
        value = value.replace("\t", "");
        obj = value;
        break;
      default:
        break;
    }
    return obj;
  }
  private Object getNumericValue(Cell cell) {
    // 處理日期格式、時(shí)間格式
    if (HSSFDateUtil.isCellDateFormatted(cell)) {
      return cell.getDateCellValue();
    } else if (cell.getCellStyle().getDataFormat() == 58) {
      // 處理自定義日期格式：m月d日(通過判斷單元格的格式id解決，id的值是58)
      double value = cell.getNumericCellValue();
      return org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
    } else {
      return numericToBigDecimal(cell);
    }
  }
  private Object numericToBigDecimal(Cell cell) {
    String valueOf = String.valueOf(cell.getNumericCellValue());
    BigDecimal bd = new BigDecimal(valueOf);
    return bd;
  }
}

3.ExcelIn注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
 * @author Lei
 * @time 2019/10/29
 * @Description
 */
@Retention(value = RetentionPolicy.RUNTIME)
@Target(value = {ElementType.TYPE, ElementType.FIELD})
public @interface ExcelIn {
  /**
   * 導(dǎo)入sheet名稱
   *
   * @return
   */
  String sheetName() default "";
  /**
   * 字段對應(yīng)的表頭名稱
   *
   * @return
   */
  String title() default "";
}

4.創(chuàng)建excel中的對象

import lombok.Data;
import lombok.ToString;
import java.util.Date;
/**
 * @author Lei
 * @time 2019/10/29
 * @Description
 */
@ToString
@Data
@ExcelIn(sheetName = "用戶")
public class User {
  private String id;
  @ExcelIn(title = "姓名")
  private String name;
  @ExcelIn(title = "年齡")
  private Integer age;
  @ExcelIn(title = "出生日期")
  private Date birthDate;
}

5.controller層接收

@PostMapping("/batch/excel")
  @ApiOperation(value = "根據(jù)excel文件批量導(dǎo)入")
  public ResponseVO batchAddDeviceByExcelImport(MultipartFile multipartFile) {
    return new ResponseVO(deviceService.addDeviceByExcelImport(multipartFile));
  }

6.service處理(此處僅打印)

public boolean addDeviceByExcelImport(MultipartFile multipartFile) {
    File file = null;
    try {
      file = File.createTempFile("temp", null);
    } catch (IOException e) {
      e.printStackTrace();
    }
    try {
      multipartFile.transferTo(file);
    } catch (IOException e) {
      e.printStackTrace();
    }
    file.deleteOnExit();
    InputStream inputStream = null;
    try {
      inputStream = new FileInputStream(file);
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    }
    ExcelImportUtil<User> reader = new ExcelImportUtil<>();
    List<User> userList = null;
    try {
      userList = reader.read(inputStream, User.class);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("51302", e.getMessage());
    }
      userList.stream().forEach(e -> log.info(e.toString()));
    return true;
  }

7.測試

（1）兩種文件類型的excel

（2）excel中格式如下，注意紅色箭頭所指的地方對應(yīng)user對象中的字段以及sheet名

（3）swagger測試

（4）成功打印

總結(jié)

以上所述是小編給大家介紹的java springboot poi 從controller 接收不同類型excel 文件處理,希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！
如果你覺得本文對你有幫助，歡迎轉(zhuǎn)載，煩請注明出處，謝謝！

您可能感興趣的文章: