pom依赖
<!--hutool-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.22</version>
</dependency>
<!--autoPoi-->
<dependency>
<groupId>org.jeecgframework</groupId>
<artifactId>autopoi-web</artifactId>
<version>1.4.6</version>
<exclusions>
<exclusion>
<artifactId>xercesImpl</artifactId>
<groupId>xerces</groupId>
</exclusion>
</exclusions>
</dependency>
<!--easyPoi-->
<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-spring-boot-starter -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.4.0</version>
</dependency>
工具类如下:
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.sql.Timestamp;
@Slf4j
public class ExcelUtils {
/**
* 合并单元格
*/
public static void mergeRemarks(Workbook workbook, Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, // 第一行(0)
lastRow, // last row(0-based)
firstCol, // 第一列(基于0)
lastCol // 最后一列(基于0)
));
//创建合并但未创建的单元格
createCell(sheet, workbook.getCellStyleAt(24), firstRow, lastRow, firstCol, lastCol);
}
/**
* 根据行列遍历创建单元格
*/
public static void createCell(Sheet sheet, CellStyle cellStyle, int firstRow, int lastRow, int firstCol, int lastCol) {
Row row;
Cell cell;
for (int i = firstRow; i <= lastRow; i++) {
for(int j = firstCol; j <= lastCol; j++) {
row = sheet.getRow(i);
if(row == null) {
row = sheet.createRow(i);
}
if(row.getCell(j) == null) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
}
}
}
}
public static CellStyle getCenterCellStyle(Workbook workbook){
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局:居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
return cellStyle;
}
/**
* excel文件流输出到浏览器
*/
public static void workBookWrite(String fileName, Workbook workbook, HttpServletResponse response) {
try (OutputStream os = response.getOutputStream()) {
response.reset();
Timestamp timestamp = new Timestamp(System.currentTimeMillis());
response.setHeader("Content-disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName + "-" + timestamp.getTime(), "UTF-8") + ".xls");
response.setContentType("application/x-download");
workbook.write(os);
} catch (Exception e) {
log.error("excel导出失败, {}", e.getMessage(), e);
}
}
}
实现方法例子:
@Override
public void export(Map<String, Object> queryParam, HttpServletRequest request, HttpServletResponse resp) {
String typeStr = (String) queryParam.get("types");
List<String> types = new ArrayList<>();
if (StringUtils.isNotBlank(typeStr)) {
types = Arrays.asList(typeStr.split(","));
} else {
LambdaQueryWrapper<SybasesetEntity> w = Wrappers.lambdaQuery(SybasesetEntity.class).eq(SybasesetEntity::getType, "verifyobjtype").eq(SybasesetEntity::getStatus, BusinessConstant.STATUS_P).orderByAsc(SybasesetEntity::getIdx);
List<SybasesetEntity> sybasesetEntities = sybasesetMapper.selectList(w);
if (!CollectionUtils.isEmpty(sybasesetEntities)) {
types = sybasesetEntities.stream().map(o -> o.getCode()).collect(Collectors.toList());
}
}
// 1、创建一个Workbook(XSSFWorkbook)
Workbook wb = new XSSFWorkbook();
if (!CollectionUtils.isEmpty(types)) {
for (String type : types) {
// 设置导出字段
List<ExcelExportEntity> entity = getExcelExportEntity(type);
// 设置工作表名称
LambdaQueryWrapper<SybasesetEntity> wrapper = Wrappers.lambdaQuery(SybasesetEntity.class).eq(SybasesetEntity::getType, "verifyobjtype").eq(SybasesetEntity::getCode, type).eq(SybasesetEntity::getStatus, BusinessConstant.STATUS_P);
SybasesetEntity sybasesetEntity = sybasesetMapper.selectOne(wrapper);
// 组装单个sheet数据
List<Map<String, Object>> dataList = getData(type, queryParam, request);
// 3、定义标题和sheetName
ExportParams exportParams = new ExportParams(null, sybasesetEntity.getName());
// 默认 ExcelType.HSSF
exportParams.setType(ExcelType.XSSF);
ExcelExportService service = new ExcelExportService();
service.createSheetForMap(wb, exportParams, entity, dataList);
}
}
ExcelUtils.workBookWrite("核验信息导出", wb, resp);
}
/**
* 设置导出字段
*
* @param type 字典 head_verify_E 和 字典 head_register_E, E这个值是 type 值
* @return
*/
public List<ExcelExportEntity> getExcelExportEntity(String type) {
// 定义总表头
List<ExcelExportEntity> allHead = new ArrayList<>();
// 设置核验信息表头
List<ExcelExportEntity> subList = new ArrayList<>();
LambdaQueryWrapper<SybasesetEntity> wrapper = Wrappers.lambdaQuery(SybasesetEntity.class).eq(SybasesetEntity::getType, "head_verify_" + type).eq(SybasesetEntity::getStatus, BusinessConstant.STATUS_P).orderByAsc(SybasesetEntity::getIdx);
List<SybasesetEntity> list = sybasesetMapper.selectList(wrapper);
for (SybasesetEntity entity : list) {
ExcelExportEntity export = new ExcelExportEntity(entity.getName(), "verify_" + entity.getCode(), 20);
subList.add(export);
}
// 合并核验信息表头
ExcelExportEntity verifyBaseHead = new ExcelExportEntity("核验信息", "verifyInfo");
verifyBaseHead.setList(subList);
allHead.add(verifyBaseHead);
// 设置登记信息表头
if ("C".equals(type)) {
List<ExcelExportEntity> subList1 = new ArrayList<>();
LambdaQueryWrapper<SybasesetEntity> wrapper1 = Wrappers.lambdaQuery(SybasesetEntity.class).eq(SybasesetEntity::getType, "head_register_leave_" + type).eq(SybasesetEntity::getStatus, BusinessConstant.STATUS_P).orderByAsc(SybasesetEntity::getIdx);
List<SybasesetEntity> list1 = sybasesetMapper.selectList(wrapper1);
for (SybasesetEntity entity : list1) {
ExcelExportEntity export = new ExcelExportEntity(entity.getName(), "register_leave_" + entity.getCode(), 20);
subList1.add(export);
}
// 合并登记信息表头
ExcelExportEntity registerBaseHead = new ExcelExportEntity("申请离开登记信息", "registerLeaveInfo");
registerBaseHead.setList(subList1);
allHead.add(registerBaseHead);
List<ExcelExportEntity> subList2 = new ArrayList<>();
LambdaQueryWrapper<SybasesetEntity> wrapper2 = Wrappers.lambdaQuery(SybasesetEntity.class).eq(SybasesetEntity::getType, "head_register_regress_" + type).eq(SybasesetEntity::getStatus, BusinessConstant.STATUS_P).orderByAsc(SybasesetEntity::getIdx);
List<SybasesetEntity> list2 = sybasesetMapper.selectList(wrapper2);
for (SybasesetEntity entity : list2) {
ExcelExportEntity export = new ExcelExportEntity(entity.getName(), "register_regress_" + entity.getCode(), 20);
subList2.add(export);
}
// 合并登记信息表头subList2 = {ArrayList@20139} size = 4
ExcelExportEntity registerBaseHead2 = new ExcelExportEntity("申请回归登记信息", "registerRegressInfo");
registerBaseHead2.setList(subList2);
allHead.add(registerBaseHead2);
} else {
List<ExcelExportEntity> subList1 = new ArrayList<>();
LambdaQueryWrapper<SybasesetEntity> wrapper1 = Wrappers.lambdaQuery(SybasesetEntity.class).eq(SybasesetEntity::getType, "head_register_" + type).eq(SybasesetEntity::getStatus, BusinessConstant.STATUS_P).orderByAsc(SybasesetEntity::getIdx);
List<SybasesetEntity> list1 = sybasesetMapper.selectList(wrapper1);
for (SybasesetEntity entity : list1) {
ExcelExportEntity export = new ExcelExportEntity(entity.getName(), "register_" + entity.getCode(), 20);
subList1.add(export);
}
// 合并登记信息表头
ExcelExportEntity registerBaseHead = new ExcelExportEntity("登记信息", "registerInfo");
registerBaseHead.setList(subList1);
allHead.add(registerBaseHead);
}
return allHead;
}
/**
* 获取导出数据
*
* @param type
* @return
*/
public List<Map<String, Object>> getData(String type, Map<String, Object> queryParam, HttpServletRequest request) {
List<Map<String, Object>> returnList = new ArrayList<>();
// 根据核验对象 查询核验列表
VerifyRecordObjectListParam param = new VerifyRecordObjectListParam();
param.setSygatesentryfk((String) queryParam.get("sygatesentryfk"));
param.setName((String) queryParam.get("name"));
param.setOufk((String) queryParam.get("oufk"));
param.setOuname((String) queryParam.get("ouname"));
param.setInverifytime_start((String) queryParam.get("inverifytime_start"));
param.setInverifytime_end((String) queryParam.get("inverifytime_end"));
param.setInguidername((String) queryParam.get("inguidername"));
param.setOutverifytime_start((String) queryParam.get("outverifytime_start"));
param.setOutverifytime_end((String) queryParam.get("outverifytime_end"));
param.setOutguidername((String) queryParam.get("outguidername"));
List<String> list = new ArrayList<>();
list.add(type);
param.setTypes(list);
param.setPage(1);
param.setLimit(-1);
IPage<VerifyRecordObjectVO> page = queryListForVerifyObject(Condition.getPage(param), param, request);
List<VerifyRecordObjectVO> records = page.getRecords();
if (!CollectionUtils.isEmpty(records)) {
Map<String, Object> map = new HashMap<>();
// 获取核验信息
List<Map<String, Object>> list1 = new ArrayList<>();
List<Map<String, Object>> list2 = new ArrayList<>();
List<Map<String, Object>> list3 = new ArrayList<>();
List<Map<String, Object>> list4 = new ArrayList<>();
for (VerifyRecordObjectVO vo : records) {
Map<String, Object> map1 = new HashMap<>();
// 获取 vo 的字段名和字段值
Field[] fields = vo.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true); // 如果字段是私有的,需要设置为可访问
try {
map1.put("verify_" + field.getName(), field.get(vo));
log.info("Field name: " + field.getName() + ",Field value: " + field.get(vo));
} catch (IllegalAccessException e) {
log.error("map.put(field.getName(), field.get(vo)); 异常", e);
}
}
list1.add(map1);
JSONObject voucherdetail = vo.getVoucherdetail();
Map<String, Object> map2 = new HashMap<>();
Map<String, Object> map3 = new HashMap<>();
Map<String, Object> map4 = new HashMap<>();
if (voucherdetail != null) {
for (String key : voucherdetail.keySet()) {
if ("drugpersonleave".equals(vo.getVouchertype())) {
map3.put("register_leave_" + key, voucherdetail.get(key));
if (key.equals("processInstanceId")) {
map3.put("register_leave_" + "processlog", createProcesslog(voucherdetail.getString("processInstanceId")));
}
if ("type".equals(key)) {
if ("L".equals(voucherdetail.get(key))) {
map3.put("register_leave_" + key, "离所");
} else if ("TL".equals(voucherdetail.get(key))) {
map3.put("register_leave_" + key, "临时离所");
} else if ("trans".equals(voucherdetail.get(key))) {
map3.put("register_leave_" + key, "转运");
} else if ("nottrans".equals(voucherdetail.get(key))) {
map3.put("register_leave_" + key, "非转运");
}
}
} else if ("drugpersonregress".equals(vo.getVouchertype())) {
map4.put("register_regress_" + key, voucherdetail.get(key));
if (key.equals("processInstanceId")) {
map4.put("register_regress_" + "processlog", createProcesslog(voucherdetail.getString("processInstanceId")));
}
} else {
map2.put("register_" + key, voucherdetail.get(key));
if (key.equals("processInstanceId")) {
map2.put("register_" + "processlog", createProcesslog(voucherdetail.getString("processInstanceId")));
}
}
}
}
list2.add(map2);
list3.add(map3);
list4.add(map4);
}
// 获取登记信息
map.put("verifyInfo", list1);
map.put("registerInfo", list2);
map.put("registerLeaveInfo", list3);
map.put("registerRegressInfo", list4);
returnList.add(map);
}
return returnList;
}
字段名加类型前缀并存储为 数据字典:
效果:
根据动态字段导出的多sheet文件