1. Brief introduction
During the system development process, we will encounter various export and import requirements. EasyExcel is currently a popular export and import framework in addition to POI. The advantages and disadvantages of EasyExcel and POI will not be introduced here. The main introduction is The following is a demand scenario: the attribute value source in the export template is an enumeration value or other columns that require drop-down constraints need to be set to drop-down
2. Entity class preparation
State enumeration class State:
public enum State {<!-- -->
GRADUATE("graduate", "graduate"),
INSTUDY("Reading", "studying"),
LEAVEL("leave", "leavel")
\t
private String cn;
private String en;
\t
State(String cn, String en) {<!-- -->
this.cn = cn;
this.en = en;
}
}
New annotation class SelectValueFrom (used to identify the source of drop-down attribute values):
@Target({<!-- -->ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface SelectValueFrom {<!-- -->
//The position of the drop-down column in the excel table
int index();
//The drop-down value is the system enumeration value
Class[] enumValue() default {<!-- -->};
//Custom drop-down string
String[] stringValue() default {<!-- -->};
}
Entity class Student:
@Entity
@Data
public class Student {<!-- -->
private Long id;
private String name;
private String grade;
private Integer age;
\t//state
private State state;
}
Export class StudentExcelDto:
@Data
public class StudentExcelDto {<!-- -->
@ExcelProperty("name")
private String name;
@ExcelProperty("grade")
private String grade;
@ExcelProperty("Age")
private Integer age;
@SelectValueFrom(index = 4, enumValue = State.class)
@ExcelProperty("status")
private State state;
}
3. Set attribute column drop-down interceptor writing
@Slf4j
public class SelectColumnWriteHandler implements SheetWriteHandler {<!-- -->
private Map<Integer, List<String>> selectMap;
//Start rendering the drop-down list from a certain row
private int index;
private char[] alphabetArr = new char[]{<!-- -->'A', 'B', 'C', 'D', 'E', ' F', 'G', 'H', 'I', 'J', 'K', 'L',
'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', ' U', 'V', 'W', 'X', 'Y', 'Z'};
public SelectColumnWriteHandler(Map<Integer, List<String>> selectMap, int index) {<!-- -->
this.selectMap = selectMap;
this.index = index;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {<!-- -->
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {<!-- -->
if (selectMap == null || selectMap.size() == 0) {<!-- -->
return;
}
// Get the current sheet page
Sheet curSheet = writeSheetHolder.getSheet();
DataValidationHelper helper = curSheet.getDataValidationHelper();
for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) {<!-- -->
//Define the name of the data source sheet
String hiddenName = "hidden" + curSheet.getSheetName() + entry.getKey();
//Create a sheet page to store the data source, and finally need to hide it
Workbook workbook= writeWorkbookHolder.getWorkbook();
Sheet hidden = workbook.createSheet(hiddenName);
//Set the first row, last row, first column, and last column of the drop-down cell
CellRangeAddressList rangeAddressList = new CellRangeAddressList(index, 65533, entry.getKey(), entry.getKey());
int rowLen = entry.getValue().size();
//Set the value of the dictionary sheet page, one dictionary item for each column
for (int i = 0; i < rowLen; i + + ) {<!-- -->
Row row = hidden.getRow(i);
if (row == null) {<!-- -->
row = hidden.createRow(i);
}
row.createCell(entry.getKey()).setCellValue(entry.getValue().get(i));
}
String excelColumn = getExcelColumnEn(entry.getKey());
// Drop-down box data source eg: dictionary sheet!$B1:$B2
String refers = hiddenName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
//Create a name that can be referenced by other cells
Name name = workbook.createName();
// Set the name of the name
name.setNameName("dict" + curSheet.getSheetName() + entry.getKey());
//Set formula
name.setRefersToFormula(refers);
//Set reference constraints
DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + curSheet.getSheetName() + entry.getKey());
//Set constraints
DataValidation validation = helper.createValidation(constraint, rangeAddressList);
if (validation instanceof HSSFDataValidation) {<!-- -->
validation.setSuppressDropDownArrow(false);
} else {<!-- -->
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
}
//Prevent input of non-drop-down box values
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.createErrorBox("Prompt", "This value is inconsistent with the cell definition format!");
//Add drop-down box constraints
writeSheetHolder.getSheet().addValidationData(validation);
//Set the data source sheet page to be hidden
int hiddenIndex = workbook.getSheetIndex(hiddenName);
if (!workbook.isSheetHidden(hiddenIndex)) {<!-- -->
workbook.setSheetHidden(hiddenIndex, true);
}
}
}
/**
* Obtain the corresponding English column name through the index of the column in the Excel table
* @param num
* @return
*/
private String getExcelColumnEn(int num) {<!-- -->
String column = "";
int len = alphabetArr.length - 1;
if (num <= len) {<!-- -->
column = alphabetArr[num] + "";
} else {<!-- -->
int first = num / len;
int second = num % len;
column = alphabetArr[first - 1] + "";
if (second == 0) {<!-- -->
column = column + alphabetArr[len] + "";
} else {<!-- -->
column = column + alphabetArr[second - 1] + "";
}
}
return column;
}
}
Export tool class ExcelUtil:
public class ExcelUtil {<!-- -->
/**Use reflection to obtain the annotations of the class attributes, and then put the attribute value source into the Map and return it to the caller
*@param tclass exports the Class object of the entity class (StudentExcelDto)
*@param supplier functional interface, returns a customized drop-down map
**/
public static <T> Map<Integer, List<String>> buildSelectMap (Class<T> tClass, Supplier<Map<Integer, List<String>>> supplier) {<!-- -->
Map<Integer, List<String>> res = new HashMap<>();
if (null != supplier) {<!-- -->
res.putAll(supplier.get());
}
Field[] fields = tClass.getDeclaredFields();
try {<!-- -->
for (Field field : fields) {<!-- -->
SelectValueFrom selectValueFrom = field.getAnnotation(SelectValueFrom.class);
if (null == selectValueFrom) {<!-- -->
continue;
}
int index = selectValueFrom.index();
String[] strValues = selectValueFrom.stringValue();
List<String> valueList = res.getOrDefault(index, new ArrayList<>());
if (strValues.length != 0) {<!-- -->
valueList.addAll(Arrays.asList(strValues));
}
Class[] enumValues = selectValueFrom.enumValue();
if (enumValues.length != 0) {<!-- -->
for (Class enumValue : enumValues) {<!-- -->
Object[] enumConstants = enumValue.getEnumConstants();
Field cnField = enumValue.getDeclaredField("cn");
cnField.setAccessible(Boolean.TRUE);
for (Object enumConstant : enumConstants) {<!-- -->
valueList.add(cnField.get(enumConstant).toString());
}
}
}
if (CollectionUtils.isNotEmpty(valueList)) {<!-- -->
res.put(index, valueList);
}
}
} catch (Exception e) {<!-- -->
}
return res;
}
}
Test classTest:
public class Test {<!-- -->
public static void main(String[] args) throws Exception {<!-- -->
String fileName ="C:\\workspace\template.xlsx";
Map<Integer, List<String>> selectValueMap = ExcelUtil.buildSelectMap(StudentExcelDto.class, null);
EasyExcel.write(fileName, StudentExcelDto.class)
.sheet("Sheet1")
.head(StudentExcelDto.class)
.registerWriteHandler(new SelectColumnWriteHandler(selectValueMap, 1))
.doWrite(new ArrayList<>());
}