基于springmvc的easypoi简单使用

﹏ヽ暗。殇╰゛Y 2022-05-09 07:56 280阅读 0赞

# 基于springmvc的easypoi简单使用 #

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

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

> [http://git.oschina.net/jueyue/easypoi][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的配置  
默认视图级别设置低点

<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 id="beanNameViewResolver"
    class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0">
    </bean>
    
    <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;
        }

![这里写图片描述][SouthEast]

### 2.注解导出 ###

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

两个实体类

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 写的是实际列名，而不是属性名

导出结果  
![这里写图片描述][SouthEast 2]

### 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;
        }

导出结果  
![这里写图片描述][SouthEast 3]

## 导入 ##

导入的时候也是利用了注解，基本上就是导出的反操作  
需要说明的是目前官方的导入集合还没修复，好在我在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

[http_git.oschina.net_jueyue_easypoi]: http://git.oschina.net/jueyue/easypoi
[SouthEast]: /images/20220509/95d6870fc7074f9690c9ae7699fbc055.png
[SouthEast 1]: /images/20220509/62fb8714ae954e88bdab7b4d2de746e0.png
[SouthEast 2]: /images/20220509/a357cf6c3c8543a5b781d6a03bdc7794.png
[SouthEast 3]: /images/20220509/2c49f1889ffb4c07bd743506051511e8.png