Easyexcel-应用
目录
Easyexcel 应用
pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>Fill 填充
- 横向填充 WriteDirectionEnum.HORIZONTAL
- 垂直填充 WriteDirectionEnum.VERTICAL
// 设置填充配置
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
// 模板路径
String templatePath = physResultStatManager.localFileStorePath + bucketName + "template.xlsx";
// 写入新的文件本地路径
String saveLocalPath = physResultStatManager.localFileStorePath + bucketName + fileName + ".xlsx";
ExcelWriter excelWriter = null;
try{
excelWriter = EasyExcel.write(saveLocalPath)
.withTemplate(templatePath)
.build();
WriteSheet bmiSheet = EasyExcel.writerSheet(0, "BMI Data")
.automaticMergeHead(true)
.build();
excelWriter.fill(new FillWrapper("data", List.of()), fillConfig, bmiSheet);
excelWriter.fill(new FillWrapper("title", List.of()), fillConfig, bmiSheet);
} catch(Exception e) {
} finally {
if (excelWriter != null) {
excelWriter.close();
}
}Sheet 多次写入同一模版
WriteSheet visionSheet = EasyExcel.writerSheet(2, "Vision Data").build();
for (int i = 0 ; i < 3 ; i++) {
// 每一行数据
List<GradeVisionDTO> gradeVisions = visionDataMap.get(i);
// 写入的表
WriteTable writeTable = EasyExcel
.writerTable(i)
// 简单列宽样式策略
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(21))
// 简单的行高风格策略
.registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 86, (short) 30))
// 构建表头
.head(physResultStatManager.buildTemplateHead(phIdNameMap.get(phId)))
.needHead(Boolean.TRUE)
.build();
excelWriter.write(gradeVisions, visionSheet, writeTable);
}复杂表头构建参考
public List<List<String>> buildTemplateHead(String title) {
List<List<String>> headers = new ArrayList<>();
List<String> column1 = new ArrayList<>();
column1.add(title);
column1.add("");
column1.add("");
headers.add(column1);
headers.add(column1);
List<String> column3 = new ArrayList<>();
column3.add(title);
column3.add("Color Vision");
column3.add("Others");
headers.add(column3);
List<String> column4 = new ArrayList<>();
column4.add(title);
column4.add("Vision Acuity");
column4.add("Uncorrected Vision Acuity（UCVA）and <0.8-");
headers.add(column4);
List<String> column5 = new ArrayList<>();
column5.add(title);
column5.add("Vision Acuity");
column5.add("Corrected Vision and <0.8-");
headers.add(column5);
List<String> column6 = new ArrayList<>();
column6.add(title);
column6.add("Ametropia Suggestions");
column6.add("Myopia");
headers.add(column6);
List<String> column7 = new ArrayList<>();
column7.add(title);
column7.add("Ametropia Suggestions");
column7.add("Hyperopia has effected vision");
headers.add(column7);
List<String> column8 = new ArrayList<>();
column8.add(title);
column8.add("Ametropia Suggestions");
column8.add("Astigmatism");
headers.add(column8);
List<String> column9 = new ArrayList<>();
column9.add(title);
column9.add("Ametropia Suggestions");
column9.add("Anisometropia");
headers.add(column9);
List<String> column10 = new ArrayList<>();
column10.add(title);
column10.add("Ametropia Suggestions");
column10.add("No need");
headers.add(column10);
List<String> column11 = new ArrayList<>();
column11.add(title);
column11.add("Slit-lamp Examination");
column11.add("Normal");
headers.add(column11);
List<String> column12 = new ArrayList<>();
column12.add(title);
column12.add("Slit-lamp Examination");
column12.add("Dry eye");
headers.add(column12);
List<String> column13 = new ArrayList<>();
column13.add(title);
column13.add("Slit-lamp Examination");
column13.add("Conjunctivitis");
headers.add(column13);
List<String> column14 = new ArrayList<>();
column14.add(title);
column14.add("Slit-lamp Examination");
column14.add("Strabismus");
headers.add(column14);
List<String> column15 = new ArrayList<>();
column15.add(title);
column15.add("Slit-lamp Examination");
column15.add("Moderate-Severe Trichiasis");
headers.add(column15);
List<String> column16 = new ArrayList<>();
column16.add(title);
column16.add("Slit-lamp Examination");
column16.add("Other Suggestion");
headers.add(column16);
List<String> column17 = new ArrayList<>();
column17.add("Total");
column17.add("Total");
column17.add("Total");
headers.add(column17);
return headers;
}设置水平单元格样式
public HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为白色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//边框
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
//自动换行
headWriteCellStyle.setWrapped(true);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 12);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = MergeFirstColumnHandler.getWriteCellStyle();
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}单元格合并
- 合并第一列
- 并且按照规则设置颜色
- 合并最后一行的第一列和第二列
- 当写入多个tab时，支持合并
public class MergeFirstColumnHandler implements CellWriteHandler {
/**
* 需要合并列的下标，从0开始
*/
private final int[] mergeColumnIndex = new int[]{0};
private final static XSSFColor DEFAULT_COLOR = new XSSFColor(new byte[]{(byte) 230, (byte) 240, (byte) 222}, null);
public static WriteCellStyle getWriteCellStyle() {
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//边框
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
//自动换行
contentWriteCellStyle.setWrapped(true);
//文字
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteCellStyle.setWriteFont(contentWriteFont);
return contentWriteCellStyle;
}
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
Cell cell = context.getCell();
WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
// 当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
// 从第几行开始合并
int mergeRowIndex = 3;
if (curRowIndex > mergeRowIndex) {
for (int columnIndex : mergeColumnIndex) {
if (curColIndex == columnIndex) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
if (curRowIndex == 16) {
if (curColIndex == 1) {
// 最后一行 合并第一列和第二列
mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, 0, 1);
}
}
if ((curRowIndex - 16) % 17 == 0 || curColIndex == 16) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
CellStyle cellStyle = getCellStyle(workbook);
cell.setCellStyle(cellStyle);
context.getFirstCellData().setWriteCellStyle(null);
}
}
public CellStyle getCellStyle(Workbook workbook) {
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
cellStyle.setFillForegroundColor(DEFAULT_COLOR);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
XSSFFont font = new XSSFFont();
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
return cellStyle;
}
public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {
Sheet sheet = writeSheetHolder.getSheet();
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);
sheet.addMergedRegion(cellRangeAddress);
}
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
cell.getNumericCellValue();
Row preRow = cell.getSheet().getRow(curRowIndex - 1);
if (preRow == null) {
// 当获取不到上一行数据时，使用缓存sheet中数据
preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
}
Cell preCell = preRow.getCell(curColIndex);
if (preCell == null) {
return;
}
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
preCell.getNumericCellValue();
// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
if (curData.equals(preData)) {
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);
}
}
}
}