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 ExcelDemo2 {
public static void main(String[] args) {
Cascade();
}
public static void Cascade() {
String fileName = "test02.xlsx";
//Create an excel
// @SuppressWarnings("resource")
XSSFWorkbook workbook = new XSSFWorkbook();
//The first line of the first level
int firstRow = 1;
//How many columns are there in the first level?
int lastRow = 49;
//The first level column
int firstCol = 0;
//The column character of the first level column
String offset = "A";
int lastCol = firstCol;
//The second level column
int secondeCol = 3;
//Hide the name of the sheet page
String hideSheetName = "level";
//Create a sheet that needs to be filled in by the user
XSSFSheet sheet = workbook.createSheet("Device List");
Row row0 = sheet.createRow(0);
//first level
row0.createCell(firstCol).setCellValue("Device Brand");
//second level
row0.createCell(secondeCol).setCellValue("Device Model");
//Brand list
String[] firstArr = {"Baode","Xu Ji","Shijiazhuang Kelin","Beijing Yi Aishide"};
//Device model
String[]baode = {"XWL-PD35A"};
String[] xuji = {"XD930AB2","XD930AB4","MBU80"};
String[] kelin = {"KLD8500"};
String[] eaisd = {"EM600LCD"};
Map<String,String[]> levelMap = new HashMap<>();
levelMap.put("Baode",baode);
levelMap.put("Xu Ji",xuji);
levelMap.put("Shijiazhuang Kelin",kelin);
levelMap.put("Beijing Yi Aisd",eaisd);
prepareDataSheet(workbook, hideSheetName, firstArr, levelMap);
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
// Provincial rules
DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(firstArr);
//The four parameters are: starting row, ending row, starting column, ending column
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
//verify
provinceDataValidation.createErrorBox("error", "Please select the correct brand");
provinceDataValidation.setShowErrorBox(true);
provinceDataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(provinceDataValidation);
//Set validity for the first 20 rows
for(int i = firstRow;i <= lastRow;i + + ){
setDataValidation(offset,sheet,i,secondeCol);
}
FileOutputStream os = null;
try {
os = new FileOutputStream(fileName);
workbook.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(os);
}
}
private static void prepareDataSheet(XSSFWorkbook workbook, String hideSheetName, String[] firstArr, Map<String, String[]> levelMap) {
//Create a hidden sheet page specifically used to store regional information
//Therefore it cannot be created before the actual page, otherwise it cannot be hidden.
Sheet hideSheet = workbook.createSheet(hideSheetName);
//The function of this line is to hide this sheet. Comment this line when the function is not completed. You can check whether the information in the hidden sheet is correct.
workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);
//--Set the referenced data start --//
int rowId = 0;
//Set the first line to store provincial information
Row brandRow = hideSheet.createRow(rowId + + );
brandRow.createCell(0).setCellValue("First Level");
for(int i = 0; i < firstArr.length; i + + ){
Cell provinceCell = brandRow.createCell(i + 1);
provinceCell.setCellValue(firstArr[i]);
}
//Write specific data into each row. The beginning of the row is the parent area, followed by the child area.
for(int i = 0; i < firstArr.length; i + + ){
String key = firstArr[i];
String[] secondArr = levelMap.get(key);
Row row1 = hideSheet.createRow(rowId + + );
row1.createCell(0).setCellValue(key);
for(int j = 0; j < secondArr.length; j + + ){
Cell cell0 = row1.createCell(j + 1);
cell0.setCellValue(secondArr[j]);
}
//Add name manager
String range = getRange(1, rowId, secondArr.length);
Name name = workbook.createName();
//key cannot be repeated
name.setNameName(key);
String formula = hideSheetName + "!" + range;
name.setRefersToFormula(formula);
}
}
/**
* Set validity
* @param offset The column where the main affected cell is located, that is, which cell affects the linkage of this cell
* @param sheet
* @param rowNum row number
* @param colNum column number
*/
public static void setDataValidation(String offset,XSSFSheet sheet, int rowNum,int colNum) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
DataValidation data_validation_list;
int offsetRow = rowNum + 1;
data_validation_list = getDataValidationByFormula(
"INDIRECT($" + offset + (offsetRow) + ")", rowNum, colNum,dvHelper);
sheet.addValidationData(data_validation_list);
}
/**
* Load drop-down list content
* @param formulaString
* @param naturalRowIndex
* @param naturalColumnIndex
* @param dvHelper
* @return
*/
private static DataValidation getDataValidationByFormula(
String formulaString, int naturalRowIndex, int naturalColumnIndex,XSSFDataValidationHelper dvHelper) {
//Load the contents of the drop-down list
//Example: If formulaString = "INDIRECT($A$2)" means that the rule data will obtain data with the same key as the value of cell A2 from the name manager,
//If A2 is Jiangsu Province, then here is the city information under Jiangsu Province.
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
//Set which cell the data validity is loaded on.
//The four parameters are: starting row, ending row, starting column, ending column
int firstRow = naturalRowIndex;
int lastRow = naturalRowIndex;
int firstCol = naturalColumnIndex;
int lastCol = naturalColumnIndex;
CellRangeAddressList regions = new CellRangeAddressList(firstRow,
lastRow, firstCol, lastCol);
//Data validity object
// Binding
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);
}
//Set input information prompt information
data_validation_list.createPromptBox("Drop-down selection prompt", "Please use the drop-down method to select the appropriate value!");
//Set input error message
//data_validation_list.createErrorBox("Selection error message", "The value you entered is not in the alternative list, please pull down and select the appropriate value!");
return data_validation_list;
}
/**
* Calculate formula
* @param offset offset, if 0 is given, it means starting from column A, 1 means starting from column B
* @param rowId Which row?
* @param colCount How many columns are there in total?
* @return If input parameters 1,1,10 are given, it means from B1-K1. Finally returns $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) { // Between 26-51, including boundaries (only two alphabet calculations)
if ((colCount - 25) % 26 == 0) {// Boundary value
endSuffix = (char)('A' + 25);
} else {
endSuffix = (char)('A' + (colCount - 25) % 26 - 1);
}
} else {//Above 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;
}
}
}
Generated with drop-down list
