2021-03-16

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();
        }
    }

}