EasyExcel写入
参考: https://alibaba-easyexcel.github.io/index.html · https://www.yuque.com/easyexcel/doc/write
maven
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
实体对象
@Setter
@Getter
@ToString
@ContentRowHeight(112) // 内容行高
@HeadRowHeight(30) // 头行高
public class GoodsReferencePriceExcelDTO {
private static final long serialVersionUID = 1L;
@ColumnWidth(10) // 单元格宽度
@ExcelProperty(value = {"主材清单(参考价)", "空间"})
private String space;
@ColumnWidth(10)
@ExcelProperty({"主材清单(参考价)", "位置"})
private String workingFace;
@ColumnWidth(15)
@ExcelProperty({"主材清单(参考价)", "品类"})
private String category;
/** 图片写入 */
@ColumnWidth(25)
@ExcelProperty({"主材清单(参考价)", "参考图片"})
private String thumbnail; // converter = StringImageConverter.class 适用于本地文件
private byte[] thumbnail;
private URL thumbnail; // 网络较好时 方便实用
private File thumbnail; // 适用于本地文件
private InputStream thumbnail;
}
监听器 2.1.6-jar
单元格 CellWriteHandler 2.2.0-beta1
public class CustomCellWriteHandler implements CellWriteHandler {
private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
// 自定义图片格式时 需要设置空值 否则图片会重复写入两次
cellData.setType(CellDataTypeEnum.EMPTY);
}
/** 图片属性设置 */
private void setImageValue(CellData cellData, Cell cell) {
Sheet sheet = cell.getSheet();
int index = sheet.getWorkbook().addPicture(cellData.getImageValue(), HSSFWorkbook.PICTURE_TYPE_JPEG);
Drawing drawing = sheet.getDrawingPatriarch();
if (Objects.isNull(drawing)) {
drawing = sheet.createDrawingPatriarch();
}
CreationHelper helper = sheet.getWorkbook().getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor();
// 距单元格左侧距离
anchor.setDx1(Units.EMU_PER_CHARACTER);
// 距单元格右侧距离
anchor.setDx2(-Units.EMU_PER_CHARACTER);
// 距单元格顶部距离
anchor.setDy1(Units.EMU_PER_CHARACTER);
// 距单元格底部距离
anchor.setDy2(-Units.EMU_PER_CHARACTER);
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);
anchor.setRow1(cell.getRowIndex());
anchor.setRow2(cell.getRowIndex() + 1);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
drawing.createPicture(anchor, index);
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 这里可以对cell进行任何操作
String sheetName = writeSheetHolder.getWriteSheet().getSheetName();
Sheet sheet = writeSheetHolder.getSheet();
Row row = cell.getRow();
CellData cellData = cellDataList.get(0);
if (Objects.nonNull(cellData) && cellData.getType().equals(CellDataTypeEnum.IMAGE)) {
row.setHeightInPoints((float)112.5);
}
this.buildExportMaterial(sheet, head, cell, isHead);
}
/** 超链接*/
private void buildUrl(Workbook workbook, Cell cell) {
CreationHelper createHelper = workbook.getCreationHelper();
Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
hyperlink.setAddress(cell.getStringCellValue());
cell.setHyperlink(hyperlink);
cell.setCellValue("点击跳转");
}
/** 样式*/
private void buildExportExcel(Sheet sheet, Head head, Cell cell, Boolean isHead, String firstCaseValue) {
Workbook workbook = sheet.getWorkbook();
if (isHead) {
// 列宽
List<Integer> columnWidths;
ExcelUtils.setColumnWidth(sheet, columnWidths);
}
if (!isHead) {
Font font = ExcelUtils.getFont(workbook, HSSFColor.HSSFColorPredefined.BLACK.getIndex());
CellStyle style = ExcelUtils.getStyle(workbook, font, HSSFColor.HSSFColorPredefined.TAN.getIndex());
cell.setCellStyle(style);
cell.getRow().setHeightInPoints((float)25);
}
}
}
单元格 RowWriteHandler
public class CustomRowWriteHandler implements RowWriteHandler {
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Row row, Integer integer, Boolean aBoolean) {
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Row row, Integer relativeRowIndex, Boolean isHead) {
// 这里可以对row进行任何操作
int size = writeSheetHolder.getHead().size() - 1;
String sheetName = writeSheetHolder.getWriteSheet().getSheetName();
Sheet sheet = writeSheetHolder.getSheet();
this.buildExportExcel(sheet, row, size);
}
private void buildExportExcel(Sheet sheet, Row row, int size) {
Iterator<Cell> cellIterator = row.cellIterator();
String stringCellValue = cellIterator.next().getStringCellValue();
if (stringCellValue.contains(DosageListExcelConstantEnum.SPACE_NAME.getConstant()) {
ExcelUtils.mergeCell(sheet, row.getRowNum(), row.getRowNum(), 0, size);
}
}
}
合并单元格 AbstractMergeStrategy 2.2.0-beta1(不包括该版本)之前的版本可用
public class MyMergeStrategy extends AbstractMergeStrategy {
private List<Integer> spaceMerge;
private Sheet sheet;
public MyMergeStrategy() {
}
public MyMergeStrategy(List<Integer> spaceMerge) {
this.spaceMerge = spaceMerge;
}
private void mergeSpace(int index) {
Integer rowCnt = 2;
for (Integer count : spaceMerge) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCnt, rowCnt + count - 1, index, index);
sheet.addMergedRegionUnsafe(cellRangeAddress);
rowCnt += count;
}
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
this.sheet = sheet;
// 第几行开始设置合并单元格 一次即可布局全局
int rowIndex = 2;
if (cell.getRowIndex() == rowIndex) {
if (DosageListExcelConstantEnum.SPACE.getConstant().equals(head.getFieldName())) {
mergeSpace(0);
}
}
}
}
写入代码
private byte[] specialHandleExcel() {
// 根据用户传入字段 假设我们要忽略 space .excludeColumnFiledNames
Set<String> excludeColumnFiledNameSet = new HashSet<>();
excludeColumnFiledNameSet.add("space");
// 根据用户传入字段 假设我们只要导出 date .includeColumnFiledNames
Set<String> includeColumnFiledNames = new HashSet<>();
//includeColumnFiledNames.add("space");
// 每隔2行会合并 把eachColumn 设置成 3 也就是我们数据的长度,所以就第一列会合并。当然其他合并策略也可以自己写 .registerWriteHandler
LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
// 对固定区间进行合并 可多个同时使用 首行 0 首列 0 firstRowIndex 起始行(包括) lastRowIndex 终止行(包括) fistColumnIndex 起始列(包括) lastColumnIndex 终止列(包括) .registerWriteHandler
OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(2, 5, 0, 0);
// 自定义合并单元格 2.2.0-beta1版本及之后失效
MyMergeStrategy strategy = new MyMergeStrategy();
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
ExcelWriter writer = EasyExcel.write(outputStream)
.registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(new CustomRowWriteHandler())
.build();
// 组织数据
DataListDTO dataListDTO = this.buildDataList();
// 工作簿 记录
int sheetNo = -1;
// 主材
sheetNo++;
MyMergeStrategy materialStrategy = new MyMergeStrategy(dataListDTO.getReferencePriceSpaceMerge().get(DosageListExcelConstantEnum.MATERIAL_REFERENCE_PRICE.getConstant()));
WriteSheet sheet = EasyExcel.writerSheet(sheetNo, DosageListExcelConstantEnum.MATERIAL_REFERENCE_PRICE.getConstant())
.head(GoodsReferencePriceExcelDTO.class)
.registerWriteHandler(materialStrategy)
.build();
writer.write(dataListDTO.getMaterialReferencePriceList(), sheet);
// 家具
sheetNo++;
MyMergeStrategy furnitureStrategy = new MyMergeStrategy(dataListDTO.getReferencePriceSpaceMerge().get(DosageListExcelConstantEnum.FURNITURE_REFERENCE_PRICE.getConstant()));
Set<String> furnitureExcludeColumnFiledName = new HashSet<>();
furnitureExcludeColumnFiledName.add("workingFace");
WriteSheet sheet = EasyExcel.writerSheet(sheetNo, DosageListExcelConstantEnum.FURNITURE_REFERENCE_PRICE.getConstant())
.excludeColumnFiledNames(furnitureExcludeColumnFiledName)
.head(GoodsReferencePriceExcelDTO.class)
.registerWriteHandler(furnitureStrategy)
.build();
writer.write(dataListDTO.getFurnitureReferencePriceList(), sheet);
writer.finish();
try {
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
return outputStream.toByteArray();
}
请求数据
public void excelDownload(DosageListExcelParameterDTO excelDTO, HttpServletResponse response) throws Exception {
// 获取数据
ExcelExportDTO exportData = excelService.getExcelExportData(excelDTO);
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String codedFileName = java.net.URLEncoder.encode(exportData.getFilename(), StandardCharsets.UTF_8.name());
response.setHeader("Content-disposition", "attachment;filename=" + codedFileName + ".xlsx");
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
ServletOutputStream os = response.getOutputStream();
// 写入
os.write(exportData.getBytes());
os.flush();
os.close();
}
遇到的问题
1.数据量过大时 建议调整单元格样式设置 CellStyle 否则可能会报 创建样式超过阈值
2.web中的写 请求时间需要控制 否则会报504 或者 can not close io