基于springmvc的easypoi简单使用
最近要做一些excel导入导出的工作，就花时间研究了一下，官方文档实在是有点不足，在这里分享一下研究成果
项目框架，SSM框架，基础框架是直接从mybatis-plus项目上 clone下来的
使用eclipse开发
官方资料
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>转自：https://blog.csdn.net/m0_37149617/article/details/53870099
452
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
