第一种 不适合数据量大的下拉列
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.yunyang.simple.dbModel.dao.BaseDao;
import com.yunyang.simple.dbModel.service.BaseService;
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 org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
import org.springframework.web.context.ContextLoader;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 用户excel拦截器
*/
public class UserWriteHandler implements SheetWriteHandler {
/* @Autowired
BaseService baseService;
@Autowired
BaseDao dao;*/
private List<String> roleNameList;
private List<String> levelOneList;
private List<String> levelTwoList;
private List<String> levelThreeList;
private List<String> levelFourList;
private List<String> projectNameList;
public UserWriteHandler(List<String> roleNameList, List<String> levelOneList, List<String> levelTwoList, List<String> levelThreeList, List<String> levelFourList, List<String> projectNameList) {
this.roleNameList = roleNameList;
this.levelOneList = levelOneList;
this.levelTwoList = levelTwoList;
this.levelThreeList = levelThreeList;
this.levelFourList = levelFourList;
this.projectNameList = projectNameList;
}
/**
* 在空参构造方法里查询 动态的下拉列表
*/
public UserWriteHandler() {
BaseDao dao = GetBeanUtil.getBean(BaseDao.class);
List<String> list = dao.singleList("SELECT name FROM u_role");
/* for (int i = 0; i < 100; i++) {
list.remove(0);
}
*/
this.roleNameList = list;
this.levelOneList = list;
this.levelTwoList = list;
this.levelThreeList = list;
this.levelFourList = list;
this.projectNameList = list;
}
public static void main(String[] args) {
// BaseDao dao = wac.getBean(BaseDao.class);
// System.out.println(bean);
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
//装换数据
String[] roleNameArr = roleNameList.toArray(new String[roleNameList.size()]);
String[] levelOneArr = levelOneList.toArray(new String[levelOneList.size()]);
String[] levelTwoArr = levelTwoList.toArray(new String[levelTwoList.size()]);
String[] levelThreeArr = levelThreeList.toArray(new String[levelThreeList.size()]);
String[] levelFourArr = levelFourList.toArray(new String[levelFourList.size()]);
String[] projectNameArr = projectNameList.toArray(new String[projectNameList.size()]);
Map<Integer, String[]> mapDropDown = new HashMap<>();
mapDropDown.put(3, roleNameArr);
mapDropDown.put(4, levelOneArr);
mapDropDown.put(5, levelTwoArr);
mapDropDown.put(6, levelThreeArr);
mapDropDown.put(7, levelFourArr);
mapDropDown.put(8, projectNameArr);
Sheet sheet = writeSheetHolder.getSheet();
///开始设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
//设置下拉框
for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
/*起始行、终止行、起始列、终止列**/
CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
/*设置下拉框数据**/
DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
DataValidation dataValidation = helper.createValidation(constraint, addressList);
sheet.addValidationData(dataValidation);
}
}
}
第二中，数据量大时推荐使用
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.yunyang.simple.dbModel.dao.BaseDao;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SpinnerLongHandler implements SheetWriteHandler {
/**
* 下拉框内容map Integer数据所在列数，string[]下拉数据列表
*/
private Map<Integer, String[]> spinnerMap;
/**
* 导出数据的list大小
*/
private int dataSize;
public SpinnerLongHandler(Map<Integer, String[]> spinnerMap, int dataSize) {
this.spinnerMap = spinnerMap;
this.dataSize = dataSize;
}
public SpinnerLongHandler() {
BaseDao dao = GetBeanUtil.getBean(BaseDao.class);
List<String> list = dao.singleList("SELECT name FROM u_role");
List<String> roleNameList = list;
String[] roleNameArr = roleNameList.toArray(new String[roleNameList.size()]);
this.spinnerMap=new HashMap<>();
this.spinnerMap.put(3, roleNameArr);
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
//获取一个workbook
Sheet sheet = writeSheetHolder.getSheet();
//设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
//定义sheet的名称
String hiddenName = "hidden";
//1.创建一个隐藏的sheet 名称为 hidden
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet hidden = workbook.createSheet(hiddenName);
Name category1Name = workbook.createName();
category1Name.setNameName(hiddenName);
for (Map.Entry<Integer, String[]> entry : spinnerMap.entrySet()) {
//下拉框的起始行,结束行,起始列,结束列
CellRangeAddressList addressList = new CellRangeAddressList(1, dataSize + 500, entry.getKey(), entry.getKey());
//获取excel列名
String excelLine = getExcelLine(entry.getKey());
//2.循环赋值
String[] values = entry.getValue();
for (int i = 0, length = values.length; i < length; i++) {
// 3:表示你开始的行数 3表示 你开始的列数
Row row = hidden.getRow(i);
if (row == null) {
row = hidden.createRow(i);
}
row.createCell(entry.getKey()).setCellValue(values[i]);
}
//4. =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
String refers = "=" + hiddenName + "!$" + excelLine +
"$1:$" + excelLine + "$" + (values.length);
//5 将刚才设置的sheet引用到你的下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
//设置列为隐藏
int hiddenIndex = workbook.getSheetIndex("hidden");
if (!workbook.isSheetHidden(hiddenIndex)) {
workbook.setSheetHidden(hiddenIndex, true);
}
}
/**
* @param num 列数
* @return java.lang.String
* @Description 返回excel列标A-Z-AA-ZZ
* @Author chou
* @Date 2020/9/8
*/
public static 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;
}
}
调用
@ApiOperation("用户导入模板动态生成下载")
@GetMapping("/download")
@IgnoreToken
public void download(HttpServletResponse response) throws IOException {
/* response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//编码 防止中文乱码
fileName = URLEncoder.encode(fileName,"UTF-8");
response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");*/
response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode("userTemplate.xlsx","UTF-8"));
List<UUserVO> list=new ArrayList<>();
list.add(new UUserVO());
//直接在实体类的空参构造里 查询变化的参数
SpinnerLongHandler userWriteHandler = new SpinnerLongHandler();
EasyExcel.write(response.getOutputStream(), UUserVO.class)
.sheet("用户测试")
.registerWriteHandler(userWriteHandler)
.doWrite(list);//模板放入空数据 不是模板放查询数据
}
