基于springmvc的easypoi简单使用

最新推荐文章于 2024-08-20 10:14:19 发布

rockysx

最新推荐文章于 2024-08-20 10:14:19 发布

阅读量555

点赞数

分类专栏： java

java 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

基于springmvc的easypoi简单使用

最近要做一些excel导入导出的工作，就花时间研究了一下，官方文档实在是有点不足，在这里分享一下研究成果

项目框架，SSM框架，基础框架是直接从mybatis-plus项目上 clone下来的
使用eclipse开发
官方资料

http://git.oschina.net/jueyue/easypoi

maven基本依赖

  <dependency>
     <groupId>org.jeecg</groupId>
     <artifactId>easypoi-base</artifactId>
     <version>2.3.1</version>
 </dependency>
 <dependency>
     <groupId>org.jeecg</groupId>
     <artifactId>easypoi-web</artifactId>
     <version>2.3.1</version>
 </dependency>
 <dependency>
     <groupId>org.jeecg</groupId>
     <artifactId>easypoi-annotation</artifactId>
     <version>2.3.1</version>
 </dependency>

springmvc的配置
默认视图级别设置低点

<!-- 默认的视图解析器 在上边的解析错误时使用 (默认使用html)- -->
<bean id="defaultViewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver"
p:order="3">
<property name="viewClass"
value="org.springframework.web.servlet.view.JstlView" />
<property name="contentType" value="text/html" />
<property name="prefix" value="/webpage/" />
<property name="suffix" value=".jsp" />
</bean>

Bean视图设置级别高一些,然后把我们的4个试图配置上,就完成了
<!-- Bean解析器,级别高于默认解析器,寻找bean对象进行二次处理 -->
<bean id="beanNameViewResolver"
class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0">
</bean>
<!-- Excel 处理 根据用户输入进行对象处理 -->
<bean id="jeecgExcelView" class="org.jeecgframework.poi.excel.view.JeecgSingleExcelView" />
<bean id="jeecgTemplateExcelView" class="org.jeecgframework.poi.excel.view.JeecgTemplateExcelView" />
<bean id="jeecgTemplateWordView" class="org.jeecgframework.poi.excel.view.JeecgTemplateWordView" />
<bean id="jeecgMapExcelView" class="org.jeecgframework.poi.excel.view.JeecgMapExcelView" />

2.0.8版本后加上了@Controller 里面只要在
<context:component-scan base-package="org.jeecgframework.poi.excel.view">
加入就可以了

完成了这些配置以后就可以开始开发了

导出

1. Map方式导出

ExcelExportEntity的构造函数是ExcelExportEntity(String name, Object key, int width)
指定了column名和property名，用于生成表头，但是不知道这个width有什么作用。
然后设置了文件名，title，second title，sheet名等

@RequestMapping("/MapExportExcel")
    public String exportMerchantProfitQuery(ModelMap modelMap, HttpServletRequest request) {
        List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
        entityList.add(new ExcelExportEntity("用户ID", "id", 35));
        entityList.add(new ExcelExportEntity("用户名", "name", 15));
        entityList.add(new ExcelExportEntity("用户年龄", "age", 15));
        List<Map<String, String>> dataResult = getData();

        modelMap.put(MapExcelConstants.ENTITY_LIST, entityList);
        modelMap.put(MapExcelConstants.MAP_LIST, dataResult);
        modelMap.put(MapExcelConstants.FILE_NAME, "商户利润");
        Date now = new Date();
        modelMap.put(NormalExcelConstants.PARAMS, new ExportParams("商户利润详情", "创建时间" + now.toLocaleString(), "商户"));
        return MapExcelConstants.JEECG_MAP_EXCEL_VIEW;
    }

private List<Map<String, String>> getData() {
        List<Map<String, String>> dataResult = new ArrayList<Map<String, String>>();
        Map<String, String> u1 = new LinkedHashMap<String, String>();
        u1.put("id", "1");
        u1.put("name", "cyf");
        u1.put("age", "21");
        Map<String, String> u2 = new LinkedHashMap<String, String>();
        u2.put("id", "2");
        u2.put("name", "cy");
        u2.put("age", "22");
        dataResult.add(u1);
        dataResult.add(u2);
        return dataResult;
    }

这里写图片描述

2.注解导出

注解导出需要在实体类上先加上注解
这里先简要介绍
这里写图片描述

两个实体类

package com.baomidou.springmvc.model.system;

import java.io.Serializable;
import java.util.Date;
import org.jeecgframework.poi.excel.annotation.Excel;

public class Product implements Serializable{
    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    private int id;
    @Excel(name = "商品名", needMerge = true)
    private String name;
    @Excel(name = "价格", needMerge = true)
    private double price;
    @Excel(name = "购买时间", exportFormat="yyyy/mm/dd", needMerge = true)
    private Date time;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public double getPrice() {
        return price;
    }
    public void setPrice(double price) {
        this.price = price;
    }
    public Date getTime() {
        return time;
    }
    public void setTime(Date time) {
        this.time = time;
    }
}

package com.baomidou.springmvc.model.system;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecgframework.poi.excel.annotation.ExcelCollection;
import org.jeecgframework.poi.excel.annotation.ExcelEntity;
import org.jeecgframework.poi.excel.annotation.ExcelTarget;

import com.baomidou.mybatisplus.annotations.TableName;

/**
 *
 * 系统用户表
 *
 */
@ExcelTarget("User")
@TableName("sys_user")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    /** 用户ID */
    @Excel(name = "用户id" , needMerge = true)
    private Long id;

    /** 用户名 */
    @Excel(name = "用户名", needMerge = true)
    private String name;


    /** 用户年龄 */
    @Excel(name = "年龄", needMerge = true)
    private Integer age;

    @ExcelEntity(name = "商品")
    private Product product;

    /**购买的商品*/
    @ExcelCollection(name = "商品序列")
    private List<Product> products; 

    /**创建时间*/
    @Excel(name = "创建时间" ,exportFormat="yyyy-mm-dd" , needMerge = true )
    private Date time;

    /**性别*/
    @Excel(name="性别" , replace={"男_1","女_0"}, needMerge = true)
    private int sex;



    public List<Product> getProducts() {
        return products;
    }

    public void setProducts(List<Product> products) {
        this.products = products;
    }

    public Product getProduct() {
        return product;
    }

    public void setProduct(Product product) {
        this.product = product;
    }



    public int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public Date getTime() {
        return time;
    }

    public void setTime(Date time) {
        this.time = time;
    }



    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return this.age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

}

controller 这里需要注意的一点是
setExclusions 写的是实际列名，而不是属性名

@RequestMapping("/excelAnno")
    public String excelAnno(ModelMap map1) {
        List<User> list = getUsers();
        map1.put(NormalExcelConstants.CLASS, User.class);
        map1.put(NormalExcelConstants.FILE_NAME, "用户导出测试");
        ExportParams ep = new ExportParams("历史总包滚存分析1", "历史总包滚存分析2");
        ep.setExclusions(new String[] { "年龄" });// 这里填替换后的
        map1.put(NormalExcelConstants.PARAMS, ep);
        map1.put(NormalExcelConstants.DATA_LIST, list);
        return NormalExcelConstants.JEECG_EXCEL_VIEW;
    }

private List<User> getUsers() {
        Product p1 = new Product();
        Product p2 = new Product();
        p1.setId(1);
        p1.setName("apple");
        p1.setPrice(10);
        p1.setTime(new Date());

        p2.setId(2);
        p2.setName("pear");
        p2.setPrice(30);
        p2.setTime(new Date());

        User u1 = new User();
        u1.setAge(21);
        u1.setId(Long.parseLong("1"));
        u1.setName("cyf");
        u1.setProduct(p1);
        u1.setSex(1);
        List<Product> products = new ArrayList<Product>();
        products.add(p2);
        products.add(p1);
        u1.setProducts(products);
        u1.setTime(new Date());

        User u2 = new User();
        u2.setAge(23);
        u2.setId(Long.parseLong("2"));
        u2.setName("cy");
        u2.setProduct(p2);
        u2.setSex(1);
        u2.setProducts(products);
        u2.setTime(new Date());

        List<User> users = new ArrayList<User>();
        users.add(u1);
        users.add(u2);

        return users;
    }

导出结果
这里写图片描述

3.多sheet导出

@RequestMapping("/multiplyexcelAnno")
    public void multiplyexcelAnno(HttpServletRequest req, HttpServletResponse resp) throws UnsupportedEncodingException {
        Map<String, Object> map1 = getTestMap();
        Map<String, Object> map2 = getTestMap();
        List<Map<String,Object>> list1 = new ArrayList<Map<String,Object>>();
        list1.add(map1);
        list1.add(map2);
        Workbook workbook = exportExcel(list1, ExcelType.HSSF);
        req.setCharacterEncoding("UTF-8");
        resp.setCharacterEncoding("UTF-8");
        resp.setContentType("application/x-download");
        String filedisplay = "product.xls";
        filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
        resp.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);

        try {
            OutputStream out = resp.getOutputStream();
            workbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

private Map<String, Object> getTestMap() {
        Map<String,Object> map1  = new LinkedHashMap<String,Object>();
        List<User> list = getUsers();
        map1.put(NormalExcelConstants.CLASS, User.class);
        map1.put(NormalExcelConstants.FILE_NAME, "用户导出测试");
        ExportParams ep = new ExportParams("历史总包滚存分析1", "111"+(1000*Math.random()));
        ep.setExclusions(new String[] { "年龄" });// 这里填替换后的
        map1.put(NormalExcelConstants.PARAMS, ep);
        map1.put(NormalExcelConstants.DATA_LIST, list);
        return map1;
    }

    public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {
        Workbook workbook;
        if (ExcelType.HSSF.equals(type)) {
            workbook = new HSSFWorkbook();
        } else {
            workbook = new XSSFWorkbook();
        }
        for (Map<String, Object> map : list) {
            ExcelExportServer server = new ExcelExportServer();
            ExportParams params = (ExportParams) map.get("params");
            Class<?> entry = (Class<?>) map.get("entity");
            Collection<?> data = (Collection<?>) map.get("data");
            server.createSheet(workbook, params,entry ,data);
        }
        return workbook;
    }

导出结果
这里写图片描述

导入

导入的时候也是利用了注解，基本上就是导出的反操作
需要说明的是目前官方的导入集合还没修复，好在我在pull request里找到了解决的方法，封装了拿来用，果然可以

@RequestMapping(value = "/import", method = RequestMethod.POST)
    @ResponseBody
    public void importExcel(MultipartFile  file,HttpServletRequest request) {
        try {

            ImportParams params = new ImportParams();
            params.setTitleRows(1);
            params.setHeadRows(2);
            params.setNeedSave(true);
            String path = request.getSession().getServletContext().getRealPath("");
            File f = new File(path+"/excel/"+file.getOriginalFilename());
            if(!f.exists()){
                try {
                    File dir = new File(path+"/excel/");
                    dir.mkdirs();
                    if(f.createNewFile()){
                        System.out.println("创建文件成功");
                    }else{
                        System.out.println("创建文件失败");
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            file.transferTo(f);
            List<User> list = WrapperUtil.warpedImportExcel( f, User.class, params);
            System.out.println(JSON.toJSONString(list));
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

前端页面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<c:set var="ctx" value="${pageContext.request.contextPath}"/>
<html>
<head>
    <title>用户列表</title>
</head>
<body>
 <input type="button" value="Map导出" onclick="download1()"/>
 <input type="button" value="anno导出" onclick="download2()"/>
  <input type="button" value="mulanno导出" onclick="download3()"/>

  <form action="import" method="POST" enctype="multipart/form-data">  
    <input type="file" name="file"/>  
    <input type="submit" value="上传" />  
  </form>
</body>
<script type="text/javascript">
function download1(){
    window.open('/mybatisplus-spring-mvc/MapExportExcel');
}
function download2(){
    window.open('/mybatisplus-spring-mvc/excelAnno');
}
function download3(){
    window.open('/mybatisplus-spring-mvc/multiplyexcelAnno');
}


</script>
</html>

--------------------- 本文来自 m0_37149617 的CSDN 博客，全文地址请点击：https://blog.csdn.net/m0_37149617/article/details/53870099?utm_source=copy