Evaluating Arbitrary Formula

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Evaluating Arbitrary Formula

Blake Watson
Hello!

All of the evaluation stuff I can find works on a Cell object. I thought I
had seen something for evaluation of formula strings but I don't see it in
the FormulaEvaluator class (where I thought I had before).

​We're trying to get conditionals going here, so we have formulae that are
not in cells.

I feel like we might have to use the ​FormulaParser to get the Ptg[] but
I'm not sure what we do at that point.

Hints?

--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email] <[hidden email]>
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Greg Woolsey
This is in the nightly builds now and the upcoming 3.16 release. It
probably should go in the release notes/change log on the web site, but
hasn't yet.

The main change was revision r1782894 [1] in reference to issue 58131 [2].

There is now a ConditionalFormattingEvaluator class [3] to support these.
You can see one use in the unit test [4], which uses a sample workbook [5]
built from the publicly available Excel help examples for conditional
formatting.

For evaluating other arbitrary expressions, there are new variations of
WorkbookEvaluator.evaluate(...) [6] to support these, which are used by the
conditional formatting evaluator as well.

Check out the code, JavaDocs, and tests, and feel free to ask questions and
give any feedback you have.

[1] https://svn.apache.org/viewvc?view=rev&rev=1782894
[2] https://bz.apache.org/bugzilla/show_bug.cgi?id=58131
[3]
https://poi.apache.org/apidocs/org/apache/poi/ss/formula/ConditionalFormattingEvaluator.html
[4]
http://svn.apache.org/repos/asf/poi/trunk/src/ooxml/testcases/org/apache/poi/ss/usermodel/ConditionalFormattingEvalTest.java
[5]
http://svn.apache.org/repos/asf/poi/trunk/test-data/spreadsheet/ConditionalFormattingSamples.xlsx
[6]
https://poi.apache.org/apidocs/org/apache/poi/ss/formula/WorkbookEvaluator.html

> All of the evaluation stuff I can find works on a Cell object. I thought I
> had seen something for evaluation of formula strings but I don't see it
in
> the FormulaEvaluator class (where I thought I had before).
>
> I feel like we might have to use the ?00BFormulaParser to get the Ptg[]
but

> I'm not sure what we do at that point.
>
> Hints?
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742
> [hidden email] <[hidden email]>
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
> ransaction_X�u� �?
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Blake Watson
Thanks a lot! Is there a list somewhere to see what features are planned
for 3.17+? I can't seem to find a POI roadmap that shows (e.g.) that
conditional support was planned, or what's planned for future releases.

On Fri, Apr 14, 2017 at 8:54 AM, Greg Woolsey <[hidden email]>
wrote:

> This is in the nightly builds now and the upcoming 3.16 release. It
> probably should go in the release notes/change log on the web site, but
> hasn't yet.
>
> The main change was revision r1782894 [1] in reference to issue 58131 [2].
>
> There is now a ConditionalFormattingEvaluator class [3] to support these.
> You can see one use in the unit test [4], which uses a sample workbook [5]
> built from the publicly available Excel help examples for conditional
> formatting.
>
> For evaluating other arbitrary expressions, there are new variations of
> WorkbookEvaluator.evaluate(...) [6] to support these, which are used by
> the
> conditional formatting evaluator as well.
>
> Check out the code, JavaDocs, and tests, and feel free to ask questions and
> give any feedback you have.
>
> [1] https://urldefense.proofpoint.com/v2/url?u=https-3A__svn.
> apache.org_viewvc-3Fview-3Drev-26rev-3D1782894&d=DwIFaQ&c=
> dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=r9MBM2C98rE4MQqH6V_
> 9N3LfYdAUkr0hhNYEU_MRrMg&e=
> [2] https://urldefense.proofpoint.com/v2/url?u=https-3A__bz.
> apache.org_bugzilla_show-5Fbug.cgi-3Fid-3D58131&d=DwIFaQ&c=
> dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=k3UpwdutL-pPZvlVnLNgUCGvJ3F-
> 7Rpcfk1EyN6EaFc&e=
> [3]
> https://urldefense.proofpoint.com/v2/url?u=https-3A__poi.
> apache.org_apidocs_org_apache_poi_ss_formula_
> ConditionalFormattingEvaluator.html&d=DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=
> p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=k9H02-_hML-
> L7JFQrOvSawvJPgqqwkZnqkwoP4Vf3qA&e=
> [4]
> https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> apache.org_repos_asf_poi_trunk_src_ooxml_testcases_org_
> apache_poi_ss_usermodel_ConditionalFormattingEvalTest.java&d=DwIFaQ&c=
> dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=cEnDZW3IixM1O0pBB6tV-
> IVCuMERO2ECqb_3A2jIbFs&e=
> [5]
> https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> apache.org_repos_asf_poi_trunk_test-2Ddata_spreadsheet_
> ConditionalFormattingSamples.xlsx&d=DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=
> p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=qIftQTAEY1icGjsRyc7x6oa_
> 5g4tjQCHt2ZjKNRoVag&e=
> [6]
> https://urldefense.proofpoint.com/v2/url?u=https-3A__poi.
> apache.org_apidocs_org_apache_poi_ss_formula_WorkbookEvaluator.html&d=
> DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> 3stXXb3Psthjw&m=oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=
> sZiv6666WcpGP0RZpgUoZ-pF5ZmMxwdRos3CNST2LsA&e=
>
> > All of the evaluation stuff I can find works on a Cell object. I thought
> I
> > had seen something for evaluation of formula strings but I don't see it
> in
> > the FormulaEvaluator class (where I thought I had before).
> >
> > I feel like we might have to use the ?00BFormulaParser to get the Ptg[]
> but
> > I'm not sure what we do at that point.
> >
> > Hints?
> >
> > --
> >
> > *Blake Watson*
> >
> > *PNMAC*
> > Application Development Manager
> > 5898 Condor Drive
> > Moorpark, CA 93021
> > (805) 330.4911 x7742
> > [hidden email] <[hidden email]>
> > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> > ransaction_X�u� �?
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email] <[hidden email]>
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Greg Woolsey
It's all open-source, done in the spare time of volunteers who have a need
and/or desire to work on it.

In my case, I add features I need for work, and fix bugs that get in my way
or may cause my users trouble.  And sometimes fix bugs that just plain
annoy me.

There may be features and topics people are working on, but no centralized
plan or roadmap.  Patches and test cases are always welcome, many fixes and
features come in that way.

That's how I got started - I wanted formulas to evaluate faster, so I dug
in, found an avenue for improvement, submitted a patch, got good feedback
from committers about impacts I hadn't thought of, reworked the patch,
submitted it, and had it applied.  My work got what it needed, open source
got better, and the end product was better than my individual efforts
alone, a multiplier my boss could understand.

On Fri, Apr 14, 2017 at 9:43 AM Blake Watson <[hidden email]> wrote:

> Thanks a lot! Is there a list somewhere to see what features are planned
> for 3.17+? I can't seem to find a POI roadmap that shows (e.g.) that
> conditional support was planned, or what's planned for future releases.
>
> On Fri, Apr 14, 2017 at 8:54 AM, Greg Woolsey <[hidden email]>
> wrote:
>
> > This is in the nightly builds now and the upcoming 3.16 release. It
> > probably should go in the release notes/change log on the web site, but
> > hasn't yet.
> >
> > The main change was revision r1782894 [1] in reference to issue 58131
> [2].
> >
> > There is now a ConditionalFormattingEvaluator class [3] to support these.
> > You can see one use in the unit test [4], which uses a sample workbook
> [5]
> > built from the publicly available Excel help examples for conditional
> > formatting.
> >
> > For evaluating other arbitrary expressions, there are new variations of
> > WorkbookEvaluator.evaluate(...) [6] to support these, which are used by
> > the
> > conditional formatting evaluator as well.
> >
> > Check out the code, JavaDocs, and tests, and feel free to ask questions
> and
> > give any feedback you have.
> >
> > [1] https://urldefense.proofpoint.com/v2/url?u=https-3A__svn.
> > apache.org_viewvc-3Fview-3Drev-26rev-3D1782894&d=DwIFaQ&c=
> > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=r9MBM2C98rE4MQqH6V_
> > 9N3LfYdAUkr0hhNYEU_MRrMg&e=
> > [2] https://urldefense.proofpoint.com/v2/url?u=https-3A__bz.
> > apache.org_bugzilla_show-5Fbug.cgi-3Fid-3D58131&d=DwIFaQ&c=
> > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> >
> oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=k3UpwdutL-pPZvlVnLNgUCGvJ3F-
> > 7Rpcfk1EyN6EaFc&e=
> > [3]
> > https://urldefense.proofpoint.com/v2/url?u=https-3A__poi.
> > apache.org_apidocs_org_apache_poi_ss_formula_
> > ConditionalFormattingEvaluator.html&d=DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=
> > p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=k9H02-_hML-
> > L7JFQrOvSawvJPgqqwkZnqkwoP4Vf3qA&e=
> > [4]
> > https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> > apache.org_repos_asf_poi_trunk_src_ooxml_testcases_org_
> > apache_poi_ss_usermodel_ConditionalFormattingEvalTest.java&d=DwIFaQ&c=
> > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=cEnDZW3IixM1O0pBB6tV-
> > IVCuMERO2ECqb_3A2jIbFs&e=
> > [5]
> > https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> > apache.org_repos_asf_poi_trunk_test-2Ddata_spreadsheet_
> > ConditionalFormattingSamples.xlsx&d=DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=
> > p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=qIftQTAEY1icGjsRyc7x6oa_
> > 5g4tjQCHt2ZjKNRoVag&e=
> > [6]
> > https://urldefense.proofpoint.com/v2/url?u=https-3A__poi.
> > apache.org_apidocs_org_apache_poi_ss_formula_WorkbookEvaluator.html&d=
> > DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > 3stXXb3Psthjw&m=oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=
> > sZiv6666WcpGP0RZpgUoZ-pF5ZmMxwdRos3CNST2LsA&e=
> >
> > > All of the evaluation stuff I can find works on a Cell object. I
> thought
> > I
> > > had seen something for evaluation of formula strings but I don't see it
> > in
> > > the FormulaEvaluator class (where I thought I had before).
> > >
> > > I feel like we might have to use the ?00BFormulaParser to get the Ptg[]
> > but
> > > I'm not sure what we do at that point.
> > >
> > > Hints?
> > >
> > > --
> > >
> > > *Blake Watson*
> > >
> > > *PNMAC*
> > > Application Development Manager
> > > 5898 Condor Drive
> > > Moorpark, CA 93021
> > > (805) 330.4911 x7742 <(805)%20330-4911>
> > > [hidden email] <[hidden email]>
> > > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> > > ransaction_X�u� �?
> >
>
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742 <(805)%20330-4911>
> [hidden email] <[hidden email]>
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Blake Watson
Well, sure: I just thought maybe whoever's in charge of beta/releasing
might announce what features are in the works somewhere.

On Fri, Apr 14, 2017 at 10:04 AM, Greg Woolsey <[hidden email]>
wrote:

> It's all open-source, done in the spare time of volunteers who have a need
> and/or desire to work on it.
>
> In my case, I add features I need for work, and fix bugs that get in my way
> or may cause my users trouble.  And sometimes fix bugs that just plain
> annoy me.
>
> There may be features and topics people are working on, but no centralized
> plan or roadmap.  Patches and test cases are always welcome, many fixes and
> features come in that way.
>
> That's how I got started - I wanted formulas to evaluate faster, so I dug
> in, found an avenue for improvement, submitted a patch, got good feedback
> from committers about impacts I hadn't thought of, reworked the patch,
> submitted it, and had it applied.  My work got what it needed, open source
> got better, and the end product was better than my individual efforts
> alone, a multiplier my boss could understand.
>
> On Fri, Apr 14, 2017 at 9:43 AM Blake Watson <[hidden email]>
> wrote:
>
> > Thanks a lot! Is there a list somewhere to see what features are planned
> > for 3.17+? I can't seem to find a POI roadmap that shows (e.g.) that
> > conditional support was planned, or what's planned for future releases.
> >
> > On Fri, Apr 14, 2017 at 8:54 AM, Greg Woolsey <[hidden email]>
> > wrote:
> >
> > > This is in the nightly builds now and the upcoming 3.16 release. It
> > > probably should go in the release notes/change log on the web site, but
> > > hasn't yet.
> > >
> > > The main change was revision r1782894 [1] in reference to issue 58131
> > [2].
> > >
> > > There is now a ConditionalFormattingEvaluator class [3] to support
> these.
> > > You can see one use in the unit test [4], which uses a sample workbook
> > [5]
> > > built from the publicly available Excel help examples for conditional
> > > formatting.
> > >
> > > For evaluating other arbitrary expressions, there are new variations of
> > > WorkbookEvaluator.evaluate(...) [6] to support these, which are used
> by
> > > the
> > > conditional formatting evaluator as well.
> > >
> > > Check out the code, JavaDocs, and tests, and feel free to ask questions
> > and
> > > give any feedback you have.
> > >
> > > [1] https://urldefense.proofpoint.com/v2/url?u=https-3A__svn.
> > > apache.org_viewvc-3Fview-3Drev-26rev-3D1782894&d=DwIFaQ&c=
> > > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> 3stXXb3Psthjw&m=
> > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=r9MBM2C98rE4MQqH6V_
> > > 9N3LfYdAUkr0hhNYEU_MRrMg&e=
> > > [2] https://urldefense.proofpoint.com/v2/url?u=https-3A__bz.
> > > apache.org_bugzilla_show-5Fbug.cgi-3Fid-3D58131&d=DwIFaQ&c=
> > > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> 3stXXb3Psthjw&m=
> > >
> > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=k3UpwdutL-
> pPZvlVnLNgUCGvJ3F-
> > > 7Rpcfk1EyN6EaFc&e=
> > > [3]
> > > https://urldefense.proofpoint.com/v2/url?u=https-3A__poi.
> > > apache.org_apidocs_org_apache_poi_ss_formula_
> > > ConditionalFormattingEvaluator.html&d=DwIFaQ&c=
> dmLomitc30UP5j2qU8E1rg&r=
> > > p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=k9H02-_hML-
> > > L7JFQrOvSawvJPgqqwkZnqkwoP4Vf3qA&e=
> > > [4]
> > > https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> > > apache.org_repos_asf_poi_trunk_src_ooxml_testcases_org_
> > > apache_poi_ss_usermodel_ConditionalFormattingEvalTest.java&d=DwIFaQ&c=
> > > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> 3stXXb3Psthjw&m=
> > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=cEnDZW3IixM1O0pBB6tV-
> > > IVCuMERO2ECqb_3A2jIbFs&e=
> > > [5]
> > > https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> > > apache.org_repos_asf_poi_trunk_test-2Ddata_spreadsheet_
> > > ConditionalFormattingSamples.xlsx&d=DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=
> > > p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=qIftQTAEY1icGjsRyc7x6oa_
> > > 5g4tjQCHt2ZjKNRoVag&e=
> > > [6]
> > > https://urldefense.proofpoint.com/v2/url?u=https-3A__poi.
> > > apache.org_apidocs_org_apache_poi_ss_formula_WorkbookEvaluator.html&d=
> > > DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > > 3stXXb3Psthjw&m=oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=
> > > sZiv6666WcpGP0RZpgUoZ-pF5ZmMxwdRos3CNST2LsA&e=
> > >
> > > > All of the evaluation stuff I can find works on a Cell object. I
> > thought
> > > I
> > > > had seen something for evaluation of formula strings but I don't see
> it
> > > in
> > > > the FormulaEvaluator class (where I thought I had before).
> > > >
> > > > I feel like we might have to use the ?00BFormulaParser to get the
> Ptg[]
> > > but
> > > > I'm not sure what we do at that point.
> > > >
> > > > Hints?
> > > >
> > > > --
> > > >
> > > > *Blake Watson*
> > > >
> > > > *PNMAC*
> > > > Application Development Manager
> > > > 5898 Condor Drive
> > > > Moorpark, CA 93021
> > > > (805) 330.4911 x7742 <(805)%20330-4911>
> > > > [hidden email] <[hidden email]>
> > > > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> > > > ransaction_X�u� �?
> > >
> >
> >
> >
> > --
> >
> > *Blake Watson*
> >
> > *PNMAC*
> > Application Development Manager
> > 5898 Condor Drive
> > Moorpark, CA 93021
> > (805) 330.4911 x7742 <(805)%20330-4911>
> > [hidden email] <[hidden email]>
> > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> >
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email] <[hidden email]>
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Greg Woolsey
Probably the best bet for that would be the developers' list.  We don't
know what features are in until we decide to tag a release candidate :)

The dev list includes all public discussions and commits, which is the
closest I can think of to tracking what's being worked on.  Also a good
place to discuss and solicit feedback on patch ideas.

https://lists.apache.org/list.html?dev@...

On Fri, Apr 14, 2017 at 10:37 AM Blake Watson <[hidden email]>
wrote:

> Well, sure: I just thought maybe whoever's in charge of beta/releasing
> might announce what features are in the works somewhere.
>
> On Fri, Apr 14, 2017 at 10:04 AM, Greg Woolsey <[hidden email]>
> wrote:
>
> > It's all open-source, done in the spare time of volunteers who have a
> need
> > and/or desire to work on it.
> >
> > In my case, I add features I need for work, and fix bugs that get in my
> way
> > or may cause my users trouble.  And sometimes fix bugs that just plain
> > annoy me.
> >
> > There may be features and topics people are working on, but no
> centralized
> > plan or roadmap.  Patches and test cases are always welcome, many fixes
> and
> > features come in that way.
> >
> > That's how I got started - I wanted formulas to evaluate faster, so I dug
> > in, found an avenue for improvement, submitted a patch, got good feedback
> > from committers about impacts I hadn't thought of, reworked the patch,
> > submitted it, and had it applied.  My work got what it needed, open
> source
> > got better, and the end product was better than my individual efforts
> > alone, a multiplier my boss could understand.
> >
> > On Fri, Apr 14, 2017 at 9:43 AM Blake Watson <[hidden email]>
> > wrote:
> >
> > > Thanks a lot! Is there a list somewhere to see what features are
> planned
> > > for 3.17+? I can't seem to find a POI roadmap that shows (e.g.) that
> > > conditional support was planned, or what's planned for future releases.
> > >
> > > On Fri, Apr 14, 2017 at 8:54 AM, Greg Woolsey <[hidden email]>
> > > wrote:
> > >
> > > > This is in the nightly builds now and the upcoming 3.16 release. It
> > > > probably should go in the release notes/change log on the web site,
> but
> > > > hasn't yet.
> > > >
> > > > The main change was revision r1782894 [1] in reference to issue 58131
> > > [2].
> > > >
> > > > There is now a ConditionalFormattingEvaluator class [3] to support
> > these.
> > > > You can see one use in the unit test [4], which uses a sample
> workbook
> > > [5]
> > > > built from the publicly available Excel help examples for conditional
> > > > formatting.
> > > >
> > > > For evaluating other arbitrary expressions, there are new variations
> of
> > > > WorkbookEvaluator.evaluate(...) [6] to support these, which are used
> > by
> > > > the
> > > > conditional formatting evaluator as well.
> > > >
> > > > Check out the code, JavaDocs, and tests, and feel free to ask
> questions
> > > and
> > > > give any feedback you have.
> > > >
> > > > [1] https://urldefense.proofpoint.com/v2/url?u=https-3A__svn.
> > > > apache.org_viewvc-3Fview-3Drev-26rev-3D1782894&d=DwIFaQ&c=
> > > > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > 3stXXb3Psthjw&m=
> > > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=r9MBM2C98rE4MQqH6V_
> > > > 9N3LfYdAUkr0hhNYEU_MRrMg&e=
> > > > [2] https://urldefense.proofpoint.com/v2/url?u=https-3A__bz.
> > > > apache.org_bugzilla_show-5Fbug.cgi-3Fid-3D58131&d=DwIFaQ&c=
> > > > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > 3stXXb3Psthjw&m=
> > > >
> > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=k3UpwdutL-
> > pPZvlVnLNgUCGvJ3F-
> > > > 7Rpcfk1EyN6EaFc&e=
> > > > [3]
> > > > https://urldefense.proofpoint.com/v2/url?u=https-3A__poi.
> > > > apache.org_apidocs_org_apache_poi_ss_formula_
> > > > ConditionalFormattingEvaluator.html&d=DwIFaQ&c=
> > dmLomitc30UP5j2qU8E1rg&r=
> > > > p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> > > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=k9H02-_hML-
> > > > L7JFQrOvSawvJPgqqwkZnqkwoP4Vf3qA&e=
> > > > [4]
> > > > https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> > > > apache.org_repos_asf_poi_trunk_src_ooxml_testcases_org_
> > > >
> apache_poi_ss_usermodel_ConditionalFormattingEvalTest.java&d=DwIFaQ&c=
> > > > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > 3stXXb3Psthjw&m=
> > > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=cEnDZW3IixM1O0pBB6tV-
> > > > IVCuMERO2ECqb_3A2jIbFs&e=
> > > > [5]
> > > > https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> > > > apache.org_repos_asf_poi_trunk_test-2Ddata_spreadsheet_
> > > >
> ConditionalFormattingSamples.xlsx&d=DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=
> > > > p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> > > >
> oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=qIftQTAEY1icGjsRyc7x6oa_
> > > > 5g4tjQCHt2ZjKNRoVag&e=
> > > > [6]
> > > > https://urldefense.proofpoint.com/v2/url?u=https-3A__poi.
> > > >
> apache.org_apidocs_org_apache_poi_ss_formula_WorkbookEvaluator.html&d=
> > > > DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > > > 3stXXb3Psthjw&m=oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=
> > > > sZiv6666WcpGP0RZpgUoZ-pF5ZmMxwdRos3CNST2LsA&e=
> > > >
> > > > > All of the evaluation stuff I can find works on a Cell object. I
> > > thought
> > > > I
> > > > > had seen something for evaluation of formula strings but I don't
> see
> > it
> > > > in
> > > > > the FormulaEvaluator class (where I thought I had before).
> > > > >
> > > > > I feel like we might have to use the ?00BFormulaParser to get the
> > Ptg[]
> > > > but
> > > > > I'm not sure what we do at that point.
> > > > >
> > > > > Hints?
> > > > >
> > > > > --
> > > > >
> > > > > *Blake Watson*
> > > > >
> > > > > *PNMAC*
> > > > > Application Development Manager
> > > > > 5898 Condor Drive
> > > > > Moorpark, CA 93021
> > > > > (805) 330.4911 x7742 <(805)%20330-4911> <(805)%20330-4911>
> > > > > [hidden email] <[hidden email]>
> > > > > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> > > > > ransaction_X�u� �?
> > > >
> > >
> > >
> > >
> > > --
> > >
> > > *Blake Watson*
> > >
> > > *PNMAC*
> > > Application Development Manager
> > > 5898 Condor Drive
> > > Moorpark, CA 93021
> > > (805) 330.4911 x7742 <(805)%20330-4911> <(805)%20330-4911>
> > > [hidden email] <[hidden email]>
> > > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> > >
> >
>
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742 <(805)%20330-4911>
> [hidden email] <[hidden email]>
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Blake Watson
Cool. I'll get on that!

On Fri, Apr 14, 2017 at 10:48 AM, Greg Woolsey <[hidden email]>
wrote:

> Probably the best bet for that would be the developers' list.  We don't
> know what features are in until we decide to tag a release candidate :)
>
> The dev list includes all public discussions and commits, which is the
> closest I can think of to tracking what's being worked on.  Also a good
> place to discuss and solicit feedback on patch ideas.
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.
> apache.org_list.html-3Fdev-40poi.apache.org&d=DwIFaQ&c=
> dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> wWV6I426tfNHSnUny_EWojzjtTbqvnYu-d4NF9JyxQo&s=
> TqoSy3ICyFrw0yAK-gxrWSn-aI0lt_DlaKz1Ajgje7c&e=
>
> On Fri, Apr 14, 2017 at 10:37 AM Blake Watson <[hidden email]>
> wrote:
>
> > Well, sure: I just thought maybe whoever's in charge of beta/releasing
> > might announce what features are in the works somewhere.
> >
> > On Fri, Apr 14, 2017 at 10:04 AM, Greg Woolsey <[hidden email]>
> > wrote:
> >
> > > It's all open-source, done in the spare time of volunteers who have a
> > need
> > > and/or desire to work on it.
> > >
> > > In my case, I add features I need for work, and fix bugs that get in my
> > way
> > > or may cause my users trouble.  And sometimes fix bugs that just plain
> > > annoy me.
> > >
> > > There may be features and topics people are working on, but no
> > centralized
> > > plan or roadmap.  Patches and test cases are always welcome, many fixes
> > and
> > > features come in that way.
> > >
> > > That's how I got started - I wanted formulas to evaluate faster, so I
> dug
> > > in, found an avenue for improvement, submitted a patch, got good
> feedback
> > > from committers about impacts I hadn't thought of, reworked the patch,
> > > submitted it, and had it applied.  My work got what it needed, open
> > source
> > > got better, and the end product was better than my individual efforts
> > > alone, a multiplier my boss could understand.
> > >
> > > On Fri, Apr 14, 2017 at 9:43 AM Blake Watson <[hidden email]>
> > > wrote:
> > >
> > > > Thanks a lot! Is there a list somewhere to see what features are
> > planned
> > > > for 3.17+? I can't seem to find a POI roadmap that shows (e.g.) that
> > > > conditional support was planned, or what's planned for future
> releases.
> > > >
> > > > On Fri, Apr 14, 2017 at 8:54 AM, Greg Woolsey <
> [hidden email]>
> > > > wrote:
> > > >
> > > > > This is in the nightly builds now and the upcoming 3.16 release. It
> > > > > probably should go in the release notes/change log on the web site,
> > but
> > > > > hasn't yet.
> > > > >
> > > > > The main change was revision r1782894 [1] in reference to issue
> 58131
> > > > [2].
> > > > >
> > > > > There is now a ConditionalFormattingEvaluator class [3] to support
> > > these.
> > > > > You can see one use in the unit test [4], which uses a sample
> > workbook
> > > > [5]
> > > > > built from the publicly available Excel help examples for
> conditional
> > > > > formatting.
> > > > >
> > > > > For evaluating other arbitrary expressions, there are new
> variations
> > of
> > > > > WorkbookEvaluator.evaluate(...) [6] to support these, which are
> used
> > > by
> > > > > the
> > > > > conditional formatting evaluator as well.
> > > > >
> > > > > Check out the code, JavaDocs, and tests, and feel free to ask
> > questions
> > > > and
> > > > > give any feedback you have.
> > > > >
> > > > > [1] https://urldefense.proofpoint.com/v2/url?u=https-3A__svn.
> > > > > apache.org_viewvc-3Fview-3Drev-26rev-3D1782894&d=DwIFaQ&c=
> > > > > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > > 3stXXb3Psthjw&m=
> > > > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=r9MBM2C98rE4MQqH6V_
> > > > > 9N3LfYdAUkr0hhNYEU_MRrMg&e=
> > > > > [2] https://urldefense.proofpoint.com/v2/url?u=https-3A__bz.
> > > > > apache.org_bugzilla_show-5Fbug.cgi-3Fid-3D58131&d=DwIFaQ&c=
> > > > > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > > 3stXXb3Psthjw&m=
> > > > >
> > > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=k3UpwdutL-
> > > pPZvlVnLNgUCGvJ3F-
> > > > > 7Rpcfk1EyN6EaFc&e=
> > > > > [3]
> > > > > https://urldefense.proofpoint.com/v2/url?u=https-3A__poi.
> > > > > apache.org_apidocs_org_apache_poi_ss_formula_
> > > > > ConditionalFormattingEvaluator.html&d=DwIFaQ&c=
> > > dmLomitc30UP5j2qU8E1rg&r=
> > > > > p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> > > > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=k9H02-_hML-
> > > > > L7JFQrOvSawvJPgqqwkZnqkwoP4Vf3qA&e=
> > > > > [4]
> > > > > https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> > > > > apache.org_repos_asf_poi_trunk_src_ooxml_testcases_org_
> > > > >
> > apache_poi_ss_usermodel_ConditionalFormattingEvalTest.java&d=DwIFaQ&c=
> > > > > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > > 3stXXb3Psthjw&m=
> > > > > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=
> cEnDZW3IixM1O0pBB6tV-
> > > > > IVCuMERO2ECqb_3A2jIbFs&e=
> > > > > [5]
> > > > > https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> > > > > apache.org_repos_asf_poi_trunk_test-2Ddata_spreadsheet_
> > > > >
> > ConditionalFormattingSamples.xlsx&d=DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=
> > > > > p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> > > > >
> > oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=qIftQTAEY1icGjsRyc7x6oa_
> > > > > 5g4tjQCHt2ZjKNRoVag&e=
> > > > > [6]
> > > > > https://urldefense.proofpoint.com/v2/url?u=https-3A__poi.
> > > > >
> > apache.org_apidocs_org_apache_poi_ss_formula_WorkbookEvaluator.html&d=
> > > > > DwIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > > > > 3stXXb3Psthjw&m=oVx4cx4CWoEwyK7LauHPeeobVSud97PWgIY35BqX884&s=
> > > > > sZiv6666WcpGP0RZpgUoZ-pF5ZmMxwdRos3CNST2LsA&e=
> > > > >
> > > > > > All of the evaluation stuff I can find works on a Cell object. I
> > > > thought
> > > > > I
> > > > > > had seen something for evaluation of formula strings but I don't
> > see
> > > it
> > > > > in
> > > > > > the FormulaEvaluator class (where I thought I had before).
> > > > > >
> > > > > > I feel like we might have to use the ?00BFormulaParser to get the
> > > Ptg[]
> > > > > but
> > > > > > I'm not sure what we do at that point.
> > > > > >
> > > > > > Hints?
> > > > > >
> > > > > > --
> > > > > >
> > > > > > *Blake Watson*
> > > > > >
> > > > > > *PNMAC*
> > > > > > Application Development Manager
> > > > > > 5898 Condor Drive
> > > > > > Moorpark, CA 93021
> > > > > > (805) 330.4911 x7742 <(805)%20330-4911> <(805)%20330-4911>
> > > > > > [hidden email] <[hidden email]>
> > > > > > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> > > > > > ransaction_X�u� �?
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > >
> > > > *Blake Watson*
> > > >
> > > > *PNMAC*
> > > > Application Development Manager
> > > > 5898 Condor Drive
> > > > Moorpark, CA 93021
> > > > (805) 330.4911 x7742 <(805)%20330-4911> <(805)%20330-4911>
> > > > [hidden email] <[hidden email]>
> > > > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> > > >
> > >
> >
> >
> >
> > --
> >
> > *Blake Watson*
> >
> > *PNMAC*
> > Application Development Manager
> > 5898 Condor Drive
> > Moorpark, CA 93021
> > (805) 330.4911 x7742 <(805)%20330-4911>
> > [hidden email] <[hidden email]>
> > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> >
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email] <[hidden email]>
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

RE: Evaluating Arbitrary Formula

Allison, Timothy B.
+1

Come on in, the water's fine!

-----Original Message-----
From: Blake Watson [mailto:[hidden email]]
Sent: Friday, April 14, 2017 2:01 PM
To: POI Users List <[hidden email]>
Subject: Re: Evaluating Arbitrary Formula

Cool. I'll get on that!

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Blake Watson
Greg,

​I'm having trouble figuring out how to use the conditional stuff. ​I can't
seem to =find= ConditionalFormattingEvaluator in the POI jar.

If I could find it, I'm not sure how I'd use it:

ec = ConditionalFormattingEvaluator(Workbook wb, WorkbookEvaluatorProvider
provider);

How do I create "provider"? I don't get the "IStabilityClass" thing.

Once having it, I would guess I call:

list = ec.getConditionalFormattingForCell(someCell);

and for each item in the list:

fmt = item.getFormatting();

But does getConditionalFormattingForCell return all the formats POSSIBLE or
only the ones that apply based on the spreadsheet's current state? And if
the former, how do I eliminate the ones that apply?

I feel like I'm looking completely in the wrong direction, but I haven't
been able to locate any examples of how to use this.
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Greg Woolsey
The class is org.apache.poi.ss.formula.ConditionalFormattingEvaluator in
poi-3.16.jar.

Use is something like this:

WorkbookEvaluatorProvider wbEvalProv = (WorkbookEvaluatorProvider)
workbook.getCreationHelper().createFormulaEvaluator();
ConditionalFormattingEvaluator cfEval = new
ConditionalFormattingEvaluator(workbook, wbEvalProv);
// if cell values have changed, clear cached format results
cfEvaluator.clearAllCachedValues();
List<EvaluationConditionalFormatRule> rules =
cfEvaluator.getConditionalFormattingForCell(cell);
// check rules list for null, do what you want with results
// EvaluationConditionalFormatRule provides a more user-friendly API to the
disparate underlying elements for a given match



On Wed, Apr 19, 2017 at 2:28 PM Blake Watson <[hidden email]> wrote:

> Greg,
> ​
> ​I'm having trouble figuring out how to use the conditional stuff. ​I can't
> seem to =find= ConditionalFormattingEvaluator in the POI jar.
>
> If I could find it, I'm not sure how I'd use it:
>
> ec = ConditionalFormattingEvaluator(Workbook wb, WorkbookEvaluatorProvider
> provider);
>
> How do I create "provider"? I don't get the "IStabilityClass" thing.
>
> Once having it, I would guess I call:
>
> list = ec.getConditionalFormattingForCell(someCell);
>
> and for each item in the list:
>
> fmt = item.getFormatting();
>
> But does getConditionalFormattingForCell return all the formats POSSIBLE or
> only the ones that apply based on the spreadsheet's current state? And if
> the former, how do I eliminate the ones that apply?
>
> I feel like I'm looking completely in the wrong direction, but I haven't
> been able to locate any examples of how to use this.
>
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Greg Woolsey
Missed the 2nd half of the question.  This class only returns rules that
match the current state of the workbook for a given cell - rules that would
be applied were it open in Excel.  Note that this logic is limited to
evaluating functions actually implemented in POI, which is most of them,
but there are some exceptions and a few open bugs.

The unit test has one example of usage:

http://svn.apache.org/repos/asf/poi/trunk/src/ooxml/testcases/org/apache/poi/ss/usermodel/ConditionalFormattingEvalTest.java

as does my submission to Vaadin-Spreadsheet:

https://github.com/vaadin/spreadsheet/issues/461#issuecomment-279091036

On Wed, Apr 19, 2017 at 3:26 PM Greg Woolsey <[hidden email]> wrote:

> The class is org.apache.poi.ss.formula.ConditionalFormattingEvaluator in
> poi-3.16.jar.
>
> Use is something like this:
>
> WorkbookEvaluatorProvider wbEvalProv = (WorkbookEvaluatorProvider)
> workbook.getCreationHelper().createFormulaEvaluator();
> ConditionalFormattingEvaluator cfEval = new
> ConditionalFormattingEvaluator(workbook, wbEvalProv);
> // if cell values have changed, clear cached format results
> cfEvaluator.clearAllCachedValues();
> List<EvaluationConditionalFormatRule> rules =
> cfEvaluator.getConditionalFormattingForCell(cell);
> // check rules list for null, do what you want with results
> // EvaluationConditionalFormatRule provides a more user-friendly API to
> the disparate underlying elements for a given match
>
>
>
> On Wed, Apr 19, 2017 at 2:28 PM Blake Watson <[hidden email]>
> wrote:
>
>> Greg,
>> ​
>> ​I'm having trouble figuring out how to use the conditional stuff. ​I
>> can't
>> seem to =find= ConditionalFormattingEvaluator in the POI jar.
>>
>> If I could find it, I'm not sure how I'd use it:
>>
>> ec = ConditionalFormattingEvaluator(Workbook wb, WorkbookEvaluatorProvider
>> provider);
>>
>> How do I create "provider"? I don't get the "IStabilityClass" thing.
>>
>> Once having it, I would guess I call:
>>
>> list = ec.getConditionalFormattingForCell(someCell);
>>
>> and for each item in the list:
>>
>> fmt = item.getFormatting();
>>
>> But does getConditionalFormattingForCell return all the formats POSSIBLE
>> or
>> only the ones that apply based on the spreadsheet's current state? And if
>> the former, how do I eliminate the ones that apply?
>>
>> I feel like I'm looking completely in the wrong direction, but I haven't
>> been able to locate any examples of how to use this.
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Blake Watson
Thank you! This was very helpful!

On Wed, Apr 19, 2017 at 3:35 PM, Greg Woolsey <[hidden email]>
wrote:

> Missed the 2nd half of the question.  This class only returns rules that
> match the current state of the workbook for a given cell - rules that would
> be applied were it open in Excel.  Note that this logic is limited to
> evaluating functions actually implemented in POI, which is most of them,
> but there are some exceptions and a few open bugs.
>
> The unit test has one example of usage:
>
> https://urldefense.proofpoint.com/v2/url?u=http-3A__svn.
> apache.org_repos_asf_poi_trunk_src_ooxml_testcases_org_
> apache_poi_ss_usermodel_ConditionalFormattingEvalTest.java&d=DwIFaQ&c=
> dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> ftB0odLP9NpiHdLKILpfF8ePNrsTAR_3Oe2uV1pgn7s&s=
> uz8szbVrYsfZxKUvlK3XJ0grjXaToTnwMnBv39ZBFrM&e=
>
> as does my submission to Vaadin-Spreadsheet:
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__github.
> com_vaadin_spreadsheet_issues_461-23issuecomment-2D279091036&d=DwIFaQ&c=
> dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=
> ftB0odLP9NpiHdLKILpfF8ePNrsTAR_3Oe2uV1pgn7s&s=2Q-
> KjSfhwqGzVNryzhoGkd0TB92h4bYfsQBwIvjZy98&e=
>
> On Wed, Apr 19, 2017 at 3:26 PM Greg Woolsey <[hidden email]>
> wrote:
>
> > The class is org.apache.poi.ss.formula.ConditionalFormattingEvaluator in
> > poi-3.16.jar.
> >
> > Use is something like this:
> >
> > WorkbookEvaluatorProvider wbEvalProv = (WorkbookEvaluatorProvider)
> > workbook.getCreationHelper().createFormulaEvaluator();
> > ConditionalFormattingEvaluator cfEval = new
> > ConditionalFormattingEvaluator(workbook, wbEvalProv);
> > // if cell values have changed, clear cached format results
> > cfEvaluator.clearAllCachedValues();
> > List<EvaluationConditionalFormatRule> rules =
> > cfEvaluator.getConditionalFormattingForCell(cell);
> > // check rules list for null, do what you want with results
> > // EvaluationConditionalFormatRule provides a more user-friendly API to
> > the disparate underlying elements for a given match
> >
> >
> >
> > On Wed, Apr 19, 2017 at 2:28 PM Blake Watson <[hidden email]>
> > wrote:
> >
> >> Greg,
> >> ​
> >> ​I'm having trouble figuring out how to use the conditional stuff. ​I
> >> can't
> >> seem to =find= ConditionalFormattingEvaluator in the POI jar.
> >>
> >> If I could find it, I'm not sure how I'd use it:
> >>
> >> ec = ConditionalFormattingEvaluator(Workbook wb,
> WorkbookEvaluatorProvider
> >> provider);
> >>
> >> How do I create "provider"? I don't get the "IStabilityClass" thing.
> >>
> >> Once having it, I would guess I call:
> >>
> >> list = ec.getConditionalFormattingForCell(someCell);
> >>
> >> and for each item in the list:
> >>
> >> fmt = item.getFormatting();
> >>
> >> But does getConditionalFormattingForCell return all the formats
> POSSIBLE
> >> or
> >> only the ones that apply based on the spreadsheet's current state? And
> if
> >> the former, how do I eliminate the ones that apply?
> >>
> >> I feel like I'm looking completely in the wrong direction, but I haven't
> >> been able to locate any examples of how to use this.
> >>
> >
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email] <[hidden email]>
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Nick Burch-2
In reply to this post by Greg Woolsey
On Wed, 19 Apr 2017, Greg Woolsey wrote:
> Missed the 2nd half of the question.  This class only returns rules that
> match the current state of the workbook for a given cell - rules that
> would be applied were it open in Excel.  Note that this logic is limited
> to evaluating functions actually implemented in POI, which is most of
> them, but there are some exceptions and a few open bugs.

Is it worth adding this as another demo method to
src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java ?

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Greg Woolsey
Hadn't seen those example files.  I'll think about what an example might
look like.  I'm sure it would be useful to someone.

On Wed, Apr 19, 2017 at 10:46 PM Nick Burch <[hidden email]> wrote:

> On Wed, 19 Apr 2017, Greg Woolsey wrote:
> > Missed the 2nd half of the question.  This class only returns rules that
> > match the current state of the workbook for a given cell - rules that
> > would be applied were it open in Excel.  Note that this logic is limited
> > to evaluating functions actually implemented in POI, which is most of
> > them, but there are some exceptions and a few open bugs.
>
> Is it worth adding this as another demo method to
> src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java ?
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Evaluating Arbitrary Formula

Greg Woolsey
I added example code and output per Nick's suggestion.  I didn't do
anything fancy in terms of converting things like color indexes to text
names or RGB values, but it does show how to evaluate which rules apply to
a given cell, and having multiple rules apply, with possibly
conflicting/overriding formatting.

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java

On Thu, Apr 20, 2017 at 9:53 AM Greg Woolsey <[hidden email]> wrote:

> Hadn't seen those example files.  I'll think about what an example might
> look like.  I'm sure it would be useful to someone.
>
> On Wed, Apr 19, 2017 at 10:46 PM Nick Burch <[hidden email]> wrote:
>
>> On Wed, 19 Apr 2017, Greg Woolsey wrote:
>> > Missed the 2nd half of the question.  This class only returns rules that
>> > match the current state of the workbook for a given cell - rules that
>> > would be applied were it open in Excel.  Note that this logic is limited
>> > to evaluating functions actually implemented in POI, which is most of
>> > them, but there are some exceptions and a few open bugs.
>>
>> Is it worth adding this as another demo method to
>> src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java ?
>>
>> Nick
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
Reply | Threaded
Open this post in threaded view
|

characterRun.getSymbolChar() returns the same char for different symbols

Teresa Kim
In reply to this post by Allison, Timothy B.
Dear POI users

I got a doc document which contains uncommon greek mu and registered
symbol and tried to use characterRun.getSymbolChar() method to identify
these two symbols.
I have noticed however, characterRun.getSymbolChar() always returns the
same character and such that I could not find a way to notice the
different symbols.

I looked at the CharacterRun.java and tried to print out
_props.getXchSym() and found that this infact prints two different
values for the greek mu and regiestered symbol, i.e. '-3987' and '3870'.

I really don't know if I am doing right thing in that I could use
_props.getXchSym() directly instead of using
characterRun.getSymbolChar() method which returns (char)_props.getXchSym().

To make it work, I added one method next to
characterRun.getSymbolChar()  in CharacterRun class that returns
_props.getXchSym().
Would you please take a look at this and could see if this could be
added into CharacterRun class?

I enclose a word document which contains those two symbols and a snippet
for the new method I made to the CharacterRun class for your reference.



  public int getSymbolCharacterAsitis()
   {
     if (isSymbol()) {
         return _props.getXchSym();
     } else
       throw new IllegalStateException("Not a symbol CharacterRun");
   }


Many thanks in advance
Teresa



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

RE: characterRun.getSymbolChar() returns the same char for different symbols

Allison, Timothy B.
Teresa,
  Thank you for sharing this with us.  Would you mind opening a ticket on our Bugzilla [1] and attaching the triggering document there.  Attachments don't come through on our mailing list.
  Thank you, again.

            Best,

                     Tim

[1] https://bz.apache.org/bugzilla/enter_bug.cgi?product=POI

-----Original Message-----
From: Teresa Kim [mailto:[hidden email]]
Sent: Thursday, October 5, 2017 4:11 AM
To: POI Users List <[hidden email]>
Subject: characterRun.getSymbolChar() returns the same char for different symbols

Dear POI users

I got a doc document which contains uncommon greek mu and registered symbol and tried to use characterRun.getSymbolChar() method to identify these two symbols.
I have noticed however, characterRun.getSymbolChar() always returns the same character and such that I could not find a way to notice the different symbols.

I looked at the CharacterRun.java and tried to print out
_props.getXchSym() and found that this infact prints two different values for the greek mu and regiestered symbol, i.e. '-3987' and '3870'.

I really don't know if I am doing right thing in that I could use
_props.getXchSym() directly instead of using
characterRun.getSymbolChar() method which returns (char)_props.getXchSym().

To make it work, I added one method next to
characterRun.getSymbolChar()  in CharacterRun class that returns _props.getXchSym().
Would you please take a look at this and could see if this could be added into CharacterRun class?

I enclose a word document which contains those two symbols and a snippet for the new method I made to the CharacterRun class for your reference.



  public int getSymbolCharacterAsitis()
   {
     if (isSymbol()) {
         return _props.getXchSym();
     } else
       throw new IllegalStateException("Not a symbol CharacterRun");
   }


Many thanks in advance
Teresa


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]