private static final int FLAG1 = 1;
private static final int FLAG2 = 2;
private static final int FLAG3 = 3;
/**
* 动态列头
*
* @return
*/
private List<List<String>> head(Integer headFlag) {
List<User> userList = new ArrayList<>();
int size = userList.size();
String fields[] = new String[size];
for (int i = 0; i < userList.size(); i++) {
String name = userList.get(i).getName();
fields[i] = name;
}
List<String> strings = Arrays.asList(fields);
ArrayList<String> heads = new ArrayList<>();
heads.addAll(strings);
//不同头标记额外不同的列头
switch (headFlag) {
case 1:
heads.add("xxxx");
break;
case 2:
heads.add("xxxx");
break;
case 3:
heads.add("xxxx");
break;
}
List<List<String>> headList = new ArrayList<List<String>>();
int length = heads.size();
for (int i = 0; i < length; i++) {
List<String> list = new ArrayList<>();
list.add(heads.get(i));
headList.add(list);
}
return headList;
}
/**
* 动态猎头要写出的数据
*
* @param users
* @param headFlag FLAG1 FLAG2 FLAG3
* @return
*/
private List<List<Object>> dataList(List<User> users, Integer headFlag) {
List<List<Object>> list = new ArrayList<List<Object>>();
if (!ObjectUtils.isEmpty(users)) {
users.forEach(archive -> {
List<Object> data = new ArrayList<Object>();
data.add(archive.getAge());
//不同类型的表头要写的数据
if (FLAG1 == headFlag) {
data.add("xxxxxx");
}
if (FLAG2 == headFlag) {
data.add("xxxxxx");
}
if (FLAG3 == headFlag) {
data.add("xxxxxx");
}
list.add(data);
});
}
return list;
}
/**
* 实际写出的数据
* key: FLAG1 FLAG2 FLAG3
* value: 对应的数据
*/
public void handelExcelWriteData() {
//实际要写出的数据
List<User> waitUserList = new ArrayList<>();
List<User> userDateList = new ArrayList<>();
List<User> noUserList = new ArrayList<>();
Map<List<List<String>>, List<User>> hashMap1 = new HashMap<>();
Map<List<List<String>>, List<User>> hashMap2 = new HashMap<>();
Map<List<List<String>>, List<User>> hashMap3 = new HashMap<>();
List<List<String>> head1 = head(FLAG1);
hashMap1.put(head1, waitUserList);
List<List<String>> head2 = head(FLAG2);
hashMap2.put(head2, userDateList);
List<List<String>> head3 = head(FLAG3);
hashMap3.put(head3, noUserList);
Map<Integer, Map<List<List<String>>, List<User>>> dataListMap = new HashMap();
//不同类型对应的数据
dataListMap.put(FLAG1, hashMap1);
dataListMap.put(FLAG2, hashMap2);
dataListMap.put(FLAG3, hashMap3);
this.handelExcelData(dataListMap);
}
/**
* 处理excel数据
* @param dataListMap
*/
private void handelExcelData(Map<Integer, Map<List<List<String>>, List<User>>> dataListMap) {
String path = "实际要导出的路径";
try {
File parentFile = new File(path);
if (!parentFile.exists()) {
parentFile.mkdirs();
parentFile.createNewFile();
}
String[] split0 = new String[0];//选择下拉框 TODO 根据实际情况去做下列数据
String[] split1 = new String[0];//选择下拉框 TODO 根据实际情况去做下列数据
ExcelWriter excelWriter = EasyExcel.write(path).build();
Iterator<Map.Entry<Integer, Map<List<List<String>>, List<User>>>> iterator = dataListMap.entrySet().iterator();
while (iterator.hasNext()) {
Map.Entry<Integer, Map<List<List<String>>, List<User>>> next = iterator.next();
//key FLAG1 FLAG2 FLAG3
Integer key = next.getKey();
if (key == FLAG1) {
this.writeExcel(next, FLAG1, excelWriter, split0, split1);
} else if (key == FLAG2) {
this.writeExcel(next, FLAG2, excelWriter, split0, split1);
} else if (key == FLAG3) {
this.writeExcel(next, FLAG3, excelWriter, split0, split1);
}
}
excelWriter.finish();
} catch (Exception e) {
//异常写出空文件
ExcelWriter excelWriter = EasyExcel.write(path).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0).sheetName("sheet" + 0)
.build();
excelWriter.write(new ArrayList<>(), writeSheet);
excelWriter.finish();
}
}
/**
*
* @param next 实际数据
* @param controlHeadFlag
* @param excelWriter
* @param split0
* @param split1
*/
private void writeExcel(Map.Entry<Integer, Map<List<List<String>>, List<User>>> next,
Integer controlHeadFlag, ExcelWriter excelWriter, String[] split0,
String[] split1) {
Map<List<List<String>>, List<User>> value = next.getValue();
Iterator<Map.Entry<List<List<String>>, List<User>>> iterator1 = value.entrySet().iterator();
//里面只有一个
while (iterator1.hasNext()) {
Map.Entry<List<List<String>>, List<User>> next1 = iterator1.next();
//动态要写出的数据
List<User> value1 = next1.getValue();
if (!CollectionUtils.isEmpty(value1)) {
//行号（有多少数据就有多少行）
int row = value1.size();
//动态列头
List<List<String>> key1 = next1.getKey();
//列号（其实就是下拉框在第几列）
int raw = key1.size();
List<List<Object>> lists = this.dataList(value1, controlHeadFlag);
WriteSheet writeSheet = EasyExcel.writerSheet(controlHeadFlag - 1).sheetName("sheet" + controlHeadFlag)
.head(key1)
.registerWriteHandler(new SpinnerWriteHandler(split0, split1, row, raw))
.build();
excelWriter.write(lists, writeSheet);
}
}
}
/**
* 下拉监听
* 动态下拉框输出处理
*/
@Slf4j
public class SpinnerWriteHandler implements SheetWriteHandler {
// 下拉框数组
private String[] split0;
// 下拉框数组
private String[] split1;
//行
private Integer row;
//列
private Integer raw;
Map<Integer, String> staticMap = new HashMap();
{
staticMap.put(1, "XXXXXX");//新建的sheet用于存放下拉框内的数据,
staticMap.put(2, "XXXXXX");
}
public SpinnerWriteHandler(String[] split0, String[] split1, Integer row, Integer raw) {
this.split0 = split0;
this.split1 = split1;
this.row = row;
this.raw = raw;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// //设置下拉框数据
Map<Integer, String[]> map = new HashMap<>();
//这里时两列相邻的下拉框
map.put(raw - 1, this.split0); //动态列号
map.put(raw - 2, this.split1);//动态列号
//动态解决单个单元格下拉框超过255字符
log.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
int flag = 0;
// 省市下拉框
for (Map.Entry<Integer, String[]> entry : map.entrySet()) {
++flag;
// 创建sheet，突破下拉框255的限制
//获取一个workbook
Workbook workbook = writeWorkbookHolder.getWorkbook();
//定义sheet的名称
String sheetName = staticMap.get(flag);
//1.创建一个隐藏的sheet
Name name = workbook.getName(sheetName);
String[] value = entry.getValue();
if (!ObjectUtils.isEmpty(value)) {
if (ObjectUtils.isEmpty(name)) {
Sheet sheet = workbook.createSheet(sheetName);
Name category1Name = workbook.createName();
category1Name.setNameName(sheetName);
int length1 = entry.getValue().length;
for (int i = 0, length = length1; i < length; i++) {
// i:表示你开始的行数 0表示你开始的列数
sheet.createRow(i).createCell(0).setCellValue(value[i]);
}
if (!ObjectUtils.isEmpty(value)) {
//从被创建的sheet第一个单元格开始向下填充 填充到实际数据长度【value.length的行号】
category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (value.length));
// sheet设置隐藏
workbook.setSheetHidden(workbook.getSheetIndex(sheetName), true);
}
} else {
Name category1Name = workbook.getName(sheetName);
Sheet sheet1 = workbook.getSheet(sheetName);
int length1 = entry.getValue().length;
for (int i = 0, length = length1; i < length; i++) {
// i:表示你开始的行数 0表示你开始的列数
sheet1.createRow(i).createCell(0).setCellValue(value[i]);
}
if (!ObjectUtils.isEmpty(value)) {
category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (value.length));
}
}
//从第一行填充至row行（包含），第 entry.getKey() 列至entry.getKey()列【因为只填充一个单元格,所以起始一致】
CellRangeAddressList addressList = new CellRangeAddressList(1, row, entry.getKey(), entry.getKey());
DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
DataValidation dataValidation3 = helper.createValidation(constraint8, addressList);
writeSheetHolder.getSheet().addValidationData(dataValidation3);
//处理Excel兼容性问题
if (dataValidation3 instanceof XSSFDataValidation) {
dataValidation3.setSuppressDropDownArrow(true);
dataValidation3.setShowErrorBox(true);
} else {
dataValidation3.setSuppressDropDownArrow(false);
}
Sheet sheet0 = writeSheetHolder.getSheet();
//5 将刚才设置的sheet引用到你的下拉列表中
sheet0.addValidationData(dataValidation3);
}
}
}
}