需求: 选择不同的时间段从数据库中获取报表文件的数据,生成对应的多个excel,然后把这些excel压缩打包下载下来。
过程大致如下面三幅图所示:
jxls官方英文教程
1、导入依赖
<!--excel生成工具-->
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>1.0.9</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.14</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.4</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
加上plugin
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<configuration>
<nonFilteredFileExtensions>
<nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
</nonFilteredFileExtensions>
</configuration>
</plugin>
2、编写JxlsUtil,jxls的工具类
JxlsUtils 代码
public class JxlsUtils {
static {
//添加自定义指令(可覆盖jxls原指令)
XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class); //合并单元格命令
}
public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
Context context = PoiTransformer.createInitialContext();
if (model != null) {
for (String key : model.keySet()) {
context.putVar(key, model.get(key));
}
}
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
Transformer transformer = jxlsHelper.createTransformer(is, os);
//获得配置
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
//设置静默模式,不报警告
//evaluator.getJexlEngine().setSilent(true);
//函数强制,自定义功能
Map<String, Object> funcs = new HashMap<String, Object>();
funcs.put("utils", new JxlsUtils()); //添加自定义功能
evaluator.getJexlEngine().setFunctions(funcs);
//必须要这个,否者表格函数统计会错乱
jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
}
//把excel打包成zip 然后再输出
public static void exportZipExcel(String fileName, ZipOutputStream zos, InputStream is, Map<String, Object> model) throws IOException {
Context context = PoiTransformer.createInitialContext();
if (model != null) {
for (String key : model.keySet()) {
context.putVar(key, model.get(key));
}
}
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
ByteArrayOutputStream output = new ByteArrayOutputStream();
Transformer transformer = jxlsHelper.createTransformer(is, output);
//获得配置
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
//函数强制,自定义功能
Map<String, Object> funcs = new HashMap<String, Object>();
funcs.put("utils", new JxlsUtils()); //添加自定义功能
evaluator.getJexlEngine().setFunctions(funcs);
jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
//命名文件
// zos.putNextEntry(new ZipEntry( URLEncoder.encode(fileName + ".xlsx", "UTF-8")));
zos.putNextEntry(new ZipEntry(fileName + ".xlsx"));
//把xlxs输出流作为输入流输入到zip输出流中
zos.write(output.toByteArray());
zos.flush();
zos.closeEntry();
output.close();
//必须要这个,否者表格函数统计会错乱
// jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
}
public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
}
public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
File template = getTemplate(templatePath);
if (template != null) {
exportExcel(new FileInputStream(template), os, model);
} else {
throw new Exception("Excel 模板未找到。");
}
}
//获取jxls模版文件
public static File getTemplate(String path) {
File template = new File(path);
if (template.exists()) {
return template;
}
return null;
}
// 日期格式化
public String dateFmt(Date date, String fmt) {
return dateFmt(date, fmt, "");
}
// 日期格式化
public String dateFmt(Date date, String fmt, String defaultStr) {
if (date == null) {
return defaultStr;
}
try {
SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
return dateFmt.format(date);
} catch (Exception e) {
e.printStackTrace();
}
return "";
}
// 静态的日期格式化
public static String dateFormate(Date date, String fmt) {
try {
SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
return dateFmt.format(date);
} catch (Exception e) {
e.printStackTrace();
}
return "";
}
MergeCommand代码
/**
* 合并单元格命令
* Created by Zhusx. on 2019/4/24.
*/
public class MergeCommand extends AbstractCommand {
private String cols; //合并的列数
private String rows; //合并的行数
private Area area;
private CellStyle cellStyle; //第一个单元格的样式
@Override
public String getName() {
return "merge";
}
@Override
public Command addArea(Area area) {
if (super.getAreaList().size() >= 1) {
throw new IllegalArgumentException("You can add only a single area to 'merge' command");
}
this.area = area;
return super.addArea(area);
}
@Override
public Size applyAt(CellRef cellRef, Context context) {
int rows = 1, cols = 1;
if(StringUtils.isNotBlank(this.rows)){
Object rowsObj = getTransformationConfig().getExpressionEvaluator().evaluate(this.rows, context.toMap());
if(rowsObj != null && NumberUtils.isDigits(rowsObj.toString())){
rows = NumberUtils.toInt(rowsObj.toString());
}
}
if(StringUtils.isNotBlank(this.cols)){
Object colsObj = getTransformationConfig().getExpressionEvaluator().evaluate(this.cols, context.toMap());
if(colsObj != null && NumberUtils.isDigits(colsObj.toString())){
cols = NumberUtils.toInt(colsObj.toString());
}
}
if(rows > 1 || cols > 1){
Transformer transformer = this.getTransformer();
if(transformer instanceof PoiTransformer){
return poiMerge(cellRef, context, (PoiTransformer)transformer, rows, cols);
}else if(transformer instanceof JexcelTransformer){
return jexcelMerge(cellRef, context, (JexcelTransformer)transformer, rows, cols);
}
}
area.applyAt(cellRef, context);
return new Size(1, 1);
}
protected Size poiMerge(CellRef cellRef, Context context, PoiTransformer transformer, int rows, int cols){
Sheet sheet = transformer.getWorkbook().getSheet(cellRef.getSheetName());
CellRangeAddress region = new CellRangeAddress(
cellRef.getRow(),
cellRef.getRow() + rows - 1,
cellRef.getCol(),
cellRef.getCol() + cols - 1);
sheet.addMergedRegion(region);
//合并之后单元格样式会丢失,以下操作将合并后的单元格恢复成合并前第一个单元格的样式
area.applyAt(cellRef, context);
if(cellStyle == null){
PoiCellData cellData = (PoiCellData)transformer.getCellData(cellRef);
cellStyle = cellData.getCellStyle();
}
setRegionStyle(cellStyle, region, sheet);
return new Size(cols, rows);
}
protected Size jexcelMerge(CellRef cellRef, Context context, JexcelTransformer transformer, int rows, int cols){
try {
transformer.getWritableWorkbook().getSheet(cellRef.getSheetName())
.mergeCells(
cellRef.getRow(),
cellRef.getCol(),
cellRef.getRow() + rows - 1 ,
cellRef.getCol() + cols - 1);
area.applyAt(cellRef, context);
} catch (WriteException e) {
throw new IllegalArgumentException("合并单元格失败");
}
return new Size(cols, rows);
}
private static void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
Row row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
Cell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
}
cell.setCellStyle(cs);
}
}
}
public String getCols() {
return cols;
}
public void setCols(String cols) {
this.cols = cols;
}
public String getRows() {
return rows;
}
public void setRows(String rows) {
this.rows = rows;
}
}
在JxlsUtil中写上excel要使用到方法(不要写成静态方法)
// 日期格式化
public String dateFmt(Date date, String fmt) {
return dateFmt(date, fmt, "");
}
// 日期格式化
public String dateFmt(Date date, String fmt, String defaultStr) {
if (date == null) {
return defaultStr;
}
try {
SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
return dateFmt.format(date);
} catch (Exception e) {
e.printStackTrace();
}
return "";
}
在excel可以通过上文定义的utils去使用这个方法
3、定义excel模板
我把模板放在了resources的templates/xls/文件夹下
//遍历List的批注
jx:each(items="detailList" var="detail" lastCell="G6")
//jxls扫码范围的批注
jx:area(lastCell="H18")
excel模板说明
excel表对应java代码
最终效果
4、调用接口生成的excel代码
取出数据生成excel
@GetMapping(value = "/export/purchase")
public void purchaseExport(HttpServletResponse response,String id) throws Exception {
try {
PurchaseOrderExportVo vo=new PurchaseOrderExportVo();
//Excel导出配置 获取resources下面的模板
File file = ResourceUtils.getFile("classpath:templates/xls/sell_accept.xlsx");
String fileName = "销售验收单";
//设置响应头
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName+".xlsx", "UTF-8"));
response.setContentType("application/vnd.ms-excel");
Map<String,Object> map=new HashMap();
map.put("detailList",vo.getOrderList());
map.put("vo",vo);
try(InputStream is = new FileInputStream(file )) {
try (OutputStream os = response.getOutputStream()) {
//开始到处excel
JxlsUtils.exportExcel(file.getPath(), os, map);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
5、调用接口生成多个excel,并且打包下载
@GetMapping(value = "/export/purchaseOrders")
public String purchaseAcceptExports(HttpServletRequest request, HttpServletResponse response, @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd") Date startTime, @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd") Date endTime, @RequestParam String type) {
endTime = new Date(endTime.getTime() + 86400000L);
List<PurchaseOrderDto> purchaseOrderDtos = directionalService.getPurchaseOrderByTimes(startTime, endTime);
if (purchaseOrderDtos == null || purchaseOrderDtos.size() == 0) return "参数为空";
try (ServletOutputStream bos = response.getOutputStream();
//定义个zip输出流
ZipOutputStream zos = new ZipOutputStream(bos)) {
//Excel导出配置 获取resources下面的模板
String url = ResourceUtils.getURL("classpath:").getPath();
String config = url + "templates/xls/sell_accept.xlsx";
String fileName = "销售验收单";
//以zip压缩包方式响应
response.setContentType("application/zip");
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName + ".zip", "UTF-8"));
//循环输出多个文件
purchaseOrderDtos.stream().forEach(dto -> {
PurchaseOrderExportVo vo = new PurchaseOrderExportVo();
//中间逻辑过程 省略。。。。。。。。。。。。。。。。。。
Map<String, Object> map = new HashMap();
map.put("detailList", dto.getDetailList());
map.put("vo", vo);
try (InputStream is = new FileInputStream(config)) {
JxlsUtils.exportZipExcel(fileName + "_" + dto.getFileNo(), zos, is, map);
} catch (Exception e) {
}
});
//刷新zip输出流
zos.flush();
zos.close();
bos.close();
} catch (Exception e) {
e.printStackTrace();
}
return "下载成功";
}
调用此接口就会生成多个excel表的数据流然后打包下载
6、vue端请求下载excel压缩包的代码
var elemIF = document.createElement('iframe')
elemIF.src = this.baseURL + this.CONSTAINT.getCenterUrl(this.title) + '?startTime=' + this.$moment(this.start_time).format('YYYY-MM-DD') + '&endTime=' +
this.$moment(this.end_time).format('YYYY-MM-DD') +
'&type=' + this.CONSTAINT.getTypp(this.title)
elemIF.style.display = 'none'
document.body.appendChild(elemIF)
点击下载按钮
7、each嵌套
在exel中有循环,循环中还有循环
8、excel动态合并单元格
//合并代码
jx:merge(rows="detailList.size()" lastCell="A4")
如果多列都要合并,没列都要添加合并批注
合并效果
9、excel插入序号的方式
可以在List的每一项中加入下标然后再取出来展示
或者可以通过此单元格自增实现序号功能