Re: Conditional Formatting issue

[Greg Woolsey]

This is my busiest month of the year for my day job, but eventually I want
to look into the date code in POI, as I suspect there are some parts
handling this case explicitly and perhaps this path and a few others that
could benefit from some standardization around whatever that behavior is.

Unless someone beats me to it and submits a patch ;)

Greg

On Wed, Oct 25, 2017 at 10:34 AM Blake Watson 
wrote:

> ​
> ​Yeah, I feel like I should be able to create a simple example that doesn't
> use the conditional stuff so far but haven't been able to change it much
> without, uh, brekaing the brokenness. So I think there's something catching
> the issue in most cases.​
>

Re: Conditional Formatting issue

[Blake Watson]

​
​Yeah, I feel like I should be able to create a simple example that doesn't
use the conditional stuff so far but haven't been able to change it much
without, uh, brekaing the brokenness. So I think there's something catching
the issue in most cases.​

Re: Conditional Formatting issue

[Greg Woolsey]

Sounds like an input validation issue with EDate and other similar
functions to me.  With Conditional Formatting I tried to not swallow/ignore
errors in evaluation, exactly because I didn't want to hide bugs like this
appears to be.

Excel seems to assume a double of -1 equals a date of 0/0/1900, which of
course is invalid, but somehow Excel doesn't mind.  Not sure yet what POI
should do with that.  Open to suggestions.

On Mon, Oct 23, 2017 at 2:01 PM Blake Watson  wrote:

> Greg--
>
> >>most of these are long shots, as they only throw an unexpected NPE from
> the
> line you indicate when the input double is -Double.MIN_VALUE.  Probably not
> very helpful.<<
>
> This is exactly right. The error is in EDate.java which is calling
> calendar.setTime(-1).
>
> So, the workbook has a cell, Model!A4 which resolves to a -1 (but not null)
> date. But, when EData calls getJavaDate(-1), that returns "nil" which is
> then passed to calendar.setTime. I'm going to try to really simplify the
> example, which...maybe is only incidental to conditional formatting? Or
> there's something that the conditional formatting isn't catching that it
> should?
>
>
>
> On Mon, Oct 23, 2017 at 9:57 AM, Blake Watson 
> wrote:
>
> > OK: I was not doing it for my example, but adding it in didn't change
> > anything. I am creating a cell object, but I'm not changing any values
> (or
> > formats).
> >
> > I was able to open the file as well, after some fussing around. I think
> it
> > was just something I didn't get about FileInputStream. So I now have the
> > code in Java and with a stack trace. This produces the error. I'll put
> the
> > whole thing up in Bugzilla, then I'll go back and try to find the exact
> > cause.
> >
> > public class Main {
> > public static void main(String[] args) {
> > try {
> > Workbook wb = WorkbookFactory.create(new
> File("condfail.xlsx"));
> > Sheet sheet = (wb.getSheet("IRPPCalc"));
> > Row row = (sheet.getRow(11));
> > XSSFCell cell = (XSSFCell) row.getCell(18);
> > WorkbookEvaluatorProvider fe =
> (WorkbookEvaluatorProvider) wb.getCreationHelper().createFormulaEvaluator();
> > System.out.println("");
> > ConditionalFormattingEvaluator condfmt = new
> ConditionalFormattingEvaluator(wb, fe);
> > System.out.println("" +
> condfmt.getConditionalFormattingForCell(cell));
> > } catch(Exception e) {e.printStackTrace();};
> > }
> > }
> >
> >
> >
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742 <(805)%20330-4911>
> blake.wat...@pnmac.com
> www.PennyMacUSA.com 
>

Re: Conditional Formatting issue

[Blake Watson]

Greg--

>>most of these are long shots, as they only throw an unexpected NPE from
the
line you indicate when the input double is -Double.MIN_VALUE.  Probably not
very helpful.<<

This is exactly right. The error is in EDate.java which is calling
calendar.setTime(-1).

So, the workbook has a cell, Model!A4 which resolves to a -1 (but not null)
date. But, when EData calls getJavaDate(-1), that returns "nil" which is
then passed to calendar.setTime. I'm going to try to really simplify the
example, which...maybe is only incidental to conditional formatting? Or
there's something that the conditional formatting isn't catching that it
should?



On Mon, Oct 23, 2017 at 9:57 AM, Blake Watson 
wrote:

> OK: I was not doing it for my example, but adding it in didn't change
> anything. I am creating a cell object, but I'm not changing any values (or
> formats).
>
> I was able to open the file as well, after some fussing around. I think it
> was just something I didn't get about FileInputStream. So I now have the
> code in Java and with a stack trace. This produces the error. I'll put the
> whole thing up in Bugzilla, then I'll go back and try to find the exact
> cause.
>
> public class Main {
> public static void main(String[] args) {
> try {
> Workbook wb = WorkbookFactory.create(new 
> File("condfail.xlsx"));
> Sheet sheet = (wb.getSheet("IRPPCalc"));
> Row row = (sheet.getRow(11));
> XSSFCell cell = (XSSFCell) row.getCell(18);
> WorkbookEvaluatorProvider fe = (WorkbookEvaluatorProvider) 
> wb.getCreationHelper().createFormulaEvaluator();
> System.out.println("");
> ConditionalFormattingEvaluator condfmt = new 
> ConditionalFormattingEvaluator(wb, fe);
> System.out.println("" + 
> condfmt.getConditionalFormattingForCell(cell));
> } catch(Exception e) {e.printStackTrace();};
> }
> }
>
>
>


-- 

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.wat...@pnmac.com
www.PennyMacUSA.com 

Re: Conditional Formatting issue

[Blake Watson]

OK: I was not doing it for my example, but adding it in didn't change
anything. I am creating a cell object, but I'm not changing any values (or
formats).

I was able to open the file as well, after some fussing around. I think it
was just something I didn't get about FileInputStream. So I now have the
code in Java and with a stack trace. This produces the error. I'll put the
whole thing up in Bugzilla, then I'll go back and try to find the exact
cause.

public class Main {
public static void main(String[] args) {
try {
Workbook wb = WorkbookFactory.create(new File("condfail.xlsx"));
Sheet sheet = (wb.getSheet("IRPPCalc"));
Row row = (sheet.getRow(11));
XSSFCell cell = (XSSFCell) row.getCell(18);
WorkbookEvaluatorProvider fe =
(WorkbookEvaluatorProvider)
wb.getCreationHelper().createFormulaEvaluator();
System.out.println("");
ConditionalFormattingEvaluator condfmt = new
ConditionalFormattingEvaluator(wb, fe);
System.out.println("" +
condfmt.getConditionalFormattingForCell(cell));
} catch(Exception e) {e.printStackTrace();};
}
}

Re: Conditional Formatting issue

[Blake Watson]

I may not be, let me double-check.

On Fri, Oct 20, 2017 at 11:06 AM, Greg Woolsey 
wrote:

> I just tried opening the file you attached in the last message.  It opened
> fine for me, no uncaught exceptions.  When I opened it in Excel 2016, I
> noticed the date formatted cells apparently resolve to empty values, which
> are then treated as numeric 0 by Excel, and displayed as "1/0/1900".
>
> I didn't see any conditional formatting issues, in fact my processing
> appears to show them applying OK via POI.
>
> You mentioned in your first message, which I missed, that you are creating
> a new cell after parsing, in a range covered by an existing conditional
> formatting rule, then setting its value.
>
> The JavaDoc for ConditionalFormattingEvaluator notes you need to call
> clearAllCachedValues() whenever cell values change.  Are you doing this?
> That could be the cause of your error.
>
> On Thu, Oct 19, 2017 at 2:31 PM Blake Watson 
> wrote:
>
> > So, in working out the Java for this, I've somehow got myself into a
> > condition where I can't even open the workbook (in Java). When I step
> > through this:
> >
> > FileInputStream fis = new FileInputStream("condfail.xlsx");
> > Workbook wb = new XSSFWorkbook(fis);
> >
> > I got a NoClassDefFoundError.
> >
> > Exception in thread "main" java.lang.NoClassDefFoundError:
> > org/apache/xmlbeans/XmlObject
> > Disconnected from the target VM, address: '127.0.0.1:47092', transport:
> > 'socket'
> > at com.fidnip.Main.main(Main.java:22)
> > Caused by: java.lang.ClassNotFoundException:
> org.apache.xmlbeans.XmlObject
> > at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
> > at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
> > at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
> > at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
> > ... 1 more
> > ​
> >
> > -
> > To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
> > For additional commands, e-mail: user-h...@poi.apache.org
>



-- 

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.wat...@pnmac.com
www.PennyMacUSA.com 

Re: Conditional Formatting issue

[Greg Woolsey]

I just tried opening the file you attached in the last message.  It opened
fine for me, no uncaught exceptions.  When I opened it in Excel 2016, I
noticed the date formatted cells apparently resolve to empty values, which
are then treated as numeric 0 by Excel, and displayed as "1/0/1900".

I didn't see any conditional formatting issues, in fact my processing
appears to show them applying OK via POI.

You mentioned in your first message, which I missed, that you are creating
a new cell after parsing, in a range covered by an existing conditional
formatting rule, then setting its value.

The JavaDoc for ConditionalFormattingEvaluator notes you need to call
clearAllCachedValues() whenever cell values change.  Are you doing this?
That could be the cause of your error.

On Thu, Oct 19, 2017 at 2:31 PM Blake Watson  wrote:

> So, in working out the Java for this, I've somehow got myself into a
> condition where I can't even open the workbook (in Java). When I step
> through this:
>
> FileInputStream fis = new FileInputStream("condfail.xlsx");
> Workbook wb = new XSSFWorkbook(fis);
>
> I got a NoClassDefFoundError.
>
> Exception in thread "main" java.lang.NoClassDefFoundError:
> org/apache/xmlbeans/XmlObject
> Disconnected from the target VM, address: '127.0.0.1:47092', transport:
> 'socket'
> at com.fidnip.Main.main(Main.java:22)
> Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject
> at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
> at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
> ... 1 more
> ​
>
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org

Re: Conditional Formatting issue

[Blake Watson]

So, in working out the Java for this, I've somehow got myself into a
condition where I can't even open the workbook (in Java). When I step
through this:

FileInputStream fis = new FileInputStream("condfail.xlsx");
Workbook wb = new XSSFWorkbook(fis);

I got a NoClassDefFoundError.

Exception in thread "main" java.lang.NoClassDefFoundError:
org/apache/xmlbeans/XmlObject
Disconnected from the target VM, address: '127.0.0.1:47092', transport:
'socket'
at com.fidnip.Main.main(Main.java:22)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
... 1 more
​


condfail.xlsx
Description: MS-Excel 2007 spreadsheet

-
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org

Re: Conditional Formatting issue

[Blake Watson]

>
>
> Could you open a bug in bugzilla, upload the file, and a snippet of code
> needed to reproduce the error? It's much less likely to get lost /
> forgotten on bugzilla than email!
>

​I'm tryin', Nick! I'm not really a Java guy so it's taking me a while to
get set up.​

Re: Conditional Formatting issue

[Nick Burch]

On Wed, 18 Oct 2017, Blake Watson wrote:
I've attached a greatly reduced version of the spreadsheet. About 15 
cells with one conditional. I've tried to reduce it further but can't 
seem to do it without altering the error. Actually, that might be 
important. Most of my tweaks, if they don't fix the problem, result in:


NullPointerException
org.apache.poi.ss.formula.ConditionalFormattingEvaluator.getRef
(ConditionalFormattingEvaluator.java:210)


Could you open a bug in bugzilla, upload the file, and a snippet of code 
needed to reproduce the error? It's much less likely to get lost / 
forgotten on bugzilla than email!


Nick

-
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org

Re: Conditional Formatting issue

[Blake Watson]

>
>
>  * Ensure you're on a version of Eclipse that supports Java 8
>  * Checkout from svn / git
>  * On the command line, do "ant compile" to have dependencies fetched
>  * In Eclipse, do Import -> General -> Existing Project into Workspace
>  * Point it at your checkout
>  * Wait for the build to finish
>  * Right click on a unit test and do Run As -> JUnit Test
>

​Almost made it.​ The build results in 37 errors, 693 warnings, "858
others". OK, I need Java version 1.8 (I have 1.8, didn't think I anything
lower than 1.8) but I clicked on the Eclipse fix and now it works! Heyo!
Just a few warning about resource leaks...

Hmmm. I don't see any source for the conditional stuff.

Re: Conditional Formatting issue

[Blake Watson]

I've attached a greatly reduced version of the spreadsheet. About 15 cells
with one conditional. I've tried to reduce it further but can't seem to do
it without altering the error. Actually, that might be important. Most of
my tweaks, if they don't fix the problem, result in:

NullPointerException
 org.apache.poi.ss.formula.ConditionalFormattingEvaluator.getRef
(ConditionalFormattingEvaluator.java:210)
​
Like, if you delete the S column, which actually removes the Conditional,
theoretically, you'll get this error.


RPPCalculator4.xlsx
Description: MS-Excel 2007 spreadsheet

-
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org

Re: Conditional Formatting issue

[Blake Watson]

I'll try the tests thing following Nick's steps. I'm sure I didn't do that
in that order.

As far as the conditionals go, I've got the spreadsheet down to two pages.
The error occurs on the Page A—but it goes away if I eliminate Page B.

I don't have the full stack trace at the moment.(I can't remember if I ever
got more than the Calendar.java single line.)

>>most of these are long shots, as they only throw an unexpected NPE from
the
>>line you indicate when the input double is -Double.MIN_VALUE.  Probably
not
>>very helpful.

Maybe! I've got a formatting rule of

=AND($N11>=EDATE($D$6,3),$S11>0)

And I note that D6 is null, which wouldn't be very interesting but it's
referenced on Page B.

===Blake===

Re: Conditional Formatting issue

[Greg Woolsey]

Also, to run all the tests, I use the Ant tasks in build.xml.  Selecting a
package and trying to run all tests in Eclipse has trouble in some cases
because of the customized classpaths needed.  It is possible to build a
test that runs in Eclipse but fails in the build because Eclipse puts more
stuff on the classpath by default than the build does.  In particular,
referencing OOXML classes in a common test package can pass in Eclipse, but
will fail at build time and when run via Ant.

On Wed, Oct 18, 2017 at 11:22 AM Nick Burch  wrote:

> On Wed, 18 Oct 2017, Blake Watson wrote:
> > Related: I downloaded Eclipse and POI to build a test case, but I'm kind
> > of at a loss. I haven't been able to run the tests from Eclipse.
>
> The steps ought to be:
>   * Ensure you're on a version of Eclipse that supports Java 8
>   * Checkout from svn / git
>   * On the command line, do "ant compile" to have dependencies fetched
>   * In Eclipse, do Import -> General -> Existing Project into Workspace
>   * Point it at your checkout
>   * Wait for the build to finish
>   * Right click on a unit test and do Run As -> JUnit Test
>
> Nick
>
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
>

Re: Conditional Formatting issue

[Nick Burch]

On Wed, 18 Oct 2017, Blake Watson wrote:
Related: I downloaded Eclipse and POI to build a test case, but I'm kind 
of at a loss. I haven't been able to run the tests from Eclipse.


The steps ought to be:
 * Ensure you're on a version of Eclipse that supports Java 8
 * Checkout from svn / git
 * On the command line, do "ant compile" to have dependencies fetched
 * In Eclipse, do Import -> General -> Existing Project into Workspace
 * Point it at your checkout
 * Wait for the build to finish
 * Right click on a unit test and do Run As -> JUnit Test

Nick

-
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org

Re: Conditional Formatting issue

[Greg Woolsey]

It took me the better part of a week to get tests running the first time I
set it up.  And I thought I was good with Ant and Eclipse, and passable
with Maven. Just a lot of moving parts.

Do you have a full stack trace for the error?  Seeing where it comes from
may help me dig into it.

I checked all the POI calls to Calendar.setTime(Date) that could pass a
null Date and trigger that NPE, and came up with these possibilities.

most of these are long shots, as they only throw an unexpected NPE from the
line you indicate when the input double is -Double.MIN_VALUE.  Probably not
very helpful.

* WorkdayCalculator.calculateWorkdays(...) -- used by various functions
* WorkdayCalculator.isWeekend(...) -- used by various functions
* WorkdayCalculator.pastDaysOfWeek(...) -- used by various functions
* These formula function implementations don't check for null/invalid
dates, can end up throwing this NPE
** WeekNum.evaluate()
** EDate.evaluate()
** EOMonth.evaluate()


On Wed, Oct 18, 2017 at 9:48 AM Blake Watson  wrote:

> I'm using 3.17 and working on a simplest possible case. There's something
> about this particular sheet that seems to be causing an issue and I haven't
> figured out what yet. It's on a sheet with four conditional aspects in toto
> and two of the formats are white-on-white (basically making text
> invisible), but it's not particularly large or tricky. The formatting is
> all based on values on the same sheet...
>
> Related: I downloaded Eclipse and POI to build a test case, but I'm kind of
> at a loss. I haven't been able to run the tests from Eclipse.
>

Re: Conditional Formatting issue

[Blake Watson]

I'm using 3.17 and working on a simplest possible case. There's something
about this particular sheet that seems to be causing an issue and I haven't
figured out what yet. It's on a sheet with four conditional aspects in toto
and two of the formats are white-on-white (basically making text
invisible), but it's not particularly large or tricky. The formatting is
all based on values on the same sheet...

Related: I downloaded Eclipse and POI to build a test case, but I'm kind of
at a loss. I haven't been able to run the tests from Eclipse.

Re: Conditional Formatting issue

[Greg Woolsey]

Catching up after vacation, coming in late to this.  What POI release is
this?  I had a bug when shifting references for conditions that applied to
ranges of cells at one point.  That should be fixed in 3.17, was probably
present in 3.16 and 3.15.

On Wed, Oct 11, 2017 at 8:40 AM Blake Watson  wrote:

> I'll give it a shot!
>
> On Tue, Oct 10, 2017 at 11:48 PM, Nick Burch  wrote:
>
> > On Tue, 10 Oct 2017, Blake Watson wrote:
> >
> >> I'm trying to create a simplest example of this but I have a situation
> >> where I:
> >>
> >> 1. Load a workbook with a conditional.
> >> 2. Create a FormulaEvaluator for that workbook.
> >> 3. Create a ConditionalFormattingEvaluator for that workbook and
> >> evaluator.
> >> 4. Create a Cell for a cell in the workbook that has formatting.
> >> 5. Call getConditionalFormattingForCell for that CFE made in #3 and the
> >> cell made in #4.
> >> 6. POI returns a "NullPointerException java.utilCalendar.setTime
> >> (Calendar.java:1770)
> >>
> >
> > Can you turn this into a junit unit test? If so, please upload it to
> > bugzilla and we'll step through it with a debugger to see where the POI
> bug
> > is! We'll also then have a unit test to confirm it's fixed + stays fixed
> in
> > the future :)
> >
> > Nick
> >
> > -
> > To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
> > For additional commands, e-mail: user-h...@poi.apache.org
> >
> >
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742 <(805)%20330-4911>
> blake.wat...@pnmac.com
> www.PennyMacUSA.com 
>

Re: Conditional Formatting issue

[Blake Watson]

I'll give it a shot!

On Tue, Oct 10, 2017 at 11:48 PM, Nick Burch  wrote:

> On Tue, 10 Oct 2017, Blake Watson wrote:
>
>> I'm trying to create a simplest example of this but I have a situation
>> where I:
>>
>> 1. Load a workbook with a conditional.
>> 2. Create a FormulaEvaluator for that workbook.
>> 3. Create a ConditionalFormattingEvaluator for that workbook and
>> evaluator.
>> 4. Create a Cell for a cell in the workbook that has formatting.
>> 5. Call getConditionalFormattingForCell for that CFE made in #3 and the
>> cell made in #4.
>> 6. POI returns a "NullPointerException java.utilCalendar.setTime
>> (Calendar.java:1770)
>>
>
> Can you turn this into a junit unit test? If so, please upload it to
> bugzilla and we'll step through it with a debugger to see where the POI bug
> is! We'll also then have a unit test to confirm it's fixed + stays fixed in
> the future :)
>
> Nick
>
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
>


-- 

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.wat...@pnmac.com
www.PennyMacUSA.com 

Re: Conditional Formatting issue

[Nick Burch]

On Tue, 10 Oct 2017, Blake Watson wrote:

I'm trying to create a simplest example of this but I have a situation
where I:

1. Load a workbook with a conditional.
2. Create a FormulaEvaluator for that workbook.
3. Create a ConditionalFormattingEvaluator for that workbook and evaluator.
4. Create a Cell for a cell in the workbook that has formatting.
5. Call getConditionalFormattingForCell for that CFE made in #3 and the
cell made in #4.
6. POI returns a "NullPointerException java.utilCalendar.setTime
(Calendar.java:1770)


Can you turn this into a junit unit test? If so, please upload it to 
bugzilla and we'll step through it with a debugger to see where the POI 
bug is! We'll also then have a unit test to confirm it's fixed + stays 
fixed in the future :)


Nick

-
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org