Mailing-List: contact user-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <user@poi.apache.org>
MIME-Version: 1.0
In-Reply-To: 
 <CAJ0TPGJj_P1bQWNRvfzD-sXKMwZUYH67e42G6CJ+Qe200YLcWA@mail.gmail.com>
References: 
 <CAJ0TPGJxHvv5=wWueQo6LcSyFe6r-jjNkS+Z_ueYmUx1G15EGA@mail.gmail.com>
 <CAM+Tpp+mdXFtfUYjeAH+ykZCbwvODXhCZU9HLNXu-V6D03uwJg@mail.gmail.com>
 <CAM+TppJTQWv8+3s-nfyHHwPzePD5gpdG+sMmYn3OVQJnVnPEGw@mail.gmail.com>
 <CAJ0TPGJj_P1bQWNRvfzD-sXKMwZUYH67e42G6CJ+Qe200YLcWA@mail.gmail.com>
From: "Javen O'Neal" <javenoneal@gmail.com>
Date: Wed, 7 Oct 2015 01:21:21 -0700
Message-ID: 
 <CAM+Tpp+v_GK+qLYyOhvEj4omDPhE9T0dZaZAzSLUr4E4ShfUSw@mail.gmail.com>
Subject: Re: Copy cell with validation
To: POI Users List <user@poi.apache.org>
Content-Type: text/plain; charset=UTF-8

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 <bengt@rodehav.com> 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 <javenoneal@gmail.com>:
>
>> 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" <javenoneal@gmail.com> 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" <bengt@rodehav.com> 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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org