基于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)- -->
<beanid="defaultViewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver"
p:order="3">
<propertyname="viewClass"
value="org.springframework.web.servlet.view.JstlView" />
<propertyname="contentType"value="text/html" />
<propertyname="prefix"value="/webpage/" />
<propertyname="suffix"value=".jsp" />
</bean>
Bean视图设置级别高一些,然后把我们的4个试图配置上,就完成了
<!-- Bean解析器,级别高于默认解析器,寻找bean对象进行二次处理 -->
<beanid="beanNameViewResolver"
class="org.springframework.web.servlet.view.BeanNameViewResolver"p:order="0">
</bean>
<!-- Excel 处理根据用户输入进行对象处理 -->
<beanid="jeecgExcelView"class="org.jeecgframework.poi.excel.view.JeecgSingleExcelView" />
<beanid="jeecgTemplateExcelView"class="org.jeecgframework.poi.excel.view.JeecgTemplateExcelView" />
<beanid="jeecgTemplateWordView"class="org.jeecgframework.poi.excel.view.JeecgTemplateWordView" />
<beanid="jeecgMapExcelView"class="org.jeecgframework.poi.excel.view.JeecgMapExcelView" />
2.0.8版本后加上了@Controller 里面只要在
<context:component-scanbase-package="org.jeecgframework.poi.excel.view">
加入就可以了
完成了这些配置以后就可以开始开发了
导出
1. Map方式导出
ExcelExportEntity的构造函数是ExcelExportEntity(String name, Objectkey, 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(), "商户"));
returnMapExcelConstants.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;
publicclassProductimplementsSerializable{
/**
*
*/
privatestaticfinallong serialVersionUID = 1L;
privateint id;
@Excel(name = "商品名", needMerge = true)
private String name;
@Excel(name = "价格", needMerge = true)
privatedouble price;
@Excel(name = "购买时间", exportFormat="yyyy/mm/dd", needMerge = true)
private Date time;
publicintgetId() {
return id;
}
publicvoidsetId(int id) {
this.id = id;
}
public String getName() {
return name;
}
publicvoidsetName(String name) {
this.name = name;
}
publicdoublegetPrice() {
return price;
}
publicvoidsetPrice(double price) {
this.price = price;
}
public Date getTime() {
return time;
}
publicvoidsetTime(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")
publicclassUserimplementsSerializable {
privatestaticfinallong 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)
privateint sex;
public List<Product> getProducts() {
return products;
}
publicvoidsetProducts(List<Product> products) {
this.products = products;
}
public Product getProduct() {
return product;
}
publicvoidsetProduct(Product product) {
this.product = product;
}
publicintgetSex() {
return sex;
}
publicvoidsetSex(int sex) {
this.sex = sex;
}
public Date getTime() {
return time;
}
publicvoidsetTime(Date time) {
this.time = time;
}
public Long getId() {
returnthis.id;
}
publicvoidsetId(Long id) {
this.id = id;
}
public String getName() {
returnthis.name;
}
publicvoidsetName(String name) {
this.name = name;
}
public Integer getAge() {
returnthis.age;
}
publicvoidsetAge(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);
returnNormalExcelConstants.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")
publicvoidmultiplyexcelAnno(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;
}
publicstatic 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
publicvoidimportExcel(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();
}
}
前端页面
<%@ pagecontentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<c:setvar="ctx"value="${pageContext.request.contextPath}"/>
<html>
<head>
<title>用户列表</title>
</head>
<body>
<inputtype="button"value="Map导出"onclick="download1()"/>
<inputtype="button"value="anno导出"onclick="download2()"/>
<inputtype="button"value="mulanno导出"onclick="download3()"/>
<formaction="import"method="POST"enctype="multipart/form-data">
<inputtype="file"name="file"/>
<inputtype="submit"value="上传" />
</form>
</body>
<scripttype="text/javascript">
functiondownload1(){
window.open('/mybatisplus-spring-mvc/MapExportExcel');
}
functiondownload2(){
window.open('/mybatisplus-spring-mvc/excelAnno');
}
functiondownload3(){
window.open('/mybatisplus-spring-mvc/multiplyexcelAnno');
}
</script>
</html>