ComboBox中的Apache POI双值 [英] Apache POI Double Values in ComboBox
问题描述
我想要一个带有以下条目的ComboBox:
{"0,5"，"1"，"1,5"，"2"，"2,5"}
我使用DataValidation:
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"}
但是在生成excel文件之后，下拉列表中还有其他内容.
0，5，1，1，5
那是为什么?
如果我使用点符号(0.5，1，1.5)，那么下一个问题是，当我从ComboBox中选择时，excel会自动将其格式化为日期，例如1.5-> 01. May
从您的描述看来，在您的Excel中，十进制分隔符是当前区域设置的逗号.因此，{"0,5", "1", "1,5", "2", "2,5"}中的此逗号与列表约束公式中用作列表定界符的逗号冲突.这是因为此列表约束公式将为<formula1>"0,5,1,1,5,2,2,5"</formula1>.
使用{"0.5", "1", "1.5", "2", "2.5"}时，列表约束公式将为<formula1>"0.5,1,1.5,2,2.5"</formula1>.但是，现在此公式中的点与您的区域设置冲突，在逗号中将逗号作为小数点分隔符，将点用作日期文字的分隔符.
这是一个众所周知的Excel问题.当前的Excel版本正在通过使用另一种存储列表约束的方法来解决此问题:<x12ac:list>"0,5",1,"1,5",2,"2,5"</x12ac:list>而不是:<formula1>"0,5,1,1,5,2,2,5"</formula1>.但是apache poi不支持此功能.
作为一种解决方法，我建议使用隐藏工作表来存储列表项.
示例:
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();
}
}
I want to have a ComboBox with the following entrys:
{"0,5", "1", "1,5", "2", "2,5"}
I use DataValidation:
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);
The list is of the following structure:
list = new String[]{ "0,5", "1", "1,5", "2", "2,5" }
But after generating the excel file, something else is in the dropdown list.
0, 5, 1, 1, 5
Why is that?
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
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>.
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.
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.
Example:
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屋！
