基于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>

转自http://blog.csdn.net/m0_37149617/article/details/53870099