本帖最后由 wuqingvika 于 2023-3-28 12:13 编辑
有个需求 需要导出带图片的excel 不过是一个实体对象 字段是分散在excel中的 如这个报名信息 字段key 和value是如下这种分散型 不是直接像我们平时导出标题在第一行的
我之前做过导出的例子是一行行导出带图片的
现在不知道在其基础上改造 还是有什么 后端好用的工具包 方便实现功能的
=========================================【已解决,有点啰嗦 建议直接跳到下面的方案二 比较简单】============================================================
最后是仿照评论里 大佬给的例子 实现了 利用easyexcel填充自定义模板 实现好了 贴相关代码如下（ps:后续有空整理优化完再放上来）
导入pom.xml
[XML] 纯文本查看 复制代码 <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!--尝试导出复杂格式的excel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.4</version>
</dependency>
[Java] 纯文本查看 复制代码 @GetMapping("/export2")
public void exportToExcel2(HttpServletResponse response) throws IOException {
//这段是从数据库取一个byte[]照片用的是之前导出的例子
List<Person> persons = personservice.findAll();
byte[] image = persons.get(0).getImage();
ByteArrayOutputStream os = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel.write(os).withTemplate(excelService.getTemplate()).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
// 填充数据
User user = new User();
user.setUname("吴斯齐扬");
user.setUsex("女");
user.setUbirthday("199X.10.01");
user.setUcardid("320123xxxxxxxx");
user.setUmarriage("保密");
user.setUnation("汉族");
user.setUnative("江苏xxxx");
user.setUbirthplace("江苏xxxx");
user.setUpolity("保密");
user.setUhealth("良好");
user.setUworktime("2023-03-23");
excelWriter.fill(user, writeSheet);
Map<String, Object> map = new HashMap<>(64);
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
map.put("cdate", formatter.format(new Date()));
excelWriter.fill(map,writeSheet);
excelWriter.fill(new FillWrapper("data1", data1()), fillConfig, writeSheet);
// 别忘记关闭流
excelWriter.finish();
// 合并单元格
mergeExcel(os, response, image);
}
private void mergeExcel(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, byte[] image) throws IOException {
ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
XSSFWorkbook workbook = new XSSFWorkbook(byteArrayInputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
// 合并列
sheet.addMergedRegion(new CellRangeAddress(8, 8, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(8, 8, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(8, 8, 5, 9));
sheet.addMergedRegion(new CellRangeAddress(8, 8, 10, 11));
sheet.addMergedRegion(new CellRangeAddress(9, 9, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(9, 9, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(9, 9, 5, 9));
sheet.addMergedRegion(new CellRangeAddress(9, 9, 10, 11));
// 合并行
sheet.addMergedRegion(new CellRangeAddress(6, 9, 0, 0));
// 设置边框（其它可自行设置）
//RegionUtil.setBorderBottom(BorderStyle.THIN, new CellRangeAddress(8, 8, 1, 2), sheet);
//添加照片
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 11, 2, (short) 12, 6);
anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
patriarch.createPicture(anchor, workbook.addPicture(image, HSSFWorkbook.PICTURE_TYPE_JPEG));//byteArrayOut.toByteArray()
byteArrayInputStream.close();
byteArrayOutputStream.close();
//生成excel到浏览器
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=persons.xlsx");
workbook.write(response.getOutputStream());
workbook.close();
}
private List<WorkHistory> data1() {
List<WorkHistory> list = new ArrayList<>();
WorkHistory workHistory;
for (int i = 1; i <= 3; i++) {
workHistory = new WorkHistory();
workHistory.setUbegintime("202"+i+".05.01");
workHistory.setUendtime("202"+i+".12.30");
workHistory.setUworkcomp("xxx公司");
workHistory.setUworkdesc("java开发");
list.add(workHistory);
}
return list;
}
写了一个service读取模板文件
[Java] 纯文本查看 复制代码 @Service
public class ExcelService {
public InputStream getTemplate() throws IOException {
ClassPathResource resource = new ClassPathResource("templates/template_user.xlsx");
return resource.getInputStream();
}
}
模板文件样式
最后导出效果如下（附一些代码说明）
最后再次谢谢各位大佬们的建议！！！太给力了！！！！
=======================================================================方案二 =======================================================
可以参考：Java 导出Excel利器 JXLS（excel模板配置教程）
https://blog.csdn.net/baidu_14958441/article/details/128528237
【已实现】
pom.xml=================================
[XML] 纯文本查看 复制代码 <!--尝试用JXLS导出-->
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.11.0</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>2.11.0</version>
</dependency>
controller==========================
[Java] 纯文本查看 复制代码 @GetMapping("/export3")
public void exportEmployees(HttpServletResponse response) throws IOException {
// 设置响应头
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=employees.xlsx");
// 获取员工列表
List<Person> users = personservice.findAll();
// 创建JxlsHelper对象
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
// 使用模板填充数据
try (InputStream is = getClass().getResourceAsStream("/templates/template_user2.xlsx")) {
try (OutputStream os = response.getOutputStream()) {
Context context = new Context();
context.putVar("user", users.get(0));
jxlsHelper.processTemplate(is, os, context);
}
}
}
编辑excel模板===================
在“审阅”里面添加/编辑批注。注：A1单元格LastCell尽量比数据域再大一格。
jx:area(lastCell="K7")
jx:image(lastCell="J6" src="user.image" imageType="JPEG")
最后导出 效果
| 
发表于 2023-3-23 12:11
|
发表于 2023-3-23 13:35
