package xls20170828comboBox;
import java.io.FileOutputStream;
import java.io.IOException;
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.hssf.util.CellRangeAddressList;
/**
* ------------------------------------------------------------------------------
* https://wuhaidong.iteye.com/blog/2039848
* ------------------------------------------------------------------------------
手工设置:
excel菜单栏上--数据--有效性--允许--序列,
excel菜单栏上--数据--有效性--输入信息.
程序现实:
首先,https://poi.apache.org/官方下载POI3.2 jar包.
注:经本人测试转载的文章中有一些错误:
1. 使用POI3.2.jar创建的提示框不支持中文,最终我换成了POI3.5.jar就能支持中文了.
2. DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("B1");
这样写有点问题,提示字段中可输入的值只能输入B1的内容.
这一句的意思是设置约束条件引用B1单元格中的内容,查找API后发现必须要有一个约束才行,我的灵活处理的方法是设置为BB1,
一般不会有这么多字段,BB1没有约束就相当于创建了一个没有约束的单元格.
本为是对上一篇博客的中内容的封装,封装后的方法使用起来更方便一些.
*------------------------------------------------------------------------------
* @author ZengWenFeng
* @date 2017.08.28
*/
public class PoiTest2
{
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
{
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(data_validation_list);
//
return sheet;
}
/**
* 设置单元格上提示
*
* @param sheet 要设置的sheet.
* @param promptTitle 标题
* @param promptContent 内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow, int endRow, int firstCol, int endCol)
{
// 构造constraint对象
DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("BB1");
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint);
data_validation_view.createPromptBox(promptTitle, promptContent);
sheet.addValidationData(data_validation_view);
//
return sheet;
}
public static void main(String[] args) throws IOException
{
HSSFWorkbook wb = new HSSFWorkbook();// excel文件对象
HSSFSheet sheetlist = wb.createSheet("sheetlist");// 工作表对象
FileOutputStream out = new FileOutputStream("d:\success.xls");
String[] textlist =
{
// "列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5",
"列表1", "列表2", "列表3", "列表4", "列表5"
};
// Exception in thread "main" java.lang.IllegalArgumentException: String literals in formulas can't be bigger than 255 characters ASCII
// at org.apache.poi.ss.formula.ptg.StringPtg.<init>(StringPtg.java:65)
// at org.apache.poi.hssf.usermodel.DVConstraint.createListFormula(DVConstraint.java:412)
// at org.apache.poi.hssf.usermodel.DVConstraint.createFormulas(DVConstraint.java:385)
// at org.apache.poi.hssf.usermodel.HSSFDataValidation.createDVRecord(HSSFDataValidation.java:195)
// at org.apache.poi.hssf.usermodel.HSSFSheet.addValidationData(HSSFSheet.java:396)
// at xls20170828comboBox.PoiTest2.setHSSFValidation(PoiTest2.java:58)
// at xls20170828comboBox.PoiTest2.main(PoiTest2.java:161)
// 行号开始、行号结束、列号开始、列号结束
sheetlist = setHSSFValidation(sheetlist, textlist, 0, 500, 0, 0);// 第一列的前501行都设置为选择列表形式.
// sheetlist = setHSSFPrompt(sheetlist, "promt Title", "prompt Content", 0, 500, 1, 1);// 第二列的前501行都设置提示.
wb.write(out);
out.close();
}
}
package xls20170828comboBox;
import java.io.Fi
阅读背景：
String literals in formulas can't be bigger than 255 characters ASCII
来源：互联网
分享到：
