现在有一个需求，从本地导入一个Excel，在本地这个Excel的指定列插入三级联动（省市区）。
下图是本地（文件路径为C:\Users\白杉\Desktop\555.xlsx）一个普通的Excel，现在要给省、市、区这三列加上三级联动的效果。
我要指定在第5列（省）开始插入三级联动，这是代码跑完后的效果（只写了2个测试数据）：
以下是具体操作步骤，CharStatusEnum是我结合三级联动代码，优化第n列插入与对应字符处理了映射关系，无需变动。
1、导入jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>2、抽取一个ExcelUtils的导入方法
package excel.utils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
public class ExcelUtils {
private static final Integer FIRSTROW = 1;
private static final Integer ENDROW = 3000;
/**
* @param filePath 文件地址名称
* @param mapOneList 一级省数据
* @param map 二级市、区数据
* @param startCol 三级联动插入的指定列
*/
public static void export(String filePath, List<String> mapOneList, Map<String, List<String>> map, Integer startCol) throws Exception {
List list = new ArrayList();
FileInputStream fis = new FileInputStream(filePath);
// 2.创建Excel
// 1)创建workbook
Workbook workbook = WorkbookFactory.create(fis);
// 2)创建sheet
Sheet mainSheet = workbook.getSheetAt(0);
// 用于展示
//导出数据到主sheet
setMainSheet(mainSheet, list);
//三级联动 sheet
Sheet mapSheet = workbook.createSheet("SHEET_MAP");
// true:隐藏/false:显示
//省市区关系sheet
workbook.setSheetHidden(workbook.getSheetIndex(mapSheet), true);// 设置sheet是否隐藏
// 3.写入数据（将三级联动的map数据写入到对应的excel下拉框中）
writeData(workbook, mapSheet, mapOneList, map);// 将数据写入隐藏的sheet中并做好关联关系
// 4.设置数据有效性----CharStatusEnum.A从第几列开始插入，A从第1列，B第二列
for (CharStatusEnum charStatusEnum : CharStatusEnum.values()) {
if (charStatusEnum.getValue() == startCol) {
setDataValid(workbook, mainSheet, mapOneList, map, charStatusEnum);
}
}
FileOutputStream os = null;
try {
String exisname = filePath.substring(0, filePath.lastIndexOf("\\"));
File f = new File(exisname);
if (!f.exists()) {
f.mkdirs();//创建目录
}
// 创建可写入的Excel工作簿
File file = new File(filePath);
if (!file.exists()) {
boolean bool = file.createNewFile();
System.out.println(bool);
} else {
file.delete();
file.createNewFile();
}
os = new FileOutputStream(filePath);
workbook.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(os);
}
}
private static void writeData(Workbook hssfWorkBook, Sheet mapSheet, List<String> provinceList, Map<String, List<String>> siteMap) {
//循环将父数据写入siteSheet的第1行中
int siteRowId = 0;
Row provinceRow = mapSheet.createRow(siteRowId++);
provinceRow.createCell(0).setCellValue("父列表");
for (int i = 0; i < provinceList.size(); i++) {
//有多少个省，创建多少个下拉框
provinceRow.createCell(i + 1).setCellValue(provinceList.get(i));
}
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
Iterator<String> keyIterator = siteMap.keySet().iterator();
while (keyIterator.hasNext()) {
String key = keyIterator.next();
List<String> son = siteMap.get(key);
Row siteRow = mapSheet.createRow(siteRowId++);
siteRow.createCell(0).setCellValue(key);
for (int i = 0; i < son.size(); i++) {
siteRow.createCell(i + 1).setCellValue(son.get(i));
}
// 添加名称管理器
String range = getRange(1, siteRowId, son.size());
Name name = hssfWorkBook.createName();
name.setNameName(key);
String formula = mapSheet.getSheetName() + "!" + range;
name.setRefersToFormula(formula);
}
}
/**
* 计算formula
*
* @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列
* @param rowId 第几行
* @param colCount 一共多少列
* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
*/
private static 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;
}
}
/**
* 循环单个下拉框的数据写入genderSheet的第A列中
*
* @param hssfWorkBook
* @param sheet
* @param list
* @param name sheet名称
*/
private static void writeDropDownData(Workbook hssfWorkBook, Sheet sheet, List<String> list, String name) {
//循环单个下拉框的数据写入genderSheet的第A列中
for (int i = 0; i < list.size(); i++) {
Row genderRow = sheet.createRow(i);
genderRow.createCell(0).setCellValue(list.get(i));
}
initGenderMapping(hssfWorkBook, sheet.getSheetName(), list.size(), name);// 创建数据规则
}
/**
* 创建下拉数据规则
*
* @param workbook
* @param genderSheetName
* @param genderQuantity
* @param name
*/
private static void initGenderMapping(Workbook workbook, String genderSheetName, int genderQuantity, String name) {
Name genderName = workbook.createName();
genderName.setNameName(name);
genderName.setRefersToFormula(genderSheetName + "!$A$1:$A$" + genderQuantity);
}
private static void setMainSheet(Sheet mainSheet, List list) {
for (int j = 0; j < list.size(); j++) {
//行
Row row = mainSheet.createRow(j + 1);
Object obj = list.get(j);
if (obj != null) {
Field[] fields = obj.getClass().getDeclaredFields();
try {
for (int i = 0; i < fields.length; i++) {
if (i == 0) {
//excel 第一列 为序号
Cell cell = row.createCell(i);
cell.setCellValue(j + 1);
} else {
Field f = fields[i];
f.setAccessible(true);
String name = f.getName();
Object value = f.get(obj);
if (value == null) {
value = "";
}
//列
Cell cell = row.createCell(i);
cell.setCellValue(value.toString());
}
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
private static void setDataValid(Workbook workbook, Sheet mainSheet, List<String> provinceList, Map<String, List<String>> siteMap, CharStatusEnum charEnum) {
//设置省份下拉
DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) mainSheet);
String[] dataArray = provinceList.toArray(new String[0]);
Sheet hidden = workbook.createSheet("hidden");
Cell cell = null;
for (int i = 0, length = dataArray.length; i < length; i++) {
String name = dataArray[i];
Row row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = workbook.createName();
namedCell.setNameName("hidden");
namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
//加载数据,将名称为hidden的
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, "hidden");
// 四个参数分别是：起始行、终止行、起始列、终止列.
// 1 (省份下拉框代表从excel第1+1行开始) 10(省份下拉框代表从excel第1+10行结束) 1(代表第几列开始，0是第一列，1是第二列) 1(代表第几列结束，0是第一列，1是第二列)
// CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(1, 10, 1, 1);
CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(FIRSTROW, ENDROW, charEnum.getValue() - 1, charEnum.getValue() - 1);
DataValidation provinceDataValidation = dvHelper.createValidation(constraint, provinceRangeAddressList);
provinceDataValidation.createErrorBox("error", "请选择正确");
provinceDataValidation.setShowErrorBox(true);
// provinceDataValidation.setSuppressDropDownArrow(true);
//将第二个sheet设置为隐藏
// true:隐藏/false:显示
workbook.setSheetHidden(workbook.getSheetIndex(hidden), true);// 设置sheet是否隐藏
mainSheet.addValidationData(provinceDataValidation);
// 设置市、区下拉
// i <= 10 ,10代表市、区下拉框到10+1行结束
for (int i = 0; i <= 10; i++) {
// setDataValidation('B', mainSheet, i + 1, 2);// "B"是指父类所在的列，i+1初始值为1代表从第2行开始，2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么最后一个参数就3
setDataValidation(charEnum.getCharacter(), mainSheet, i + 1, charEnum.getValue());// "B"是指父类所在的列，i+1初始值为1代表从第2行开始，2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么最后一个参数就3
}
}
/**
* 设置有效性
*
* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
private static void setDataValidation(char offset, Sheet sheet, int rowNum, int colNum) {
DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
DataValidation dataValidationList1;
DataValidation dataValidationList2;
dataValidationList1 = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum - 1, colNum, dvHelper);
dataValidationList2 = getDataValidationByFormula("INDIRECT($" + (char) (offset + 1) + (rowNum) + ")", rowNum - 1, colNum + 1, dvHelper);
sheet.addValidationData(dataValidationList1);
sheet.addValidationData(dataValidationList2);
}
private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, DataValidationHelper dvHelper) {
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
CellRangeAddressList regions = new CellRangeAddressList(naturalRowIndex, 65535, naturalColumnIndex, naturalColumnIndex);
DataValidation data_validation_list = dvHelper.createValidation(dvConstraint, regions);
data_validation_list.setEmptyCellAllowed(false);
if (data_validation_list instanceof XSSFDataValidationHelper) {
// data_validation_list.setSuppressDropDownArrow(true);
data_validation_list.setShowErrorBox(true);
} else {
// data_validation_list.setSuppressDropDownArrow(false);
}
// 设置输入信息提示信息
data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
return data_validation_list;
}
}3、抽取的一个枚举类以及测试的运行用例
package excel.utils;
import java.util.*;
/**
* 三级联动指定插入位置，eg: B为指定第二列插入，C为指定第三列插入
*/
public enum CharStatusEnum {
A('A',1), B('B',2), C('C',3),
D('D',4),E('E',5),F('F',6),
G('G',7),H('H',8),I('I',9),J('J',10);
private char character;
private int value;
//构造方法
CharStatusEnum(char character, int value) {
this.character=character;
this.value=value;
}
//自定义方法
public void setCharacter(char character){
this.character=character;
}
public void setValue(int value){
this.value=value;
}
public char getCharacter() {
return character;
}
public int getValue() {
return value;
}
@Override
public String toString() {
return "CharStatusEnum{" +
"character=" + character +
", value=" + value +
'}';
}
// 写了个main方法，运行写的测试方法
public static void main(String[] args) throws Exception {
String filePath = "C:\\Users\\白杉\\Desktop\\555.xlsx";
//所有一级
List<String> mapOneList = new ArrayList<String>();
mapOneList.add("广东省");
mapOneList.add("湖北省");
//关系map
Map<String, List<String>> map = new HashMap<String, List<String>>();
map.put("广东省", Arrays.asList("广州市", "佛山市"));
map.put("湖北省", Arrays.asList("武汉市", "荆州市"));
map.put("广州市", Arrays.asList("白云区", "越秀区"));
map.put("佛山市", Arrays.asList("顺德区", "南海区"));
ExcelUtils.export(filePath,mapOneList,map,5);
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
