今天捣鼓那个下拉框呢
这个Workbook有三个实现类
首先po一个xssf的下拉框实现工具类:
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import java.io.FileOutputStream;
/**
* describe:
*
* @author xiepanpan
* @date 2018/07/11
*/
public class Test {
public static void main(String[] args) {
try
{
dropDownList42007("D:\\test.xlsx");
}
catch (Exception e) {
e.printStackTrace();
}
}
public static void dropDownList42007(String filePath)
throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("下拉列表测试");
String[] datas = new String[] {"维持","恢复","调整"};
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
.createExplicitListConstraint(datas);
CellRangeAddressList addressList = null;
XSSFDataValidation validation = null;
for (int i = 0; i < 100; i++) {
addressList = new CellRangeAddressList(i, i, 0, 0);
validation = (XSSFDataValidation) dvHelper.createValidation(
dvConstraint, addressList);
// 07默认setSuppressDropDownArrow(true);
// validation.setSuppressDropDownArrow(true);
// validation.setShowErrorBox(true);
sheet.addValidationData(validation);
}
FileOutputStream stream = new FileOutputStream(filePath);
workbook.write(stream);
stream.close();
addressList = null;
validation = null;
}
}
HSSFWorkbook 的下拉框设置:
//加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(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);
//数据有效性对象
DataValidation dataValidation = new HSSFDataValidation(regions,constraint);
sheet.addValidationData(dataValidation);
SXSSFWorkbook的下拉框设置方法:
/**
* 设置下拉框
* @param formulaString 下拉框数组
* @param firstRow 起始行 从0开始
* @param lastRow 终止行
* @param firstCol 起始列
* @param lastCol 终止列
* @param <E>
* @return
*/
public <E> MyExportExcel setDropDownBox(String[] formulaString,int firstRow, int lastRow,int firstCol,int lastCol){
//加载下拉列表内容
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(formulaString);
//设置下拉框位置
CellRangeAddressList addressList = null;
addressList = new CellRangeAddressList(firstRow, 500, 1, 1);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
//处理Excel兼容性问题
if(dataValidation instanceof XSSFDataValidation){
//数据校验
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}else{
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
return this;
}
效果:
至于表头为啥整这么炫,是因为我用了jeeplus框架
参考博客:
https://blog.csdn.net/u014792342/article/details/47973087?locationNum=13
http://www.it610.com/article/3597562.htm
http://wuhaidong.iteye.com/blog/2039848
https://my.oschina.net/sxxachao/blog/607322