Maven
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
excel导出处理模块
@Override
public ResultMsg export(SatisfactionTemplateExportDTO search, HttpServletRequest request, HttpServletResponse response) {
try {
PageInfo<SatisfactionOrderPageResqVO> userList=hosSatisfactionOrderService.findPage(search);
if (userList.getList().size()==0){
return new ResultMsg(false,"没有数据导出");
}
HosSatisfactionQuestionExample example=new HosSatisfactionQuestionExample();
example.createCriteria().andSatisfactionIdEqualTo(search.getId());
List<HosSatisfactionQuestion> questionList=hosSatisfactionQuestionService.selectByExample(example);
logger.info("导出问题列表:{}",JSON.toJSONString(questionList));
//表头
List<List<String>> head = executeHead(questionList);
//导出数据容器
ArrayList<HashMap<Integer, String>> dataList = new ArrayList<>();
userList.getList().forEach(user->{
HashMap<Integer, String> detailShuled = new HashMap<>();
if(!StringUtils.isEmpty(user.getAccountPhone())){
try {
user.setAccountPhone(AesUtils.decrypt(user.getAccountPhone(), dbAesConfig.getAesKey()));
} catch (Exception e) {
logger.error("解密电话号码失败:{}",user.getAccountPhone());
}
}
// 创建一个SimpleDateFormat对象,指定日期/时间格式
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
detailShuled.put(0, user.getAccountPhone());
detailShuled.put(1, formatter.format(user.getCreaterTime()));
for (int i = 0; i < questionList.size(); i++){
if (questionList.get(i).getQuestionType()== NumberConstant.THREE.getNumber()){
SatisfactionCommitAnswerExportResqVO completionQuestionAnswer= hosSatisfactionCommitRecondService.getCompletionQuestionAnswer(user.getId(),questionList.get(i).getId());
detailShuled.put(i+2, completionQuestionAnswer!=null?completionQuestionAnswer.getContextValue():"");
}else {
StringBuilder contextValueBuilder = new StringBuilder();
List<SatisfactionCommitAnswerExportResqVO> choiceQuestionAnswer= hosSatisfactionCommitRecondService.getChoiceQuestionAnswer(user.getId(),questionList.get(i).getId());
choiceQuestionAnswer.forEach(item->{
contextValueBuilder.append(item.getContextValue()).append(';');
});
detailShuled.put(i+2, contextValueBuilder.length() > 0
? contextValueBuilder.substring(0, contextValueBuilder.length() - 1)
: "");
}
}
dataList.add(detailShuled);
logger.info("导出表头列表:{}",JSON.toJSONString(detailShuled));
});
logger.info("导出表头列表:{}",JSON.toJSONString(head));
logger.info("导出问题列表:{}",JSON.toJSONString(dataList));
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("满意度调查.xlsx", "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
EasyExcel.write(response.getOutputStream())
//自适应宽度
.registerWriteHandler(new ExcelCellWriteWidthConfig())
// 这里放入动态头
.head(head).sheet("Sheet1").doWrite(dataList);
} catch (Exception e) {
e.printStackTrace();
logger.error("导出问卷数据异常:{}", e.getMessage(), e);
}
return new ResultMsg(true,"数据导出成功!");
}
计算列宽
package com.qhjk.gyzx.admin.service.utils;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
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;
/**
* @ClassName CustomCellWriteWidthConfig
* @Description TODD
* @Author luwei
* @Date 2024/4/19 17:07
**/
public class ExcelCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
// 单元格文本长度大于60换行
if (columnWidth >= 0) {
if (columnWidth > 60) {
columnWidth = 60;
}
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<WriteCellData<?>> 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;
}
}
}
}
}
示例