Conditional Formatting issue

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

Conditional Formatting issue

Blake Watson
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)

I can't figure out how 6 could be happening, but I have figured out WHEN it
happens. This particular sheet has four conditional formatting rules. They
apply to ranges on the sheet, but whenever I try to pull the conditional
for a cell in the range not in the first row, the error in 6 occurs.

For example, one range applies to =$S$11:$S$34 and if I pull the
conditional for S11, no problem. If I pull for S12, I get the error in 6.
Another example range would be =$L$11:$S$34, and I can pull for L11, M11,
N11, all the way to S11, with no problem. But if I do L12, M12, L13,
whatever...that's when the Calendar.java error pops up.

The conditions in place for some of the ranges are ultra-simple:

=$O11=0
=$E11=0

I did find some date-based conditionals (the only thing I could think that
would cause the error) so I removed those and I'm still getting the errors.

Any thoughts?
--

*Blake Watson*

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

Re: Conditional Formatting issue

Nick Burch-2
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Conditional Formatting issue

Blake Watson
I'll give it a shot!

On Tue, Oct 10, 2017 at 11:48 PM, Nick Burch <[hidden email]> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


--

*Blake Watson*

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

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 <[hidden email]> wrote:

> I'll give it a shot!
>
> On Tue, Oct 10, 2017 at 11:48 PM, Nick Burch <[hidden email]> 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: [hidden email]
> > For additional commands, e-mail: [hidden email]
> >
> >
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742 <(805)%20330-4911>
> [hidden email]
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
Reply | Threaded
Open this post in threaded view
|

CellUtil setting fonts not working?

Alexander Hörnlein-2

Hi all,

this is my "minimal" code:

final Workbook wb = new XSSFWorkbook();
final Sheet s = wb.createSheet();
final Row r = s.createRow(0);
final Cell c1 = r.createCell(0);
c1.setCellValue("foo");
final Cell c2 = r.createCell(1);
c2.setCellValue("bar");
final Cell c3 = r.createCell(2);
c3.setCellValue("baz");
final Cell c4 = r.createCell(3);
c4.setCellValue("bat");
final Font f = wb.createFont();
f.setBold(true);
{// 1
   final Map<String, Object> props = new HashMap<>();
   props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT);
   props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);
   props.put(CellUtil.FONT, f);
   CellUtil.setCellStyleProperties(c1, props);
}
{ // 2
   CellUtil.setCellStyleProperty(c2, CellUtil.ALIGNMENT,
HorizontalAlignment.RIGHT);
   CellUtil.setCellStyleProperty(c2, CellUtil.BORDER_BOTTOM,
BorderStyle.DOUBLE);
   CellUtil.setCellStyleProperty(c2, CellUtil.FONT, f);
}
{ // 3
   final CellStyle cs = wb.createCellStyle();
   cs.setAlignment(HorizontalAlignment.RIGHT);
cs.setBorderBottom(BorderStyle.DOUBLE);
cs.setFont(f);
   c3.setCellStyle(cs);
}
{ // 4
   {
     final Map<String, Object> props = new HashMap<>();
props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT);
     CellUtil.setCellStyleProperties(c4, props);
   }
   {
     final Map<String, Object> props = new HashMap<>();
     props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);
     CellUtil.setCellStyleProperties(c4, props);
   }
{
     final Map<String, Object> props = new HashMap<>();
     props.put(CellUtil.FONT, f);
     CellUtil.setCellStyleProperties(c4, props);
   }
}
final OutputStream os = new FileOutputStream(new
File("D:\\_scratch\\exceloutput_test\\res_" + System.currentTimeMillis()
+ ".xlsx"));
wb.write(os);
os.close();
wb.close();

In 1 one (bis) setCellStyleProperties is used, in 2 three
setCellStyleProperty calls are used, in 3 a CellStyle is created and in
4 each part of the style is set with one setCellStyleProperties.

What I /should/ get: All cells are right aligned with border on top and
bold.

What I get is foo, bar, baz, bat are right aligned with a border on top,
but only baz is bold.

POI 3.17 is used.

Is this a bug or am I doing something wrong here?

cheers
Alexander

Reply | Threaded
Open this post in threaded view
|

Re: CellUtil setting fonts not working?

Javen O'Neal
Reading your code, I would expect foo, bar, and baz would be bold and bat
should not be bold. Are you getting the opposite of this?

On Oct 18, 2017 05:39, "Alexander Hörnlein" <
[hidden email]> wrote:


Hi all,

this is my "minimal" code:

final Workbook wb = new XSSFWorkbook();
final Sheet s = wb.createSheet();
final Row r = s.createRow(0);
final Cell c1 = r.createCell(0);
c1.setCellValue("foo");
final Cell c2 = r.createCell(1);
c2.setCellValue("bar");
final Cell c3 = r.createCell(2);
c3.setCellValue("baz");
final Cell c4 = r.createCell(3);
c4.setCellValue("bat");
final Font f = wb.createFont();
f.setBold(true);
{// 1
  final Map<String, Object> props = new HashMap<>();
  props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT);
  props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);
  props.put(CellUtil.FONT, f);
  CellUtil.setCellStyleProperties(c1, props);
}
{ // 2
  CellUtil.setCellStyleProperty(c2, CellUtil.ALIGNMENT,
HorizontalAlignment.RIGHT);
  CellUtil.setCellStyleProperty(c2, CellUtil.BORDER_BOTTOM,
BorderStyle.DOUBLE);
  CellUtil.setCellStyleProperty(c2, CellUtil.FONT, f);
}
{ // 3
  final CellStyle cs = wb.createCellStyle();
  cs.setAlignment(HorizontalAlignment.RIGHT);
cs.setBorderBottom(BorderStyle.DOUBLE);
cs.setFont(f);
  c3.setCellStyle(cs);
}
{ // 4
  {
    final Map<String, Object> props = new HashMap<>();
props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT);
    CellUtil.setCellStyleProperties(c4, props);
  }
  {
    final Map<String, Object> props = new HashMap<>();
    props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);
    CellUtil.setCellStyleProperties(c4, props);
  }
{
    final Map<String, Object> props = new HashMap<>();
    props.put(CellUtil.FONT, f);
    CellUtil.setCellStyleProperties(c4, props);
  }
}
final OutputStream os = new FileOutputStream(new
File("D:\\_scratch\\exceloutput_test\\res_" + System.currentTimeMillis() +
".xlsx"));
wb.write(os);
os.close();
wb.close();

In 1 one (bis) setCellStyleProperties is used, in 2 three
setCellStyleProperty calls are used, in 3 a CellStyle is created and in 4
each part of the style is set with one setCellStyleProperties.

What I /should/ get: All cells are right aligned with border on top and
bold.

What I get is foo, bar, baz, bat are right aligned with a border on top,
but only baz is bold.

POI 3.17 is used.

Is this a bug or am I doing something wrong here?

cheers
Alexander
Reply | Threaded
Open this post in threaded view
|

Re: CellUtil setting fonts not working?

Javen O'Neal-2
Whoops. I missed a line of code. I expect that all 4 cells are bold. I'll
test this out later today to see what's up.

On Oct 18, 2017 06:34, "Javen O'Neal" <[hidden email]> wrote:

> Reading your code, I would expect foo, bar, and baz would be bold and bat
> should not be bold. Are you getting the opposite of this?
>
> On Oct 18, 2017 05:39, "Alexander Hörnlein" <alexander.hoernlein@uni-
> wuerzburg.de> wrote:
>
>
> Hi all,
>
> this is my "minimal" code:
>
> final Workbook wb = new XSSFWorkbook();
> final Sheet s = wb.createSheet();
> final Row r = s.createRow(0);
> final Cell c1 = r.createCell(0);
> c1.setCellValue("foo");
> final Cell c2 = r.createCell(1);
> c2.setCellValue("bar");
> final Cell c3 = r.createCell(2);
> c3.setCellValue("baz");
> final Cell c4 = r.createCell(3);
> c4.setCellValue("bat");
> final Font f = wb.createFont();
> f.setBold(true);
> {// 1
>   final Map<String, Object> props = new HashMap<>();
>   props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT);
>   props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);
>   props.put(CellUtil.FONT, f);
>   CellUtil.setCellStyleProperties(c1, props);
> }
> { // 2
>   CellUtil.setCellStyleProperty(c2, CellUtil.ALIGNMENT,
> HorizontalAlignment.RIGHT);
>   CellUtil.setCellStyleProperty(c2, CellUtil.BORDER_BOTTOM,
> BorderStyle.DOUBLE);
>   CellUtil.setCellStyleProperty(c2, CellUtil.FONT, f);
> }
> { // 3
>   final CellStyle cs = wb.createCellStyle();
>   cs.setAlignment(HorizontalAlignment.RIGHT);
> cs.setBorderBottom(BorderStyle.DOUBLE);
> cs.setFont(f);
>   c3.setCellStyle(cs);
> }
> { // 4
>   {
>     final Map<String, Object> props = new HashMap<>();
> props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT);
>     CellUtil.setCellStyleProperties(c4, props);
>   }
>   {
>     final Map<String, Object> props = new HashMap<>();
>     props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);
>     CellUtil.setCellStyleProperties(c4, props);
>   }
> {
>     final Map<String, Object> props = new HashMap<>();
>     props.put(CellUtil.FONT, f);
>     CellUtil.setCellStyleProperties(c4, props);
>   }
> }
> final OutputStream os = new FileOutputStream(new
> File("D:\\_scratch\\exceloutput_test\\res_" + System.currentTimeMillis()
> + ".xlsx"));
> wb.write(os);
> os.close();
> wb.close();
>
> In 1 one (bis) setCellStyleProperties is used, in 2 three
> setCellStyleProperty calls are used, in 3 a CellStyle is created and in 4
> each part of the style is set with one setCellStyleProperties.
>
> What I /should/ get: All cells are right aligned with border on top and
> bold.
>
> What I get is foo, bar, baz, bat are right aligned with a border on top,
> but only baz is bold.
>
> POI 3.17 is used.
>
> Is this a bug or am I doing something wrong here?
>
> cheers
> Alexander
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: CellUtil setting fonts not working?

Alexander Hörnlein-2
In reply to this post by Alexander Hörnlein-2

Hi,

I found out how to make them all bold. Instead of

props.put(CellUtil.FONT, f);

or

CellUtil.setCellStyleProperty(c, CellUtil.FONT, f);

I had to use

... CellUtil.Font, Short.valueOf(f.getIndex()));

This is similar to bug #49254
(https://bz.apache.org/bugzilla/show_bug.cgi?id=49254) where they use

CellUtil.setCellStyleProperty(cell, workbook, CellUtil.FONT,
Short.valueOf(fontIndex));

as a workaround.



Am 18.10.2017 um 14:39 schrieb Alexander Hörnlein:

>
> Hi all,
>
> this is my "minimal" code:
>
> final Workbook wb = new XSSFWorkbook();
> final Sheet s = wb.createSheet();
> final Row r = s.createRow(0);
> final Cell c1 = r.createCell(0);
> c1.setCellValue("foo");
> final Cell c2 = r.createCell(1);
> c2.setCellValue("bar");
> final Cell c3 = r.createCell(2);
> c3.setCellValue("baz");
> final Cell c4 = r.createCell(3);
> c4.setCellValue("bat");
> final Font f = wb.createFont();
> f.setBold(true);
> {// 1
>   final Map<String, Object> props = new HashMap<>();
>   props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT);
>   props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);
>   props.put(CellUtil.FONT, f);
>   CellUtil.setCellStyleProperties(c1, props);
> }
> { // 2
>   CellUtil.setCellStyleProperty(c2, CellUtil.ALIGNMENT,
> HorizontalAlignment.RIGHT);
>   CellUtil.setCellStyleProperty(c2, CellUtil.BORDER_BOTTOM,
> BorderStyle.DOUBLE);
>   CellUtil.setCellStyleProperty(c2, CellUtil.FONT, f);
> }
> { // 3
>   final CellStyle cs = wb.createCellStyle();
>   cs.setAlignment(HorizontalAlignment.RIGHT);
> cs.setBorderBottom(BorderStyle.DOUBLE);
> cs.setFont(f);
>   c3.setCellStyle(cs);
> }
> { // 4
>   {
>     final Map<String, Object> props = new HashMap<>();
> props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT);
>     CellUtil.setCellStyleProperties(c4, props);
>   }
>   {
>     final Map<String, Object> props = new HashMap<>();
>     props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);
>     CellUtil.setCellStyleProperties(c4, props);
>   }
> {
>     final Map<String, Object> props = new HashMap<>();
>     props.put(CellUtil.FONT, f);
>     CellUtil.setCellStyleProperties(c4, props);
>   }
> }
> final OutputStream os = new FileOutputStream(new
> File("D:\\_scratch\\exceloutput_test\\res_" +
> System.currentTimeMillis() + ".xlsx"));
> wb.write(os);
> os.close();
> wb.close();
>
> In 1 one (bis) setCellStyleProperties is used, in 2 three
> setCellStyleProperty calls are used, in 3 a CellStyle is created and
> in 4 each part of the style is set with one setCellStyleProperties.
>
> What I /should/ get: All cells are right aligned with border on top
> and bold.
>
> What I get is foo, bar, baz, bat are right aligned with a border on
> top, but only baz is bold.
>
> POI 3.17 is used.
>
> Is this a bug or am I doing something wrong here?
>
> cheers
> Alexander
>
>

--
Beste Grüße
Alexander Hörnlein

--
*CaseTrain* • Fakultätsübergreifendes Blended Learning Projekt •
http://casetrain.uni-wuerzburg.de • [hidden email]

Rechenzentrum der Universität Würzburg • 1U10
Mail  [hidden email]
Tel.  +49 931 31-*86738*
Mobil +49 176 49887115
WWW   http://casetrain.uni-wuerzburg.de/hoernlein
Map   http://casetrain.uni-wuerzburg.de/map
Reply | Threaded
Open this post in threaded view
|

Re: CellUtil setting fonts not working?

Javen O'Neal-2
Similarly, if you want to set a foreground or background color, it must be
an indexed color.
bug: https://bz.apache.org/bugzilla/show_bug.cgi?id=59442

And conditional formatting:
https://bz.apache.org/bugzilla/show_bug.cgi?id=60895

Those are the only 2 open bugs for CellUtil right now.

Mark Murphy, one of the devs, and I are working on expanding a similar
utility to be able to efficiently change cell styles without creating as
many intermediate cell styles. See bugs 58787 and 54593.

You could also use CellUtil.setFont, which will search your workbook for
cell styles that match the cell's current style with the desired font. If
no such cell style exists, it will create a new cell style. This is
significantly better than creating a new cell style for every cell.
However, if you making multiple modifications to a cell and don't use
CellUtil.setCellStyleProperties to set all the properties at once, then
some unnecessary styles may be created. If it becomes an issue (most likely
running out of RAM due to creating too many styles), you could probably
defragment and remove unused styles.

Consider the scenario:
Workbook wb = new XSSFWorkbook();
Cell cell = wb.createSheet().createRow(0).createCell(0);
//wb has 1 style, the default cell style
XSSFFont font = new XSSFFont();
font.setBold(true);
CellUtil.setFont(cell, font);
// now the workbook has 2 styles: default and default+boldfont
CellUtil.setAlignment(cell, HorizontalAlignment.RIGHT);
// now the workbook has 3 styles: default, default+boldfont,
default+boldfont+rightalignment. The default+boldfont is no longer
referenced and should be removed from the workbook.

Anyway, sounds like you have it figured out. Let us know if you have any
more questions.

On Wed, Oct 18, 2017 at 7:37 AM, Alexander Hörnlein <
[hidden email]> wrote:

>
> Hi,
>
> I found out how to make them all bold. Instead of
>
> props.put(CellUtil.FONT, f);
>
> or
>
> CellUtil.setCellStyleProperty(c, CellUtil.FONT, f);
>
> I had to use
>
> ... CellUtil.Font, Short.valueOf(f.getIndex()));
>
> This is similar to bug #49254 (https://bz.apache.org/bugzill
> a/show_bug.cgi?id=49254) where they use
>
> CellUtil.setCellStyleProperty(cell, workbook, CellUtil.FONT,
> Short.valueOf(fontIndex));
>
> as a workaround.
>
>
>
>
> Am 18.10.2017 um 14:39 schrieb Alexander Hörnlein:
>
>>
>> Hi all,
>>
>> this is my "minimal" code:
>>
>> final Workbook wb = new XSSFWorkbook();
>> final Sheet s = wb.createSheet();
>> final Row r = s.createRow(0);
>> final Cell c1 = r.createCell(0);
>> c1.setCellValue("foo");
>> final Cell c2 = r.createCell(1);
>> c2.setCellValue("bar");
>> final Cell c3 = r.createCell(2);
>> c3.setCellValue("baz");
>> final Cell c4 = r.createCell(3);
>> c4.setCellValue("bat");
>> final Font f = wb.createFont();
>> f.setBold(true);
>> {// 1
>>   final Map<String, Object> props = new HashMap<>();
>>   props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT);
>>   props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);
>>   props.put(CellUtil.FONT, f);
>>   CellUtil.setCellStyleProperties(c1, props);
>> }
>> { // 2
>>   CellUtil.setCellStyleProperty(c2, CellUtil.ALIGNMENT,
>> HorizontalAlignment.RIGHT);
>>   CellUtil.setCellStyleProperty(c2, CellUtil.BORDER_BOTTOM,
>> BorderStyle.DOUBLE);
>>   CellUtil.setCellStyleProperty(c2, CellUtil.FONT, f);
>> }
>> { // 3
>>   final CellStyle cs = wb.createCellStyle();
>>   cs.setAlignment(HorizontalAlignment.RIGHT);
>> cs.setBorderBottom(BorderStyle.DOUBLE);
>> cs.setFont(f);
>>   c3.setCellStyle(cs);
>> }
>> { // 4
>>   {
>>     final Map<String, Object> props = new HashMap<>();
>> props.put(CellUtil.ALIGNMENT, HorizontalAlignment.RIGHT);
>>     CellUtil.setCellStyleProperties(c4, props);
>>   }
>>   {
>>     final Map<String, Object> props = new HashMap<>();
>>     props.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);
>>     CellUtil.setCellStyleProperties(c4, props);
>>   }
>> {
>>     final Map<String, Object> props = new HashMap<>();
>>     props.put(CellUtil.FONT, f);
>>     CellUtil.setCellStyleProperties(c4, props);
>>   }
>> }
>> final OutputStream os = new FileOutputStream(new
>> File("D:\\_scratch\\exceloutput_test\\res_" + System.currentTimeMillis()
>> + ".xlsx"));
>> wb.write(os);
>> os.close();
>> wb.close();
>>
>> In 1 one (bis) setCellStyleProperties is used, in 2 three
>> setCellStyleProperty calls are used, in 3 a CellStyle is created and in 4
>> each part of the style is set with one setCellStyleProperties.
>>
>> What I /should/ get: All cells are right aligned with border on top and
>> bold.
>>
>> What I get is foo, bar, baz, bat are right aligned with a border on top,
>> but only baz is bold.
>>
>> POI 3.17 is used.
>>
>> Is this a bug or am I doing something wrong here?
>>
>> cheers
>> Alexander
>>
>>
>>
> --
> Beste Grüße
> Alexander Hörnlein
>
> --
> *CaseTrain* • Fakultätsübergreifendes Blended Learning Projekt •
> http://casetrain.uni-wuerzburg.de • [hidden email]
>
> Rechenzentrum der Universität Würzburg • 1U10
> Mail  [hidden email]
> Tel.  +49 931 31-*86738*
> Mobil +49 176 49887115
> WWW   http://casetrain.uni-wuerzburg.de/hoernlein
> Map   http://casetrain.uni-wuerzburg.de/map
>
Reply | Threaded
Open this post in threaded view
|

Re: Conditional Formatting issue

Blake Watson
In reply to this post by Greg Woolsey
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.
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]> 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.
>
Reply | Threaded
Open this post in threaded view
|

Re: Conditional Formatting issue

Nick Burch-2
In reply to this post by Blake Watson
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

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

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===
Reply | Threaded
Open this post in threaded view
|

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.



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

RPPCalculator4.xlsx (23K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Conditional Formatting issue

Blake Watson
In reply to this post by Nick Burch-2
>
>
>  * 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.
Reply | Threaded
Open this post in threaded view
|

Re: Conditional Formatting issue

Nick Burch-2
In reply to this post by Blake Watson
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

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.​
Reply | Threaded
Open this post in threaded view
|

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


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

condfail.xlsx (23K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
12