ComboBox 中的 Apache POI 双值 [英] Apache POI Double Values in ComboBox

问题描述

我想要一个包含以下条目的组合框:

I want to have a ComboBox with the following entrys:

{"0,5", "1", "1,5", "2", "2,5"}

{"0,5", "1", "1,5", "2", "2,5"}

我使用数据验证:

    DataValidation dataValidation = null;
    DataValidationConstraint constraint = null;
    DataValidationHelper validationHelper = null;

    validationHelper = new XSSFDataValidationHelper(sheet);
    CellRangeAddressList addressList = new  CellRangeAddressList(row, row, col, col);
    constraint = validationHelper.createExplicitListConstraint(list);
    dataValidation = validationHelper.createValidation(constraint, addressList);
    dataValidation.setSuppressDropDownArrow(true);   
    sheet.addValidationData(dataValidation);

列表的结构如下:

list = new String[]{ "0,5", "1", "1,5", "2", "2,5" }

list = new String[]{ "0,5", "1", "1,5", "2", "2,5" }

但是在生成 excel 文件后，下拉列表中还有其他内容.

But after generating the excel file, something else is in the dropdown list.

0, 5, 1, 1, 5

0, 5, 1, 1, 5

这是为什么?

如果我使用点符号 (0.5, 1, 1.5)，下一个问题是，当我从 ComboBox 中选择时，excel 将其自动格式化为日期，例如 1.5 -> 01.可能

If I use a point notation (0.5, 1, 1.5) the next problem is, when I choose from the ComboBox, excel autoformat its to a date, e.g 1.5 -> 01. May

推荐答案

从您的描述看来，在您的 Excel 中，十进制分隔符是来自您当前区域设置的逗号.所以 {"0,5", "1", "1,5", "2", "2,5"} 中的这个逗号与 list 中用作列表分隔符的逗号冲突约束公式.这是因为这个列表约束公式将是 "0,5,1,1,5,2,2,5".

From your description it seems that in your Excel the decimal delimiter is comma from your current locale settings. So this comma in {"0,5", "1", "1,5", "2", "2,5"} is in conflict with the comma used as list delimiter in list constraint formula. This is because this list constraint formula will be <formula1>"0,5,1,1,5,2,2,5"</formula1>.

当使用{"0.5", "1", "1.5", "2", "2.5"}时，列表约束公式为"0.5,1,1.5,2,2.5"​​.但是现在这个公式中的点与您的语言环境设置冲突，在日期文字中使用逗号作为十进制分隔符，点作为分隔符.

When used {"0.5", "1", "1.5", "2", "2.5"}, the list constraint formula will be <formula1>"0.5,1,1.5,2,2.5"</formula1>. But now the dot in this formula is in conflict with your locale settings having comma as decimal delimiter and dot as delimiter in date literals.

这是一个众所周知的 Excel 问题.当前的 Excel 版本通过使用不同类型的存储列表约束来解决这个问题:<x12ac:list>"0,5",1,"1,5",2,"2,5"</x12ac:list> 而不是:"0,5,1,1,5,2,2,5".但是apache poi 不支持这个.

This is a well known Excel problem. Current Excel versions are solving this by using a different kind of storing the list constraint: <x12ac:list>"0,5",1,"1,5",2,"2,5"</x12ac:list> instead of: <formula1>"0,5,1,1,5,2,2,5"</formula1>. But apache poi does not support this.

作为一种解决方法，我建议使用隐藏表来存储列表项.

As a workaround I suggest using a hidden sheet to store the list items.

示例:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.util.*;

class CreateExcelDataValidationListName {

 public static void main(String[] args) throws Exception{

   Workbook workbook = new XSSFWorkbook();  

   //create sheet for storing the list items:
   Sheet sheet = workbook.createSheet("ListSheet");
   sheet.createRow(0).createCell(0).setCellValue("SourceList");
   int r = 1;
   for (double d = 0.5; d < 3; d+=0.5) {
    sheet.createRow(r++).createCell(0).setCellValue(d);
   }
   //unselect that sheet because we will hide it later
   sheet.setSelected(false);
   //create a named range for the list contraint
   Name namedCell = workbook.createName();
   namedCell.setNameName("SourceList");
   String reference = "ListSheet!$A$2:$A$5";
   namedCell.setRefersToFormula(reference);

   //create the visible sheet
   sheet = workbook.createSheet("Sheet1");

   sheet.createRow(0).createCell(0).setCellValue("Take the ListItems from B1:");
   sheet.setActiveCell(new CellAddress("B1"));

   sheet.autoSizeColumn(0);

   //create the data validation
   DataValidationHelper dvHelper = sheet.getDataValidationHelper();
   DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("SourceList");
   CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1);            
   DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);

   sheet.addValidationData(validation);

   //hide the ListSheet
   workbook.setSheetHidden(0, true);
   //set Sheet1 active
   workbook.setActiveSheet(1);

   FileOutputStream out = new FileOutputStream("CreateExcelDataValidationList.xlsx");
   workbook.write(out);
   out.close();
   workbook.close();

 }
}

这篇关于ComboBox 中的 Apache POI 双值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！