EasyExcel导入导出样式、自适应列宽、自适应行高
·
一、先加依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
二、话不多说,直接先看导出样式设置,可以根据自己实际情况调整:
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
public class EasyExcelUtils {
/**
* 设置excel样式
*/
public static HorizontalCellStyleStrategy getStyleStrategy() {
// 头的策略 样式调整
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 头背景 浅绿
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
// 头字号
headWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
headWriteFont.setFontName("宋体");
headWriteCellStyle.setWriteFont(headWriteFont);
// 自动换行
headWriteCellStyle.setWrapped(true);
// 设置细边框
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
// 设置边框颜色 25灰度
headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 内容的策略 宋体
WriteCellStyle contentStyle = new WriteCellStyle();
// 设置垂直居中
contentStyle.setWrapped(true);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置 水平居中
// contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont contentWriteFont = new WriteFont();
// 内容字号
contentWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
contentWriteFont.setFontName("宋体");
contentStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);
}
}
三、自适应列宽
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.collections.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;
public class CustomCellWriteWidthConfig 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;
}
}
}
}
}
四、自适应行高
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import java.util.Iterator;
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行高度
int maxHeight = 1;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getCellTypeEnum() == CellType.STRING) {
String value = cell.getStringCellValue();
int len = value.length();
int num = 0;
if (len > 50) {
num = len % 50 > 0 ? len / 50 : len / 2 - 1;
}
if (num > 0) {
for (int i = 0; i < num; i++) {
value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i);
}
}
if (value.contains("\n")) {
int length = value.split("\n").length;
maxHeight = Math.max(maxHeight, length) + 1;
}
}
}
row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));
}
}
五、导出
- 先创建导出实体类
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.terton.aisp.ssp.entity.Feedback;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelIgnoreUnannotated // 只导出带有ExcelProperty注解的字段
public class ExcelFeedbackVO extends Feedback {
/**
* 站点ID
*/
// 设置表头名称 ,索引最好带上,不会出问题。
@ExcelProperty(value = "站点id", index = 0)
private String siteId;
/**
* 搜索内容
*/
@ExcelProperty(value = "搜索内容", index = 1)
private String title;
/**
* 反馈内容
*/
@ExcelProperty(value = "反馈内容", index = 2)
private String content;
/**
* IP地址
*/
@ExcelProperty(value = "搜索IP", index = 3)
private String ip;
}
2、创建一个对外暴露的接口。
@PostMapping("/downloadBack")
public void downloadBack(HttpServletResponse response, String siteId, Integer content) throws TException, IOException {
List<Feedback> list = feedbackService.searchBack(siteId, content);
setResponse(response, "列表");
EasyExcel.write(response.getOutputStream(), ExcelFeedbackVO.class /* 引用需要导出的字段*/)
.sheet("列表")
.registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/
.registerWriteHandler(new CustomCellWriteHeighConfig()) /*自适应行高(根据自己情况选择使用,我这里没用到)*/
.registerWriteHandler(EasyExcelUtils.getStyleStrategy()) /*引用样式*/
.doWrite(list); /* 想到导出模板的话,此处给一个空的集合就好*/
}
3、设置响应体信息(由于我这个项目导入导出比较多,把此方法变成公共方法)
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
public class SetResponse {
/**
* 导出设置响应信息
*
* @param response 响应
*/
public static void setResponse(HttpServletResponse response,String fileName) {
// 文件名
String sheetName = URLEncoder.encode(fileName, StandardCharsets.UTF_8) + ".xlsx";
// contentType 响应内容的类型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// 设置字符
response.setCharacterEncoding("utf-8");
// 设置文件名
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + sheetName);
}
}
4、访问接口
模板
正常导出
六、导入
1、监听器(关键)
注意事项:监听器里面不能使用注入的形式将service注入进来,所以可以使用构造器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.terton.aisp.common.enums.ExcelNameModelEnum;
import com.terton.aisp.common.util.PingYinUtil;
import com.terton.aisp.word.entity.MassWord;
import com.terton.aisp.word.enums.UploadBatchCountEnum;
import com.terton.aisp.word.service.IMassWordService;
import com.terton.aisp.word.service.impl.MassWordServiceImpl;
import com.terton.aisp.word.vo.ExcelMassWordModelVO;
import com.terton.framework.util.StringUtil;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
@Slf4j
@AllArgsConstructor
@NoArgsConstructor
public class UploadMassWordListener extends AnalysisEventListener<ExcelMassWordModelVO> {
private IMassWordService massWordService;
private String siteId;
private Integer wordLevel;
private String userName;
private List<ExcelMassWordModelVO> list = new ArrayList<>();
private List<String> massWordList = new ArrayList<>();
private List<String> massWords = new ArrayList<>();
private Integer successNum = 0;
private Integer errorNum = 0;
/**
* 表头校验 此方法里面可进行对导入的excel的格式进行判断,先执行
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
// headMap 表头数据,拿出进行判断就好
}
/**
* 每读一行触发一次
*
* @param modelVO ”
* @param analysisContext “
*/
@Override
public void invoke(ExcelMassWordModelVO modelVO, AnalysisContext analysisContext) {
log.info("读取到一条数据:{}",modelVO)
// 可以对数据进行判断是否有效。 根据自己的实际业务逻辑判断,
// 判断完成的数据可以用一个List来接收
// 为了防止数据过多,数据一直存在内存中,这里可以设置一个阈值,当list中的数据超过这个值,就先把这批数据添加到数据库。
if (StringUtil.notBlankAndNull(modelVO.getMassWord()) && StringUtil.notBlankAndNull(modelVO.getTargetWord())) {
if (!CollectionUtils.isEmpty(massWords) && massWords.contains(modelVO.getMassWord())) {
throw new RuntimeException(ExcelNameModelEnum.FailImport.RESULT_FIELD.getText() + modelVO.getMassWord());
}
massWords.add(modelVO.getMassWord());
massWordList.add(modelVO.getMassWord());
list.add(modelVO);
try {
if (list.size() >= UploadBatchCountEnum.MASS_WORD.getCount() /*我这里阈值设置的是500*/) {
save();
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
}
/**
* 完毕触发
*
* @param analysisContext ”
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 写数据库
if (!CollectionUtils.isEmpty(list)) {
save();
}
massWords.clear();
log.info("成功写入数量:{}", successNum);
log.info("失败写入数量:{}", errorNum);
successNum = 0;
errorNum = 0;
}
private void save() {
try {
List<MassWord> recordList = massWordService.getByMasswordsAndSiteId(massWordList, siteId);
if (!CollectionUtils.isEmpty(recordList)) {
List<String> collect = recordList.stream().map(MassWord::getMassWord).collect(Collectors.toList());
if (!CollectionUtils.isEmpty(collect)) {
throw new RuntimeException(ExcelNameModelEnum.FailImport.RESULT_FIELD.getText() + collect);
}
}
for (ExcelMassWordModelVO wordModelVO : list) {
MassWord newMassWord = getMassWord(wordModelVO);
massWordService.save(newMassWord);
successNum += 1;
}
} catch (Exception e) {
errorNum += 1;
throw new RuntimeException(e.getMessage());
}
massWordList.clear();
list.clear();
}
private MassWord getMassWord(ExcelMassWordModelVO wordModelVO) {
MassWord massWord = new MassWord();
massWord.setMassWord(wordModelVO.getMassWord());
massWord.setTargetWord(wordModelVO.getTargetWord());
massWord.setWordLevel(wordLevel);
massWord.setSiteId(siteId);
massWord.setAcronym(PingYinUtil.getFirstSpell(wordModelVO.getMassWord()));
massWord.setFullPinYin(PingYinUtil.getFullSpell(wordModelVO.getMassWord()));
massWord.setCrTime(new Date());
massWord.setCrUser(userName);
return massWord;
}
public UploadMassWordListener(MassWordServiceImpl massWordService, String siteId,Integer wordLevel, String userName) {
this.massWordService = massWordService;
this.siteId = siteId;
this.wordLevel = wordLevel;
this.userName = userName;
}
}
2、导入与监听器的使用。
public void upload(MultipartFile file, String siteId, Integer wordLevel,String userName) throws IOException {
UploadMassWordListener listener = new UploadMassWordListener(this, siteId, wordLevel, userName);
EasyExcel.read(file.getInputStream(), ExcelMassWordModelVO.class, listener)
.sheet(0)
.headRowNumber(1)
.doRead();
}
到这里大概就结束了,希望对你有用。
更多推荐
已为社区贡献2条内容
所有评论(0)