You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "cool.aquarian" <gl...@gmail.com> on 2017/07/05 20:47:43 UTC
Copy Conditional formatting from one Worksheet to another
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
Re: Copy Conditional formatting from one Worksheet to another
Posted by Greg Woolsey <gr...@gmail.com>.
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 <do...@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 <gl...@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
> >
> >
>
Re: Copy Conditional formatting from one Worksheet to another
Posted by Dominik Stadler <do...@gmx.at>.
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 <gl...@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
>
>