Java 使用easypoi读取excel文件的内容

一、引入pom依赖

     <!-- easy poi-->
        <dependency>
            <groupId>com.deepoove</groupId>
            <artifactId>poi-tl</artifactId>
            <version>1.8.1</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.3</version>
        </dependency>

二、导入实体

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;

/**
 * @author 
 * @description 设备转交导入接口 入参
 * @date 2022/12/9 11:21
 */
@Data
public class DeviceExcelDTO implements Serializable {
    private static final long serialVersionUID = 1L;

    @Excel(name = "资产编码", orderNum = "0")
    private String assetCode;
}

三、ExcelUtil

import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.cloud.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;

/**
 * Excel工具类
 */
public class ExcelUtil {
    /**
     * 导出
     *
     * @param list           数据列表
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      元素类型
     * @param fileName       文件名
     * @param isCreateHeader 是否创建列头
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 导出
     *
     * @param list      数据列表
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass 元素类型
     * @param fileName  文件名
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * 导出,适用于导出多个sheet的Excel,配合createOneSheet方法一起使用
     *
     * @param list     数据列表(元素是Map)
     * @param fileName 文件名
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    /**
     * 导入
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return Collections.emptyList();
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
        if (file == null) {
            return Collections.emptyList();
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    }

    /**
     * 功能描述：根据接收的Excel文件来导入多个sheet,根据索引可返回一个集合
     *
     * @param file       导入文件
     * @param sheetIndex 导入sheet索引,从0开始
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  Excel实体类
     */
    public static <T> List<T> importExcel(MultipartFile file, int sheetIndex, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
        if (file == null) {
            return Collections.emptyList();
        }
        // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
        ImportParams params = new ImportParams();
        // 第几个sheet页
        params.setStartSheetIndex(sheetIndex);
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    }

    /**
     * 功能描述：根据接收的Excel文件来导入多个sheet,根据索引可返回一个集合
     *
     * @param filePath   导入文件路径
     * @param sheetIndex 导入sheet索引
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  Excel实体类
     */
    public static <T> List<T> importExcel(String filePath, int sheetIndex, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        ImportParams params = new ImportParams();
        params.setStartSheetIndex(sheetIndex);
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    }

    /**
     * 用于导出多sheet的Excel文件
     *
     * @param sheetName 自定义sheetName
     * @param clazz     pojo实体类
     * @param data      List<Map<String, Object>> or List<POJO>
     * @return map
     */
    public static Map<String, Object> createOneSheet(String sheetName, Class<?> clazz, List<?> data) {
        ExportParams params = new ExportParams("", sheetName, ExcelType.XSSF);
        return createOneSheet(params, clazz, data);
    }

    /**
     * 创建一个表格并填充内容,返回map供工作簿使用,map的key必须写死
     *
     * @param params 导出配置
     * @param clazz  带@Excel注解字段的POJO实体类
     * @param data   List<Map<String, Object>> or List<POJO>
     * @return map
     */
    private static Map<String, Object> createOneSheet(ExportParams params, Class<?> clazz, List<?> data) {
        Map<String, Object> map = new HashMap<>(8);
        map.put("title", params);
        map.put("entity", clazz);
        map.put("data", data);
        return map;
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    }
}

四、Controller

  @PostMapping("/import/device-list")
    public Response<ImportDeviceVO> importDeviceList(@RequestParam("file") MultipartFile file,
                                                     @NotNull @RequestParam String dealerCode,
                                                     @NotNull @RequestParam String applyOrderType) {
        if (file == null || file.isEmpty()) {
            throw new BizServiceException(ErrorCodeEnums.EMPTY_FILE);
        }
        List<DeviceExcelDTO> deviceExcelDTOList = new ArrayList<>();
        try {
            deviceExcelDTOList = ExcelUtil.importExcel(file, 0, 0, 1, DeviceExcelDTO.class);
        } catch (Exception e) {
            throw new BizServiceException(ErrorCodeEnums.IMPORT_DEVICE_FAIL);
        }
        List<String> assetCodeList =
                deviceExcelDTOList.stream().map(DeviceExcelDTO::getAssetCode).filter(e -> StringUtils.isNotBlank(e)).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(assetCodeList)) {
            throw new BizServiceException(ErrorCodeEnums.EMPTY_FILE);
        }
        return Response.ok(dealerService.importDeviceList(assetCodeList,
                ImportDeviceDTO.builder().applyOrderType(applyOrderType).dealerCode(dealerCode).build()));
    }

五、导入的excel