Hello Jason, As it turned out, we created the original listboxes (data validation) ourselves using POI. As a workaround, I now create them (in many duplicates) in my target sheets instead of having to copy them from my template sheet. It worked for me.
Thanks for your advice, /Bengt 2015-10-07 10:21 GMT+02:00 Javen O'Neal <[email protected]>: > If you haven't already, search through bugzilla [1] to see if anyone > has had a similar problem. File a bug and summarize what was discussed > here. If you're comfortable throwing together a unit test and > attempting solution, that'd bring us really close to seeing this > solved in a future release of POI. It sounds like you'll need to touch > a handful of files because you're copying data validation between two > sheets in a workbook (this would be even harder if it were sheets from > different workbooks). > > I made the CellCopyPolicy class so that it'd be easy to accommodate > changes like yours. The more important problem here is figuring out > how to copy data validation--worry about integrating once you've > figured out DataValidation [2], XSSFDataValidationHelper [3] > > Here's something to get you started: > > 1. find all DataValidations that apply to srcCell > for (DataValidation validation : srcCell.getSheet().getDataValidations()) { > for (CellRangeAddress region : > validation.getRegions().getCellRangeAddresses()) { > if (region.isInRange(srcCell.getRow(), srcCell.getColumn())) { > // DataValidation applies to srcCell > } > } > } > 2. For each DataValidation that applies to > srhttps:// > poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFDataValidationHelper.htmlcCell > : > if srcCell and destCell are on same sheet, could potentially add > destCell to CellRangeAddressList (if validation type is formula, > formula may need to be offset for destCell, which would probably > require a new DataValidation rule). > if srcCell and destCell are on a different sheet, would probably > need to copy the rule to the new sheet (need to verify if a single > DataValidation rule is allowed to apply to cells on different sheets). > 3. Procedure for copying DataValidation to another sheet. I'm guessing > you'll need to create the appropriate data validation depending on > validation constraint type, unless you just clone/copy the > ValidationData and manipulate the fields as needed (might be easier > than rebuilding a near-identical DataValidation object from the base > constructor). > switch > (DataValidation.getValidationConstraint().getValidationType()) { > case DECIMAL: > XSSFDataValidation validation = > XSSFDataValidationHelper.createDecimalConstraint(...); > ... > } > destCell.getSheet().addValidationData(validation); > > Of course, these are all just suggestions. I haven't looked at > DataValidation very closely, so I could be very wrong here. Also, if > you plan on copying data validation for multiple cells, you may want > to use a different technique. For example, a single-cell copy would > copy a DataValidation that applies to a group of cells and create a > new DataValidation on a different sheet that applies to just one cell, > rather than a group of cells with the same address but on a different > sheet. I'm doing something similar for copying mergedRegions on line > 2677 and 2704 of XSSFSheet.java [4] for my copyRows change--since > copying merged regions one row at a time would not copy merged regions > that span multiple rows. > > [1] https://bz.apache.org/bugzilla/buglist.cgi?product=POI > [2] > https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataValidation.html#getRegions() > [3] > https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFDataValidationHelper.html > [4] > https://bz.apache.org/bugzilla/attachment.cgi?id=33138&action=diff#src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java_sec4 > Javen O'Neal > > > On Tue, Oct 6, 2015 at 11:47 PM, Bengt Rodehav <[email protected]> wrote: > > Thanks for your reply Javen, > > > > It would be nice if the CellCopyPolicy passed to the copyCellFrom() > method > > had an option to include the data validation as well. I've used data > > validation using listboxes with POI. However, the data validation is > > connected to the sheet and not to the cell which is a problem for me. I > > also cannot see how to get/extract the data validations for the source > cell > > and redirect it to the target cell. In my case the source sheet and the > > target sheet is not the same so I also need to clone the data validation > > somehow. > > > > /Bengt > > > > 2015-10-06 17:39 GMT+02:00 Javen O'Neal <[email protected]>: > > > >> When implementing data validation, check out the example from the User > >> Guide [1]. Scroll down for the XSSF section. The DataValidation > interface > >> [2] is pretty small. Searching Google for "data validation POI" gives > quite > >> a few helpful examples how to use the class if the User Guide doesn't > >> answer those questions. > >> > >> [1] https://poi.apache.org/spreadsheet/quick-guide.html#Validation > >> [2] > >> > >> > https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataValidation.html > >> On 6 Oct 2015 07:56, "Javen O'Neal" <[email protected]> wrote: > >> > >> > Please check out the patch I have in the queue for bug 58348 comment 4 > >> > [1]. I don't think my implementation copies data validation or > >> conditional > >> > formating, but this would be the place to add it. > >> > > >> > [1] > >> > > >> > https://bz.apache.org/bugzilla/attachment.cgi?id=33138&action=diff#src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java_sec5 > >> > On 6 Oct 2015 06:36, "Bengt Rodehav" <[email protected]> wrote: > >> > > >> >> I have template sheets that I use to copy cells with formatting to > other > >> >> sheets. I do it this way: > >> >> > >> >> private static void copyCell(Cell oldCell, Cell newCell) { > >> >> newCell.setCellStyle(oldCell.getCellStyle()); > >> >> > >> >> switch (oldCell.getCellType()) { > >> >> case Cell.CELL_TYPE_STRING: > >> >> newCell.setCellValue(oldCell.getRichStringCellValue()); > >> >> break; > >> >> case Cell.CELL_TYPE_NUMERIC: > >> >> newCell.setCellValue(oldCell.getNumericCellValue()); > >> >> break; > >> >> case Cell.CELL_TYPE_BLANK: > >> >> newCell.setCellType(Cell.CELL_TYPE_BLANK); > >> >> break; > >> >> case Cell.CELL_TYPE_FORMULA: > >> >> newCell.setCellFormula(oldCell.getCellFormula()); > >> >> break; > >> >> case Cell.CELL_TYPE_BOOLEAN: > >> >> newCell.setCellValue(oldCell.getBooleanCellValue()); > >> >> break; > >> >> case Cell.CELL_TYPE_ERROR: > >> >> newCell.setCellErrorValue(oldCell.getErrorCellValue()); > >> >> break; > >> >> default: > >> >> break; > >> >> } > >> >> } > >> >> > >> >> This seems to work and since I use the same style, the formatting is > >> >> copied > >> >> as well. > >> >> > >> >> However, some of the cells have data validations that renders as a > >> >> listbox. > >> >> How can I copy the data validation from one cell to another? If the > >> source > >> >> cell has a listbox I want the target cell to have one too. > >> >> > >> >> /Bengt > >> >> > >> > > >> > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > >
