poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Woolsey <greg.wool...@gmail.com>
Subject Re: Copy Conditional formatting from one Worksheet to another
Date Wed, 19 Jul 2017 19:56:39 GMT
Dominik is correct, there is no easy way to do it.  The OOXML format for
these has document part relations, rule indexes, theme colors, shared
document style entries referenced by index, and other references that need
to be carefully managed along the way or the resulting zipped XML package
won't be internally consistent, and Excel will choke on it.  The
XSSFConditionalFormatting object is just the central piece, but it's CF*
fields have attributes that reference elements in styles.xml by implicit
array index, among other things.

The only way I know that would work would be to individually read each
property of each rule from one sheet and create brand new objects with
those properties in the other workbook, following examples in the
documentation on creating a new conditional formatting rule.  Anything that
tries to shortcut this will almost certainly fail to account for something.

On Wed, Jul 19, 2017 at 11:39 AM Dominik Stadler <dominik.stadler@gmx.at>
wrote:

> Hi,
>
> as nobody answered there is likely no direct solution, nobody seems to have
> done this before.
>
> You cannot assign items from one XSSFWorkbook directly into another, you
> rather need to either copy all the members or somehow duplicate the underly
> CT... objects. Sometimes such copying is done by serializing the CT...
> object into XML and then back into a new object, however I could not find
> an example on a quick search in the sources...
>
> the XSSFConditionalFormatting consists of the ranges and the actual rules,
> the rules map to a CTCfRule which contains more objects to fully describe
> the formatting rules, you will likely need to transfer all these with code
> or by exporting/importing to/from XML
>
> Dominik.
>
> On Wed, Jul 5, 2017 at 10:47 PM, cool.aquarian <glasskater@gmail.com>
> wrote:
>
> > Hello,
> >
> > I am trying to copy conditional formatting in one .xlsx worksheet to
> > another
> > (in a generic way). Here is what I am doing:
> > <code>
> >                         XSSFSheet xssfSheet = (XSSFSheet) sheet;
> >       //source sheet
> >                         XSSFSheet newXssfSheet = (XSSFSheet) newSheet;
> >  //target sheet
> >                         XSSFSheetConditionalFormatting scf =
> > xssfSheet.getSheetConditionalFormatting();
> >                         XSSFSheetConditionalFormatting nscf =
> > newXssfSheet.getSheetConditionalFormatting();
> >
> >                         for (int idx = 0; idx < scf.
> > getNumConditionalFormattings(); idx++) {
> >                                 XSSFConditionalFormatting cf = scf.
> > getConditionalFormattingAt(idx);
> >                                 nscf.addConditionalFormatting(cf);
> >                         }
> > </code>
> >
> > I can see that this gets the conditional formatting sections from
> original
> > sheet correctly.
> > But when I write the new sheet to a file, it makes the excel to ask to
> > repair the worksheet after opening.
> >
> > Any advise on this?
> >
> >
> >
> > --
> > View this message in context: http://apache-poi.1045710.n5.
> > nabble.com/Copy-Conditional-formatting-from-one-Worksheet-
> > to-another-tp5728044.html
> > Sent from the POI - User mailing list archive at Nabble.com.
> >
> > ---------------------------------------------------------------------
> > 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