easyPOI
基于注解导入导出excel表，基于excel模板导出，apache poi和easypoi版本冲突问题
<!--easypoi依赖-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>一、基于注解导入导出
测试代码
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import com.lyh.poi.vo.User;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
@Api(value = "基于注解导入导出",tags = "基于注解导入导出")
@RestController
@RequestMapping("excel/zhujie")
public class ExcelSimpleController {
/**
* 导出excel表
*/
@GetMapping(value = "/out", produces = "application/octet-stream")
@ApiOperationSupport(order = 1)
@ApiOperation(value = "导出用户表")
public void excel(HttpServletResponse response) {
List<User> userList = new ArrayList<>();
for (int i=1; i<=5; i++){
User user = new User();
user.setUser_id(i+"");
user.setPassword("123456"+i);
user.setUsername("李华"+i);
user.setPhone("1888633066"+i);
user.setEmail("19640599"+i+"@qq.com");
userList.add(user);
}
String title = "用户表";
ExportParams params = new ExportParams(title, "用户数据", ExcelType.HSSF);
Workbook workbook = ExcelExportUtil.exportExcel(params, User.class, userList);
ServletOutputStream out = null;
try {
response.setHeader("content-type", "application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(title + ".xls", "UTF-8"));
out = response.getOutputStream();
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != out) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
@ApiOperation("导入用户数据")
@ApiOperationSupport(order = 2)
@PostMapping({"/import"})
public String importExcel(@RequestParam MultipartFile file) {
ImportParams params = new ImportParams();
//去掉第一行
params.setTitleRows(1);
try {
List<User> list = ExcelImportUtil.importExcel(file.getInputStream(), User.class, params);
if (list != null && list.size() > 0) {
List<User> dataList = list.stream().map((userVo) -> {
User user = new User();
//属性对拷，可拷贝集合
BeanUtils.copyProperties(userVo,user);
return user;
}).collect(Collectors.toList());
//批量保存到数据库
boolean b = false; //TODO 修改批量保存到数据的接口
if (b) {
return "导入成功！";
}
}
} catch (Exception e) {
e.printStackTrace();
}
return "导入失败！";
}
}需要的实体类
public class User {
@Excel(name = "用户编码")
private String user_id;
@Excel(name = "密码", width = 20)
private String password;
@Excel(name = "用户姓名", width = 20)
private String username;
@Excel(name = "手机号码", width = 20)
private String phone;
@Excel(name = "电子邮箱", width = 20)
private String email;
}二、基于excel模板导出
模板 指令介绍:
- 空格分割
- 三目运算 {{test ? obj:obj2}}
- n: 表示 这个cell是数值类型 {{n:}}
- le: 代表长度{{le:()}} 在if/else 运用{{le:() > 8 ? obj1 : obj2}}
-fd: 格式化时间 {{fd:(obj;yyyy-MM-dd)}} - fn: 格式化数字 {{fn:(obj;###.00)}}
- fe: 遍历数据,创建row
- !fe: 遍历数据不创建row
- $fe: 下移插入,把当前行,下面的行全部下移.size()行,然后插入
- #fe: 横向遍历
- v_fe: 横向遍历值
- !if: 删除当前列 {{!if:(test)}}
- 单引号表示常量值 ‘’ 比如’1’ 那么输出的就是 1
- &NULL& 空格
- &INDEX& 表示循环中的序号,自动添加
- ]] 换行符 多行遍历导出
- sum： 统计数据
- cal: 基础的±X% 计算
- dict: 字典
- i18n: 国际化
- {{}}: 表达式里面的数据取值
- easypoi不会改变excel原有的样式，如果是遍历，easypoi会根据模板的那一行样式进行复制
1、excel文件模板：月报表.xlsx
2、导出效果 截图
3、对应代码
@GetMapping(value = "/out/report", produces = "application/octet-stream")
@ApiOperation(value = "月报表 导出excel表")
public void excelout(HttpServletResponse response) {
Map<String, Object> total = new HashMap<>();
List<Map<String, Object>> mapList = new ArrayList<>();
for (int i = 1; i <= 5; i++) {
Map<String, Object> map = new HashMap<>();
map.put("id", i + "");
map.put("enTimea", i+"月");
map.put("enTimeb", "本年截止0"+i+"月累计");
//数据中的第一列
map.put("enBenyue", "本月" + i);
map.put("enShangyue", "上月" + i);
map.put("enTongbi", "内容"+i);
map.put("leijiyongliang", "累计用量" + i);
map.put("tongbizengzhang", "同比增长" + i);
//数据中的第二列
map.put("enBenyue1", "本月" + i);
map.put("enShangyue1", "上月" + i);
map.put("enTongbi1", "内容"+i);
map.put("leijiyongliang1", "累计用量" + i);
map.put("tongbizengzhang1", "同比增长" + i);
mapList.add(map);
}
total.put("list", mapList);
String templateName = "月报表.xlsx";
TemplateExportParams params = new TemplateExportParams("template/" + templateName);
Workbook workbook = ExcelExportUtil.exportExcel(params, total);
ServletOutputStream out = null;
try {
response.setHeader("content-type", "application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(templateName, "UTF-8"));
out = response.getOutputStream();
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != out) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}三、以上测试项目
测试项目git地址：https://gitee.com/liyanhau/easypoi-lyh-test.git
启动测试项目(已集成swwage升级版knife4j)，访问地址：http://localhost:6270/doc.html
四、poi依赖问题
如果你的项目已经导入了apache的poi，使用下面依赖可解决冲突问题
<!--easypoi依赖====开始=====-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
<exclusion>
<groupId>org.slf4j</groupId>
<artifactId>*</artifactId>
</exclusion>
<exclusion>
<groupId>slf4j-log4j12</groupId>
<artifactId>*</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17-beta1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17-beta1</version>
</dependency>
<!--easypoi依赖====结束===-->
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
