该代码示例展示了如何在Java中利用Hutool和Apache POI库来创建并导出Excel文件。首先，引入了相关依赖，然后定义了一个`export`方法，该方法接收参数并根据条件获取数据。通过`ExcelWriter`实例设置样式，创建标题和表头信息，并写入数据到Excel工作表中。此外，还包含了设置标题样式和布局的辅助方法。
引入hutool工具包
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>4.5.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>commons-net</groupId>
<artifactId>commons-net</artifactId>
<version>3.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>部分代码段
public void export(ParamDto data, HttpServletResponse response) {
try (OutputStream out = response.getOutputStream()) {
List<String> taskList = null;
List<Map<String, Object>> list = new ArrayList<>();
List<Map<String, Object>> titleList = new ArrayList<>();
if (StringUtils.isEmpty(data.getTaskName())) {
taskList = getTaskList(data.getDepartment());
}
ExcelWriter writer = ExcelUtil.getWriter();
writer.setColumnWidth(-1, 18);
// 设置样式
StyleSet style = writer.getStyleSet();
CellStyle cellStyle = style.getCellStyle();
cellStyle.setWrapText(true);
CellStyle headCellStyle = style.getHeadCellStyle();
//水平居中
headCellStyle.setAlignment(HorizontalAlignment.LEFT);
headCellStyle.setWrapText(true);
//设置内容字体
Font font = writer.createFont();
//加粗
font.setBold(true);
//设置标题字体大小
font.setFontHeightInPoints((short) 16);
headCellStyle.setFont(font);
writer.setStyleSet(style);
writer.renameSheet(0, "评价记录");
if (CollectionUtils.isEmpty(taskList)) {
list = this.getActivityList(data, setList());
titleList = this.getActivityList(data, setTitleList());
if(!CollectionUtils.isEmpty(list)) {
//创建标题和表头信息
if (!CollectionUtils.isEmpty(titleList)) {
writer.merge(15, titleList.get(0).get("bumenchejian") + "评价记录", true);
writer.merge(3, StringUtils.join("单位:", titleList.get(0).get("bumenchejian")), true);
writer.writeRow(new ArrayList<>());
layout(writer, titleList, 0);
writer.setRowHeight(2, 40);
}
setHeader(writer);
writer.write(list, true);
}
} else {
Integer num = 0;
for (int i = 0; i < taskList.size(); i++) {
list = this.getActivityList(data.setTaskName(taskList.get(i)), setList());
titleList = this.getActivityList(data.setTaskName(taskList.get(i)), setTitleList());
if(!CollectionUtils.isEmpty(list)) {
//创建标题和表头信息
if (!CollectionUtils.isEmpty(titleList)) {
if(i==0) {
writer.merge(15, titleList.get(0).get("bumenchejian") + "评价记录", true);
}else {
writer.writeRow(new ArrayList<>());
}
writer.merge(3, StringUtils.join("单位:", titleList.get(0).get("bumenchejian")), true);
writer.writeRow(new ArrayList<>());
writer.setRowHeight(num + 2, 40);
layout(writer, titleList, num);
}else {
writer.merge(15, "评价记录", true);
writer.merge(3, StringUtils.join("单位:"), true);
writer.writeRow(new ArrayList<>());
writer.writeRow(new ArrayList<>());
layout(writer, num);
}
num += list.size() + 4;
setHeader(writer);
writer.write(list, true);
}
}
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
String name = new String("评价记录".getBytes("utf-8"), "iso-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xls");
response.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, "fileName");
writer.flush(out, true);
// 关闭writer，释放内存
writer.close();
IoUtil.close(out);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void setHeader(ExcelWriter writer) {
Map<String, String> objectMap = new LinkedHashMap<>();
objectMap.put("xuhao", "序号");
objectMap.put("zuoyebuzhou", "作业步骤");
objectMap.put("weixianyuanhuoqianza", "危险源");
objectMap.put("zhuyaohouguo", "主要后果");
objectMap.put("gongchengcuoshi", "工程控制");
objectMap.put("guanlicuoshi", "管理措施");
objectMap.put("peixunjiaoyu", "培训教育");
objectMap.put("getifanghu", "个体防护");
objectMap.put("yingjichuzhi", "应急处置");
objectMap.put("lquzhi", "L");
objectMap.put("equzhi", "E");
objectMap.put("cquzhi", "C");
objectMap.put("dzhi7", "D");
objectMap.put("pingjiajibie", "F");
objectMap.put("guankongjibie", "FF");
objectMap.put("jianyixinzenggaijinc", "措施");
writer.setHeaderAlias(objectMap);
}
public static void layout(ExcelWriter writer, List<Map<String, Object>> list, Integer i) {
writer.merge(1 + i, 1 + i, 4, 10,
StringUtils.join("风险点岗位:", list.get(0).get("fengxiandiangangwei"))
.replace("[", "").replace("]", ""), true);
writer.merge(1 + i, 1 + i, 11, 15,
StringUtils.join("工作任务:", list.get(0).get("fengxiandianmingchen")), true);
commonLayout(writer, i);
}
public static void layout(ExcelWriter writer, Integer i) {
writer.merge(1 + i, 1 + i, 4, 10,
StringUtils.join("风险点岗位:")
.replace("[", "").replace("]", ""), true);
writer.merge(1 + i, 1 + i, 11, 15,
StringUtils.join("工作任务:"), true);
commonLayout(writer, i);
}
public static void commonLayout(ExcelWriter writer, Integer i) {
writer.merge(2 + i, 3 + i, 0, 0, "序号", true);
writer.merge(2 + i, 3 + i, 1, 1, "作业步骤", true);
writer.merge(2 + i, 3 + i, 2, 2, "危险源", true);
writer.merge(2 + i, 3 + i, 3, 3, "主要后果", true);
writer.merge(2 + i, 2 + i, 4, 8, "A", true);
writer.merge(2 + i, 3 + i, 9, 9, "L", true);
writer.merge(2 + i, 3 + i, 10, 10, "E", true);
writer.merge(2 + i, 3 + i, 11, 11, "C", true);
writer.merge(2 + i, 3 + i, 12, 12, "D", true);
writer.merge(2 + i, 3 + i, 13, 13, "F", true);
writer.merge(2 + i, 3 + i, 14, 14, "FF", true);
writer.merge(2 + i, 3 + i, 15, 15, "措施", true);
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
