依赖包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>
Controller导出代码
@ApiOperation(value = "导出", notes = "导出", httpMethod = "GET")
@RequestMapping(value = "/export", method = RequestMethod.GET, produces = CTRL_PRODUCE)
public void export(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = "供应商评价汇总表" + LocalDate.now();
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter excelWriter = null;
try {
int[] mergeColumnIndex = {0, 1, 2};
int mergeRowIndex = 1;
int mergeBaseIndex = 0;
excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0, "sheet1").build();
WriteTable writeTable = EasyExcel.writerTable(0).head(ExcelDto.class)
.registerWriteHandler(new ExcelColumnWidth())
.registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumnIndex, mergeBaseIndex))
.registerWriteHandler(ExcelMergeUtil.getStyleStrategy())
.needHead(true).build();
excelWriter.write(getDataList(), writeSheet, writeTable);
WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "sheet2").build();
WriteTable writeTable1 = EasyExcel.writerTable(1).head(ExcelDto.class)
.registerWriteHandler(new ExcelColumnWidth())
.registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumnIndex, mergeBaseIndex))
.registerWriteHandler(ExcelMergeUtil.getStyleStrategy()))
.needHead(true).build();
excelWriter.write(getDataList(), writeSheet1, writeTable1);
WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "sheet3").build();
WriteTable writeTable2 = EasyExcel.writerTable(2).head(ExcelDto.class)
.registerWriteHandler(new ExcelColumnWidth())
.registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumnIndex, mergeBaseIndex))
.registerWriteHandler(ExcelMergeUtil.getStyleStrategy())
.needHead(true).build();
excelWriter.write(getDataList(), writeSheet2, writeTable2);
} catch (IOException e) {
e.printStackTrace();
} finally {
Objects.requireNonNull(excelWriter).finish();
}
}
表头 ExcelDto
package com.yunshang.service.modules.web.sysSupplier.dto;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class ExcelDto {
@ExcelProperty({"一阶段评价", "供应商名称"})
private String gysmc;
@ExcelProperty({"一阶段评价", "累计合同数量"})
private Integer ljhtsl;
@ExcelProperty({"一阶段评价", "项目"})
private String xm;
@ExcelProperty({"一阶段评价", "设备名称"})
private String sbmc;
@ExcelProperty({"二阶段评价", "售后服务"})
private String shfw;
@ExcelProperty({"二阶段评价", "二阶段项目得分"})
private String ejdxmzhdf;
@ExcelProperty({"二阶段评价", "二阶段综合得分"})
private String ejdzhdf;
}
设置列宽和默认行高 ExcelColumnWidth
package com.yunshang.service.modules.web.sysSupplier.entity;
import com.alibaba.excel.metadata.Head;
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.poi.ss.usermodel.Cell;
import java.util.List;
public class ExcelColumnWidth extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> list, Cell cell, Head head,
Integer integer, Boolean isHead) {
if (isHead && cell.getRowIndex() == 1) {
int columnWidth = cell.getStringCellValue().getBytes().length;
int cellIndex = cell.getColumnIndex();
switch (cellIndex) {
case 0:
columnWidth = 35;
break;
case 1:
columnWidth = 18;
break;
case 2:
columnWidth = 20;
break;
case 3:
columnWidth = 15;
break;
default:
columnWidth = 15;
break;
}
writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
writeSheetHolder.getSheet().setDefaultRowHeight((short)(2* 200));
}
}
}
合并工具类 ExcelMergeUtil
package com.yunshang.service.modules.web.sysSupplier;
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;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
public class ExcelMergeUtil implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
private int mergeBaseIndex;
public ExcelMergeUtil() {
}
public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex, int mergeBaseIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
this.mergeBaseIndex = mergeBaseIndex;
}
@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 afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
int curRowIndex = cell.getRowIndex();
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex, mergeBaseIndex);
break;
}
}
}
}
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int mergeBaseIndex) {
Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
Boolean dataBool = preData.equals(curData);
Boolean bool = cell.getRow().getCell(mergeBaseIndex).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(mergeBaseIndex).getStringCellValue());
if (dataBool && bool) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
public static HorizontalCellStyleStrategy getStyleStrategy() {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
headWriteCellStyle.setWriteFont(headWriteFont);
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
contentWriteCellStyle.setWrapped(true);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
数据处理 getDataList
private List<ExcelDto> getDataList() {
List<ExcelDto> excelDtos = new ArrayList<>();
return excelDtos;
}