导入依赖(pom.xml)
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
导出excel(serviceImpl)
@Override
public void goodsInfoDownload() {
List<GoodsInfoDto> list=goodsMapper.selectGoodsInfo();
SimpleDateFormat formatter= new SimpleDateFormat("yyyyMMdd");
Date date = new Date(System.currentTimeMillis());
String excelName1=formatter.format(date)+"商品明细.xlsx";
String excelName="H:\\"+excelName1;
ExcelWriter excelWriter=null;
try {
excelWriter=EasyExcel
.write(excelName,GoodsInfoDto.class)
//这个是我自己写的一个类，主要用来限制excel的列宽在5~30之间（方法在下面），这个可以不要
.registerWriteHandler(new MyExcelHandler())
.build();
WriteSheet writeSheet=EasyExcel.writerSheet("goods").build();
excelWriter.write(list,writeSheet);
} finally {
if (excelWriter!=null){
excelWriter.finish();
}
}
}
限制列宽（config）
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 java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyExcelHandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 30;
//因为在自动列宽的过程中，有些设置地方让列宽显得紧凑，所以做出了个判断
private static final int MIN_COLUMN_WIDTH = 5;
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
public void Custemhandler() {
}
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
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 > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}else {
if(columnWidth<MIN_COLUMN_WIDTH){
columnWidth =MIN_COLUMN_WIDTH;
}
}
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* 256);
}
}
}
}
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData 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;
}
}
}
}
}
参考文章：https://blog.csdn.net/weixin_44811578/article/details/107101248?spm=1001.2014.3001.5506
