#因?yàn)轫?xiàng)目中需要導(dǎo)入一些信息，但是這些信息比較不常見(jiàn)，且在項(xiàng)目字典數(shù)據(jù)中維護(hù)有這些數(shù)據(jù)，所以在導(dǎo)出模板的時(shí)候，把這些數(shù)據(jù)一并導(dǎo)出，可以減少用戶的編寫(xiě)，避免在導(dǎo)入的時(shí)候因?yàn)閿?shù)據(jù)錯(cuò)誤，發(fā)生一些業(yè)務(wù)問(wèn)題
直接開(kāi)始
一、首先引入依賴? ?easyexcel依賴
<!--easyexcel依賴-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
二、創(chuàng)建自定義注解
import java.lang.annotation.*;
/**
* 標(biāo)注導(dǎo)出的列為下拉框類型，并為下拉框設(shè)置內(nèi)容
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {
/**
* 固定下拉內(nèi)容
*/
String[] source() default {};
/**
* 動(dòng)態(tài)下拉內(nèi)容
*/
Class<? extends ExcelDynamicSelect>[] sourceClass() default {};
/**
* 設(shè)置下拉框的起始行，默認(rèn)為第二行
*/
int firstRow() default 1;
/**
* 設(shè)置下拉框的結(jié)束行，默認(rèn)為最后一行
*/
int lastRow() default 0x10000;
}
三、解析自定義注解接口
public interface ExcelDynamicSelect {
/**
* 獲取動(dòng)態(tài)生成的下拉框可選數(shù)據(jù)
* @return 動(dòng)態(tài)生成的下拉框可選數(shù)據(jù)
*/
String[] getSource();
}
四、創(chuàng)建一個(gè)實(shí)體類，（這邊以我項(xiàng)目?jī)?nèi)的演示）
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import org.springblade.basicdata.service.impl.DeptTemplateServiceImpl;
import org.springblade.basicdata.service.impl.SchoolTemplateServiceImpl;
import org.springblade.basicdata.service.impl.StationlateServiceImpl;
import org.springblade.basicdata.service.impl.ZgTypeslateServiceImpl;
import java.io.Serializable;
@Data
@HeadRowHeight(20)
@ContentRowHeight(18)
public class TeacherExcel implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 職工編號(hào)
*/
@ExcelProperty(index = 0, value = "職工編號(hào)")
@ColumnWidth(20)
private String no;
/**
* 姓名
*/
@ExcelProperty(index = 1, value = "姓名")
@ColumnWidth(30)
private String name;
/**
* 身份證
*/
@ExcelProperty(index = 2, value = "身份證號(hào)")
@ColumnWidth(25)
private String idCard;
/**
* 電話
*/
@ExcelProperty(index = 3, value = "聯(lián)系電話")
@ColumnWidth(15)
private String phone;
/**
* 學(xué)校id
*/
//需要自定義實(shí)現(xiàn) SchoolTemplateServiceImpl，下面有具體的實(shí)現(xiàn)方式
@ExcelSelected(sourceClass = SchoolTemplateServiceImpl.class)
@ExcelProperty(index = 4, value = "校區(qū)")
@ColumnWidth(45)
private String schoolName;
/**
* 部門
*/
//需要自定義實(shí)現(xiàn) DeptTemplateServiceImpl，下面有具體的實(shí)現(xiàn)方式
@ExcelSelected(sourceClass = DeptTemplateServiceImpl.class)
@ExcelProperty(index = 5, value = "部門")
@ColumnWidth(45)
private String bumenName;
/**
* 崗位類別
*/
//需要自定義實(shí)現(xiàn) StationlateServiceImpl，下面有具體的實(shí)現(xiàn)方式
@ExcelSelected(sourceClass = StationlateServiceImpl.class)
@ExcelProperty(index = 6, value = "崗位類別")
@ColumnWidth(15)
private String station;
/**
* 職工類型
*/
//需要自定義實(shí)現(xiàn) ZgTypeslateServiceImpl，下面有具體的實(shí)現(xiàn)方式
@ExcelSelected(sourceClass = ZgTypeslateServiceImpl.class)
@ExcelProperty(index = 7, value = "職工類型")
@ColumnWidth(15)
private String zhigongleibie;
/**
* 備注
*/
@ExcelProperty(index = 8, value = "備注")
@ColumnWidth(15)
private String remarks;
}五、重點(diǎn)是以下的代碼
1、以崗位類型為例，展示數(shù)據(jù)的實(shí)現(xiàn)方式
創(chuàng)建StationlateServiceImpl實(shí)現(xiàn)類，實(shí)現(xiàn)方法 為我自己的業(yè)務(wù)需求，你們可以根據(jù)你們的情況去修改，這邊返回給注解的是一個(gè)字符串的數(shù)組，也就是需要導(dǎo)出的下拉選數(shù)據(jù)
import java.util.List;
import java.util.stream.Collectors;
/**
* 崗位類別下拉選數(shù)據(jù)實(shí)現(xiàn)類
*/
public class StationlateServiceImpl implements ExcelDynamicSelect {
private static IDictClient dictClient;
static {
dictClient = SpringUtil.getBean(IDictClient.class);
}
@Override
public String[] getSource() {
R<List<Dict>> dictClientList =
dictClient.getList(DictEnum.POST_TYPE.getName());
if(Func.isNotEmpty(dictClientList) && dictClientList.getCode() == 200){
if(Func.isNotEmpty(dictClientList.getData())){
return Func.toStrArray(dictClientList.getData().stream().map(Dict::getDictValue).collect(Collectors.joining(",")));
}
}
return new String[0];
}
}Func.toStrArray方法就是講給定的逗號(hào)拼接的字符串轉(zhuǎn)為字符串?dāng)?shù)組，這個(gè)百度一下，一大堆文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-774185.html
六、EasyExcelUtil工具類
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Slf4j
public class EasyExcelUtil {
/**
* 創(chuàng)建即將導(dǎo)出的sheet頁(yè)（sheet頁(yè)中含有帶下拉框的列）
* @param head 導(dǎo)出的表頭信息和配置
* @param sheetNo sheet索引
* @param sheetName sheet名稱
* @param <T> 泛型
* @return sheet頁(yè)
*/
public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);
return EasyExcel.writerSheet(sheetNo, sheetName)
.head(head)
.registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
.build();
}
/**
* 解析表頭類中的下拉注解
* @param head 表頭類
* @param <T> 泛型
* @return Map<下拉框列索引, 下拉框內(nèi)容> map
*/
private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();
// getDeclaredFields(): 返回全部聲明的屬性；getFields(): 返回public類型的屬性
Field[] fields = head.getDeclaredFields();
for (int i = 0; i < fields.length; i++){
Field field = fields[i];
// 解析注解信息
ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
if (selected != null) {
ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
String[] source = excelSelectedResolve.resolveSelectedSource(selected);
if (source != null && source.length > 0){
excelSelectedResolve.setSource(source);
excelSelectedResolve.setFirstRow(selected.firstRow());
excelSelectedResolve.setLastRow(selected.lastRow());
if (property != null && property.index() >= 0){
selectedMap.put(property.index(), excelSelectedResolve);
} else {
selectedMap.put(i, excelSelectedResolve);
}
}
}
}
return selectedMap;
}
public static boolean isIDNumber(String IDNumber) {
if (IDNumber == null || "".equals(IDNumber)) {
return false;
}
// 定義判別用戶身份證號(hào)的正則表達(dá)式（15位或者18位，最后一位可以為字母）
String regularExpression = "(^[1-9]\\d{5}(18|19|20)\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$)|" +
"(^[1-9]\\d{5}\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}$)";
//假設(shè)18位身份證號(hào)碼:41000119910101123X 410001 19910101 123X
//^開(kāi)頭
//[1-9] 第一位1-9中的一個(gè) 4
//\\d{5} 五位數(shù)字 10001（前六位省市縣地區(qū)）
//(18|19|20) 19（現(xiàn)階段可能取值范圍18xx-20xx年）
//\\d{2} 91（年份）
//((0[1-9])|(10|11|12)) 01（月份）
//(([0-2][1-9])|10|20|30|31)01（日期）
//\\d{3} 三位數(shù)字 123（第十七位奇數(shù)代表男，偶數(shù)代表女）
//[0-9Xx] 0123456789Xx其中的一個(gè) X（第十八位為校驗(yàn)值）
//$結(jié)尾
//假設(shè)15位身份證號(hào)碼:410001910101123 410001 910101 123
//^開(kāi)頭
//[1-9] 第一位1-9中的一個(gè) 4
//\\d{5} 五位數(shù)字 10001（前六位省市縣地區(qū)）
//\\d{2} 91（年份）
//((0[1-9])|(10|11|12)) 01（月份）
//(([0-2][1-9])|10|20|30|31)01（日期）
//\\d{3} 三位數(shù)字 123（第十五位奇數(shù)代表男，偶數(shù)代表女），15位身份證不含X
//$結(jié)尾
boolean matches = IDNumber.matches(regularExpression);
//判斷第18位校驗(yàn)值
if (matches) {
if (IDNumber.length() == 18) {
try {
char[] charArray = IDNumber.toCharArray();
//前十七位加權(quán)因子
int[] idCardWi = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};
//這是除以11后，可能產(chǎn)生的11位余數(shù)對(duì)應(yīng)的驗(yàn)證碼
String[] idCardY = {"1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2"};
int sum = 0;
for (int i = 0; i < idCardWi.length; i++) {
int current = Integer.parseInt(String.valueOf(charArray[i]));
int count = current * idCardWi[i];
sum += count;
}
char idCardLast = charArray[17];
int idCardMod = sum % 11;
if (idCardY[idCardMod].toUpperCase().equals(String.valueOf(idCardLast).toUpperCase())) {
return true;
} else {
return false;
}
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
return false;
}
return matches;
}
public static boolean isMobile(String phone){
Pattern p = null;
Matcher m = null;
boolean b = false;
// 驗(yàn)證手機(jī)號(hào)
String s2="^[1](([3|5|6|7|8|9][\\d])|([4][4,5,6,7,8,9])|([6][2,5,6,7])|([7][^9])|([9][1,8,9]))[\\d]{8}$";
if(StringUtils.isNotBlank(phone)){
p = Pattern.compile(s2);
m = p.matcher(phone);
b = m.matches();
}
return b;
}
}七、SelectedSheetWriteHandler
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.Map;
@Data
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {
private final Map<Integer, ExcelSelectedResolve> selectedMap;
/**
* 設(shè)置閾值，避免生成的導(dǎo)入模板下拉值獲取不到，可自行設(shè)置數(shù)量大小
*/
private static final Integer LIMIT_NUMBER = 25;
/**
* Called before create the sheet
*/
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
/**
* Called after the sheet is created
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 這里可以對(duì)cell進(jìn)行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
selectedMap.forEach((k, v) -> {
// 設(shè)置下拉列表的行： 首行，末行，首列，末列
CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
// 如果下拉值總數(shù)大于25，則使用一個(gè)新sheet存儲(chǔ)，避免生成的導(dǎo)入模板下拉值獲取不到
if (v.getSource().length > LIMIT_NUMBER) {
//定義sheet的名稱
//1.創(chuàng)建一個(gè)隱藏的sheet 名稱為 hidden + k
String sheetName = "hidden" + k;
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet hiddenSheet = workbook.createSheet(sheetName);
for (int i = 0, length = v.getSource().length; i < length; i++) {
// 開(kāi)始的行數(shù)i，列數(shù)k
hiddenSheet.createRow(i).createCell(k).setCellValue(v.getSource()[i]);
}
Name category1Name = workbook.createName();
category1Name.setNameName(sheetName);
String excelLine = getExcelLine(k);
// =hidden!$H:$1:$H$50 sheet為hidden的 H1列開(kāi)始H50行數(shù)據(jù)獲取下拉數(shù)組
String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.getSource().length + 1);
// 將剛才設(shè)置的sheet引用到你的下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, rangeList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
// 設(shè)置存儲(chǔ)下拉列值得sheet為隱藏
int hiddenIndex = workbook.getSheetIndex(sheetName);
if (!workbook.isSheetHidden(hiddenIndex)) {
workbook.setSheetHidden(hiddenIndex, true);
}
}
// 設(shè)置下拉列表的值
DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
// 設(shè)置約束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止輸入非下拉選項(xiàng)的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
// validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "請(qǐng)輸入下拉選項(xiàng)中的內(nèi)容");
sheet.addValidationData(validation);
});
}
/**
* 返回excel列標(biāo)A-Z-AA-ZZ
*
* @param num 列數(shù)
* @return java.lang.String
*/
private String getExcelLine(int num) {
String line = "";
int first = num / 26;
int second = num % 26;
if (first > 0) {
line = (char) ('A' + first - 1) + "";
}
line += (char) ('A' + second) + "";
return line;
}
}
八、ExcelDynamicSelect
public interface ExcelDynamicSelect {
/**
* 獲取動(dòng)態(tài)生成的下拉框可選數(shù)據(jù)
* @return 動(dòng)態(tài)生成的下拉框可選數(shù)據(jù)
*/
String[] getSource();
}
九、ExcelSelectedResolve
@Data
@Slf4j
public class ExcelSelectedResolve {
/**
* 下拉內(nèi)容
*/
private String[] source;
/**
* 設(shè)置下拉框的起始行，默認(rèn)為第二行
*/
private int firstRow;
/**
* 設(shè)置下拉框的結(jié)束行，默認(rèn)為最后一行
*/
private int lastRow;
public String[] resolveSelectedSource(ExcelSelected excelSelected) {
if (excelSelected == null) {
return null;
}
// 獲取固定下拉框的內(nèi)容
String[] source = excelSelected.source();
if (source.length > 0) {
return source;
}
// 獲取動(dòng)態(tài)下拉框的內(nèi)容
Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
if (classes.length > 0) {
try {
ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
String[] dynamicSelectSource = excelDynamicSelect.getSource();
if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
return dynamicSelectSource;
}
} catch (InstantiationException | IllegalAccessException e) {
log.error("解析動(dòng)態(tài)下拉框數(shù)據(jù)異常", e);
}
}
return null;
}
}十、導(dǎo)出接口
/** 教師入職Excel模板導(dǎo)出 */
@ApiLog("教師入職Excel模板導(dǎo)出")
@GetMapping("export-template")
@ApiOperationSupport(order = 7)
@ApiOperation(value = "教師入職Excel模板導(dǎo)出")
public void exportTeacher(HttpServletResponse response, HttpServletRequest request) {
String filename = "教師入職信息導(dǎo)入模板";
try {
String userAgent = request.getHeader("User-Agent");
if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
// 針對(duì)IE或者以IE為內(nèi)核的瀏覽器：
filename = java.net.URLEncoder.encode(filename, "UTF-8");
} else {
// 非IE瀏覽器的處理:
filename = new String(filename.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", filename + Func.formatDateTime(new Date()) + ".xlsx"));
response.setHeader("Cache-Control", "no-cache");
response.setHeader("Pragma", "no-cache");
response.setDateHeader("Expires", -1);
response.setCharacterEncoding("UTF-8");
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet writeSheet = EasyExcelUtil.writeSelectedSheet(TeacherExcel.class, 0, "教師入職信息數(shù)據(jù)表");
excelWriter.write(new ArrayList<String>(), writeSheet);
excelWriter.finish();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}十一、導(dǎo)出效果圖
文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-774185.html
完結(jié)，有問(wèn)題歡迎指正，QQ 781999475
到了這里，關(guān)于EasyExcel導(dǎo)出帶下拉選數(shù)據(jù)的Excel數(shù)據(jù)導(dǎo)入模板的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
