java easypoi导入excel忽略空白数据

转载

mob64ca14017c37 2024-09-23 19:33:55

文章标签 SpringBoot easypoi excel List 实体类 文章分类 Java 后端开发

1、增加依赖

<!-- excel easypoi -->
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-base</artifactId>
	<version>3.0.3</version>
</dependency>
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-web</artifactId>
	<version>3.0.3</version>
</dependency>
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-annotation</artifactId>
	<version>3.0.3</version>
</dependency>

2、实现一个工具类

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 org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.NoSuchElementException;

public class ExcelUtil {

    /**
     * 导出Excel
     * @param list 需要导出的数据列表
     * @param title 标题
     * @param sheetName sheet名称
     * @param pojoClass 实体类
     * @param fileName 导出文件名称
     * @param isCreateHeader 是否创建表头
     * @param response
     * @throws IOException
     */
    public static <T> void exportExcel(List<T> list, String title, String sheetName, Class<T> 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);
    }

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

    /**
     * 导出Excel
     * @param list 需要导出的数据列表
     * @param pojoClass 实体类
     * @param fileName 导出文件名称
     * @param exportParams 导出参数
     * @param response 返回请求
     * @throws IOException
     */
    public static <T> void exportExcel(List<T> list, Class<T> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 默认导出方法
     * @param list 需要导出的数据列表
     * @param pojoClass 实体类
     * @param fileName 导出文件名称
     * @param response 返回请求
     * @param exportParams 导出参数
     * @throws IOException
     */
    private static <T> void defaultExport(List<T> list, Class<T> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载Excel文件
     * @param fileName 文件名
     * @param response
     * @param workbook Excel数据
     * @throws IOException
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * 导入Excel
     * @param filePath 文件路径
     * @param titleRows 标题行数
     * @param headerRows 表头行数
     * @param pojoClass 实体类
     * @throws IOException
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * 导入Excel
     * @param file 文件
     * @param pojoClass 实体类
     * @throws IOException
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return importExcel(file, 1, 1, pojoClass);
    }

    /**
     * 导入Excel
     * @param file 文件
     * @param titleRows 标题行数
     * @param headerRows 表头行数
     * @param pojoClass 实体类
     * @throws IOException
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        return importExcel(file, titleRows, headerRows, false, pojoClass);
    }

    /**
     * 导入Excel
     * @param file 文件
     * @param titleRows 标题行数
     * @param headerRows 表头行数
     * @param needVerfiy 是否需要校验
     * @param pojoClass 实体类
     * @throws IOException
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * 导入Excel
     * @param inputStream 输入流
     * @param titleRows 标题行数
     * @param headerRows 表头行数
     * @param needVerfiy 是否需要校验
     * @param pojoClass 实体类
     * @throws IOException
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl("/excel/");
        params.setNeedSave(true);
        params.setNeedVerfiy(needVerfiy);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel类型的枚举类
     */
    public enum ExcelTypeEnum {
        XLSX("xlsx"),
        XLS("xls");


        private final String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }
    }
}

3、测试

新增一个实体类

这里主要用到了@Excel注解，其中name对应Excel上的列名，orderNum表示展示顺序，里面还有很多有用的属性，大家可以查看一下源码，根据自己的需求来设置。

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import javax.validation.constraints.Min;
import javax.validation.constraints.NotBlank;
import java.util.Date;

/**
 * @ClassName UserEntity
 * @Description 用户实体类
 * @Author weiyanqiang
 * @Date 2019/6/23 22:28
 * @Version 1.0
 */
@Data
public class UserEntity  {

    private int id;

    @Excel(name = "姓名", orderNum = "0")
    @NotBlank(message = "the name can't be blank!")
    private String name;

    @Excel(name = "年龄", orderNum = "1")
    @Min(value = 18, message = "the age can't be younger than {value}!")
    private int age;

    /**
     * 解决json时间格式化的问题
     */
    @Excel(name = "出生日期", orderNum = "2", format = "yyyy-MM-dd HH:mm:ss", width = 20.5)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date birthDay;

    /**
     * replace表示当married为false时，导出“否”，true时，导出“是”，导入相反
     */
    @Excel(name = "已婚", orderNum = "3", replace = {"否_false","是_true"})
    private boolean married;

    @Excel(name = "年薪", orderNum = "4")
    private float salary;

}

创建一个controller类和service类

/**
 * @ClassName UserController
 * @Description 用户控制类
 * @Author weiyanqiang
 * @Date 2019/6/25 21:41
 * @Version 1.0
 */
@Slf4j
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/export")
    public ServerResponse<String> exportExcel(HttpServletResponse response) {
        userService.exportUsers(response);
        return ServerResponse.createBySuccessMessage("导出成功！");
    }

    @PostMapping("/import")
    public ServerResponse<String> importExcel(@RequestParam("file") MultipartFile file) {
        userService.importUsers(file);
        return ServerResponse.createBySuccessMessage("导入成功！");
    }

}

@Override
    public void exportUsers(HttpServletResponse response){
        try {
            // 从数据查询所有的用户信息
            List<UserEntity> list = queryAll();
            ExcelUtil.exportExcel(list, "人员信息", "user", UserEntity.class, "users", true, response);
        } catch (IOException e) {
            log.error("导出人员信息出错", e);
            throw new RuntimeException(e);
        }
    }

    @Override
    public void importUsers(MultipartFile file) {
        try {
            List<UserEntity> list = ExcelUtil.importExcel(file, 1, 1, UserEntity.class);
            // 把用户信息列表插入到数据库
            insertList(list);
        } catch (IOException e) {
            log.error("导入人员信息出错", e);
            throw new RuntimeException(e);
        }
    }