poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bengt Rodehav <be...@rodehav.com>
Subject Re: Copy cell with validation
Date Thu, 08 Oct 2015 14:21:56 GMT
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 <javenoneal@gmail.com>:

> 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
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message