From cb021d80b68aee4c16d6bd42c349308ad2c20e78 Mon Sep 17 00:00:00 2001
From: huanghongfa <huanghongfa123456>
Date: 星期五, 08 十月 2021 11:07:51 +0800
Subject: [PATCH] 调整预约登记明细导出excel格式
---
springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/excel/Custemhandler.java | 58 +++++++++++++++++++++++-----
springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/ComActReserveApi.java | 29 ++++++++++++--
springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/utlis/AssemblyUtils.java | 14 ++++++
3 files changed, 84 insertions(+), 17 deletions(-)
diff --git a/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/utlis/AssemblyUtils.java b/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/utlis/AssemblyUtils.java
index 8a870a7..eb24549 100644
--- a/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/utlis/AssemblyUtils.java
+++ b/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/utlis/AssemblyUtils.java
@@ -25,6 +25,8 @@
List<List<Object>> resultList = new ArrayList<>();
//构建单个用户数据
List<Object> userData = new ArrayList<>();
+
+ String noExport = "以上信息仅用于疫苗防控";
//遍历答案列表
Long reserveRecordId = 0L;
Long reserveSubId = 0L;
@@ -33,7 +35,8 @@
if (answerContentVOList != null && answerContentVOList.size() > 0) {
for (ComActQuestnaireAnswerContentVO userAnswers : answerContentVOList) {
//判断是文字描述直接跳过
- if (userAnswers.getOptionType().equals(5) || userAnswers.getOptionType().equals(11)) {
+ if (userAnswers.getOptionType().equals(5) || userAnswers.getOptionType().equals(11)
+ || (StringUtils.isNotEmpty(userAnswers.getAnswerContent()) && userAnswers.getAnswerContent().contains(noExport))) {
continue;
}
//判断reserveRecordId为空则为第一条记录,默认加上昵称和灯谜是
@@ -70,6 +73,11 @@
if(!reserveSubId.equals(userAnswers.getReserveSubId())){
reserveSubId = userAnswers.getReserveSubId();
+ //如果是承诺字段则不导出
+ if(StringUtils.isNotEmpty(userAnswers.getAnswerContent()) && userAnswers.getAnswerContent().contains(noExport)){
+ continue;
+ }
+
//判断当前的组件类型是否是图片上传，导出时需要导出图片
if(userAnswers.getOptionType().equals(11)){
reserveSubId = userAnswers.getReserveSubId();
@@ -99,6 +107,10 @@
userData.add(answer.substring(0, answer.length() - 1));
sb = new StringBuffer();
}else{
+ //如果是承诺字段则不导出
+ if(StringUtils.isNotEmpty(userAnswers.getAnswerContent()) && userAnswers.getAnswerContent().contains(noExport)){
+ continue;
+ }
//判断当前的组件类型是否是图片上传，导出时需要导出图片
if(userAnswers.getOptionType().equals(11)){
reserveSubId = userAnswers.getReserveSubId();
diff --git a/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/ComActReserveApi.java b/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/ComActReserveApi.java
index 3f38844..042ddcd 100644
--- a/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/ComActReserveApi.java
+++ b/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/ComActReserveApi.java
@@ -4,6 +4,7 @@
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
+import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
@@ -32,6 +33,8 @@
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
+import org.apache.poi.ss.usermodel.HorizontalAlignment;
+import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.*;
@@ -466,6 +469,7 @@
String ftpUrl = "/mnt/data/web/excel/";
String name = "登记明细导出数据.xlsx";
+ String noHaed = "承诺";
try {
SFTPUtil sftp = new SFTPUtil(sftpConfig.getUserName(), sftpConfig.getPassword(), sftpConfig.getHost(), sftpConfig.getPort());
sftp.login();
@@ -499,19 +503,34 @@
if (sub.getType() != 5 && sub.getType() != 11) {
List<String> headn = new ArrayList<>();
headn.add(firstRowContent);
- if(sub.getType() == 11){
- headn.add(sub.getContent().substring(0, sub.getContent().indexOf("（")));
- }else{
- headn.add(sub.getContent());
+ if(!sub.getContent().equals(noHaed)){
+ if(sub.getType() == 11){
+ headn.add(sub.getContent().substring(0, sub.getContent().indexOf("（")));
+ }else{
+ headn.add(sub.getContent());
+ }
+ headList.add(headn);
}
- headList.add(headn);
}
});
//数据转换
List<List<Object>> datalist = AssemblyUtils.ReserveData(questnaireAnswersDTOS.getAnswers());
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
+ //设置头居中
+ headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
+ //设置头字体
+ WriteFont headWriteFont = new WriteFont();
+ headWriteFont.setFontHeightInPoints((short)14);
+ headWriteFont.setBold(true);
+ headWriteCellStyle.setWriteFont(headWriteFont);
+
+
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
+ //设置 水平居中
+ contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
+ //设置 垂直居中
+ contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setWrapped(true);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
diff --git a/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/excel/Custemhandler.java b/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/excel/Custemhandler.java
index 519193a..9b37fd8 100644
--- a/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/excel/Custemhandler.java
+++ b/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/excel/Custemhandler.java
@@ -1,5 +1,6 @@
package com.panzhihua.community_backstage.excel;
+import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
@@ -7,30 +8,65 @@
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
+import org.springframework.util.CollectionUtils;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;
public class Custemhandler extends AbstractColumnWidthStyleStrategy {
- private static final int MAX_COLUMN_WIDTH = 100;
- //the maximum column width in Excel is 255 characters
+
+ private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
public Custemhandler() {
}
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
- if (isHead && cell.getRowIndex() != 0) {
- int columnWidth = cell.getStringCellValue().getBytes().length;
- if (columnWidth > MAX_COLUMN_WIDTH) {
- columnWidth = MAX_COLUMN_WIDTH;
- } else {
- columnWidth = columnWidth + 3;
+ boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
+ if (needSetWidth) {
+ Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
+ if (maxColumnWidthMap == null) {
+ maxColumnWidthMap = new HashMap(16);
+ CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
- writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
+ Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
+ if (columnWidth >= 0) {
+ if (columnWidth > 30) {
+ columnWidth = 30;
+ }
+
+ Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
+ if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
+ ((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
+ writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 90);
+ }
+
+ }
}
}
-
- private final static Logger logger = LoggerFactory.getLogger(Custemhandler.class);
+ 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:
+ return cellData.getStringValue().getBytes().length;
+ case BOOLEAN:
+ return cellData.getBooleanValue().toString().getBytes().length;
+ case NUMBER:
+ return cellData.getNumberValue().toString().getBytes().length;
+ default:
+ return -1;
+ }
+ }
+ }
+ }
}
\ No newline at end of file
--
Gitblit v1.7.1