2003 version of Excel
import java.io.FileOutputStream;
import java.util.*;
import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
/**
* @ClassName: test3
* @Author: yongtao.ding on 2019/5/29 9:48
* @Version: 1.0
*/
public class test3 {
public static void main(String[] args) {
Cascade();
}
public static void Cascade() {
//Create an excel
@SuppressWarnings("resource")
Workbook book = new HSSFWorkbook();
//Create a sheet that needs to be filled in by the user
HSSFSheet sheetPro = (HSSFSheet) book.createSheet("Province, City, County");
Row row0 = sheetPro.createRow(0);
row0.createCell(0).setCellValue("province");
row0.createCell(1).setCellValue("city");
row0.createCell(2).setCellValue("area");
// Query all province names
List<String> provinceArr = new ArrayList<String>();
provinceArr.add("Anhui Province");
provinceArr.add("Zhejiang Province");
// Organize the data and put it into a Map. The mapkey stores the parent location and the value stores the sub-area under the location.
Map<String, List<String>> areaMap = new HashMap<String, List<String>>();
areaMap.put("Zhejiang Province", Lists.newArrayList("Hangzhou City", "Ningbo City"));
areaMap.put("Anhui Province", Lists.newArrayList("Wuhu City", "Chuzhou City"));
areaMap.put("Wuhu City", Lists.newArrayList("Gejiang District", "Sanshan District"));
areaMap.put("Chuzhou City", Lists.newArrayList("Lai'an County", "Fengyang County"));
//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 = book.createSheet("area");
// 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.
// book.setSheetHidden(book.getSheetIndex(hideSheet), true);
int rowId = 0;
//Set the first line to store provincial information
Row provinceRow = hideSheet.createRow(rowId + + );
provinceRow.createCell(0).setCellValue("Province List");
for (int i = 0; i < provinceArr.size(); i + + ) {
Cell provinceCell = provinceRow.createCell(i + 1);
provinceCell.setCellValue(provinceArr.get(i));
}
//Write specific data into each row. The beginning of the row is the parent area, followed by the child area.
Iterator<String> keyIterator = areaMap.keySet().iterator();
while (keyIterator.hasNext()) {
String key = keyIterator.next();
List<String> son = areaMap.get(key);
Row row = hideSheet.createRow(rowId + + );
row.createCell(0).setCellValue(key);
for (int i = 0; i < son.size(); i + + ) {
Cell cell = row.createCell(i + 1);
cell.setCellValue(son.get(i));
}
//Add name manager
String range = getRange(1, rowId, son.size());
Name name = book.createName();
// key cannot be repeated
name.setNameName(key);
String formula = "area!" + range;
name.setRefersToFormula(formula);
}
// Provincial rules
DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(provinceArr.toArray(new String[] {}));
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 20, 0, 0);
DataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
provinceDataValidation.createErrorBox("error", "Please select the correct province");
sheetPro.addValidationData(provinceDataValidation);
//Set validity for the first 20 rows
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:/testbbb8888888888.xls");
book.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(os);
}
}
/**
* 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, HSSFSheet sheet, int rowNum, int colNum) {
DVConstraint formula = DVConstraint.createFormulaListConstraint("INDIRECT($" + offset + "$" + rowNum + ")");
int firstRow = rowNum - 1;
int lastRow = rowNum - 1;
int firstCol = colNum - 1;
int lastCol = colNum - 1;
CellRangeAddressList rangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidation cacse = new HSSFDataValidation(rangeAddressList, formula);
cacse.createErrorBox("error", "Please select the correct city");
cacse.createPromptBox("Drop-down selection prompt", "Please use the drop-down method to select the appropriate value!");
sheet.addValidationData(cacse);
}
/**
* 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;
}
}
}
2007 version of Excel
import java.io.FileOutputStream;
import java.util.*;
import com.google.common.collect.Lists;
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;
/**
* @ClassName: test
* @Author: yongtao.ding on 2019/5/29 9:48
* @Version: 1.0
*/
public class test {
public static void main(String[] args) {
Cascade();
}
public static void Cascade() {
//Create an excel
@SuppressWarnings("resource")
Workbook book = new XSSFWorkbook();
//Create a sheet that needs to be filled in by the user
XSSFSheet sheetPro = (XSSFSheet) book.createSheet("Province, City, County");
Row row0 = sheetPro.createRow(0);
row0.createCell(0).setCellValue("province");
row0.createCell(1).setCellValue("city");
row0.createCell(2).setCellValue("area");
// Query all province names
List<String> provinceArr = new ArrayList<String>();
provinceArr.add("Anhui Province");
provinceArr.add("Zhejiang Province");
// Organize the data and put it into a Map. The mapkey stores the parent location and the value stores the sub-area under the location.
Map<String, List<String>> areaMap = new HashMap<String, List<String>>();
areaMap.put("Zhejiang Province", Lists.newArrayList("Hangzhou City", "Ningbo City"));
areaMap.put("Anhui Province", Lists.newArrayList("Wuhu City", "Chuzhou City"));
areaMap.put("Wuhu City", Lists.newArrayList("Gejiang District", "Sanshan District"));
areaMap.put("Chuzhou City", Lists.newArrayList("Lai'an County", "Fengyang County"));
//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 = book.createSheet("area");
// 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.
// book.setSheetHidden(book.getSheetIndex(hideSheet), true);
int rowId = 0;
//Set the first line to store provincial information
Row provinceRow = hideSheet.createRow(rowId + + );
provinceRow.createCell(0).setCellValue("Province List");
for (int i = 0; i < provinceArr.size(); i + + ) {
Cell provinceCell = provinceRow.createCell(i + 1);
provinceCell.setCellValue(provinceArr.get(i));
}
//Write specific data into each row. The beginning of the row is the parent area, followed by the child area.
Iterator<String> keyIterator = areaMap.keySet().iterator();
while (keyIterator.hasNext()) {
String key = keyIterator.next();
List<String> son = areaMap.get(key);
Row row = hideSheet.createRow(rowId + + );
row.createCell(0).setCellValue(key);
for (int i = 0; i < son.size(); i + + ) {
Cell cell = row.createCell(i + 1);
cell.setCellValue(son.get(i));
}
//Add name manager
String range = getRange(1, rowId, son.size());
Name name = book.createName();
// key cannot be repeated
name.setNameName(key);
String formula = "area!" + range;
name.setRefersToFormula(formula);
}
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheetPro);
// Provincial rules
DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(provinceArr.toArray(new String[] {}));
//The four parameters are: starting row, ending row, starting column, ending column
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 20, 0, 0);
DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
// verify
provinceDataValidation.createErrorBox("error", "Please select the correct province");
provinceDataValidation.setShowErrorBox(true);
provinceDataValidation.setSuppressDropDownArrow(true);
sheetPro.addValidationData(provinceDataValidation);
//Set validity for the first 20 rows
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);
}
}
/**
* 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;
data_validation_list = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", 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 - 1;
int lastRow = naturalRowIndex - 1;
int firstCol = naturalColumnIndex - 1;
int lastCol = naturalColumnIndex - 1;
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 prompt", "The value you entered is not in the alternative list, please drop 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;
}
}
}
Time verification 2003 version
package com.dbappsecurity.controller;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.io.FileOutputStream;
import java.io.IOException;
/**
* @ClassName: test2
* @Author: yongtao.ding on 2019/5/29 10:15
* @Version: 1.0
*/
public class test2 {
public static void testConstraintExcel2003() throws IOException {
Workbook book = new HSSFWorkbook();
Sheet sheet = book.createSheet("first worksheet");
// Only time can be entered, ranging from 2000 to 2020
// rule
DVConstraint dateConstraint =
DVConstraint.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "1900-01-01", "2100-01-01", "yyyy-Mm-dd");
// scope
CellRangeAddressList rangeAddressList = new CellRangeAddressList(0, 10, 0, 0);
// range + rule
DataValidation dataValidation = new HSSFDataValidation(rangeAddressList, dateConstraint);
dataValidation.createErrorBox("error", "The input format is incorrect");
//Apply time verification rules
sheet.addValidationData(dataValidation);
// drop-down verification
String[] provList = new String[] {"Anhui Province", "Zhejiang Province", "Shanxi Province"};
DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(provList);
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(0, 10, 1, 1);
DataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
provinceDataValidation.createErrorBox("error", "Please select the correct province");
sheet.addValidationData(provinceDataValidation);
FileOutputStream os = null;
try {
os = new FileOutputStream("D:/testConstraintExcel2003.xls");
book.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
os.close();
}
}
public static void main(String[] args) throws IOException {
testConstraintExcel2003();
}
}
Data verification 2007 version
@Test
//Single cell verification
public void testConstraintExcel2007() {
Workbook book = new XSSFWorkbook();
Sheet sheet = book.createSheet("first worksheet");
\t
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
\t
// Here the check value is allowed to enter an integer between 0-100
\t// rule
DataValidationConstraint dvConstraint = dvHelper.createIntegerConstraint(OperatorType.BETWEEN, "0", "100");
\t
\t// scope
CellRangeAddressList rangeAddressList = new CellRangeAddressList(0, 10, 0, 0);
// range + rule
DataValidation validation = dvHelper.createValidation(dvConstraint, rangeAddressList);
validation.createErrorBox("error", "You must enter an integer");
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
//Apply time verification rules
sheet.addValidationData(validation);
\t
// Drop-down verification needs to call setShowErrorBox, otherwise the wrong data may be filled in and there will be no pop-up warning.
String[] provList = new String[] {"Anhui Province", "Zhejiang Province", "Shanxi Province"};
DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(provList);
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(0, 10, 1, 1);
DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
provinceDataValidation.createErrorBox("error", "Please select the correct province");
provinceDataValidation.setShowErrorBox(true);
provinceDataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(provinceDataValidation);
\t
FileOutputStream os = null;
try {
os = new FileOutputStream("D:/testConstraintExcel2007.xlsx");
\t\t
book.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(os);
}
}
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java Skill TreeHomepageOverview 138,710 people are learning the system