基本描述
场景
用户传入List数据,要求生成Excel文件
(糟糕的需求是真糟糕!!!)
本次算是未完成版[应付需求还是可以的](需要硬代码去编写模板,各位宝子们先将就下,后续会跟新传参版)
特别提醒
时间字段 我们当做字符处理的写模板的时候不要用format属性(暂无特别好的解决方案,有大神可以以指导下喽)
原理
利用Easypoi 中的本身提供ExcelExportEntity去处理
实现描述
输入信息:
[
{
"name": "西索",
"sex": 1,
"createTime": "2023-11-11 12:12:12",
"address": [
{
"currentAddress": "友客鑫市",
"beforeAddress": "天空竞技场"
},
{
"currentAddress": "黑暗大陆",
"beforeAddress": "玛塔卡王国"
}
]
},
{
"name": "库洛洛",
"sex": 1,
"createTime": "2023-11-11 12:12:12",
"address": [
{
"currentAddress": "东方",
"beforeAddress": "流星街"
}
]
}
]
输出结果:
代码实现
控制层
/**
*
* @param list
* @param response
* @throws IOException
*/
@RequestMapping(value = "export/data/map")
public void exportDataFactory(@RequestBody List<Map<String, Object>> list, HttpServletResponse response) throws IOException {
// 创建一个 Excel 导出参数对象
ExportParams exportParams = new ExportParams();
exportParams.setTitle("用户信息表");
exportParams.setSheetName("用户信息");
// 使用 EasyPoi 创建一个 Excel 文件
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息表", "用户信息"), ExcelExtraFactory.build(ExcelExtraFactory.TEMPLATE1), list);
//使用工具类导出
ZExcelUtils.export(response, workbook, exportParams.getTitle() + ".xlsx");
}
工具类
package com.example.extra;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import com.alibaba.fastjson.JSONObject;
import java.util.ArrayList;
import java.util.List;
import java.util.NoSuchElementException;
/**
* @author sszdzq
*/
public class ExcelExtraFactory {
public static final String TEMPLATE1 = "TEMPLATE1";
/**
* 模板数据生成
*
* @param template
* @return
*/
public static List<ExcelExportEntity> build(String template) {
switch (template) {
case "TEMPLATE1":
return template1Entity();
default:
;
}
throw new NoSuchElementException("no template " + template);
}
/**
* 自定义模板示例 [新模板就按照这个写就可以]
*
* @return
*/
private static List<ExcelExportEntity> template1Entity() {
List<ExcelExportEntity> entityList = new ArrayList<>();
ExcelExportEntity nameEntity = new ExcelExportEntity("姓名", "name");
nameEntity.setNeedMerge(true);
entityList.add(nameEntity);
ExcelExportEntity sexEntity = new ExcelExportEntity("性别", "sex");
sexEntity.setNeedMerge(true);//合并单元格
sexEntity.setReplace(new String[]{"男_1", "女_0", "_null"});//replace 格式话
entityList.add(sexEntity);
ExcelExportEntity createTimeEntity = new ExcelExportEntity("录入时间", "createTime");
createTimeEntity.setNeedMerge(true);
entityList.add(createTimeEntity);
ExcelExportEntity addressEntity = new ExcelExportEntity("地址", "address");
List<ExcelExportEntity> temp = new ArrayList<>();
temp.add(new ExcelExportEntity("现居地", "currentAddress"));
temp.add(new ExcelExportEntity("曾居地", "beforeAddress"));
addressEntity.setList(temp);
entityList.add(addressEntity);
return entityList;
}
}
工具类
package com.example.util;
import cn.afterturn.easypoi.excel.annotation.Excel;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.http.HttpHeaders;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.lang.reflect.*;
import java.util.Map;
/**
* @author sszdzq
*/
public class ZExcelUtils {
// Excel 导出 通过浏览器下载的形式
public static void export(HttpServletResponse response, Workbook workbook, String fileName) throws IOException {
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "iso8859-1"));
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Pragma", "no-cache");
response.setHeader(HttpHeaders.CACHE_CONTROL, "no-cache");
BufferedOutputStream bufferedOutPut = new BufferedOutputStream(response.getOutputStream());
workbook.write(bufferedOutPut);
bufferedOutPut.flush();
bufferedOutPut.close();
}
}
标签:java,excel,List,EasyPoi,ExcelExportEntity,new,import,response
From: https://blog.csdn.net/sszdzq/article/details/136928794