需求描述：java后端开发过程中，为了满足动态生成excel模板并设置指定单元格为下拉框，且下拉框的数据项来源为动态查询的需求，在基于easyExcel的情况下，使用模板填充的方式，完成该需求。
1. control层
public void getTemp(HttpServletResponse response) throws IOException{
tempService.getTemp(response);
}
2. service层
void getTemp(HttpServletResponse response);
3. service实现类（模板获取及导出相关配置设定，业务逻辑处理等）：
@Override
public void getTemp(HttpServletResponse response){
//动态模板需填充的数据
List<Object> data = new ArrayList<>();
//下拉列表1数据项
List<String> selectList1 = new ArrayList<>();
//下拉列表2数据项
List<String> selectList2 = new ArrayList<>();
//将下拉列表数据放置在一个map中
Map<Integer, List<String>> selectMap = new HashMap<>();
selectMap.put(1,selectList1);
selectMap.put(2,selectList2);
//此处firstRow为需要设置下拉框的起始行位置，可根据自身需求需要调整设置
//此处lastRow为需要设置下拉框的截止行位置，可根据自身需求需要调整设置
Integer firstRow = 0；
Integet lastRow = 100;
//使用模板填充导出的相关配置
OutputStream out = null;
BufferedOutputStream bos = null;
try {
//获取到已提前制作好的填充模板
String templateFileName = FileUtil.getPath() + "template" + File.separator + "Temp.xlsx";
//为动态生成的模板命名（带时间区分）
String fileNameWithTime = "导入模板" + DateTimeUtil.formatDate(new Date(), DateTimeUtil.SHORT_TIME_FORMAT) + ".xlsx";
//设置字符编码标准等
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(fileNameWithTime,"utf-8");
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
out = response.getOutputStream();
bos = new BufferedOutputStream(out);
ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(templateFileName).build();
//此处用到的"registerWriteHandler()"用于设置相应格式，“SelectSheetWriteHandler(selectMap,firstRow,lastRow)”为封装的设置单元格下拉框的工具类
WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(new SelectSheetWriteHandler(selectMap,firstRow,100)).build();
//向下新增行填充Config
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(data,fillConfig,writeSheet);
excelWriter.finish();
bos.flush();
}catch (IOException e){
e.printStackTrace();
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/json");
try {
response.getWriter().println("打印失败");
}catch (IOException ex){
ex.printStackTrace();
}
}
}
4. SelectSheetWriteHandler工具类封装
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.List;
import java.util.Map;
/**
* @author XXX
* @Description: 处理下拉
* @date 2022/8/10 17:39
*/
public class SelectSheetWriteHandler implements SheetWriteHandler {
/**
* 下拉框数据项Map, key为第几列，List<String>为下拉框数据项
*/
private Map<Integer, List<String>> selectMap;
/**
* 设置下拉框位置首行
*/
private Integer firstRow;
/**
* 设置下拉框位置末行
*/
private Integer lastRow;
/**
* 数据字典集
*/
private char[] alphabet = 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'};
/**
* @param selectMap 设置下拉框的数据项
* @param firstRow 设置下拉框的开始行（实质从firstRow的下一行开始生效）
* @param lastRow 设置下拉框的最后一行
*/
public SelectSheetWriteHandler(Map<Integer, List<String>> selectMap,Integer firstRow, Integer lastRow){
this.selectMap = selectMap;
this.firstRow = firstRow;
this.lastRow = lastRow;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder)
{
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if(selectMap == null || selectMap.size() ==0){
return;
}
Sheet sheet = writeSheetHolder.getSheet();
/// 开始设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();// 设置下拉框
String dictSheetName = "字典sheet";
Workbook workbook = writeWorkbookHolder.getWorkbook();
//数据字典的sheet页
Sheet dictSheet = workbook.createSheet(dictSheetName);
//将数据字典的sheet隐藏（对用户不可见）
workbook.setSheetHidden(workbook.getSheetIndex(dictSheet),true);
for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) {
/*** 起始行、终止行、起始列、终止列 **/
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, entry.getKey(), entry.getKey());
int rowLen = entry.getValue().size();
// 设置字典sheet页的值 每一列一个字典项
for (int i = 0; i < rowLen; i++) {
Row row = dictSheet.getRow(i);
if (row == null) {
row = dictSheet.createRow(i);
}
row.createCell(entry.getKey()).setCellValue(entry.getValue().get(i));
}
String excelColumn = getExcelColumn(entry.getKey());
// 下拉框数据来源 eg:字典sheet!$B1:$B2
String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
// 创建可被其他单元格引用的名称
Name name = workbook.createName();
// 设置名称的名字
name.setNameName("dict" + entry.getKey());
// 设置公式
name.setRefersToFormula(refers);
/*** 设置下拉框数据 **/
//DataValidationConstraint constraint = helper.createExplicitListConstraint((String[]) entry.getValue().toArray());
DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey());
DataValidation dataValidation = helper.createValidation(constraint, addressList);
/*** 处理Excel兼容性问题 **/
if (dataValidation instanceof HSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(false);
} else {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}
//阻止输入非下拉框的值
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.createErrorBox("提示", "此值与单元格定义格式不一致！");
sheet.addValidationData(dataValidation);
}
}
/**
* 将数字列转化成为字母列
* 主要作用在于根据传入的列获取数据字典sheet中对应列
* @param num
* @return
*/
private String getExcelColumn(int num) {
String column = "";
int len = alphabet.length - 1;
int first = num / len;
int second = num % len;
if (num <= len) {
column = alphabet[num] + "";
} else {
column = alphabet[first - 1] + "";
if (second == 0) {
column = column + alphabet[len] + "";
} else {
column = column + alphabet[second - 1] + "";
}
}
return column;
}
}
5. FileUtil工具类
public final class FileUtil {
public static InputStream getResourcesFileInputStream(String fileName){
return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName);
}
public static String getPath(){
return FileUtil.class.getResource("/").getPath();
}
public static File createNewFile(String pathName){
File file = new File(getPath() + pathName);
if(file.exists()){
file.delete();
}else{
if(!file.getParentFile().exists()){
file.getParentFile().mkdirs();
}
}
return file;
}
public static File readFile(String pathName){
return new File(getPath() + pathName);
}
}
通过以上流程，即可实现本文开端描述的需求，如果对您有所帮助，可以点赞收藏哦~
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
