From 17c81b57ec33fc692ed1499cda86bb01f1fe65a0 Mon Sep 17 00:00:00 2001
From: zhongrj <646384940@qq.com>
Date: Fri, 17 Nov 2023 16:52:27 +0800
Subject: [PATCH] 房屋数据导出字典翻译修改
---
src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java | 83 ++++++++
src/main/java/org/springblade/common/utils/SpringUtils.java | 49 ++++
src/main/java/org/springblade/modules/house/controller/HouseholdController.java | 4
src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.java | 5
src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml | 60 ++---
src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java | 49 ++++
src/main/java/org/springblade/common/excel/MySheetWriteHandler.java | 45 ++++
src/main/java/org/springblade/common/excel/ExcelDictItem.java | 24 ++
src/main/java/org/springblade/common/excel/ExcelDictConverter.java | 65 ++++++
src/main/java/org/springblade/common/excel/ExcelDictItemLabel.java | 24 ++
src/main/java/org/springblade/modules/house/excel/HouseHoldExcel.java | 97 +++------
src/main/java/org/springblade/common/excel/RowWriteHandler.java | 59 +++++
12 files changed, 465 insertions(+), 99 deletions(-)
diff --git a/src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java b/src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java
new file mode 100644
index 0000000..5530055
--- /dev/null
+++ b/src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java
@@ -0,0 +1,49 @@
+package org.springblade.common.excel;
+
+import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+
+import java.util.Iterator;
+
+/**
+ * 自适应行高
+ * @author zhongrj
+ * @since 2021-10-29
+ */
+public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
+ /**
+ * 默认高度
+ */
+ private static final Integer DEFAULT_HEIGHT = 300;
+
+ @Override
+ protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
+ }
+
+ @Override
+ protected void setContentColumnHeight(Row row, int relativeRowIndex) {
+ Iterator<Cell> cellIterator = row.cellIterator();
+ if (!cellIterator.hasNext()) {
+ return;
+ }
+
+ // 默认为 1行高度
+ Integer maxHeight = 1;
+ while (cellIterator.hasNext()) {
+ Cell cell = cellIterator.next();
+ switch (cell.getCellTypeEnum()) {
+ case STRING:
+ if (cell.getStringCellValue().indexOf("\n") != -1) {
+ int length = cell.getStringCellValue().split("\n").length;
+ maxHeight = Math.max(maxHeight, length);
+ }
+ break;
+ default:
+ break;
+ }
+ }
+
+ row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
+ }
+}
diff --git a/src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java b/src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java
new file mode 100644
index 0000000..c8ec134
--- /dev/null
+++ b/src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java
@@ -0,0 +1,83 @@
+package org.springblade.common.excel;
+
+import com.alibaba.excel.enums.CellDataTypeEnum;
+import com.alibaba.excel.metadata.CellData;
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.util.CollectionUtils;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * 自适应行款
+ * @author zhongrj
+ * @since 2021-10-29
+ */
+public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {
+ private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
+
+ @Override
+ protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
+ boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
+ if (needSetWidth) {
+ Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
+ if (maxColumnWidthMap == null) {
+ maxColumnWidthMap = new HashMap<>();
+ CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
+ }
+
+ Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
+ if (columnWidth >= 0) {
+ if (columnWidth > 254) {
+ columnWidth = 254;
+ }
+
+ Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
+ if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
+ maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
+ Sheet sheet = writeSheetHolder.getSheet();
+ sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
+ }
+
+ }
+ }
+ }
+
+ /**
+ * 计算长度
+ * @param cellDataList
+ * @param cell
+ * @param isHead
+ * @return
+ */
+ private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
+ if (isHead) {
+ return cell.getStringCellValue().getBytes().length;
+ } else {
+ CellData cellData = cellDataList.get(0);
+ CellDataTypeEnum type = cellData.getType();
+ if (type == null) {
+ return -1;
+ } else {
+ switch (type) {
+ case STRING:
+ // 换行符（数据需要提前解析好）
+ int index = cellData.getStringValue().indexOf("\n");
+ return index != -1 ?
+ cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
+ case BOOLEAN:
+ return cellData.getBooleanValue().toString().getBytes().length;
+ case NUMBER:
+ return cellData.getNumberValue().toString().getBytes().length;
+ default:
+ return -1;
+ }
+ }
+ }
+ }
+}
diff --git a/src/main/java/org/springblade/common/excel/ExcelDictConverter.java b/src/main/java/org/springblade/common/excel/ExcelDictConverter.java
new file mode 100644
index 0000000..6fe4f99
--- /dev/null
+++ b/src/main/java/org/springblade/common/excel/ExcelDictConverter.java
@@ -0,0 +1,65 @@
+package org.springblade.common.excel;
+
+import com.alibaba.excel.converters.Converter;
+import com.alibaba.excel.enums.CellDataTypeEnum;
+import com.alibaba.excel.metadata.CellData;
+import com.alibaba.excel.metadata.GlobalConfiguration;
+import com.alibaba.excel.metadata.property.ExcelContentProperty;
+import org.springblade.common.utils.SpringUtils;
+import org.springblade.modules.system.entity.DictBiz;
+import org.springblade.modules.system.service.IDictBizService;
+import org.springframework.stereotype.Component;
+
+import java.lang.reflect.Field;
+import java.util.List;
+
+/**
+ * 字典映射
+ *
+ * @author zhongrj
+ * @date 2023-11-17
+ */
+@Component
+public class ExcelDictConverter implements Converter<String> {
+
+ @Override
+ public Class supportJavaTypeKey() {
+ return Integer.class;
+ }
+
+ @Override
+ public CellDataTypeEnum supportExcelTypeKey() {
+ return CellDataTypeEnum.STRING;
+ }
+
+ @Override
+ public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
+ return null;
+ }
+
+ /**
+ * java 导出到 excel
+ * @param dictValue
+ * @param excelContentProperty
+ * @param globalConfiguration
+ * @return
+ * @throws Exception
+ */
+ @Override
+ public CellData convertToExcelData(String dictValue, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
+ // 获取字典类型
+ Field field = excelContentProperty.getField();
+ ExcelDictItem excel = field.getAnnotation(ExcelDictItem.class);
+ String dictType = excel.type();
+ IDictBizService dictBizService = SpringUtils.getBean(IDictBizService.class);
+ List<DictBiz> list = dictBizService.getList(dictType);
+ String value = "";
+ //解析返回
+ for (DictBiz dictBiz : list) {
+ if (dictBiz.getDictKey().equals(dictValue)){
+ value = dictBiz.getDictValue();
+ }
+ }
+ return new CellData(value);
+ }
+}
diff --git a/src/main/java/org/springblade/common/excel/ExcelDictItem.java b/src/main/java/org/springblade/common/excel/ExcelDictItem.java
new file mode 100644
index 0000000..23656d2
--- /dev/null
+++ b/src/main/java/org/springblade/common/excel/ExcelDictItem.java
@@ -0,0 +1,24 @@
+package org.springblade.common.excel;
+
+import com.fasterxml.jackson.annotation.JacksonAnnotationsInside;
+import java.lang.annotation.ElementType;
+import java.lang.annotation.Retention;
+import java.lang.annotation.RetentionPolicy;
+import java.lang.annotation.Target;
+
+/**
+ * excel导出字典转换注解
+ * <p>
+ * 将excel导出的字典code自动转换为字典label
+ */
+@Retention(RetentionPolicy.RUNTIME)
+@Target(ElementType.FIELD)
+@JacksonAnnotationsInside
+public @interface ExcelDictItem {
+
+ /**
+ * 字典type
+ */
+ String type();
+
+}
diff --git a/src/main/java/org/springblade/common/excel/ExcelDictItemLabel.java b/src/main/java/org/springblade/common/excel/ExcelDictItemLabel.java
new file mode 100644
index 0000000..138c76c
--- /dev/null
+++ b/src/main/java/org/springblade/common/excel/ExcelDictItemLabel.java
@@ -0,0 +1,24 @@
+package org.springblade.common.excel;
+
+import com.fasterxml.jackson.annotation.JacksonAnnotationsInside;
+import java.lang.annotation.ElementType;
+import java.lang.annotation.Retention;
+import java.lang.annotation.RetentionPolicy;
+import java.lang.annotation.Target;
+
+/**
+ * excel导入字典转换注解
+ * <p>
+ * 将excel导入的字典label自动转换为字典code
+ */
+@Retention(RetentionPolicy.RUNTIME)
+@Target(ElementType.FIELD)
+@JacksonAnnotationsInside
+public @interface ExcelDictItemLabel {
+
+ /**
+ * 字典type
+ */
+ String type();
+
+}
diff --git a/src/main/java/org/springblade/common/excel/MySheetWriteHandler.java b/src/main/java/org/springblade/common/excel/MySheetWriteHandler.java
new file mode 100644
index 0000000..b614835
--- /dev/null
+++ b/src/main/java/org/springblade/common/excel/MySheetWriteHandler.java
@@ -0,0 +1,45 @@
+package org.springblade.common.excel;
+
+import com.alibaba.excel.write.handler.SheetWriteHandler;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+public class MySheetWriteHandler implements SheetWriteHandler {
+ @Override
+ public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
+
+ }
+
+ @Override
+ public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
+ Workbook workbook = writeWorkbookHolder.getWorkbook();
+ Sheet sheet = workbook.getSheetAt(0);
+ Row row1 = sheet.createRow(0);
+ row1.setHeight((short) 500);
+ Cell cell = row1.createCell(0);
+ //设置单元格内容
+ cell.setCellValue("附件2");
+ //设置标题
+ Row row2 = sheet.createRow(1);
+ row2.setHeight((short) 800);
+ Cell cell1 = row2.createCell(0);
+ cell1.setCellValue("存量建筑垃圾堆体治理进度月报表");
+ CellStyle cellStyle = workbook.createCellStyle();
+ cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ cellStyle.setAlignment(HorizontalAlignment.CENTER);
+ Font font = workbook.createFont();
+ font.setBold(true);
+ font.setFontHeight((short) 400);
+ cellStyle.setFont(font);
+ cell1.setCellStyle(cellStyle);
+ sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 17));
+ //设置填表日期,填报人,联系方式
+ Row row3 = sheet.createRow(2);
+ row3.setHeight((short) 500);
+ row3.createCell(1).setCellValue("填表日期");
+ row3.createCell(11).setCellValue("填表人");
+ row3.createCell(15).setCellValue("联系方式");
+ }
+}
diff --git a/src/main/java/org/springblade/common/excel/RowWriteHandler.java b/src/main/java/org/springblade/common/excel/RowWriteHandler.java
new file mode 100644
index 0000000..38a7282
--- /dev/null
+++ b/src/main/java/org/springblade/common/excel/RowWriteHandler.java
@@ -0,0 +1,59 @@
+package org.springblade.common.excel;
+
+import com.alibaba.excel.metadata.CellData;
+import com.alibaba.excel.metadata.Head;
+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.poi.ss.usermodel.*;
+import org.springblade.core.tool.utils.Func;
+
+import java.util.List;
+
+/**
+ * 修改单元格格式为文本格式
+ * @author zhongrj
+ * @since 2021-9-26
+ */
+public class RowWriteHandler implements CellWriteHandler {
+
+ public static CellStyle cellStyle;
+
+ /**
+ * 设置全局变量，防止 cellStyle 创建过多报错 2021-12-16 zrj
+ * The maximum number of cell styles was exceeded. You can define up to 64000styles in a .xlsx workbook
+ * @param cellStyle
+ */
+ public static void setCellStyle(CellStyle cellStyle) {
+ RowWriteHandler.cellStyle = cellStyle;
+ }
+
+ @Override
+ public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
+
+ }
+
+ @Override
+ public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+ //设置单元格格式为文本
+ Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
+ //自定义样式不为空时,重复利用
+ if(Func.isEmpty(cellStyle)){
+ cellStyle = workbook.createCellStyle();
+ setCellStyle(cellStyle);
+ }
+ DataFormat dataFormat = workbook.createDataFormat();
+ cellStyle.setDataFormat(dataFormat.getFormat("@"));
+ cell.setCellStyle(cellStyle);
+ }
+
+ //@Override 加上会报错
+ public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+
+ }
+
+ @Override
+ public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+
+ }
+}
diff --git a/src/main/java/org/springblade/common/utils/SpringUtils.java b/src/main/java/org/springblade/common/utils/SpringUtils.java
new file mode 100644
index 0000000..aba14a7
--- /dev/null
+++ b/src/main/java/org/springblade/common/utils/SpringUtils.java
@@ -0,0 +1,49 @@
+package org.springblade.common.utils;
+
+import org.springframework.beans.BeansException;
+import org.springframework.context.ApplicationContext;
+import org.springframework.context.ApplicationContextAware;
+import org.springframework.stereotype.Component;
+
+/**
+ * spring 工具类
+ * @author zhongrj
+ */
+@Component
+public class SpringUtils implements ApplicationContextAware {
+
+
+ private static ApplicationContext applicationContext;
+
+ @Override
+ public void setApplicationContext(ApplicationContext applicationContext)throws BeansException {
+ if (SpringUtils.applicationContext == null) {
+ SpringUtils.applicationContext = applicationContext;
+ }
+ }
+
+ public static ApplicationContext getApplicationContext() {
+ return applicationContext;
+ }
+
+
+ public static Object getBean(String name) {
+ return getApplicationContext().getBean(name);
+ }
+
+
+ public static <T> T getBean(Class<T> clazz) {
+ return getApplicationContext().getBean(clazz);
+ }
+
+ public static <T> T getBean(String name, Class<T> clazz) {
+ return getApplicationContext().getBean(name, clazz);
+ }
+
+ /**
+ * 获取当前环境
+ */
+ public static String getActiveProfile() {
+ return applicationContext.getEnvironment().getActiveProfiles()[0];
+ }
+}
diff --git a/src/main/java/org/springblade/modules/house/controller/HouseholdController.java b/src/main/java/org/springblade/modules/house/controller/HouseholdController.java
index 3b99f31..a7b0e72 100644
--- a/src/main/java/org/springblade/modules/house/controller/HouseholdController.java
+++ b/src/main/java/org/springblade/modules/house/controller/HouseholdController.java
@@ -166,7 +166,7 @@
/**
* 导入用户
*/
- @PostMapping("import-houseHold")
+ @PostMapping("import-household")
@ApiOperationSupport(order = 12)
@ApiOperation(value = "导入住户", notes = "传入excel")
public R importUser(MultipartFile file, Integer isCovered) {
@@ -178,7 +178,7 @@
/**
* 导出用户
*/
- @GetMapping("export-houseHold")
+ @GetMapping("export-household")
@ApiOperationSupport(order = 13)
@ApiOperation(value = "导出住户", notes = "传入user")
public void exportUser(HouseholdVO household, HttpServletResponse response) {
diff --git a/src/main/java/org/springblade/modules/house/excel/HouseHoldExcel.java b/src/main/java/org/springblade/modules/house/excel/HouseHoldExcel.java
index 99d6ac1..74bfac9 100644
--- a/src/main/java/org/springblade/modules/house/excel/HouseHoldExcel.java
+++ b/src/main/java/org/springblade/modules/house/excel/HouseHoldExcel.java
@@ -1,12 +1,14 @@
package org.springblade.modules.house.excel;
-import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
+import org.springblade.common.excel.ExcelDictConverter;
+import org.springblade.common.excel.ExcelDictItem;
+import org.springblade.common.excel.ExcelDictItemLabel;
import java.io.Serializable;
import java.util.Date;
@@ -24,17 +26,6 @@
private static final long serialVersionUID = 2L;
-
- /** 主键id */
- @ColumnWidth(15)
- @ExcelProperty( "主键ID")
- private Long id;
-
- /** 门牌地址编码 */
- @ColumnWidth(15)
- @ExcelProperty( "门牌地址编码")
- private String houseCode;
-
/** 姓名 */
@ColumnWidth(15)
@ExcelProperty( "姓名")
@@ -45,40 +36,40 @@
@ExcelProperty( "手机号")
private String phoneNumber;
- /** 绑定用户ID */
+ /** 小区名称 */
@ColumnWidth(15)
- @ExcelProperty( "绑定用户ID")
- private Long associatedUserId;
+ @ExcelProperty( "小区名称")
+ private String aoiName;
/** 角色 */
@ColumnWidth(15)
- @ExcelProperty( "角色 ")
- private Integer roleType;
-
- /** 绑定用户名称 */
- @ColumnWidth(15)
- @ExcelProperty( "绑定用户名称")
- private String associatedUserName;
+ @ExcelProperty( value = "角色",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "roleType")
+ private String roleType;
/** 与角色关系（业主，父子，其他） */
@ColumnWidth(15)
- @ExcelProperty( "与角色关系（业主，父子，其他）")
- private Integer relationship;
+ @ExcelProperty( value = "与角色关系",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "roleRelation")
+ private String relationship;
/** 是否主要联系人 1：是 0：否 */
@ColumnWidth(15)
- @ExcelProperty( "是否主要联系人 1：是 0：否")
- private Integer isPrimaryContact;
+ @ExcelProperty( value = "是否主要联系人",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "primaryContactType")
+ private String isPrimaryContact;
/** 居住状态 1: 是 0：否 */
@ColumnWidth(15)
- @ExcelProperty( "居住状态 1: 是 0：否")
- private Integer residentialStatus;
+ @ExcelProperty( value = "居住状态",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "residentialStatusType")
+ private String residentialStatus;
/** 性别 1: 男 0：女 2: 未知 */
@ColumnWidth(15)
- @ExcelProperty( "性别 1: 男 0：女 2: 未知")
- private Short gender;
+ @ExcelProperty( value = "性别",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "sex")
+ private String gender;
/** 生日 */
@ColumnWidth(15)
@@ -103,13 +94,15 @@
/** 民族 */
@ColumnWidth(15)
- @ExcelProperty( "民族")
- private Integer ethnicity;
+ @ExcelProperty( value = "民族",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "nationType")
+ private String ethnicity;
/** 学历 */
@ColumnWidth(15)
- @ExcelProperty( "学历")
- private Integer education;
+ @ExcelProperty( value = "学历",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "educationType")
+ private String education;
/** 户籍登记地 */
@ColumnWidth(15)
@@ -118,8 +111,9 @@
/** 工作状态 */
@ColumnWidth(15)
- @ExcelProperty( "工作状态")
- private Integer workStatus;
+ @ExcelProperty( value = "工作状态",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "workStatusType")
+ private String workStatus;
/** 工作单位 */
@ColumnWidth(15)
@@ -128,8 +122,9 @@
/** 婚姻状态 */
@ColumnWidth(15)
- @ExcelProperty( "婚姻状态")
- private Integer maritalStatus;
+ @ExcelProperty( value = "婚姻状态",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "marriageStatusType")
+ private String maritalStatus;
/** 车牌号 */
@ColumnWidth(15)
@@ -153,31 +148,9 @@
/** 是否党员 1：党员 2：群众 */
@ColumnWidth(15)
- @ExcelProperty( "是否党员 1：党员 2：群众")
+ @ExcelProperty( value = "是否党员",converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "partyEmberType")
private Integer partyEmber;
-
- /** 创建人 */
- @ColumnWidth(15)
- @ExcelProperty( "创建人")
- private Long createUser;
-
- /** 创建时间 */
- @ColumnWidth(15)
- @ExcelProperty( "创建时间")
- @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
- private Date createTime;
-
- /** 更新人 */
- @ColumnWidth(15)
- @ExcelIgnore
- @ExcelProperty( "更新人")
- private Long updateUser;
-
- /** 更新时间 */
- @ExcelProperty( "更新时间")
- @ColumnWidth(15)
- @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
- private Date updateTime;
/** 备注 */
@ColumnWidth(15)
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.java b/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.java
index bb34e55..f929bb2 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.java
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.java
@@ -65,6 +65,11 @@
*/
HouseholdVO getHouseholdListById(@Param("household") HouseholdEntity household);
+ /**
+ * 导出
+ * @param household
+ * @return
+ */
List<HouseHoldExcel> export(HouseholdVO household);
Integer statistics(Long userId);
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml b/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml
index 1a77b02..cb71fb9 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseholdMapper.xml
@@ -150,42 +150,32 @@
and jh.id = #{household.id}
</select>
+ <!--导出数据-->
<select id="export" resultType="org.springblade.modules.house.excel.HouseHoldExcel">
- <include refid="selectHousehold"/>
- <where>
- <if test="id != null "> and id = #{id}</if>
- <if test="houseCode != null and houseCode != ''"> and house_code = #{houseCode}</if>
- <if test="name != null and name != ''"> and name = #{name}</if>
- <if test="phoneNumber != null and phoneNumber != ''"> and phone_number = #{phoneNumber}</if>
- <if test="associatedUserId != null "> and associated_user_id = #{associatedUserId}</if>
- <if test="roleType != null "> and role_type = #{roleType}</if>
- <if test="associatedUserName != null and associatedUserName != ''"> and associated_user_name = #{associatedUserName}</if>
- <if test="relationship != null "> and relationship = #{relationship}</if>
- <if test="isPrimaryContact != null "> and is_primary_contact = #{isPrimaryContact}</if>
- <if test="residentialStatus != null "> and residential_status = #{residentialStatus}</if>
- <if test="gender != null "> and gender = #{gender}</if>
- <if test="birthday != null "> and birthday = #{birthday}</if>
- <if test="idCard != null and idCard != ''"> and id_card = #{idCard}</if>
- <if test="hkmtPass != null and hkmtPass != ''"> and hkmt_pass = #{hkmtPass}</if>
- <if test="passport != null and passport != ''"> and passport = #{passport}</if>
- <if test="ethnicity != null "> and ethnicity = #{ethnicity}</if>
- <if test="education != null "> and education = #{education}</if>
- <if test="hukouRegistration != null and hukouRegistration != ''"> and hukou_registration = #{hukouRegistration}</if>
- <if test="workStatus != null "> and work_status = #{workStatus}</if>
- <if test="employer != null and employer != ''"> and employer = #{employer}</if>
- <if test="maritalStatus != null "> and marital_status = #{maritalStatus}</if>
- <if test="cardNumber != null and cardNumber != ''"> and card_number = #{cardNumber}</if>
- <if test="otherContact != null and otherContact != ''"> and other_contact = #{otherContact}</if>
- <if test="currentAddress != null and currentAddress != ''"> and current_address = #{currentAddress}</if>
- <if test="disabilityCert != null and disabilityCert != ''"> and disability_cert = #{disabilityCert}</if>
- <if test="partyEmber != null "> and party_ember = #{partyEmber}</if>
- <if test="createUser != null "> and create_user = #{createUser}</if>
- <if test="createTime != null "> and create_time = #{createTime}</if>
- <if test="updateUser != null "> and update_user = #{updateUser}</if>
- <if test="updateTime != null "> and update_time = #{updateTime}</if>
- <if test="remark != null and remark != ''"> and remark = #{remark}</if>
- <if test="isDeleted != null "> and is_deleted = #{isDeleted}</if>
- </where>
+ select
+ jh.name,jh.phone_number phoneNumber,jh.role_type roleType,jh.relationship relationship,
+ jh.is_primary_contact isPrimaryContact,jh.residential_status residentialStatus,jh.gender,
+ jh.birthday,jh.id_card idCard,jh.hkmt_pass hkmtPass,jh.passport,jh.ethnicity,jh.education,
+ jh.hukou_registration hukouRegistration,jh.work_status workStatus,jh.employer,jh.marital_status maritalStatus,
+ jh.card_number cardNumber,jh.other_contact otherContact,jh.current_address currentAddress,
+ jh.disability_cert disabilityCert,jh.party_ember partyEmber,jh.remark,
+ jhs.district_name aoiName,
+ concat(jhs.building," ",unit," ",room) as address
+ from
+ jczz_household jh join jczz_house jhs on jh.house_code = jhs.house_code and jhs.is_deleted = 0
+ where jh.is_deleted = 0
+ <if test="household.name!=null and household.name !=''">
+ and jh.name like concat('%',#{household.name},'%')
+ </if>
+ <if test="household.phoneNumber!=null and household.phoneNumber !=''">
+ and jh.phone_number like concat('%',#{household.phoneNumber},'%')
+ </if>
+ <if test="household.idCard!=null and household.idCard !=''">
+ and jh.id_card like concat('%',#{household.idCard},'%')
+ </if>
+ <if test="household.aoiName!=null and household.aoiName !=''">
+ and jhs.district_name like concat('%',#{household.aoiName},'%')
+ </if>
</select>
--
Gitblit v1.9.3