POI下拉代码,特记
package com;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.junit.Test;
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Testw {
@Test
public void downLoadFile() throws Exception {
try {
HSSFWorkbook workbook;
workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
sheet.setColumnWidth(0, 5000);
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(2, 4000);
HSSFRow hssfRow = sheet.createRow(0);
HSSFCell cell0 = hssfRow.createCell(0);
cell0.setCellValue("教室名称");
HSSFCell cell1 = hssfRow.createCell(1);
cell1.setCellValue("课程名称");
HSSFCell cell2 = hssfRow.createCell(2);
cell2.setCellValue("学生学号");
Map<String, String> param = new HashMap<String, String>();
param.put("dtCode", "subjectDict");
List<String> sysDictList = new ArrayList<>();
sysDictList.add("X1");
sysDictList.add("X2");
sysDictList.add("X3");
sysDictList.add("X4");
sysDictList.add("X5");
sysDictList.add("X6");
//设置下拉列表
String[] subject = new String[sysDictList.size()];
for (int i = 0; i < sysDictList.size(); i++) {
subject[i] = sysDictList.get(i);
}
CellRangeAddressList subjectRegions = new CellRangeAddressList(1, 2055, 1, 1);
DVConstraint subjectConstraint = DVConstraint.createExplicitListConstraint(subject);
HSSFDataValidation subjectDataValidation = new HSSFDataValidation(subjectRegions, subjectConstraint);
sheet.addValidationData(subjectDataValidation);
subjectDataValidation.setShowErrorBox(false);// 取消弹出错误框*/
List<String> fieldRoomList = new ArrayList<>();
fieldRoomList.add("3");
fieldRoomList.add("4");
fieldRoomList.add("5");
fieldRoomList.add("6");
String[] fieldRooms = new String[fieldRoomList.size()];
for (int i = 0; i < fieldRoomList.size(); i++) {
fieldRooms[i] = fieldRoomList.get(i);
}
HSSFSheet hidden = workbook.createSheet("hidden");
HSSFCell cell = null;
for (int i = 0; i < fieldRooms.length; i++) {
HSSFRow row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(fieldRooms[i]);
}
Name namedCell = workbook.createName();
namedCell.setNameName("hidden");
// namedCell.setRefersToFormula("hidden!A1:A" + countryName.length); 导致下拉选项越选越少
//Offset(参照单元格,行偏移量,列偏移量,返回几行,返回几列)
namedCell.setRefersToFormula("OFFSET('hidden'!$A$1,0,0,COUNTA('hidden'!$A:$A)- 1,1)");
//加载数据,将名称为Sites的做为数据源
DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
// 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList addressList = new CellRangeAddressList(1, 2550, 0, 0);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
//将第二个sheet设置为隐藏
workbook.setSheetHidden(1, true);
sheet.addValidationData(validation);
FileOutputStream os = new FileOutputStream(new File("F:/学生课程安排.xls"));
workbook.write(os);
} catch (Exception e) {
e.printStackTrace();
}
}
}