Excel表导出easypoi
导出Excel
1.依赖包
<!-- easypoi -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<!--或者选择boot集成-->
<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-spring-boot-starter -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.4.0</version>
</dependency>
<!-- 其他方式,不介绍 -->
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.2</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
1可能出现的坑,含模版坑(maven版本冲突,自用4.0.0)
解决后
/*自用版本*/
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
/*排除*/
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beat1</version>
<exclusions>
<exclusion>
<artifactId>poi-ooxml</artifactId>
<groupId>org.apache.poi</groupId>
</exclusion>
<exclusion>
<artifactId>poi</artifactId>
<groupId>org.apache.poi</groupId>
</exclusion>
</exclusions>
</dependency>
/*排除*/
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
<exclusions>
<exclusion>
<artifactId>org.apache.poi</artifactId>
<groupId>poi</groupId>
</exclusion>
<exclusion>
<artifactId>poi</artifactId>
<groupId>org.apache.poi</groupId>
</exclusion>
<exclusion>
<artifactId>poi-ooxml</artifactId>
<groupId>org.apache.poi</groupId>
</exclusion>
<exclusion>
<artifactId>poi-ooxml-schemas</artifactId>
<groupId>org.apache.poi</groupId>
</exclusion>
</exclusions>
</dependency>
/*自用公司jar包冲突*/
/*排除*/
<dependency>
<groupId>com.meditrusthealth</groupId>
<artifactId>fast-common-core</artifactId>
<exclusions>
<exclusion>
<artifactId>poi</artifactId>
<groupId>org.apache.poi</groupId>
</exclusion>
</exclusions>
</dependency>
2.controller
/*
* 要用get请求
* 导出
* */
@GetMapping("export/card/list")
@ApiOperation("导出")
void exportUserManage(@ApiIgnore @RequestParam("ids") Long[] ids, @ApiIgnore HttpServletResponse response) throws Exception{
LambdaQueryWrapper<EquityCardBatchnoDetail> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.in(EquityCardBatchnoDetail::getBatchNo,ids)
// .eq(EquityCardBatchno::getDelFlag,false)
.orderByDesc(EquityCardBatchnoDetail::getCreateTime)
;
List<EquityCardBatchnoDetail> list = equityCardBatchnoDetailMapper.selectList(lambdaQueryWrapper);
for (EquityCardBatchnoDetail detail : list) {
if ("01".equals(detail.getActivationStatus())){
detail.setActivationStatus("未激活");
}
if ("02".equals(detail.getActivationStatus())){
detail.setActivationStatus("已激活");
}
}
ExcelUtils.exportExcelToTarget(response, null, list, EquityCardExcel.class);
}
3.工具类
excel
package com.meditrusthealth.mth.equity.service.util;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
/**
* excel工具类
*
* @author tecsmile@outlook.com
*/
public class ExcelUtils {
/**
* Excel导出
*
* @param response response
* @param fileName 文件名
* @param list 数据List
* @param pojoClass 对象Class
*/
public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
Class<?> pojoClass) throws IOException {
if(StringUtils.isBlank(fileName)){
//当前日期
fileName = DateUtils.format(new Date());
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), pojoClass, list);
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
}
/**
* Excel导出,先sourceList转换成List<targetClass>,再导出
*
* @param response response
* @param fileName 文件名
* @param sourceList 原数据List
* @param targetClass 目标对象Class
*/
public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection<?> sourceList,
Class<?> targetClass) throws Exception {
List targetList = new ArrayList<>(sourceList.size());
for(Object source : sourceList){
Object target = targetClass.newInstance();
BeanUtils.copyProperties(source, target);
targetList.add(target);
}
exportExcel(response, fileName, targetList, targetClass);
}
}
时间
package com.meditrusthealth.mth.equity.service.util;
import org.apache.commons.lang3.StringUtils;
import org.joda.time.DateTime;
import org.joda.time.LocalDate;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* 日期处理
*
* @author tecsmile@outlook.com
*/
public class DateUtils {
/** 时间格式(yyyy-MM-dd) */
public final static String DATE_PATTERN = "yyyy-MM-dd";
/** 时间格式(yyyy-MM-dd HH:mm:ss) */
public final static String DATE_TIME_PATTERN = "yyyy-MM-dd HH:mm:ss";
/**
* 日期格式化 日期格式为:yyyy-MM-dd
* @param date 日期
* @return 返回yyyy-MM-dd格式日期
*/
public static String format(Date date) {
return format(date, DATE_PATTERN);
}
/**
* 日期格式化 日期格式为:yyyy-MM-dd
* @param date 日期
* @param pattern 格式,如:DateUtils.DATE_TIME_PATTERN
* @return 返回yyyy-MM-dd格式日期
*/
public static String format(Date date, String pattern) {
if(date != null){
SimpleDateFormat df = new SimpleDateFormat(pattern);
return df.format(date);
}
return null;
}
/**
* 日期解析
* @param date 日期
* @param pattern 格式,如:DateUtils.DATE_TIME_PATTERN
* @return 返回Date
*/
public static Date parse(String date, String pattern) {
try {
return new SimpleDateFormat(pattern).parse(date);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
/**
* 字符串转换成日期
* @param strDate 日期字符串
* @param pattern 日期的格式,如:DateUtils.DATE_TIME_PATTERN
*/
public static Date stringToDate(String strDate, String pattern) {
if (StringUtils.isBlank(strDate)){
return null;
}
DateTimeFormatter fmt = DateTimeFormat.forPattern(pattern);
return fmt.parseLocalDateTime(strDate).toDate();
}
/**
* 根据周数,获取开始日期、结束日期
* @param week 周期 0本周,-1上周,-2上上周,1下周,2下下周
* @return 返回date[0]开始日期、date[1]结束日期
*/
public static Date[] getWeekStartAndEnd(int week) {
DateTime dateTime = new DateTime();
LocalDate date = new LocalDate(dateTime.plusWeeks(week));
date = date.dayOfWeek().withMinimumValue();
Date beginDate = date.toDate();
Date endDate = date.plusDays(6).toDate();
return new Date[]{beginDate, endDate};
}
/**
* 对日期的【秒】进行加/减
*
* @param date 日期
* @param seconds 秒数,负数为减
* @return 加/减几秒后的日期
*/
public static Date addDateSeconds(Date date, int seconds) {
DateTime dateTime = new DateTime(date);
return dateTime.plusSeconds(seconds).toDate();
}
/**
* 对日期的【分钟】进行加/减
*
* @param date 日期
* @param minutes 分钟数,负数为减
* @return 加/减几分钟后的日期
*/
public static Date addDateMinutes(Date date, int minutes) {
DateTime dateTime = new DateTime(date);
return dateTime.plusMinutes(minutes).toDate();
}
/**
* 对日期的【小时】进行加/减
*
* @param date 日期
* @param hours 小时数,负数为减
* @return 加/减几小时后的日期
*/
public static Date addDateHours(Date date, int hours) {
DateTime dateTime = new DateTime(date);
return dateTime.plusHours(hours).toDate();
}
/**
* 对日期的【天】进行加/减
*
* @param date 日期
* @param days 天数,负数为减
* @return 加/减几天后的日期
*/
public static Date addDateDays(Date date, int days) {
DateTime dateTime = new DateTime(date);
return dateTime.plusDays(days).toDate();
}
/**
* 对日期的【周】进行加/减
*
* @param date 日期
* @param weeks 周数,负数为减
* @return 加/减几周后的日期
*/
public static Date addDateWeeks(Date date, int weeks) {
DateTime dateTime = new DateTime(date);
return dateTime.plusWeeks(weeks).toDate();
}
/**
* 对日期的【月】进行加/减
*
* @param date 日期
* @param months 月数,负数为减
* @return 加/减几月后的日期
*/
public static Date addDateMonths(Date date, int months) {
DateTime dateTime = new DateTime(date);
return dateTime.plusMonths(months).toDate();
}
/**
* 对日期的【年】进行加/减
*
* @param date 日期
* @param years 年数,负数为减
* @return 加/减几年后的日期
*/
public static Date addDateYears(Date date, int years) {
DateTime dateTime = new DateTime(date);
return dateTime.plusYears(years).toDate();
}
}
4.实体类
@Data
public class EquityCardExcel {
@Excel(name = "卡号")
private String cardNo;
@Excel(name = "激活码")
private String activationCode;
@Excel(name = "激活状态")
private String activationStatus;
@Excel(name = "包编码")
private String pkgCode;
@Excel(name = "包名称")
private String pkgName;
}
进阶注解
//多种注解使用,needMerge 合并单元格;groupName 双行表头;orderNum 排序规则
//replace 替换值,isImportField true为校验;suffix = "生" ,拼接值
@Excel(name = "产品名称", needMerge = true, groupName = "二级", orderNum = "1")
private String r1;
@Excel(name = "申请时间", groupName = "二级", orderNum = "2")
private String r2;
@Excel(name = "被保险人姓名", groupName = "二级", orderNum = "3")
private String r3;
@Excel(name = "客户类型", groupName = "二级", orderNum = "4")
@Excel(name = "激活状态",replace = { "未激活_01", "激活_02" }, isImportField = "true")
private String activationStatus;
Excel模板导出
1.模板导入(表达式写正确)
表达式
空格分割
三目运算 {{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& 空格
]] 换行符 多行遍历导出
sum: 统计数据
整体风格和el表达式类似,大家应该也比较熟悉
采用的写法是{{}}代表表达式,然后根据表达式里面的数据取值
关于样式问题
easypoi不会改变excel原有的样式,如果是遍历,easypoi会根据模板的那一行样式进行复制
模板:
结果:
2.代码实现
{{$fe: maplist lm.r0
maplist为总数据 lm是一个多集合
是数据集合 lm为当前循环集合♻️的一条
public static void main(String[] args) throws Exception {
//引入写好表达式的excel
TemplateExportParams params = new TemplateExportParams("WEB-INF/doc/专项支出用款申请书_map.xls");
//本地模版位置,实际项目中打jar包测试服务器读不到问题未解决,换另一种看下方进阶
/* TemplateExportParams params = new TemplateExportParams(
"/Users/jay/Desktop/work/project/intellect-isip-insurance-admin/src/main/resources/templates/理赔下载模版.xls");
*/
Map<String, Object> map = new HashMap<String, Object>();
//k与表达式一一对应,v是显示的值
map.put("date", "2014-12-25");
map.put("money", 2000000.00);
map.put("upperMoney", "贰佰万");
map.put("company", "执笔潜行科技有限公司");
map.put("bureau", "财政局");
map.put("person", "JueYue");
map.put("phone", "1879740****");
List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
for (int i = 0; i < 4; i++) {
Map<String, String> lm = new HashMap<String, String>();
lm.put("id", i + 1 + "");
lm.put("zijin", i * 10000 + "");
lm.put("bianma", "A001");
lm.put("mingcheng", "设计");
lm.put("xiangmumingcheng", "EasyPoi " + i + "期");
lm.put("quancheng", "开源项目");
lm.put("sqje", i * 10000 + "");
lm.put("hdje", i * 10000 + "");
listMap.add(lm);
}
map.put("maplist", listMap);
Workbook workbook = ExcelExportUtil.exportExcel(params, map);
File savefile = new File("G:/excel/");
if (!savefile.exists()) {
savefile.mkdirs();
}
//文件导出查看
FileOutputStream fos = new FileOutputStream("G:/excel/专项支出用款申请书_map.xls");
workbook.write(fos);
fos.close();
}
3.Excel导出HTML
07版 xlsx
ExcelToHtmlParams params = new ExcelToHtmlParams(WorkbookFactory.create(POICacheManager.getFile("G:/excel/自己的文件.xlsx")));
response.getOutputStream().write(ExcelXorHtmlUtil.excelToHtml(params).getBytes());
03版 xls
ExcelToHtmlParams params = new ExcelToHtmlParams(WorkbookFactory.create(POICacheManager.getFile("exceltohtml/exporttemp_img.xls")),true,"yes");
response.getOutputStream().write(ExcelXorHtmlUtil.excelToHtml(params).getBytes());
更多了解可看官方文档:easypoi官方文档
自定义数据表头导出(进阶)
表头为数据库第一行数据
特殊需求用了select * ;
目的:数据库增加字段后不需要改动代码
数据库
/*自定义不用模版*/
//切换数据源
@SwitchDs(name = "lpmx")
@Override
public void customizeExcel(String startTime, String endTime, HttpServletResponse response) throws Exception {
/*总数据*/
List<Map<String, Object>> selectAllJxMaps = claimDetailsJxMapper.selectAllJx();
/*筛选后数据*/
List<Map<String, Object>> maps = claimDetailsJxMapper.selectAllJxTime(startTime, endTime);
/*解密字段*/
List<String> decryptList = new ArrayList<>();
// decryptList.add("r3");
decryptList.add("r6");//证件号
// decryptList.add("r7");
/*表头*/
List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
/*title*/
Map<String, Object> title = selectAllJxMaps.remove(0);
/*title设置*/
for (Map.Entry<String, Object> entry : title.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
ExcelExportEntity colEntity = new ExcelExportEntity(String.valueOf(value), key);
colEntity.setNeedMerge(true);
colList.add(colEntity);
}
/*数据遍历*/
for (int i = 0; i < maps.size(); i++) {
Map<String, Object> objectMap = maps.get(i);
/*属性遍历*/
for (Map.Entry<String, Object> entry : objectMap.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
/*解密*/
if (decryptList.contains(key)) {
String decrypt = FastCodeUtils.decrypt(String.valueOf(value));
if (StringUtils.isNotBlank(decrypt)) {
entry.setValue(decrypt);
}
}
}
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(null, "数据"), colList, maps);
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode("某某某数据", "UTF-8") + ".xls");
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
}