一、引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
二、常用注解介绍
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = "Test 测试表 DTO")
@ContentRowHeight(20)
@HeadRowHeight(40)
@ColumnWidth(25)
public class TestDTO {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "主键", example = "111")
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
@ExcelProperty(value = "主键", index = 2)
private String id;
@ApiModelProperty(value = "用户名", example = "zhangsan", required = true)
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
@ExcelProperty(value = {"主标题", "用户名"}, index = 1)
private String username;
@ApiModelProperty(value = "密码", example = "123456")
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
@ExcelProperty(value = {"主标题", "密码"}, index = 0)
@ColumnWidth(50)
@Sensitive(type = SensitiveTypeEnum.CUSTOMER, prefixNoMaskLen = 2, suffixNoMaskLen = 3, maskStr = "*")
private String password;
}
三、自定义样式信息类(某一单元格特定样式时使用)
import cn.hutool.core.util.StrUtil;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;
import org.apache.poi.xssf.usermodel.XSSFColor;
@Data
public class ExcelCellStyleModel {
private String sheetName;
private int colIndex;
private int rowIndex;
private String fontName;
private Double fontHeight;
private Object fontColor;
private Boolean fontBold;
private Boolean fontItalic;
private Byte fontUnderLine;
private Short fontTypeOffset;
private Boolean fontStrikeout;
private Object backgroundColor;
private BorderStyle borderTop;
private BorderStyle borderRight;
private BorderStyle borderBottom;
private BorderStyle borderLeft;
private Object topBorderColor;
private Object rightBorderColor;
private Object bottomBorderColor;
private Object leftBorderColor;
private HorizontalAlignment horizontalAlignment;
private VerticalAlignment verticalAlignment;
private Boolean wrapText;
public static ExcelCellStyleModel createFontNameCellStyleModel(String sheetName, int rowIndex, int columnIndex, String fontName) {
return createFontCellStyleModel(sheetName, rowIndex, columnIndex, fontName, null, null, null, null, null, null, null);
}
public static ExcelCellStyleModel createFontHeightCellStyleModel(String sheetName, int rowIndex, int columnIndex
, Double fontHeight) {
return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, fontHeight, null, null, null, null, null, null);
}
public static XSSFColor getRGBColor(int redNum, int greenNum, int blueNum) {
return new XSSFColor(new byte[]{(byte) redNum, (byte) greenNum, (byte) blueNum}, new DefaultIndexedColorMap());
}
public static ExcelCellStyleModel createFontColorCellStyleModel(String sheetName, int rowIndex, int columnIndex
, int redNum, int greenNum, int blueNum) {
XSSFColor fontColor = getRGBColor(redNum, greenNum, blueNum);
return createFontColorCellStyleModel(sheetName, rowIndex, columnIndex, fontColor);
}
public static ExcelCellStyleModel createFontColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object fontColor) {
return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, fontColor, null, null, null, null, null);
}
public static ExcelCellStyleModel createFontBoldCellStyleModel(String sheetName, int rowIndex, int columnIndex, Boolean fontBold) {
return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, fontBold, null, null, null, null);
}
public static ExcelCellStyleModel createFontItalicCellStyleModel(String sheetName, int rowIndex, int columnIndex, Boolean fontItalic) {
return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, fontItalic, null, null, null);
}
public static ExcelCellStyleModel createFontUnderLineCellStyleModel(String sheetName, int rowIndex, int columnIndex, Byte fontUnderLine) {
return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, fontUnderLine, null, null);
}
public static ExcelCellStyleModel createFontTypeOffsetCellStyleModel(String sheetName, int rowIndex, int columnIndex, Short fontTypeOffset) {
return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, null, fontTypeOffset, null);
}
public static ExcelCellStyleModel createFontStrikeoutCellStyleModel(String sheetName, int rowIndex, int columnIndex, Boolean fontStrikeout) {
return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, null, null, fontStrikeout);
}
public static ExcelCellStyleModel createFontCellStyleModel(String sheetName, int rowIndex, int columnIndex
, String fontName, Double fontHeight, Object fontColor, Boolean fontBold, Boolean fontItalic, Byte fontUnderLine
, Short fontTypeOffset, Boolean fontStrikeout) {
return createCellStyleModel(sheetName, rowIndex, columnIndex, fontName, fontHeight, fontColor, fontBold, fontItalic
, fontUnderLine, fontTypeOffset, fontStrikeout, null);
}
public static ExcelCellStyleModel createBackgroundColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object backgroundColor) {
return createCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, null, null, null, backgroundColor);
}
public static ExcelCellStyleModel createBackgroundColorCellStyleModel(String sheetName, int rowIndex, int columnIndex
, int redNum, int greenNum, int blueNum) {
XSSFColor backgroundColor = getRGBColor(redNum, greenNum, blueNum);
return createBackgroundColorCellStyleModel(sheetName, rowIndex, columnIndex, backgroundColor);
}
public static ExcelCellStyleModel createCellStyleModel(String sheetName, int rowIndex, int columnIndex
, String fontName, Double fontHeight, Object fontColor, Boolean fontBold, Boolean fontItalic, Byte fontUnderLine
, Short fontTypeOffset, Boolean fontStrikeout, Object backgroundColor) {
return createCellStyleModel(sheetName, rowIndex, columnIndex, fontName, fontHeight, fontColor, fontBold, fontItalic
, fontUnderLine, fontTypeOffset, fontStrikeout, backgroundColor, null, null, null, null, null, null, null, null);
}
public static ExcelCellStyleModel createTopBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex
, Object topBorderColor) {
return createBorderColorCellStyleModel(sheetName, rowIndex, columnIndex, topBorderColor, null, null, null);
}
public static ExcelCellStyleModel createRightBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex
, Object rightBorderColor) {
return createBorderColorCellStyleModel(sheetName, rowIndex, columnIndex, null, rightBorderColor, null, null);
}
public static ExcelCellStyleModel createBottomBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex
, Object bottomBorderColor) {
return createBorderColorCellStyleModel(sheetName, rowIndex, columnIndex, null, null, bottomBorderColor, null);
}
public static ExcelCellStyleModel createLeftBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex
, Object leftBorderColor) {
return createBorderColorCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, leftBorderColor);
}
public static ExcelCellStyleModel createTopBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex
, BorderStyle borderTop) {
return createBorderLineTypeCellStyleModel(sheetName, rowIndex, columnIndex, borderTop, null, null, null);
}
public static ExcelCellStyleModel createRightBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex
, BorderStyle borderRight) {
return createBorderLineTypeCellStyleModel(sheetName, rowIndex, columnIndex, null, borderRight, null, null);
}
public static ExcelCellStyleModel createBottomBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex
, BorderStyle borderBottom) {
return createBorderLineTypeCellStyleModel(sheetName, rowIndex, columnIndex, null, null, borderBottom, null);
}
public static ExcelCellStyleModel createLeftBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex
, BorderStyle borderLeft) {
return createBorderLineTypeCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, borderLeft);
}
public static ExcelCellStyleModel createBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex
, Object borderColor) {
return createBorderCellStyleModel(sheetName, rowIndex, columnIndex, null, borderColor);
}
public static ExcelCellStyleModel createBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex
, Object topBorderColor, Object rightBorderColor, Object bottomBorderColor, Object leftBorderColor) {
return createBorderCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null
, topBorderColor, rightBorderColor, bottomBorderColor, leftBorderColor);
}
public static ExcelCellStyleModel createBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex
, BorderStyle borderLineType) {
return createBorderCellStyleModel(sheetName, rowIndex, columnIndex, borderLineType, null);
}
public static ExcelCellStyleModel createBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex
, BorderStyle borderTop, BorderStyle borderRight, BorderStyle borderBottom, BorderStyle borderLeft) {
return createBorderCellStyleModel(sheetName, rowIndex, columnIndex, borderTop, borderRight, borderBottom, borderLeft
, null, null, null, null);
}
public static ExcelCellStyleModel createBorderCellStyleModel(String sheetName, int rowIndex, int columnIndex
, BorderStyle borderLineType, Object borderColor) {
return createBorderCellStyleModel(sheetName, rowIndex, columnIndex, borderLineType, borderLineType, borderLineType, borderLineType
, borderColor, borderColor, borderColor, borderColor);
}
public static ExcelCellStyleModel createBorderCellStyleModel(String sheetName, int rowIndex, int columnIndex
, BorderStyle borderTop, BorderStyle borderRight, BorderStyle borderBottom, BorderStyle borderLeft, Object topBorderColor
, Object rightBorderColor, Object bottomBorderColor, Object leftBorderColor) {
return createCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, null, null, null
, null, borderTop, borderRight, borderBottom, borderLeft, topBorderColor, rightBorderColor
, bottomBorderColor, leftBorderColor);
}
public static ExcelCellStyleModel createCellStyleModel(String sheetName, int rowIndex, int columnIndex
, String fontName, Double fontHeight, Object fontColor, Boolean fontBold, Boolean fontItalic, Byte fontUnderLine
, Short fontTypeOffset, Boolean fontStrikeout, Object backgroundColor, BorderStyle borderTop, BorderStyle borderRight
, BorderStyle borderBottom, BorderStyle borderLeft, Object topBorderColor, Object rightBorderColor, Object bottomBorderColor
, Object leftBorderColor) {
return createCellStyleModel(sheetName, rowIndex, columnIndex, fontName, fontHeight, fontColor, fontBold, fontItalic
, fontUnderLine, fontTypeOffset, fontStrikeout, backgroundColor, borderTop, borderRight, borderBottom
, borderLeft, topBorderColor, rightBorderColor, bottomBorderColor, leftBorderColor, null, null);
}
public static ExcelCellStyleModel createHorizontalAlignmentCellStyleModel(String sheetName, int rowIndex, int columnIndex
, HorizontalAlignment horizontalAlignment) {
return createAlignmentCellStyleModel(sheetName, rowIndex, columnIndex, horizontalAlignment, null);
}
public static ExcelCellStyleModel createVerticalAlignmentCellStyleModel(String sheetName, int rowIndex, int columnIndex
, VerticalAlignment verticalAlignment) {
return createAlignmentCellStyleModel(sheetName, rowIndex, columnIndex, null, verticalAlignment);
}
public static ExcelCellStyleModel createAlignmentCellStyleModel(String sheetName, int rowIndex, int columnIndex
, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment) {
return createCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null
, null, null, null, null, null, null, null
, null, null, null, null, null, null
, horizontalAlignment, verticalAlignment);
}
public static ExcelCellStyleModel createCellStyleModel(String sheetName, int rowIndex, int columnIndex
, String fontName, Double fontHeight, Object fontColor, Boolean fontBold, Boolean fontItalic, Byte fontUnderLine
, Short fontTypeOffset, Boolean fontStrikeout, Object backgroundColor, BorderStyle borderTop, BorderStyle borderRight
, BorderStyle borderBottom, BorderStyle borderLeft, Object topBorderColor, Object rightBorderColor, Object bottomBorderColor
, Object leftBorderColor, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment) {
return createCellStyleModel(sheetName, rowIndex, columnIndex, fontName, fontHeight, fontColor, fontBold, fontItalic
, fontUnderLine, fontTypeOffset, fontStrikeout, backgroundColor, borderTop, borderRight, borderBottom
, borderLeft, topBorderColor, rightBorderColor, bottomBorderColor, leftBorderColor, horizontalAlignment, verticalAlignment, null);
}
public static ExcelCellStyleModel createWrapTextCellStyleModel(String sheetName, int rowIndex, int columnIndex
, Boolean wrapText) {
return createCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null
, null, null, null, null, null, null, null
, null, null, null, null, null, null, null
, wrapText);
}
public static ExcelCellStyleModel createCellStyleModel(String sheetName, int rowIndex, int columnIndex
, String fontName, Double fontHeight, Object fontColor, Boolean fontBold, Boolean fontItalic, Byte fontUnderLine
, Short fontTypeOffset, Boolean fontStrikeout, Object backgroundColor, BorderStyle borderTop, BorderStyle borderRight
, BorderStyle borderBottom, BorderStyle borderLeft, Object topBorderColor, Object rightBorderColor, Object bottomBorderColor
, Object leftBorderColor, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, Boolean wrapText) {
ExcelCellStyleModel excelCellStyleModel = new ExcelCellStyleModel();
excelCellStyleModel.setSheetName(sheetName);
excelCellStyleModel.setRowIndex(rowIndex);
excelCellStyleModel.setColIndex(columnIndex);
fontName = fontName != null && StrUtil.equals(fontName, "") ? "宋体" : fontName;
excelCellStyleModel.setFontName(fontName);
fontHeight = fontHeight != null && fontHeight <= 0 ? null : fontHeight;
excelCellStyleModel.setFontHeight(fontHeight);
fontColor = fontColor != null && (!(fontColor instanceof IndexedColors) && !(fontColor instanceof XSSFColor))
? null : fontColor;
excelCellStyleModel.setFontColor(fontColor);
excelCellStyleModel.setFontBold(fontBold);
excelCellStyleModel.setFontItalic(fontItalic);
fontUnderLine = fontUnderLine != null && (fontUnderLine != Font.U_NONE && fontUnderLine != Font.U_SINGLE && fontUnderLine != Font.U_DOUBLE
&& fontUnderLine != Font.U_DOUBLE_ACCOUNTING && fontUnderLine != Font.U_SINGLE_ACCOUNTING) ? null : fontUnderLine;
excelCellStyleModel.setFontUnderLine(fontUnderLine);
fontTypeOffset = fontTypeOffset != null && (fontTypeOffset != Font.SS_NONE && fontTypeOffset != Font.SS_SUB && fontTypeOffset != Font.SS_SUPER)
? null : fontTypeOffset;
excelCellStyleModel.setFontTypeOffset(fontTypeOffset);
excelCellStyleModel.setFontStrikeout(fontStrikeout);
backgroundColor = backgroundColor != null && (!(backgroundColor instanceof IndexedColors) && !(backgroundColor instanceof XSSFColor))
? null : backgroundColor;
excelCellStyleModel.setBackgroundColor(backgroundColor);
excelCellStyleModel.setBorderTop(borderTop);
excelCellStyleModel.setBorderRight(borderRight);
excelCellStyleModel.setBorderBottom(borderBottom);
excelCellStyleModel.setBorderLeft(borderLeft);
excelCellStyleModel.setTopBorderColor(topBorderColor);
excelCellStyleModel.setRightBorderColor(rightBorderColor);
excelCellStyleModel.setBottomBorderColor(bottomBorderColor);
excelCellStyleModel.setLeftBorderColor(leftBorderColor);
excelCellStyleModel.setHorizontalAlignment(horizontalAlignment);
excelCellStyleModel.setVerticalAlignment(verticalAlignment);
excelCellStyleModel.setWrapText(wrapText);
return excelCellStyleModel;
}
}
四、自定义单元格样式处理器(某一单元格特定样式时使用)
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.hft.nbp.common.core.model.ExcelCellStyleModel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class ExcelCellStyleHandler extends AbstractRowWriteHandler {
private List<String> sheetNameList;
private List<ExcelCellStyleModel> cellStyleList = new ArrayList<>();
public ExcelCellStyleHandler(List<ExcelCellStyleModel> cellStyleList) {
if (CollectionUtil.isEmpty(cellStyleList)) {
return;
}
cellStyleList = cellStyleList.stream().filter(x -> x != null
&& StrUtil.isNotBlank(x.getSheetName())
&& (x.getFontColor() == null || x.getFontColor() instanceof IndexedColors
|| x.getFontColor() instanceof XSSFColor)
&& (x.getBackgroundColor() == null || x.getBackgroundColor() instanceof IndexedColors
|| x.getBackgroundColor() instanceof XSSFColor)
&& (x.getTopBorderColor() == null || x.getTopBorderColor() instanceof IndexedColors
|| x.getTopBorderColor() instanceof XSSFColor)
&& (x.getRightBorderColor() == null || x.getRightBorderColor() instanceof IndexedColors
|| x.getRightBorderColor() instanceof XSSFColor)
&& (x.getBottomBorderColor() == null || x.getBottomBorderColor() instanceof IndexedColors
|| x.getBottomBorderColor() instanceof XSSFColor)
&& (x.getLeftBorderColor() == null || x.getLeftBorderColor() instanceof IndexedColors
|| x.getLeftBorderColor() instanceof XSSFColor)
).collect(Collectors.toList());
this.cellStyleList = cellStyleList;
sheetNameList = this.cellStyleList.stream().map(ExcelCellStyleModel::getSheetName).distinct().collect(Collectors.toList());
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row
, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
return;
}
Sheet sheet = writeSheetHolder.getSheet();
if (cellStyleList == null || cellStyleList.size() <= 0 || !sheetNameList.contains(sheet.getSheetName())) {
return;
}
List<ExcelCellStyleModel> rowCellStyleList = cellStyleList.stream().filter(x ->
StrUtil.equals(x.getSheetName(), sheet.getSheetName()) && x.getRowIndex() == relativeRowIndex).collect(Collectors.toList());
if (CollectionUtils.isEmpty(rowCellStyleList)) {
return;
}
for (ExcelCellStyleModel excelCellStyleModel : rowCellStyleList) {
setCellStyle(excelCellStyleModel, row);
}
cellStyleList.removeAll(rowCellStyleList);
sheetNameList = cellStyleList.stream().map(ExcelCellStyleModel::getSheetName).distinct().collect(Collectors.toList());
}
private void setCellStyle(ExcelCellStyleModel excelCellStyleModel, Row row) {
Object backgroundColor = excelCellStyleModel.getBackgroundColor();
Boolean wrapText = excelCellStyleModel.getWrapText();
int colIndex = excelCellStyleModel.getColIndex();
Cell cell = row.getCell(colIndex);
if (cell == null) {
cell = row.createCell(colIndex);
}
XSSFCellStyle style = (XSSFCellStyle) cell.getRow().getSheet().getWorkbook().createCellStyle();
style.cloneStyleFrom(cell.getCellStyle());
if (backgroundColor != null) {
if (backgroundColor instanceof IndexedColors) {
style.setFillForegroundColor(((IndexedColors) backgroundColor).getIndex());
}
else if (backgroundColor instanceof XSSFColor) {
style.setFillForegroundColor((XSSFColor) backgroundColor);
}
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
if (wrapText != null) {
style.setWrapText(wrapText);
}
setFontStyle(row, style, excelCellStyleModel);
setBorderStyle(style, excelCellStyleModel);
setAlignmentStyle(style, excelCellStyleModel);
cell.setCellStyle(style);
}
private void setFontStyle(Row row, XSSFCellStyle style, ExcelCellStyleModel excelCellStyleModel) {
String fontName = excelCellStyleModel.getFontName();
Double fontHeight = excelCellStyleModel.getFontHeight();
Object fontColor = excelCellStyleModel.getFontColor();
Boolean fontBold = excelCellStyleModel.getFontBold();
Boolean fontItalic = excelCellStyleModel.getFontItalic();
Byte fontUnderLine = excelCellStyleModel.getFontUnderLine();
Short fontTypeOffset = excelCellStyleModel.getFontTypeOffset();
Boolean fontStrikeout = excelCellStyleModel.getFontStrikeout();
if (fontName == null && fontHeight == null && fontColor == null && fontBold == null && fontItalic == null
&& fontUnderLine == null && fontTypeOffset == null && fontStrikeout == null) {
return;
}
XSSFFont font;
if (style.getFontIndex() != 0) {
font = style.getFont();
}
else {
font = (XSSFFont) row.getSheet().getWorkbook().createFont();
font.setFontName("宋体");
}
if (fontName != null) {
font.setFontName(fontName);
}
if (fontHeight != null) {
font.setFontHeight(fontHeight);
}
if (fontColor != null) {
if (fontColor instanceof IndexedColors) {
font.setColor(((IndexedColors) fontColor).getIndex());
}
else if (fontColor instanceof XSSFColor) {
font.setColor((XSSFColor) fontColor);
}
}
if (fontBold != null) {
font.setBold(fontBold);
}
if (fontItalic != null) {
font.setItalic(fontItalic);
}
if (fontUnderLine != null) {
font.setUnderline(fontUnderLine);
}
if (fontTypeOffset != null) {
font.setTypeOffset(fontTypeOffset);
}
if (fontStrikeout != null) {
font.setStrikeout(fontStrikeout);
}
style.setFont(font);
}
private void setBorderStyle(XSSFCellStyle style, ExcelCellStyleModel excelCellStyleModel) {
BorderStyle borderTop = excelCellStyleModel.getBorderTop();
BorderStyle borderRight = excelCellStyleModel.getBorderRight();
BorderStyle borderBottom = excelCellStyleModel.getBorderBottom();
BorderStyle borderLeft = excelCellStyleModel.getBorderLeft();
Object topBorderColor = excelCellStyleModel.getTopBorderColor();
Object rightBorderColor = excelCellStyleModel.getRightBorderColor();
Object bottomBorderColor = excelCellStyleModel.getBottomBorderColor();
Object leftBorderColor = excelCellStyleModel.getLeftBorderColor();
if (borderTop == null && borderRight == null && borderBottom == null && borderLeft == null && topBorderColor == null
&& rightBorderColor == null && bottomBorderColor == null && leftBorderColor == null) {
return;
}
if (borderTop != null) {
style.setBorderTop(borderTop);
}
if (borderRight != null) {
style.setBorderRight(borderRight);
}
if (borderBottom != null) {
style.setBorderBottom(borderBottom);
}
if (borderLeft != null) {
style.setBorderLeft(borderLeft);
}
if (topBorderColor != null) {
if (topBorderColor instanceof IndexedColors) {
style.setTopBorderColor(((IndexedColors) topBorderColor).getIndex());
}
else if (topBorderColor instanceof XSSFColor) {
style.setTopBorderColor((XSSFColor) topBorderColor);
}
}
if (rightBorderColor != null) {
if (rightBorderColor instanceof IndexedColors) {
style.setRightBorderColor(((IndexedColors) rightBorderColor).getIndex());
}
else if (rightBorderColor instanceof XSSFColor) {
style.setRightBorderColor((XSSFColor) rightBorderColor);
}
}
if (bottomBorderColor != null) {
if (bottomBorderColor instanceof IndexedColors) {
style.setBottomBorderColor(((IndexedColors) bottomBorderColor).getIndex());
}
else if (bottomBorderColor instanceof XSSFColor) {
style.setBottomBorderColor((XSSFColor) bottomBorderColor);
}
}
if (leftBorderColor != null) {
if (leftBorderColor instanceof IndexedColors) {
style.setLeftBorderColor(((IndexedColors) leftBorderColor).getIndex());
}
else if (topBorderColor instanceof XSSFColor) {
style.setLeftBorderColor((XSSFColor) leftBorderColor);
}
}
}
private void setAlignmentStyle(XSSFCellStyle style, ExcelCellStyleModel excelCellStyleModel) {
HorizontalAlignment horizontalAlignment = excelCellStyleModel.getHorizontalAlignment();
VerticalAlignment verticalAlignment = excelCellStyleModel.getVerticalAlignment();
if (horizontalAlignment == null && verticalAlignment == null) {
return;
}
if (horizontalAlignment != null) {
style.setAlignment(horizontalAlignment);
}
if (verticalAlignment != null) {
style.setVerticalAlignment(verticalAlignment);
}
}
}
五、model定义
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = "Test 测试表 DTO")
public class TestDTO {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "主键", example = "111")
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
@ExcelProperty(value = "主键", index = 2)
private String id;
@ApiModelProperty(value = "用户名", example = "zhangsan", required = true)
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
@ExcelProperty(value = {"主标题", "用户名"}, index = 1)
private String username;
@ApiModelProperty(value = "密码", example = "123456")
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
@ExcelProperty(value = {"主标题", "密码"}, index = 0)
@ColumnWidth(50)
@Sensitive(type = SensitiveTypeEnum.CUSTOMER, prefixNoMaskLen = 2, suffixNoMaskLen = 3, maskStr = "*")
private String password;
}
@Data
public class Model implements Serializable {
@TableId
@ExcelIgnore
private String id;
@ExcelIgnore
private String batchdate;
@ExcelProperty(value = "基金代码", index = 0)
private String fundCode;
@ExcelProperty(value = "基金名称", index = 1)
private String fundName;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
}
六、Excel导出
List<ExcelCellStyleModel> cellStyleList = new ArrayList<>();
for (int i = 0; i < userList.size(); i++) {
cellStyleList.add(ExcelCellStyleModel.createDataFormatCellStyleModel(sheetName, i, 7, "#,##0.0000"));
cellStyleList.add(ExcelCellStyleModel.createDataFormatCellStyleModel(sheetName, i, 8, "#,##0.00"));
cellStyleList.add(ExcelCellStyleModel.createDataFormatCellStyleModel(sheetName, i, 9, "#,##0.0000"));
}
try {
String encodeFileName = EncoderUtils.encodeToUtf8(fileName + excelTypeEnum.getValue());
HttpServletResponse response = WebUtils.getResponse();
response.setHeader("Content-disposition", "attachment;filename=" + encodeFileName);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
OutputStream outputStream = response.getOutputStream();
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
for (Map.Entry<String, List<User>> entry : userMap.entrySet()) {
String sheetName = entry.getKey();
WriteSheet writeSheet = EasyExcel.writerSheet(Integer.valueOf(entry.getKey()), sheetName)
.registerWriteHandler(new ExcelCellStyleHandler(cellStyleList))
.head(User.class)
.build();
excelWriter.write(entry.getValue(), writeSheet);
}
excelWriter.finish();
} catch (Exception e) {
WebUtils.getResponse().reset();
throw new RuntimeException("导出失败", e);
}
七、Excel生成
HashMap<String, byte[]> fileMap = new HashMap<>(1);
try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
String sheetName = String.format("%s文件", stockCode);
String fileName = String.format("%s.xlsx", sheetName);
List<ExcelCellStyleModel> cellStyleList = new ArrayList<>();
for (int i = 0; i < userList.size(); i++) {
cellStyleList.add(ExcelCellStyleModel.createDataFormatCellStyleModel(sheetName, i, 4, "#,##0.00"));
cellStyleList.add(ExcelCellStyleModel.createDataFormatCellStyleModel(sheetName, i, 5, "#,##0"));
cellStyleList.add(ExcelCellStyleModel.createDataFormatCellStyleModel(sheetName, i, 6, "#,##0.00"));
cellStyleList.add(ExcelCellStyleModel.createDataFormatCellStyleModel(sheetName, i, 8, "#,##0.00"));
cellStyleList.add(ExcelCellStyleModel.createDataFormatCellStyleModel(sheetName, i, 9, "#,##0.00"));
}
EasyExcel.write(bos, User.class)
.sheet(0, sheetName)
.registerWriteHandler(new ExcelCellStyleHandler(cellStyleList))
.doWrite(userList);
fileMap.put(fileName, bos.toByteArray());
} catch (Exception e) {
throw new RuntimeException("生成文件失败", e);
}
八、Excel 导入
import com.alibaba.excel.EasyExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.IOUtils;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
@Slf4j
public class EasyExcelUtils {
public static <T> List<T> parse(MultipartFile file, String sheetName, int headRowNumber, Class<T> clazz) {
InputStream inputStream = getInputStream(file);
try {
return parse(inputStream, sheetName, headRowNumber, clazz);
} finally {
IOUtils.closeQuietly(inputStream);
}
}
private static InputStream getInputStream(MultipartFile file) {
try {
return file.getInputStream();
} catch (IOException e) {
log.error("MultipartFile转换为InputStream异常,文件名称:{}", file.getOriginalFilename());
throw new RuntimeException("MultipartFile转换为InputStream异常");
}
}
public static <T> List<T> parse(InputStream inputStream, String sheetName, int headRowNumber, Class<T> clazz) {
try {
log.info("开始解析excel文件,sheet名称:{}", sheetName);
List<T> list = EasyExcel.read(inputStream).sheet(sheetName).head(clazz).headRowNumber(headRowNumber).doReadSync();
log.info("解析excel文件成功,sheet名称:{}", sheetName);
return list;
} catch (Exception e) {
log.error("解析excel文件失败,sheet名称:{}", sheetName);
throw new RuntimeException("解析excel文件失败");
}
}
public static <T> List<T> parse(MultipartFile file, String sheetName, Class<T> clazz) {
return parse(file, sheetName, 1, clazz);
}
public static <T> List<T> parse(MultipartFile file, int sheetNumber, int headRowNumber, Class<T> clazz) {
InputStream inputStream = getInputStream(file);
try {
return parse(inputStream, sheetNumber, headRowNumber, clazz);
} finally {
IOUtils.closeQuietly(inputStream);
}
}
public static <T> List<T> parse(InputStream inputStream, int sheetNumber, int headRowNumber, Class<T> clazz) {
try {
log.info("开始解析excel第{}个sheet文件", sheetNumber);
List<T> list = EasyExcel.read(inputStream).sheet(sheetNumber - 1).head(clazz).headRowNumber(headRowNumber).doReadSync();
log.info("解析excel第{}个sheet文件成功", sheetNumber);
return list;
} catch (Exception e) {
log.error("解析excel第{}个sheet文件失败", sheetNumber);
throw new RuntimeException("解析excel文件失败");
}
}
public static <T> List<T> parse(MultipartFile file, int headRowNumber, Class<T> clazz) {
return parse(file, 1, headRowNumber, clazz);
}
public static <T> List<T> parse(MultipartFile file, Class<T> clazz) {
return parse(file, 1, 1, clazz);
}
}
- 使用实例 (涉及行数据对应对象定义参考多个
sheet
导出中model1定义及model2定义)
@ApiOperation(value = "解析excel文件", notes = "解析excel文件")
@SysLog("解析excel文件")
@PostMapping(value = "/parse", headers = "content-type=multipart/form-data")
public void parse(@RequestPart("file") MultipartFile file) {
List<TestDTO> list1 = EasyExcelUtils.parse(file, "模板二", 2, TestDTO.class);
List<Model> list2 = EasyExcelUtils.parse(file, "模板一",Model.class);
List<TestDTO> list3 = EasyExcelUtils.parse(file, 2, 2, TestDTO.class);
List<Model> list4 = EasyExcelUtils.parse(file, 1, Model.class);
List<Model> list5 = EasyExcelUtils.parse(file, Model.class);
}
转换器
public class CustomStringStringConverter implements Converter<String> {
@Override
public Class supportJavaTypeKey() {
return String.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return "自定义:" + cellData.getStringValue();
}
@Override
public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData(value);
}
}
@Data
public class ConverterData {
@ExcelProperty(converter = CustomStringStringConverter.class)
private String string;
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
private String date;
@NumberFormat("#.##%")
private String doubleData;
}
其他用法
- EasyExcel官方文档 https://easyexcel.opensource.alibaba.com/