POI 導出Excel 下拉清單多級關聯

原文：https://blog.csdn.net/m0_37956938/article/details/78084503

一、主要步驟

1 建立workbook,建立要展示的sheet,即需要使用者填寫的sheet,設定表頭等必須字段

2 建立隐藏sheet,列出下拉清單中需要展示的項資訊

3 名稱管理器設定name

4 利用名稱管理器關聯到隐藏sheet中資訊

5 設定有效性，添加驗證

二、詳細步驟

可以先熟悉在excel檔案中建立多級關聯做法，可參照Excel多級下拉菜單關聯自動比對内容

（http://blog.csdn.net/dearmorning/article/details/54312084）

級聯也可參照http://blog.csdn.net/readiay/article/details/52397419，寫的很詳細，包含使用POI寫excel，并且添加資料校驗，以及省市區級聯菜單設定，分Excel2003與Excel2007兩個版本這些問題的具體步驟和代碼，本文主要是拆分詳解excel級聯

1 建立workbook,建立要展示的sheet,即需要使用者填寫的sheet,設定表頭等必須字段

// 建立一個excel

Workbook book = new XSSFWorkbook();

// 建立需要使用者填寫的資料頁

// 設計表頭

Sheet sheetPro = book.createSheet("省市縣");

Row row0 = sheetPro.createRow(0);

row0.createCell(0).setCellValue("省");

row0.createCell(1).setCellValue("市");

row0.createCell(2).setCellValue("縣");

效果如圖：

POI 導出Excel 下拉清單多級關聯

2 建立隐藏sheet,列出下拉清單中需要展示的項資訊

//得到第一級省名稱，放在清單裡

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++);

provinceRow.createCell(0).setCellValue("省清單");

for(int i = 0; i < provinceArr.length; i ++){

Cell provinceCell = provinceRow.createCell(i + 1);

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 range = getRange(1, rowId, son.length);

Name name = book.createName();

//key不可重複,将父區域名作為key

name.setNameName(key);

String formula = "area!" + range;

name.setRefersToFormula(formula);

}

未隐藏前效果如圖：圖中每種顔色方框内省市縣互相對應，并且不可重複

POI 導出Excel 下拉清單多級關聯

3 名稱管理器設定name

// 添加名稱管理器

String range = getRange(1, rowId, son.length);

Name name = book.createName();

//key不可重複,将父區域名作為key

name.setNameName(key);

String formula = "area!" + range;

name.setRefersToFormula(formula);

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;

}

添加完名稱管理器效果如圖：

POI 導出Excel 下拉清單多級關聯

4 利用名稱管理器關聯到隐藏sheet中資訊

XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

// 舉例：若formulaString = "INDIRECT($A$2)" 表示規則資料會從名稱管理器中擷取key與單元格 A2 值相同的資料，

//如果A2是江蘇省，那麼此處就是江蘇省下的市資訊。

XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);

設定關聯後如圖：此時B5單元格受A5單元格的值影響

POI 導出Excel 下拉清單多級關聯

jar包：

<groupId>org.apache.poi</groupId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

完整代碼如下：

package com.cc.cs;

import java.io.FileOutputStream;

import java.util.HashMap;

import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.DataValidation;

import org.apache.poi.ss.usermodel.DataValidationConstraint;

import org.apache.poi.ss.usermodel.Name;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.util.CellRangeAddressList;

import org.apache.poi.util.IOUtils;

import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;

import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class NewTest {

public static void main(String[] args) {

Cascade();

}

public static void Cascade() {

// 建立一個excel

@SuppressWarnings("resource")

Workbook book = new XSSFWorkbook();

// 建立需要使用者填寫的sheet

XSSFSheet sheetPro = (XSSFSheet) 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++);

provinceRow.createCell(0).setCellValue("省清單");

for(int i = 0; i < provinceArr.length; i ++){

Cell provinceCell = provinceRow.createCell(i + 1);

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 range = getRange(1, rowId, son.length);

Name name = book.createName();

//key不可重複

name.setNameName(key);

String formula = "area!" + range;

name.setRefersToFormula(formula);

}

XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheetPro);

// 省規則

DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(provinceArr);

// 四個參數分别是：起始行、終止行、起始列、終止列

CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 20, 0, 0);

DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);

//驗證

provinceDataValidation.createErrorBox("error", "請選擇正确的省份");

provinceDataValidation.setShowErrorBox(true);

provinceDataValidation.setSuppressDropDownArrow(true);

sheetPro.addValidationData(provinceDataValidation);

//對前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:/testbbb.xlsx");

book.write(os);

} catch (Exception e) {

e.printStackTrace();

} finally {

IOUtils.closeQuietly(os);

}

public static void setDataValidation(String offset,XSSFSheet sheet, int rowNum,int colNum) {

XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

DataValidation data_validation_list;

data_validation_list = getDataValidationByFormula(

"INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum,dvHelper);

sheet.addValidationData(data_validation_list);

}

private static DataValidation getDataValidationByFormula(

String formulaString, int naturalRowIndex, int naturalColumnIndex,XSSFDataValidationHelper dvHelper) {

// 加載下拉清單内容

// 舉例：若formulaString = "INDIRECT($A$2)" 表示規則資料會從名稱管理器中擷取key與單元格 A2 值相同的資料，

//如果A2是江蘇省，那麼此處就是江蘇省下的市資訊。

XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);

// 設定資料有效性加載在哪個單元格上。

// 四個參數分别是：起始行、終止行、起始列、終止列

int firstRow = naturalRowIndex -1;

int lastRow = naturalRowIndex - 1;

int firstCol = naturalColumnIndex - 1;

int lastCol = naturalColumnIndex - 1;

CellRangeAddressList regions = new CellRangeAddressList(firstRow,

lastRow, firstCol, lastCol);

// 資料有效性對象

// 綁定

XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);

data_validation_list.setEmptyCellAllowed(false);

if (data_validation_list instanceof XSSFDataValidation) {

data_validation_list.setSuppressDropDownArrow(true);

data_validation_list.setShowErrorBox(true);

} else {

data_validation_list.setSuppressDropDownArrow(false);

}

// 設定輸入資訊提示資訊

data_validation_list.createPromptBox("下拉選擇提示", "請使用下拉方式選擇合适的值！");

// 設定輸入錯誤提示資訊

//data_validation_list.createErrorBox("選擇錯誤提示", "你輸入的值未在備選清單中，請下拉選擇合适的值！");

return data_validation_list;

}

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;

}

---------------------

原文：https://blog.csdn.net/m0_37956938/article/details/78084503

POI 導出Excel 下拉清單多級關聯

繼續閱讀

學習程式設計一個月，剛好一個月

淺析MVC、MVP、MVVM架構

mybatis如果庫裡有就更新，如果沒有就新增，根據兩列為唯一索引。

Python2笨方法學習Python--習題三：數字和數學計算

計算機網絡——Packet Tracer實作簡單區域網路組建和配置

javaSE學習04（面向對象基礎）面向對象基礎

從零開始的NodeJs + VueJs (Express + ElementUI)的學習（third）

javaSE學習03（Debug和一些練習題）1.Debug基礎練習題

DHCP自動配置設定ip位址，進階配置

c/c++ 學習筆記指針、數組、函數數組、指針、函數總結源代碼

在NVIDIA Jetson Xavier NX上使用tensorflow-gpu在NVIDIA Jetson Xavier NX上使用tensorflow-gpu