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.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* @author Tian liyuan
* 四级级联写入处理器
*/
public class CascadeWriteHandler implements SheetWriteHandler {
private static 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'};
//第一级数据
private final List<String> firstList;
//第一二级组合数据
Map<String, List<String>> firstSecondMap;
//第二三级组合数据
Map<String, List<String>> secondThirdMap;
//第三四级组合数据
Map<String, List<String>> thirdFourthMap;
//第一列位置
private final int firstIndex;
//第二列位置
private final int secondIndex;
//第三列位置
private final int thirdIndex;
//第四列位置
private final int fourthIndex;
public CascadeWriteHandler(List<String> firstList, Map<String, List<String>> firstSecondMap, Map<String, List<String>> secondThirdMap, Map<String, List<String>> thirdFourthMap, int firstIndex, int secondIndex, int thirdIndex, int fourthIndex) {
this.firstList = firstList;
this.firstSecondMap = firstSecondMap;
this.secondThirdMap = secondThirdMap;
this.thirdFourthMap = thirdFourthMap;
this.firstIndex = firstIndex;
this.secondIndex = secondIndex;
this.thirdIndex = thirdIndex;
this.fourthIndex = fourthIndex;
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
//获取工作簿
Sheet sheet = writeSheetHolder.getSheet();
Workbook book = writeWorkbookHolder.getWorkbook();
//创建一个专门用来存放地区信息的隐藏sheet页
//因此不能在现实页之前创建,否则无法隐藏。
Sheet hideSheet = book.createSheet("site");
book.setSheetHidden(book.getSheetIndex(hideSheet), true);
// 将具体的数据写入到每一行中,行开头为父级区域,后面是子区域。
int rowId = 0;
Row proviRow = hideSheet.createRow(rowId++);
proviRow.createCell(0).setCellValue("大类列表");
for (int i = 0; i < firstList.size(); i++) {
Cell proviCell = proviRow.createCell(i + 1);
proviCell.setCellValue(firstList.get(i));
}
Iterator<String> keyIterator = firstSecondMap.keySet().iterator();
while (keyIterator.hasNext()) {
String key = keyIterator.next();
List<String> son = firstSecondMap.get(key);
Row row = hideSheet.createRow(rowId++);
for (int i = 0; i < son.size(); i++) {
Cell cell = row.createCell(i + 1);
cell.setCellValue(son.get(i));
}
// 添加名称管理器
String range = getRange(1, rowId, son.size());
Name name = book.createName();
name.setNameName(key);
String formula = "site!" + range;
name.setRefersToFormula(formula);
}
for (String key : secondThirdMap.keySet()) {
List<String> son = secondThirdMap.get(key);
Row row = hideSheet.createRow(rowId++);
for (int i = 0; i < son.size(); i++) {
Cell cell = row.createCell(i + 1);
cell.setCellValue(son.get(i));
}
// 添加名称管理器
String range = getRange(1, rowId, son.size());
Name name = book.createName();
name.setNameName(key);
String formula = "site!" + range;
name.setRefersToFormula(formula);
}
for (String key : thirdFourthMap.keySet()) {
List<String> son = thirdFourthMap.get(key);
Row row = hideSheet.createRow(rowId++);
for (int i = 0; i < son.size(); i++) {
Cell cell = row.createCell(i + 1);
cell.setCellValue(son.get(i));
}
// 添加名称管理器
String range = getRange(1, rowId, son.size());
Name name = book.createName();
name.setNameName(key);
String formula = "site!" + range;
name.setRefersToFormula(formula);
}
///开始设置(大类小类)下拉框
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
// 大类规则
DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(firstList.toArray(new String[]{}));
CellRangeAddressList expRangeAddressList = new CellRangeAddressList(2, 999, firstIndex, firstIndex);//todo 调整模板后位置需同步修改firstCol
setValidation(sheet, dvHelper, expConstraint, expRangeAddressList, "提示", "你输入的值未在备选列表中,请下拉选择合适的值");
// 小类规则(各单元格按个设置)
// "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据,如果A2是浙江省,那么此处就是浙江省下面的市
// 为了让每个单元格的公式能动态适应,使用循环挨个给公式。
// 循环几次,就有几个单元格生效,次数要和上面的大类影响行数一一对应,要不然最后几个没对上的单元格实现不了级联
for (int i = 3; i < 1000; i++) {
CellRangeAddressList rangeAddressList = new CellRangeAddressList(i - 1, i - 1, secondIndex, secondIndex);//todo 调整模板后位置需同步修改firstCol
DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($" + getExcelColumn(secondIndex - 1) + "$" + i + ")");//todo 调整模板后位置需同步修改字母
setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中,请下拉选择合适的值");
}
for (int i = 3; i < 1000; i++) {
CellRangeAddressList rangeAddressList = new CellRangeAddressList(i - 1, i - 1, thirdIndex, thirdIndex);//todo 调整模板后位置需同步修改firstCol
DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($" + getExcelColumn(thirdIndex - 1) + "$" + i + ")");//todo 调整模板后位置需同步修改字母
setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中,请下拉选择合适的值");
}
for (int i = 3; i < 1000; i++) {
CellRangeAddressList rangeAddressList = new CellRangeAddressList(i - 1, i - 1, fourthIndex, fourthIndex);//todo 调整模板后位置需同步修改firstCol
DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($" + getExcelColumn(fourthIndex - 1) + "$" + i + ")");//todo 调整模板后位置需同步修改字母
setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中,请下拉选择合适的值");
}
}
/**
* 设置验证规则
*
* @param sheet sheet对象
* @param helper 验证助手
* @param constraint createExplicitListConstraint
* @param addressList 验证位置对象
* @param msgHead 错误提示头
* @param msgContext 错误提示内容
*/
private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList, String msgHead, String msgContext) {
DataValidation dataValidation = helper.createValidation(constraint, addressList);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox(msgHead, msgContext);
sheet.addValidationData(dataValidation);
}
/**
* @param offset 偏移量,如果给0,表示从A列开始,1,就是从B列
* @param rowId 第几行
* @param colCount 一共多少列
* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
* @author denggonghai 2016年8月31日 下午5:17:49
*/
public String getRange(int offset, int rowId, int colCount) {
char start = (char) ('A' + offset);
if (colCount <= 25) {
char end = (char) (start + colCount - 1);
return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
} else {
char endPrefix = 'A';
char endSuffix = 'A';
if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间,包括边界(仅两次字母表计算)
if ((colCount - 25) % 26 == 0) {// 边界值
endSuffix = (char) ('A' + 25);
} else {
endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
}
} else {// 51以上
if ((colCount - 25) % 26 == 0) {
endSuffix = (char) ('A' + 25);
endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
} else {
endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
endPrefix = (char) (endPrefix + (colCount - 25) / 26);
}
}
return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
}
}
/**
* 将数字列转化成为字母列
*/
private static String getExcelColumn(int num) {
String column = "";
int len = alphabet.length - 1;
int first = num / len;
int second = num % len;
if (num <= len) {
column = String.valueOf(alphabet[num]);
} else {
column = String.valueOf(alphabet[first - 1]);
if (second == 0) {
column = column + alphabet[len];
} else {
column = column + alphabet[second - 1];
}
}
return column;
}
}
使用: