2007版本 easypoi 省市县三级联动
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
public class Test {
public static void main(String[] args) {
Cascade();
}
public static void Cascade() {
// 创建一个excel
@SuppressWarnings("resource")
Workbook book = new HSSFWorkbook();
// 创建需要用户填写的sheet
HSSFSheet sheetPro = (HSSFSheet)book.createSheet("省市县");
Row row0 = sheetPro.createRow(0);
row0.createCell(0).setCellValue("省");
row0.createCell(1).setCellValue("市");
row0.createCell(2).setCellValue("区");
// 得到第一级省名称，放在列表里
String[] provinceArr = {"江苏省", "安徽省"};
// 依次列出各省的市、各市的县
String[] cityJiangSu = {"南京市", "苏州市", "盐城市"};
String[] cityAnHui = {"合肥市", "安庆市"};
String[] countyNanjing = {"六合县", "江宁县"};
String[] countySuzhou = {"姑苏区", "园区"};
String[] countyYancheng = {"响水县", "射阳县"};
String[] countyLiuhe = {"瑶海区", "庐阳区"};
String[] countyAnQing = {"迎江区", "大观区"};
// 将有子区域的父区域放到一个数组中
String[] areaFatherNameArr = {"江苏省", "安徽省", "南京市", "苏州市", "盐城市", "合肥市", "安庆市"};
Map<String, String[]> areaMap = new HashMap<String, String[]>();
areaMap.put("江苏省", cityJiangSu);
areaMap.put("安徽省", cityAnHui);
areaMap.put("南京市", countyNanjing);
areaMap.put("苏州市", countySuzhou);
areaMap.put("盐城市", countyYancheng);
areaMap.put("合肥市", countyYancheng);
areaMap.put("合肥市", countyLiuhe);
areaMap.put("安庆市", countyAnQing);
// 创建一个专门用来存放地区信息的隐藏sheet页
// 因此也不能在现实页之前创建，否则无法隐藏。
Sheet hideSheet = book.createSheet("area");
// 这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
book.setSheetHidden(book.getSheetIndex(hideSheet), true);
int rowId = 0;
// 设置第一行，存省的信息
Row provinceRow = hideSheet.createRow(rowId++);
for (int i = 0; i < provinceArr.length; i++) {
Cell provinceCell = provinceRow.createCell(i );
provinceCell.setCellValue(provinceArr[i]);
}
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
for (int i = 0; i < areaFatherNameArr.length; i++) {
String key = areaFatherNameArr[i];
String[] son = areaMap.get(key);
Row row = hideSheet.createRow(rowId++);
row.createCell(0).setCellValue(key);
for (int j = 0; j < son.length; j++) {
Cell cell = row.createCell(j + 1);
cell.setCellValue(son[j]);
}
// 添加名称管理器
String range1 = getRange(1, rowId, son.length);
Name name = book.createName();
// key不可重复
name.setNameName(key);
String formula = "area!" + range1;
name.setRefersToFormula(formula);
}
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheetPro);
DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(provinceArr);
CellRangeAddressList addressList = new CellRangeAddressList(1, 20, 0, 0);
// 添加菜单(将单元格与"名称"建立关联)
HSSFDataValidation validation = (HSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
validation.setShowErrorBox(false);
sheetPro.addValidationData(validation);
// 对前20行设置有效性
for (int i = 2; i < 20; i++) {
setDataValidation("A", sheetPro, i, 2);
setDataValidation("B", sheetPro, i, 3);
}
FileOutputStream os = null;
try {
os = new FileOutputStream("D:/test.xls");
book.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(os);
}
}
/**
* 设置有效性
*
* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
public static void setDataValidation(String offset, HSSFSheet sheet, int rowNum, int colNum) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
CellRangeAddressList regions = new CellRangeAddressList(rowNum - 1, rowNum - 1, colNum - 1, colNum - 1);
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("INDIRECT($" + offset +"$"+ rowNum + ")");
HSSFDataValidation dataValidation = (HSSFDataValidation)dvHelper.createValidation(dvConstraint, regions);
sheet.addValidationData(dataValidation);
}
/**
* 计算formula
*
* @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列
* @param rowId 第几行
* @param colCount 一共多少列
* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
*
*/
public 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;
}
}
/**
* excel下标转字符
*
* @param number
* @return
*/
public static String numberToLetter(int number) {
if (number <= 25) {
char start = (char)('A' + number);
return start + "";
}
char prefix = 'A';
char suffix;
if ((number - 25) % 26 == 0) {
prefix = (char)(prefix + (number - 25) / 26 - 1);
suffix = (char)('A' + 25);
} else {
prefix = (char)(prefix + (number - 25) / 26);
suffix = (char)('A' + (number - 25) % 26 - 1);
}
return prefix + "" + suffix;
}
}
