EasyExcel导出策略

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>3.3.2</version>
</dependency>
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel-core</artifactId>
	<version>3.3.2</version>
</dependency>

1.EasyExcel通用的格式策略工具类

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;

/**
 * EasyExcel通用的格式策略工具类
 * <p>设置了Excel的基础格式</p>
 * @author
 * @date 2024/9/2
 * @since JDK 17
 */
public class CommonCellStyleStrategy {
	/**
	 * 设置单元格样式
	 *
	 * @date 2024-09-02
	 * @return HorizontalCellStyleStrategy
	 * @since JDK 17
	 * @author
	 */
	public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
		// 表头策略
		WriteCellStyle headerCellStyle = new WriteCellStyle();
		// 表头水平对齐居中
		headerCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		// 背景色
		headerCellStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.getIndex());
		// 表头字体
		WriteFont headerFont = new WriteFont();
		// 字体大小（单位：磅）
		headerFont.setFontHeightInPoints((short) 15);

		headerCellStyle.setWriteFont(headerFont);
		// 自动换行
		headerCellStyle.setWrapped(true);

		// 内容策略
		WriteCellStyle contentCellStyle = new WriteCellStyle();
		// 设置背景色: 需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了
		// FillPatternType所以可以不指定
		//contentCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
		//contentCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
		// 设置内容靠左对齐
		contentCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
		// 设置字体
		WriteFont contentFont = new WriteFont();
		contentFont.setFontHeightInPoints((short) 12);
		contentCellStyle.setWriteFont(contentFont);
		// 设置自动换行
		contentCellStyle.setWrapped(Boolean.FALSE);
		// 设置边框样式和颜色
		contentCellStyle.setBorderLeft(BorderStyle.MEDIUM);
		contentCellStyle.setBorderTop(BorderStyle.MEDIUM);
		contentCellStyle.setBorderRight(BorderStyle.MEDIUM);
		contentCellStyle.setBorderBottom(BorderStyle.MEDIUM);
//		contentCellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
//		contentCellStyle.setBottomBorderColor(IndexedColors.GREEN.getIndex());
//		contentCellStyle.setLeftBorderColor(IndexedColors.YELLOW.getIndex());
//		contentCellStyle.setRightBorderColor(IndexedColors.ORANGE.getIndex());

		return new HorizontalCellStyleStrategy(headerCellStyle, contentCellStyle);
	}
}

2.精细化控制单元格内容

import java.util.List;

import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;

/**
 * 精细化控制单元格内容
 *
 * @author
 * @date 2024/9/2
 * @since JDK 17
 */
public class CustomCellWriteHandler implements CellWriteHandler {
	/**
	 * 创建单元格之前的操作
	 */
	@Override
	public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
			Head head, Integer integer, Integer integer1, Boolean aBoolean) {
	}

	/**
	 * 创建单元格之后的操作
	 */
	@Override
	public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
			Head head, Integer integer, Boolean aBoolean) {
	}

	/**
	 * 单元格内容转换之后的操作
	 */
	@Override
	public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
			WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
	}

	/**
	 * 单元格处理后(已写入值)的操作
	 */
	@Override
	public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
			List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
		// 设置超链接
		if (isHead && cell.getRowIndex() == 0 && cell.getColumnIndex() == 0) {
			CreationHelper helper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
			Hyperlink hyperlink = helper.createHyperlink(HyperlinkType.URL);
			hyperlink.setAddress("https://github.com/alibaba/easyexcel");
			cell.setHyperlink(hyperlink);
		}
		// 精确设置单元格格式
		boolean bool = isHead && cell.getRowIndex() == 1;

		if (bool) {
			// 获取工作簿
			Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
			CellStyle cellStyle = workbook.createCellStyle();

			Font cellFont = workbook.createFont();
			cellFont.setBold(Boolean.TRUE);
			cellFont.setFontHeightInPoints((short) 14);
			cellFont.setColor(IndexedColors.SEA_GREEN.getIndex());
			cellStyle.setFont(cellFont);
			cell.setCellStyle(cellStyle);
		}
	}
}

3.自适应列宽

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

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;

/**
 * 自适应列宽
 *
 * @author
 * @date 2024/9/2
 * @since JDK 17
 */
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.isNotEmpty(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 aInteger
	 */
	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;
				}
			}
		}
	}
}

4.自适应行高

import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;

/**
 * 自适应行高
 * @author
 * @date 2024/9/2
 * @since JDK 17
 */
public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
	/** 默认高度 */
	private static final Integer DEFAULT_HEIGHT = 300;

	/**
	 * Sets the height of header
	 *
	 * @param row
	 * @param relativeRowIndex
	 */
	@Override
	protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
	}

	/**
	 * Sets the height of content
	 *
	 * @param row
	 * @param 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));
	}
}

5.导出

@ApiOperation(value = "导出")
@PostMapping("/export")
public void export(HttpServletResponse response, @RequestBody BidDesignFillPageRequest request) {
	log.info("导出-开始.请求参数:{}", JSON.toJSONString(request));

	ServletOutputStream os = null;

	try {
		this.setExcelResponseProp(response, "设计单位填报列表");
		// 查询
		List<MigrateBidDesignFillExcelVo> list = migrateBidDesignFillService.queryForExport(request);

		os = response.getOutputStream();

		EasyExcel.write(os).head(MigrateBidDesignFillExcelVo.class).excelType(ExcelTypeEnum.XLSX).sheet("设计单位填报列表")
				// 注册通用格式策略
				.registerWriteHandler(CommonCellStyleStrategy.getHorizontalCellStyleStrategy())
				// 设置自定义格式策略
				.registerWriteHandler(new CustomCellWriteHandler())
				// 自适应列宽
				.registerWriteHandler(new CustomCellWriteWidthConfig())
				// 自适应行高
				.registerWriteHandler(new CustomCellWriteHeightConfig())
				.doWrite(list);

		log.info("导出-成功.请求参数:{}", JSON.toJSONString(request));
	} catch (Exception e) {
		log.error(String.format("导出-失败.请求参数:%s", JSON.toJSONString(request)), e);
	} finally {
		IOUtils.closeQuietly(os);
	}
}

/**
 * 设置响应结果
 *
 * @param response    响应结果对象
 * @param rawFileName 文件名
 * @throws UnsupportedEncodingException 不支持编码异常
 */
private void setExcelResponseProp(HttpServletResponse response, String rawFileName)
		throws UnsupportedEncodingException {
	response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
	response.setCharacterEncoding("utf-8");
	String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
	response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
}

6.MigrateBidDesignFillExcelVo

import java.io.Serial;
import java.io.Serializable;
import java.math.BigDecimal;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;

import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;

/**
 * 导出设计单位填报 excel数据
 *
 * @author wenjianhai
 * @date 2024/8/26
 * @since JDK 17
 */
@Data
@EqualsAndHashCode(callSuper = false)
@ToString(callSuper = true)
public class MigrateBidDesignFillExcelVo implements Serializable {
	@Serial
	private static final long serialVersionUID = -1724305623558672671L;

	/** 指挥部名称 */
	@ExcelProperty(value = "指挥部名称", index = 0)
	// @ColumnWidth(20)
	private String districtName;

	/** 标段名称 */
	@ExcelProperty(value = "标段名称", index = 1)
	// @ColumnWidth(40)
	private String bidName;

	/** 标段编号 */
	@ExcelProperty(value = "标段编号", index = 2)
	// @ColumnWidth(20)
	private String bidCode;

	/** 涉及线路 */
	@ExcelProperty(value = "涉及线路", index = 3)
	// @ColumnWidth(30)
	private String lineName;

	/** 涉及县区 */
	@ExcelProperty(value = "涉及县区", index = 4)
	// @ColumnWidth(20)
	private String countyName;

	/** 资金类型 */
	@ExcelProperty(value = "资金类型", index = 5)
	// @ColumnWidth(30)
	private String fundTypeName;

	/** 资金名称 */
	@ExcelProperty(value = "资金名称", index = 6)
	// @ColumnWidth(20)
	private String fundName;

	/** 单位 */
	@ExcelProperty(value = "单位", index = 7)
	// @ColumnWidth(15)
	private String unit;

	/** 实物数量 */
	@ExcelProperty(value = "实物数量", index = 8)
	// @ColumnWidth(20)
	// @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT) // 左对齐
	private BigDecimal materialCount;

	/** 初设批复金额（万元） */
	@ExcelProperty(value = "初设批复金额（万元）", index = 9)
	// @ColumnWidth(20)
	// @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT) // 左对齐
	private BigDecimal designReplyAmount;
}