EasyExcel增加下拉选择框
@Slf4j
public class TitleHandler implements SheetWriteHandler {
/**
* 下拉框值
*/
private Map<Integer,String[]> dropDownMap;
/**
* 多少行有下拉
*/
private final static Integer rowSize = 200;
public TitleHandler(Map<Integer,String[]> dropDownMap) {
this.dropDownMap = dropDownMap;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
dropDownMap.forEach((celIndex, strings) -> {
// 区间设置
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, rowSize, celIndex, celIndex);
// 下拉内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(strings);
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
sheet.addValidationData(dataValidation);
});
}
}
public class EasyExcelUtil {
public static <T> void writeExcelWithModel(OutputStream outputStream, Class<T> clazz, Map<Integer,String[]> dropDownMap) throws IOException {
EasyExcel.write(outputStream, clazz).registerWriteHandler(new TitleHandler(dropDownMap)).sheet("模板").doWrite(ListUtil.empty());
}
}
public class Test{
@Data
@ColumnWidth(20) // 定义列宽
public static class TestVO {
@ExcelProperty(value = "*姓名", index = 0)
private String name;
@ExcelProperty(value = "*年龄", index = 1)
private int age;
@ExcelProperty(value = "学校", index = 2)
private String school;
}
/**
* 测试导出下拉款
* @throws IOException
*/
@Test
public void testDropDown() throws IOException {
// 输出流
OutputStream outputStream = new FileOutputStream(new File("D:\\1.xlsx"));
HashMap<Integer, String[]> dropDownMap = new HashMap<>();
// 指定下拉框
String[] school = {"一中","二中","三中"};
dropDownMap.put(2,school);
EasyExcelUtil.writeExcelWithModel(outputStream, TestVO.class, dropDownMap);
}
}
EasyExcel标题加批注和标题字体填充红色
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import java.util.HashMap;
import java.util.List;
@Slf4j
public class TitleHandler implements CellWriteHandler {
//操作列
private List<Integer> columnIndexs;
//颜色
private Short colorIndex;
// 批注<列的下标,批注内容>
private HashMap<Integer,String> annotationsMap;
public TitleHandler(List<Integer> columnIndexs, Short colorIndex, HashMap<Integer, String> annotationsMap) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
this.annotationsMap = annotationsMap;
}
public TitleHandler(List<Integer> columnIndexs, Short colorIndex) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
}
@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 afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if(isHead){
// 设置列宽
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), 14 * 256);
writeSheetHolder.getSheet().getRow(0).setHeight((short)(1.8*256));
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
Drawing<?> drawing = sheet.createDrawingPatriarch();
// 设置标题字体样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("宋体");
headWriteFont.setFontHeightInPoints((short)14);
headWriteFont.setBold(true);
if (CollectionUtils.isNotEmpty(columnIndexs) &&
colorIndex != null &&
columnIndexs.contains(cell.getColumnIndex())) {
// 设置字体颜色
headWriteFont.setColor(colorIndex);
}
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
cell.setCellStyle(cellStyle);
if (null != annotationsMap && annotationsMap.containsKey(cell.getColumnIndex())) {
// 批注内容
String context = annotationsMap.get(cell.getColumnIndex());
// 创建绘图对象
Comment comment=drawing.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) cell.getColumnIndex(), 0, (short) 5, 5));
comment.setString(new XSSFRichTextString(context));
cell.setCellComment(comment);
}
}
}
}
public class EasyExcelUtil {
/**
* 导出excel
* @param outputStream 输出流
* @param dataList 导出的数据
* @param classT 模板类
* @param sheetName sheetName
* @param cellWriteHandlers 样式处理类
*/
public static void writeExcelWithModel(OutputStream outputStream, List<? extends Object> dataList, Class<? extends Object> classT, String sheetName, CellWriteHandler... cellWriteHandlers) {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 单元格策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 初始化表格样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(outputStream, classT).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy);
if (null != cellWriteHandlers && cellWriteHandlers.length > 0) {
for (int i = 0; i < cellWriteHandlers.length; i++) {
excelWriterSheetBuilder.registerWriteHandler(cellWriteHandlers[i]);
}
}
// 开始导出
excelWriterSheetBuilder.doWrite(dataList);
}
}
public class TestEasyExcel {
@Data
@ColumnWidth(20)
public static class TestVO {
@ExcelProperty(value = "*姓名", index = 0)
private String name;
@ExcelProperty(value = "*年龄", index = 1)
private int age;
@ExcelProperty(value = "学校", index = 2)
private String school;
}
/**
* 测试导出模板
* 1. 标题指定某列标红色字段
* 2. 标题指定某列加批注
*/
@Test
public void testExport1() throws FileNotFoundException {
// 输出流
OutputStream outputStream = new FileOutputStream(new File("D:\\1.xlsx"));
// 导出的数据
List<TestVO> dataList = new ArrayList<>();
// 指定标红色的列
List<Integer> columns = Arrays.asList(0, 1);
// 指定批注
HashMap<Integer, String> annotationsMap = new HashMap<>();
annotationsMap.put(0,"第一列标题批注");
annotationsMap.put(1,"第二列标题批注");
TitleHandler titleHandler = new TitleHandler(columns, IndexedColors.RED.index,annotationsMap);
EasyExcelUtil.writeExcelWithModel(outputStream, dataList, TestVO.class, "sheetName", titleHandler);
}
}
easyExcel同一单元格部分文字颜色、样式修改
public class WriteHandlerStrategy implements CellWriteHandler {
@Override
public void beforeCellCreate(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
final Row row, final Head head, final Integer columnIndex,
final Integer relativeRowIndex, final Boolean isHead) {
}
@Override
public void afterCellCreate(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
final Cell cell, final Head head, final Integer relativeRowIndex, final Boolean isHead) {
}
@Override
public void afterCellDataConverted(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
final CellData cellData, final Cell cell, final Head head,
final Integer relativeRowIndex, final Boolean isHead) {
}
@Override
public void afterCellDispose(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
final List<CellData> list, final Cell cell, final Head head, final Integer integer,
final Boolean aBoolean) {
}
}
@Override
public void afterCellDispose(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
final List<CellData> list, final Cell cell, final Head head, final Integer integer,
final Boolean aBoolean) {
Sheet sheet = writeSheetHolder.getSheet();
Workbook workbook = sheet.getWorkbook();
// xlsx格式,如果是老版本格式的话就用 HSSFRichTextString
XSSFRichTextString richString = new XSSFRichTextString(cell.getStringCellValue());
Font font = workbook.createFont();
font.setColor(Font.COLOR_RED);
// 从哪到哪,你想设置成什么样的字体都行startIndex,endIndex
richString.applyFont(0, 2, font);
// 再设置回每个单元格里
cell.setCellValue(richString);
}
最后最最最重要的就是,富文本得把inMemory设置为true,不然没有效果
EasyExcel.write(response.getOutputStream(), ReportVO.class)
.inMemory(true) // 富文本
.registerWriteHandler(new WriteHandlerStrategy()).build();