java中如何实现excel合并单元格
后端
InputStream filePath = this.getClass().getClassLoader().getResourceAsStream(templateFile);
// 根据模板文件生成目标文件
ExcelWriter excelWriter = EasyExcel
.write(orgInfo.getFilename()).excelType(ExcelTypeEnum.XLS)
.withTemplate(filePath).inMemory(Boolean.TRUE)
.autoCloseStream(Boolean.TRUE)
//合并单元格
.registerWriteHandler(new CustomMergeStrategy())
//设置单元格格式
.registerWriteHandler(new CustomWriteHandlerStrategy())
//动态设置sheet名称
.registerWriteHandler(new CustomSheetStrategy(orgInfo.getOrgName()))
.build();
//重新生成行、默认为false
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 第一种占位符替换
excelWriter.fill(orgInfo, writeSheet);
// 第二种占位符替换，这里定义了 duty
excelWriter.fill(new FillWrapper("duty", dutyList), fillConfig, writeSheet);
// 第三种占位符替换，这里定义了 sub
excelWriter.fill(new FillWrapper("sub", subList), fillConfig, writeSheet);
excelWriter.finish();
合并单元格：
package com.duty.common.handler;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import java.util.List;
/**
* 合并单元格
*/
public class CustomMergeStrategy extends AbstractMergeStrategy {
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (relativeRowIndex == null || relativeRowIndex == 0) {
return;
}
int rowIndex = cell.getRowIndex();
int colIndex = cell.getColumnIndex();
sheet = cell.getSheet();
Row preRow = sheet.getRow(rowIndex - 1);
//获取上一行的该格
Cell preCell = preRow.getCell(colIndex);
List list = sheet.getMergedRegions();
CellStyle cs = cell.getCellStyle();
cell.setCellStyle(cs);
for (int i = 0, len = list.size(); i
CellRangeAddress cellRangeAddress = list.get(i);
if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) {
int lastColIndex = cellRangeAddress.getLastColumn();
int firstColIndex = cellRangeAddress.getFirstColumn();
CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex);
sheet.addMergedRegion(cra);
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
return;
}
}
}
}
sheet名设置：
package com.duty.common.handler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
/**
* 自定义导出sheet拦截器
*/
public class CustomSheetStrategy implements SheetWriteHandler {
private Integer sheetNo;
private String sheetName;
public CustomSheetStrategy(String sheetName) {
this.sheetName = sheetName;
}
public CustomSheetStrategy(Integer sheetNo, String sheetName) {
this.sheetNo = sheetNo;
this.sheetName = sheetName;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
/**
* 功能：动态修改模板中sheet的名称
* sheet创建完成后调用
* @param writeWorkbookHolder
* @param writeSheetHolder
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if (sheetName == null) {
return;
}
if (sheetNo == null) {
sheetNo = 0;
}
writeWorkbookHolder.getCachedWorkbook().setSheetName(sheetNo, sheetName);
}
}
单元格样式设置
package com.duty.common.handler;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.util.List;
/**
* 设置单元格格式
*/
freemarker 导出word ：
ftl模板：
//排序
List students = studentList.stream().sorted(Comparator.comparing(Student::getId)).collect(Collectors.toList());
os = new FileOutputStream("导出文件");
Map root = new HashMap(5);
root.put("duty", student);
root.put("list", students);
freeMarkerConfigurer.getConfiguration().setClassForTemplateLoading(getClass(), "/template");
freeMarkerConfigurer.getConfiguration().setIncompatibleImprovements(Configuration.VERSION_2_3_22);
Template template = freeMarkerConfigurer.getConfiguration().getTemplate("模板名.ftl");
template.process(root, new OutputStreamWriter(os, "utf-8"));
列合并关键代码：
eden
管理员
1
2024-03-25T03:34:00Z
2023-07-14T06:21:00Z
2024-04-02T01:39:34Z
10080
1
0
0
0
0
0
14
2052-11.8.2.12085
F4EC44E9E71949449A59CA81D532F1C2
${(duty.subject)!}
编报时间：${duty.draftDate?string("yyyy年MM月dd日")}
日期
单位负责同志带班
办公室（厅）负责同志
带班
值班员
姓名
职务
联系方式
姓名
职务
联系方式
姓名
${(t.overtimeDate)!}
${(t.unitUser)!}
${(t.unitJob)!}
电话：${(t.unitTelephone)!}
手机：${(t.unitMobile)!}
${(t.officeUser)!}
${(t.officeJob)!}
电话：${(t.officeTelephone)!}
手机：${(t.officeMobile)!}
${(t.username)!}
值班室电话：${(duty.telephone)!} 传真：${(duty.fax)!}
