hutool文件导出
普通文件导出
@Override
public void getExamExcelMsg(String testpaperName) {
List<PtExamExcelVO> list = ptExamRecoveryLogMapper.getExamBag(testpaperName);
if (CollectionUtil.isNotEmpty(list)) {
list.forEach(x -> {
int outboundCount = ptExamActivityService.count(new LambdaQueryWrapper<PtExamActivity>()
.eq(PtExamActivity::getOrgCode, x.getOrgCode())
.eq(PtExamActivity::getTestpaperName, x.getTestpaperName()));
int recoveryCount = ptExamActivityService.count(new LambdaQueryWrapper<PtExamActivity>()
.eq(PtExamActivity::getOrgCode, x.getOrgCode())
.eq(PtExamActivity::getTestpaperName, x.getTestpaperName())
.eq(PtExamActivity::getIsRecovery, 1));
x.setOutbound(outboundCount);
x.setRecovery(recoveryCount);
x.setUnRecycled(outboundCount - recoveryCount);
});
}
//生成xlsx格式 需要使用 使用ExcelUtil.getWriter(true)创建。
ExcelWriter writer = ExcelUtil.getWriter(true);
//只导出有别名的列，默认是会将未命中的记录排在首位，如果不加会多导出一行
writer.setOnlyAlias(true);
// 左 字段名 右 Excel表头别名
writer.addHeaderAlias("testpaperName", "考试批次");
writer.addHeaderAlias("orgName", "机构名称");
writer.addHeaderAlias("outbound", "试卷袋出库数量");
writer.addHeaderAlias("recovery", "回收数量");
writer.addHeaderAlias("unRecycled", "未回收数量");
// 一次性写出内容，使用默认样式，强制输出标题
writer.write(list, true);
httpServletResponse.setContentType("application/vnd.ms-excel;charset=utf-8");
try {
//设置文件名
String fileName = URLEncoder.encode(testpaperName, "UTF-8");
//设置响应头 fileName.xlsx"是弹出下载对话框的文件名，不能为中文，中文请自行编码
httpServletResponse.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
//获取字节输出流
ServletOutputStream outputStream = httpServletResponse.getOutputStream();
//关闭流前，先清空缓冲区数据
writer.flush(outputStream, true);
// 关闭流，释放内存
outputStream.close();
// 关闭writer，释放内存
writer.close();
} catch (Exception e) {
log.error("文件下载失败:{}", e.getMessage());
throw new IllegalArgumentException("文件下载失败！！");
}
这里是生成文件流给前端
在没有前端的情况下想要看生成文件信息
@Override
public void getExamExcelMsg(String testpaperName) {
List<PtExamExcelVO> list = ptExamRecoveryLogMapper.getExamBag(testpaperName);
if (CollectionUtil.isNotEmpty(list)) {
list.forEach(x -> {
int outboundCount = ptExamActivityService.count(new LambdaQueryWrapper<PtExamActivity>()
.eq(PtExamActivity::getOrgCode, x.getOrgCode())
.eq(PtExamActivity::getTestpaperName, x.getTestpaperName()));
int recoveryCount = ptExamActivityService.count(new LambdaQueryWrapper<PtExamActivity>()
.eq(PtExamActivity::getOrgCode, x.getOrgCode())
.eq(PtExamActivity::getTestpaperName, x.getTestpaperName())
.eq(PtExamActivity::getIsRecovery, 1));
x.setOutbound(outboundCount);
x.setRecovery(recoveryCount);
x.setUnRecycled(outboundCount - recoveryCount);
});
}
//生成xlsx格式到F盘
ExcelWriter writer = ExcelUtil.getWriter("F:\\file\\test.xlsx");
//只导出有别名的列，默认是会将未命中的记录排在首位，如果不加会多导出一行
writer.setOnlyAlias(true);
// 左 字段名 右 Excel表头别名
writer.addHeaderAlias("testpaperName", "考试批次");
writer.addHeaderAlias("orgName", "机构名称");
writer.addHeaderAlias("outbound", "试卷袋出库数量");
writer.addHeaderAlias("recovery", "回收数量");
writer.addHeaderAlias("unRecycled", "未回收数量");
// 一次性写出内容，使用默认样式，强制输出标题
writer.write(list, true);
httpServletResponse.setContentType("application/vnd.ms-excel;charset=utf-8");
try {
//设置文件名
String fileName = URLEncoder.encode(testpaperName, "UTF-8");
//设置响应头 fileName.xlsx"是弹出下载对话框的文件名，不能为中文，中文请自行编码
httpServletResponse.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
//获取字节输出流
ServletOutputStream outputStream = httpServletResponse.getOutputStream();
//关闭流前，先清空缓冲区数据
writer.flush(outputStream, true);
// 关闭流，释放内存
outputStream.close();
// 关闭writer，释放内存
writer.close();
} catch (Exception e) {
log.error("文件下载失败:{}", e.getMessage());
throw new IllegalArgumentException("文件下载失败！！");
}
}
结果
需求：管理员设置会议，参加会议会根据管理员设置的会议要求，用户参加会议填写相关数据，并且生成一个动态的excel数据并导出
示例：
每场都可以自定义报名字段
根据需求与前端约定
字段名称（name）
字段类型（type）：select-下拉框；fill-填空。
是否必填（required）
字段属性（attribute）
下拉框：single-单选；multiple-多选。
填空：text-⽂本；number-数字。
额外字段
下拉框：options-选项
填空：length-字段长度
字段返回示例：
[
{
"name":"姓名",
"type":"fill",
"required":true,
"attribute":"text",
"length":"10"
},
{
"name":"工作单位",
"type":"fill",
"required":true,
"attribute":"text",
"length":"20"
},
{
"name":"电话",
"type":"fill",
"required":true,
"attribute":"number",
"length":"20"
},
{
"name":"会议类型",
"type":"select",
"required":true,
"attribute":"single",
"options":[
"考务会",
"审稿会"
]
},
{
"name":"科目",
"type":"select",
"required":false,
"attribute":"single",
"options":[
"语文",
"数学",
"英语",
"物理",
"历史",
"化学",
"地理",
"政治",
"生物"
]
},
{
"name":"是否住宿",
"type":"select",
"required":true,
"attribute":"single",
"options":[
"是",
"否"
]
},
{
"name":"性别",
"type":"select",
"required":false,
"attribute":"single",
"options":[
"男",
"女"
]
}
]
用户填写参数示例:
{
"姓名":"小猪猪",
"电话":"1734765xxxx",
"工作单位":"xxx有限公司",
"会议类型":"考务会",
"是否住宿":"是",
"性别":"男"
}
需要生成的报表示例：
序号、报名时间与签到时间是必须有的
使用hutool的工具ExcelUtil
- 导包
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.5.6</version>
</dependency>
- 业务代码
/**
* 导出用户报名信息
* conference.getApplyFields() 这个数据示例：
* [{"name": "姓名","type": "fill","required": true,"attribute": "text","length": "10"},{"name": "工作单位","type": "fill","required": true,"attribute": "text","length": "20"},{"name": "电话","type": "fill","required": true,"attribute": "number","length": "20"},{"name": "会议类型","type": "select","required": true,"attribute": "single","options": ["考务会","审稿会"]},{"name": "科目","type": "select","required": false,"attribute": "single","options": ["语文", "数学", "英语","物理","历史","化学","地理","政治","生物"]},{"name": "是否住宿","type": "select","required": true,"attribute": "single","options": ["是", "否"]},{"name": "性别","type": "select","required": false,"attribute": "single","options": ["男", "女"]}]
*
*conferenceApply.getInformation()这个数据示例：
*{"姓名":"小猪猪","电话":"1734765xxxx","工作单位":"xxx有限公司","会议类型":"考务会","是否住宿":"是","性别":"男"}
*
* @param query 搜索信息
* @return String
*/
@Override
public void conferenceApplyExport(ConferenceApplyListQuery query) throws IOException {
// 查询会议信息
Conference conference = conferenceMapper.selectOne(new LambdaQueryWrapper<Conference>()
.eq(Conference::getConferenceId, query.getConferenceId()).select(Conference::getApplyFields));
Assert.isTrue(ObjectUtil.isNull(conference), "未找到该会议信息!");
List<ConferenceApply> conferenceApplyList = conferenceApplyMapper.listQuery(query);
Assert.isTrue(CollectionUtil.isEmpty(conferenceApplyList), "未找到相关数据");
ExcelWriter writer = ExcelUtil.getWriter();
List<Map<String, String>> rows = new ArrayList<>();
List<Object> list = JSONUtil.parseArray(conference.getApplyFields());
List<String> names = new ArrayList<>();
for (Object o : list) {
Map<String, String> map = (Map<String, String>) JSONUtil.parse(o);
names.add(map.get("name"));
}
int number = 1;
DateTimeFormatter simpleDateFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
for (ConferenceApply conferenceApply : conferenceApplyList) {
Map<String, String> maps = new HashMap<>();
maps.put("number", Integer.toString(number));
Map<String, String> map = (Map<String, String>) JSONUtil.parse(conferenceApply.getInformation());
// 填充空白数据
names.forEach(x -> {
if (!map.containsKey(x)) {
map.put(x, "/");
}
});
maps.putAll(map);
// 报名时间
maps.put(ConferenceEnum.gmt_apply.getName(), simpleDateFormat.format(conferenceApply.getGmtApply()));
// 签到时间
maps.put(ConferenceEnum.gmt_sign.getName(), ObjectUtil.isNotEmpty(conferenceApply.getGmtSign())
? simpleDateFormat.format(conferenceApply.getGmtSign()) : "");
rows.add(maps);
number++;
}
// 报名时间
names.add(ConferenceEnum.gmt_apply.getName());
// 签到时间
names.add(ConferenceEnum.gmt_sign.getName());
// Title
writer.merge(names.size(), "会议报名表");
// Header
writer.addHeaderAlias("number", "序号");
writer.setColumnWidth(0, 20);
int num = 1;
for (String key : names) {
// 设置表头
writer.addHeaderAlias(key, key);
// 设置行间距
writer.setColumnWidth(num, 20);
num++;
}
writer.write(rows, true);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("conferenceApplyTable" + DateUtil.today() + ".xls", "utf-8"));
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
writer.close();
IoUtil.close(out);
}
效果:
1050
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
