Copy Conditional formatting from one Worksheet to another

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Copy Conditional formatting from one Worksheet to another

cool.aquarian
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?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Copy Conditional formatting from one Worksheet to another

Dominik Stadler
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 <[hidden email]> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Copy Conditional formatting from one Worksheet to another

Greg Woolsey
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 <[hidden email]>
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 <[hidden email]>
> 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: [hidden email]
> > For additional commands, e-mail: [hidden email]
> >
> >
>
Loading...