在通过apache poi进行的复制粘贴数据时,数据validation失败

我使用apache poi制作一个.xls文件。 我还包括一些数据validation也如图所示。

ObservableList<String> objectstatusList = UpgradeWorkBench.wsData.getObjectStatusDevMan("Test", "testing"); String[] strStatus = new String[objectstatusList.size()]; objectstatusList.toArray(strStatus); CellRangeAddressList addressListStatus = new CellRangeAddressList(0, 65535, 9, 9); DVConstraint dvConstraintStatus = DVConstraint.createExplicitListConstraint(strStatus); dataValidationStatus = new HSSFDataValidation(addressListStatus, dvConstraintStatus); dataValidationStatus.setSuppressDropDownArrow(false); dataValidationStatus.setErrorStyle(ErrorStyle.STOP); 

如果我手动input任何数据,validation是正确应用的。 但是,如果我从其他单元复制粘贴数据validation失败,validation将完全从该单元中删除。 任何人都可以请帮助我怎样才能validation数据即使在复制粘贴。

我看到很多链接,但无法得到正确的解决scheme

由于apache poi不能创建宏,唯一的方法是使用所需的宏创建模板并创建结果。

例:

有一个工作表Sheet1template.xls和位于该工作表的代码模块中的http://spreadsheetpage.com/index.php/tip/ensuring_that_data_validation_is_not_deleted/中的宏&#x3002;

那么你可以使用下面的代码:

 import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.*; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.DataValidation.ErrorStyle; import org.apache.poi.hssf.usermodel.*; import java.io.*; class ReadAndWriteFromTemplateWithMacro { public static void main(String[] args) { try { String templateName = "template.xls"; String resultName = "result.xls"; String sheetName = "Sheet1"; String[] strStatus = new String[]{"on", "off", "maybe"}; FileInputStream template = new FileInputStream(templateName); Workbook wb = WorkbookFactory.create(template); Sheet sheet = wb.getSheet(sheetName); if (sheet instanceof HSSFSheet) { CellRangeAddressList addressListStatus = new CellRangeAddressList(0, 65535, 9, 9); DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strStatus); DataValidation dataValidation = new HSSFDataValidation(addressListStatus, dvConstraint); dataValidation.setSuppressDropDownArrow(false); dataValidation.setErrorStyle(ErrorStyle.STOP); sheet.addValidationData(dataValidation); //create a named range for the data validation as described in http://spreadsheetpage.com/index.php/tip/ensuring_that_data_validation_is_not_deleted/ Name name = wb.createName(); name.setNameName("ValidationRange"); String reference = addressListStatus.getCellRangeAddress(0).formatAsString(sheetName, true); name.setRefersToFormula(reference); } FileOutputStream output = new FileOutputStream(resultName); wb.write(output); wb.close(); } catch (InvalidFormatException ifex) { } catch (FileNotFoundException fnfex) { } catch (IOException ioex) { } } } 

现在result.xls也将包含该宏。 如果启用了宏,那么这个宏将通过粘贴来防止破坏数据值。