导入,导出excel表格功能

引包

选择AutoPoi, jeecg开发的自动poi

<dependency>
   <groupId>org.jeecgframework</groupId>
    <artifactId>autopoi-web</artifactId>
    <version>1.3.2</version>
</dependency>

实体类

import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

/**
 * @Description: 教材(书)表
 * @Author: jeecg-boot
 * @Date: 2020-10-07
 * @Version: V1.0
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@JsonInclude(JsonInclude.Include.NON_NULL)
public class Textbook {

    /**
     * 教材主键
     */
    //@Excel(name = "教材主键", width = 15)
    private Integer textbooksId;
    /**
     * 教材名称
     */
    @Excel(name = "教材名称", width = 15)
    private String textbooksName;
    /**
     * 教材号
     */
    @Excel(name = "教材号", width = 15)
    private String textbooksNo;
    /**
     * 出版单位
     */
    @Excel(name = "出版单位", width = 15)
    private String publisher;
    /**
     * 内部书号
     */
    @Excel(name = "内部书号", width = 15)
    private String isbnNo;
    /**
     * 课程名称
     */
    @Excel(name = "课程名称", width = 15)
    private String courseTitle;
    /**
     * 专业名称
     */
    @Excel(name = "专业名称", width = 15)
    private String specialtyName;
    /**
     * 编者名
     */
    @Excel(name = "编者名", width = 15)
    private String editorName;
    /**
     * 版次
     */
    @Excel(name = "版次", width = 15)
    private String editionsBatch;
    /**
     * 货架号
     */
    @Excel(name = "货架号", width = 15)
    private String goodsNo;
    /**
     * 类型 0机密,1秘密,2内部,3公开
     */
    @Excel(name = "类型 0机密,1秘密,2内部,3公开", width = 15)
    private Integer textbooksType;
    /**
     * 库存量
     */
    @Excel(name = "库存量", width = 15)
    private Integer textbooksCount;
    /**
     * 出版时间
     */
    @Excel(name = "出版时间,规则yyyy-MM-dd HH:mm:ss", width = 20, format = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date publicationDate;

}

工具类

import com.baidu.entity.Textbook;
import org.jeecgframework.poi.excel.ExcelImportUtil;
import org.jeecgframework.poi.excel.def.NormalExcelConstants;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.jeecgframework.poi.excel.view.JeecgEntityExcelView;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.util.List;
import java.util.Map;

public class ExportXls {

    /**
     * <p>
     * 导出excel
     * </P>
     *
     * @param list  数据
     * @param clazz 类对象
     * @param title 标题
     * @author: 崔冬贤
     * @return: org.springframework.web.servlet.ModelAndView
     * @time: 2020/11/13
     */
    public static ModelAndView exportXls(List list, Class clazz, String title) {
        ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
        mv.addObject(NormalExcelConstants.FILE_NAME, title);
        mv.addObject(NormalExcelConstants.CLASS, clazz);
        //todo 可加上导出人
        mv.addObject(NormalExcelConstants.PARAMS, new ExportParams(title + "报表", "导出人:" + "admin", title));
        mv.addObject(NormalExcelConstants.DATA_LIST, list);
        return mv;
    }

    /**
     * <p>
     * 从请求里读取数据
     * </p>
     *
     * @param request 请求对象
     * @param clazz   类字节码对象
     * @return java.util.List<com.baidu.entity.Textbook>
     * @author 崔冬贤
     * @date 2021/8/2
     * @version V1.0
     */
    public static List<Textbook> getimportExcelTextbookList(HttpServletRequest request, Class clazz) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            // 获取上传文件对象
            MultipartFile file = entity.getValue();
            ImportParams params = new ImportParams();
            params.setTitleRows(2);
            params.setHeadRows(1);
            params.setNeedSave(true);
            try {
                return ExcelImportUtil.importExcel(file.getInputStream(), clazz, params);
            } catch (Exception e) {
            } finally {
                try {
                    file.getInputStream().close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

}

前段控制器接口

import com.baidu.entity.Textbook;
import com.baidu.util.ExportXls;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

/**
 * @version V1.0
 * @description:
 * @author: 崔冬贤
 * @date: 2021/7/20
 */
@RestController
@RequestMapping("/test")
public class TestController {

    @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    public Object importExcel(HttpServletRequest request, HttpServletResponse response) {
        List<Textbook> textbooks = ExportXls.getimportExcelTextbookList(request, Textbook.class);
        System.err.println(textbooks);
        return textbooks;
    }

    @RequestMapping(value = "/out", method = RequestMethod.GET)
    public Object out(HttpServletRequest request, HttpServletResponse response) {
        return ExportXls.exportXls(new ArrayList(),Textbook.class,"测试");
    }
}

PostMan测试

测试导入


测试导出

详解

根据@Excel 来自动匹配
官方文档: https://www.kancloud.cn/zhangdaiscott/autopoi/1623954