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(); 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,6 +503,7 @@ if (sub.getType() != 5 && sub.getType() != 11) { List<String> headn = new ArrayList<>(); headn.add(firstRowContent); if(!sub.getContent().equals(noHaed)){ if(sub.getType() == 11){ headn.add(sub.getContent().substring(0, sub.getContent().indexOf("（"))); }else{ @@ -506,12 +511,26 @@ } 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 = 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; 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); } 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 Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { columnWidth = columnWidth + 3; } writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); 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; } } private final static Logger logger = LoggerFactory.getLogger(Custemhandler.class); } } }
