easyExcel由于导出和导入的注解差异,所以实现不了标题功能
此处不在介绍导出导出的注解
entity:
@DropDownSetField自行封装注解:稍后解释
public class SalesOrderExportExcel {
private static final long serialVersionUID = 1L;
/**
* 履约方式
*/
@ExcelProperty(value = "履约方式[*]", index = 2,converter= ConverterHandler.class)
@DropDownSetField(sourceClass = PerformTypeOptions.class)
private Integer performType;
}
注解说明:
/**
* 标记导出excel的下拉数据集
*/
@Documented
// 作用在字段上
@Target(ElementType.FIELD)
// 运行时有效
@Retention(RetentionPolicy.RUNTIME)
public @interface DropDownSetField {
// 固定下拉内容
String[] source() default {};
// 动态下拉内容
Class[] sourceClass() default {};
}
需要设置数据源:
public class ConverterHandler implements Converter<Integer> {
@Override
public Class supportJavaTypeKey() {
return null;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return null;
}
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return null;
}
@Override
public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
if (contentProperty.getHead().getFieldName().equals("performType")) {
if (value != null) {
if (value == 1) {
return new CellData("门店履约");
}
if (value == 3) {
return new CellData("总部履约");
}
if (value == 4) {
return new CellData("自行履约");
}
if (value == 5) {
return new CellData("平台履约");
}
}
}
return new CellData("");
}
}
设置下拉内容:
public class PerformTypeOptions implements DropDownSetInterface {
@Override
public String[] getSource() {
return new String[] {"门店履约","总部履约","自行履约","平台履约"};
}
}
此处需要是实现Excel拦截器和单元格拦截器(设置样式或单元格格式):
public class ProductCellWriteHandler extends AbstractCellStyleStrategy implements SheetWriteHandler , CellWriteHandler {
private static final Integer width = 34;
private List<Map<String, Object>> list = null;
Workbook workbooks;
public ProductCellWriteHandler(List<Map<String, Object>> list){
this.list = list;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
protected void initCellStyle(Workbook workbook) {
this.workbooks = workbook;
}
@Override
protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
}
@Override
protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
}
@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 integer, Boolean isHead) {
this.initCellStyle(writeSheetHolder.getSheet().getWorkbook());
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), width * 350);
CellStyle cellStyle = workbooks.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置前景填充样式
cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());//前景填充色
Font font1 = workbooks.createFont();//设置字体
font1.setBold(true);
font1.setColor((short)1);
font1.setFontHeightInPoints((short)15);
cellStyle.setFont(font1);
cell.setCellStyle(cellStyle);
//其他列
if (!isHead){
CellStyle style = workbooks.createCellStyle();
DataFormat dataFormat = workbooks.createDataFormat();
style.setDataFormat(dataFormat.getFormat("@"));
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setAlignment(HorizontalAlignment.CENTER);
cell.setCellStyle(style);
}
//设置日期
if (!isHead && cell.getColumnIndex()==19 || !isHead && cell.getColumnIndex()==21|| !isHead && cell.getColumnIndex()==20){
CellStyle style = workbooks.createCellStyle();
DataFormat dataFormat = workbooks.createDataFormat();
style.setDataFormat(dataFormat.getFormat("yyyy/mm/dd hh:mm:ss"));
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setAlignment(HorizontalAlignment.CENTER);
cell.setCellStyle(style);
}
//设置金额
if (!isHead && cell.getColumnIndex()==15 ||!isHead && cell.getColumnIndex()==16||!isHead && cell.getColumnIndex()==22 ||!isHead && cell.getColumnIndex()==24||!isHead && cell.getColumnIndex()==25){
CellStyle style = workbooks.createCellStyle();
DataFormat dataFormat = workbooks.createDataFormat();
style.setDataFormat(dataFormat.getFormat("0.00"));
// style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("#,##0.00")); //货币
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setAlignment(HorizontalAlignment.CENTER);
cell.setCellStyle(style);
}
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
// k 为存在下拉数据集的单元格下表 v为下拉数据集
if(!CollectionUtils.isEmpty(list)){
list.forEach((item) -> {
String [] arr = (String[]) item.get("source");
// 下拉列表约束数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(arr);
// 设置下拉单元格的首行 末行 首列 末列
CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, Integer.valueOf(item.get("index").toString()), Integer.valueOf(item.get("index").toString()));
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示","此值与单元格定义格式不一致");
// validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
sheet.addValidationData(validation);
});
}
//this.setCellStyleType(cell,head,integer,writeSheetHolder);
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, 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) {
}
}
取值的工具类:
public class ResoveDropAnnotationUtil {
public static Map<String,Object> resove(DropDownSetField dropDownSetField){
if(!Optional.ofNullable(dropDownSetField).isPresent()){
return null;
}
Map<String,Object> map = new HashMap<>();
// 获取固定下拉信息
String[] source = dropDownSetField.source();
int indexFlag = dropDownSetField.index();
if(null != source && source.length > 0 && indexFlag >= 0){
map.put("index",indexFlag);
map.put("source",source);
return map;
}
// 获取动态的下拉数据
Class<? extends DropDownSetInterface>[] classes = dropDownSetField.sourceClass();
if(null != classes && classes.length > 0){
try {
DropDownSetInterface dropDownSetInterface = Arrays.stream(classes).findFirst().get().newInstance();
String[] dynamicSource = dropDownSetInterface.getSource();
int index = dropDownSetInterface.getIndex();
if(null != dynamicSource && dynamicSource.length > 0 && index >= 0){
map.put("index",index);
map.put("source",dynamicSource);
return map;
}
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return null;
}
}
导出并从注解中获取下拉内容:
String fileName = URLEncoder.encode("销售订单" + sdf.format(new Date()), "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
Field[] fields = SalesOrderExportExcel.class.getDeclaredFields();
// 响应字段对应的下拉集合
List<Map<String, Object>> list = new ArrayList<>();
Field field = null;
// 循环判断哪些字段有下拉数据集，并获取
for (int i = 0; i < fields.length; i++) {
field = fields[i];
// 解析注解信息
DropDownSetField dropDownSetField = field.getAnnotation(DropDownSetField.class);
if (null != dropDownSetField) {
Map<String, Object> sources = ResoveDropAnnotationUtil.resove(dropDownSetField);
if (sources != null) {
Map<String, Object> map = new HashMap<>();
map.put("index", sources.get("index"));
map.put("source", sources.get("source"));
list.add(map);
}
}
}
EasyExcel.write(response.getOutputStream(), SalesOrderExportExcel.class)
.registerWriteHandler(new ProductCellWriteHandler(list))
.excelType(ExcelTypeEnum.XLSX)
.sheet("销售订单")
.doWrite(salesOrderExportExcelList);
以上个人实践操作,可根据个人情况使用