java动态生成下拉框，标注信息，HSSFDataValidation和数据有效性等设置
转自: https://blog.csdn.net/xlz1786734790/article/details/97641988
原文作者: xlz1786734790
POI的基础使用
java动态生成下拉框属性，设置下拉框数据有效性
/**
* 设置下拉框元素
*
* @param firstRow 起始行
* @param endRow 结束行
* @param firstCol 起始列
* @param endCol 结束列
* @param list 下拉框选项
* @return
*/
public static HSSFDataValidation setDataValidationList(short firstRow, short endRow, short firstCol, short endCol, String[] list) {
//加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(list);
//设置数据有效性加载在哪个单元格上。
//四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
//数据有效性对象
HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
return data_validation_list; 2.设置标注信息
/**
*
* @param sheetlist 工作表对象
* @param rownum 从0开始第几行
* @param column 从0开始第几列
* @param message 提示信息
*/
public static HSSFCell setCellComment(HSSFSheet sheetlist,int rownum,int column ,String message){
//创建绘图对象
HSSFPatriarch drawingPatriarch = sheetlist.createDrawingPatriarch();
//给指定行列添加标注
HSSFCell cell1 = sheetlist.createRow(rownum).createCell(column);
//前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
HSSFComment comment = drawingPatriarch.createComment(new HSSFClientAnchor(0, 1, 0, 1, (short) 2, 2, (short) 2, 3));
comment.setString(new HSSFRichTextString(message));
cell1.setCellComment(comment);
return cell1;
}3.设置单元格输入限制，如日期，数字，文本长度等格式
/**
* 设置必填项文本长度
*
* @param sheetlist 需要操作的HSSFSheet
* @param firstRow 起始行
* @param endRow 结束行
* @param firstCol 起始列
* @param endCol 结束列
* @param title 提示标题
* @param message 提示信息
* @return
*/
public static HSSFDataValidation setDataValidationView(HSSFSheet sheetlist, short firstRow, short endRow, short firstCol, short endCol, String title, String message) {
//构造constraint对象
//DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("6");
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheetlist);
//构造constraint对象 设置有效性验证为 文本长度 最小长度为1
DVConstraint constraint = (DVConstraint) dvHelper.createTextLengthConstraint(DataValidationConstraint.ValidationType.TEXT_LENGTH, "1", "20");
//构造constraint对象 设置有效性验证日期，其他的也类似
//dvHelper.createDateConstraint(参数)
//四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
//数据有效性对象
HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint);
//设置提示标题,内容
data_validation_view.createPromptBox(title, message);
//忽略空值
data_validation_view.setEmptyCellAllowed(false);
data_validation_view.createErrorBox("您输入信息或格式有误", "请按提示信息输入");
//设置出错警告样式 0是停止 1是警告 2是信息
data_validation_view.setErrorStyle(1);
/*有的excel版本默认是false
data_validation_view.setShowPromptBox(true);
data_validation_view.setShowErrorBox(true);*/
return data_validation_view;
}4.XSSFDataValidation数据有效性版
private static XSSFDataValidation setDataValidation(XSSFSheet sheet, String[] textList, int firstRow, int endRow,
int firstCol, int endCol) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
//不同点在于构造constraint对象的方式不一样
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textList);
CellRangeAddressList addressList = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
return validation;
}5.使用实例
/**
* @param
* @param filePath Excel文件路径
* @param
* @param headers Excel列标题(数组)
* @param
* @param downData 下拉框数据(数组)
* @param
* @param downRows 下拉列的序号(数组,序号从0开始)
* @param messList 提示信息数组，从0开始
* @param messDatas 提示信息内容
* @return void
* @throws String filePath, String sheetTitle, List<String> headers, Map<String,String[]> map, String[] fatherOption
* @Title: testFunction
* @Description: 生成Excel导入模板
*/
public class DownExcelUtil {
private static org.slf4j.Logger log = LoggerFactory.getLogger(JFileUtils.class);
public static void createExcelTemplate(String filePath, String sheetTitle, List<String> headers, List<String[]> downData, List<String> downRows, List<String> messList, List<Map<String, String>> messDatas) throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();//excel文件对象
//设置居中样式
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = wb.createFont();
font.setFontName("仿宋_GB2312");
font.setFontHeightInPoints((short) 12);
//工作表对象
HSSFSheet sheetlist = wb.createSheet(sheetTitle);
//设置脚标
setCellComment(sheetlist,0,3,"请按正确格式输入身份证号!如：11013119940306312X");
setCellComment(sheetlist,0,1,"请输入真实有效姓名如：张三");
HSSFRow row = sheetlist.createRow(0);
HSSFCell cell = row.createCell(0);
//设置需要设置属性的sheet
if (headers.size() > 0) {
for (int i = 0; i < headers.size(); i++) {
//设置字体
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(headers.get(i));
//设置工作表单元格宽度
sheetlist.setColumnWidth(i, 20 * 256);
sheetlist.setDefaultColumnStyle(i, cellStyle);
cell = row.createCell(i + 1);
}
log.info("设置sheet成功" + headers.size() + "条");
}
//判断下拉框序号个数设置下拉框属性
if (downRows.size() > 0 && downData.size() == downRows.size()) {
for (int i = 0; i < downRows.size(); i++) {
short downRow = Short.parseShort(downRows.get(i));
//得到验证对象
HSSFDataValidation data_validation_list = DownExcelUtil.setDataValidationList((short) 1, (short) 5000, downRow, downRow, downData.get(i));
sheetlist.addValidationData(data_validation_list);
}
log.info("设置下拉框属性值" + downRows.size() + "组");
}
//判断提示序列号组和提示信息
if (messList.size() > 0 && messList.size() == messDatas.size()) {
for (int i = 0; i < messList.size(); i++) {
short messShort = Short.parseShort(messList.get(i));
Map<String, String> messageMap = messDatas.get(i);
Set<String> strings = messageMap.keySet();
if (strings.size() == 1) {
for (String string : strings) {
HSSFDataValidation data_validation_view = DownExcelUtil.setDataValidationView(sheetlist, (short) 1, (short) 5000, messShort, messShort, string, messageMap.get(string));
sheetlist.addValidationData(data_validation_view);
}
}
}
log.info("设置提示信息" + messList.size() + "组");
}
File f = new File(filePath);
// 不存在则新增
if (!f.getParentFile().exists()) {
f.getParentFile().mkdirs();
}
if (!f.exists()) {
f.createNewFile();
}
try {
FileOutputStream out = new FileOutputStream(f);
out.flush();
//工作表添加验证数据
wb.write(out);
//关闭流
out.close();
log.info("工作表验证数据写入成功");
} catch (Exception e) {
log.info("工作表验证数据写入失败");
e.printStackTrace();
}
}![poi excel 常用操作 [冻结、合并、链接]](https://www.hukaivip.cn/zb_users/upload/2020/06/202006171592379206706047.png)
