Ограничения при создании выпадающего списка Excel с Apache POI

Question

Я пытаюсь сгенерировать файл Excel с некоторыми проверками, я прочитал руководства для разработчиков poi. Во время реализации я получил исключение (String literals in formulas can't be bigger than 255 characters ASCII). POI объединяет все выпадающие параметры в строку с ограничением '0', проверяя ее длину и предоставляя исключение.:(
Я использую последнюю версию POI 3.8 beta 5.
И мой код:

try {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    HSSFRow row = sheet.createRow((short) 0);
    //CellRangeAddressList from org.apache.poi.ss.util package
    CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
    DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(getCountries());
    DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
    dataValidation.setSuppressDropDownArrow(false);
    sheet.addValidationData(dataValidation);
    FileOutputStream fileOut = new FileOutputStream("c:\\test.xls");
    wb.write(fileOut);
    fileOut.close();
    } catch (IOException e) {
       e.printStackTrace();
  }

После этого я попытался с XSSFWorkBook с этим кодом:

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("new sheet");
DataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet);
DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(getCountries());
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(dataValidation);
FileOutputStream fileOut = new FileOutputStream("c:\\test.xlsx");

К сожалению, безуспешно с таким результатом, который состоит из запятой и длинной строки в одной ячейке:

Но вручную в Excel я могу создать выпадающие ячейки с этим длинным списком стран.
Есть ли способ генерировать выпадающий список с длинными строками, или API не поддерживает?

Answer 1

Я понял, что сам Excel не позволяет вводить строку диапазона проверки более 255 символов, это не было ограничением POI. И теперь я использую именованные диапазоны и именованные ячейки, и у меня это работает правильно. Поэтому мне пришлось поместить токены диапазона проверки на другой лист (скрытый), и я ссылался на желаемые диапазоны ячеек из моего реального листа. Вот мой рабочий код:

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet realSheet = workbook.createSheet("Sheet xls");
HSSFSheet hidden = workbook.createSheet("hidden");
for (int i = 0, length= countryName.length; i < length; i++) {
   String name = countryName[i];
   HSSFRow row = hidden.createRow(i);
   HSSFCell cell = row.createCell(0);
   cell.setCellValue(name);
 }
 Name namedCell = workbook.createName();
 namedCell.setNameName("hidden");
 namedCell.setRefersToFormula("hidden!$A$1:$A$" + countryName.length);
 DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
 CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
 HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
 workbook.setSheetHidden(1, true);
 realSheet.addValidationData(validation);
 FileOutputStream stream = new FileOutputStream("c:\\range.xls");
 workbook.write(stream);
 stream.close();

Answer 2

Грозное решение, предоставленное Õzbek, нуждается лишь в небольшой модификации, чтобы безупречно работать с NPOI (используя C# в.NET).

Вот мой код, предоставленный для удобства программистов на C#. Он принимает лист и позиционные элементы в качестве входных данных, а также может обрабатывать несколько выпадающих, когда они расположены в столбцах.

public static void CreateDropDownListForExcel(this ISheet sheet, IList<string> dropDownValues, int startRow, int lastRow, int column) {
    if (sheet == null) {
        return;
    }

    //Create a hidden sheet on the workbook (using the column as an id) with the dropdown values
    IWorkbook workbook = sheet.Workbook;
    string dropDownName = sheet.SheetName + "DropDownValuesForColumn" + column;
    ISheet hiddenSheet = workbook.CreateSheet(dropDownName);
    for (int i = 0, length = dropDownValues.Count; i < length; i++) {
        string name = dropDownValues[i];
        IRow row = hiddenSheet.CreateRow(i);
        ICell cell = row.CreateCell(0);
        cell.SetCellValue(name);
    }

    //Create the dropdown using the fields of the hidden sheet
    IName namedCell = workbook.CreateName();
    namedCell.NameName = dropDownName;
    namedCell.RefersToFormula = (dropDownName + "!$A$1:$A$" + dropDownValues.Count);
    DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(dropDownName);
    CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, column, column);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    int hiddenSheetIndex = workbook.GetSheetIndex(hiddenSheet);
    workbook.SetSheetHidden(hiddenSheetIndex, SheetState.HIDDEN);

    //Add the Dropdown to the presenting sheet.
    sheet.AddValidationData(validation);
}

Answer 3

Выше код работает нормально. Но если я использую классы XSSF вместо HSSF, произойдет сбой, сказав об исключении в потоке

"main" java.lang.NoSuchMethodError: org.apache.poi.ss.formula.FormulaParser.parse(Ljava/lang/String;Lorg/apache/poi/‌​ss/formula/FormulaParsingWorkbook;II)[Lorg/apache/poi/ss/formula/ptg/Ptg; at org.apache.poi.xssf.usermodel.XSSFName.setRefersToFormula(XSSFName.java:195) at XLDropdown.main(XLDropdown.java:35)

Чтобы создать выпадающий список с использованием интерфейсов XSSF, я получил решение по адресу

создать выпадающий список с использованием интерфейсов apache poi XSSF

Answer 4

Вот как я использовал FormulaListConstraint, используя модель XSSF.

      byte[] addConstraint() throws IOException {
    ByteArrayOutputStream byteArrayOutputStream = null;
    byte[] output = null;
    XSSFWorkbook workbook = null;

    try {
        Resource resource = new ClassPathResource("Test.xlsx");
        InputStream input = resource.getInputStream();
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        org.apache.commons.io.IOUtils.copy(input, baos);
        byte[] uploadedFile = baos.toByteArray();
        ByteArrayInputStream bais = new ByteArrayInputStream(uploadedFile);
        workbook = new XSSFWorkbook(bais);
        XSSFSheet sheet = workbook.getSheetAt(0);
        String originalSheet = "myhiddenSheet";
        String[] roles = new String[] { "val1", "val2"/* ...... */, "valn" };
        addHiddenSheet(workbook, roles, originalSheet);
        String formulae = originalSheet.concat("!$A$1:$A$");
        Name namedCell = workbook.createName();
        namedCell.setNameName(originalSheet);
        namedCell.setRefersToFormula(formulae + roles.length);
        XSSFDataValidationHelper userRoleDataValidationHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint userRoleConstraint = (XSSFDataValidationConstraint) userRoleDataValidationHelper
                .createFormulaListConstraint(originalSheet);
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        XSSFDataValidation userStatusValidation = (XSSFDataValidation) userRoleDataValidationHelper
                .createValidation(userRoleConstraint, addressList);
        sheet.addValidationData(userStatusValidation);

        byteArrayOutputStream = new ByteArrayOutputStream();
        workbook.write(byteArrayOutputStream);
        output = byteArrayOutputStream.toByteArray();
    } finally {
        workbook.close();
        byteArrayOutputStream.close();
    }
    return output;
}
private void addHiddenSheet(Workbook workbook, String[] values, String hiddenSheetName) {
    Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
    for (int i = 0, length = values.length; i < length; i++) {
        String name = values[i];
        Row row = hiddenSheet.createRow(i);
        Cell cell = row.createCell(0);
        cell.setCellValue(name);
    }
    workbook.setSheetHidden(1, true);

}