1.添加POM文件依赖:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
我们就实现 下图为例子:
2.在项目resources——新建一个templates目录——在新建一个我们的xlsx文件模板:
3.模板里面的内容为:
{{!fe: maplist t.userName | t.storeNum | n:t.awardAmount | n:t.saleAwardAmount | n:t.engineerAwardAmount}} |
4.这里介绍下fe的用法:
- 空格分割
- 三目运算 {{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表达式类似, 采用的写法是{{}}代表表达式,然后根据表达式里面的数据取值
5. java Controller 代码:
import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.entity.TemplateExportParams; import org.apache.poi.ss.usermodel.Workbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author zjp * @Title: Export * @date 2019/8/7 */ @RestController public class Export { private static final Logger logger = LoggerFactory.getLogger(Export.class); /** * 下载月度奖金表格 * * @param response */ @RequestMapping(value = "/exportBonus") public void exportBonus2(HttpServletRequest servletRequest, HttpServletResponse response) { try { TemplateExportParams params = new TemplateExportParams("templates/提成表1.xlsx"); Map<String, Object> map = new HashMap<String, Object>(); List<Map<String, String>> listMap = new ArrayList<Map<String, String>>(); map.put("year", "2019"); map.put("month", "12"); Map<String, String> lm = new HashMap<String, String>(); lm.put("userName", "大鹏"); lm.put("storeNum", "10"); lm.put("awardAmount", "1200"); lm.put("saleAwardAmount", "300"); lm.put("engineerAwardAmount", "900"); listMap.add(lm); Map<String, String> lm2 = new HashMap<String, String>(); lm2.put("userName", "王二"); lm2.put("storeNum", "12"); lm2.put("awardAmount", "1800"); lm2.put("saleAwardAmount", "800"); lm2.put("engineerAwardAmount", "1000"); listMap.add(lm2); map.put("maplist", listMap); Workbook workbook = ExcelExportUtil.exportExcel(params, map); export(servletRequest, response, workbook, "2019年12月奖金汇总表"); } catch (Exception e) { logger.error("下载月度奖金表格异常=" + e.getMessage()); } } /** * export导出请求头设置 * 防止乱码 * @param response * @param workbook * @param fileName * @throws Exception */ private static void export(HttpServletRequest servletRequest, HttpServletResponse response, Workbook workbook, String fileName) throws Exception { response.reset(); response.setContentType("application/x-msdownload"); response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1") + ".xls"); ServletOutputStream outStream = null; try { outStream = response.getOutputStream(); workbook.write(outStream); } finally { outStream.close(); } } }
6.测试代码:在浏览器访问:http://localhost:8080/exportBonus 浏览器就帮我们下载好了
参考官方地址:http://easypoi.mydoc.io/