You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Blake Watson <bl...@pnmac.com> on 2017/10/10 23:08:55 UTC
Conditional Formatting issue
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
blake.watson@pnmac.com
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Re: Conditional Formatting issue
Posted by Greg Woolsey <gr...@gmail.com>.
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 <bl...@pnmac.com> 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
Posted by Blake Watson <bl...@pnmac.com>.
>
>
> * 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
Posted by Greg Woolsey <gr...@gmail.com>.
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 <bl...@pnmac.com>
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
Posted by Blake Watson <bl...@pnmac.com>.
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
Posted by Greg Woolsey <gr...@gmail.com>.
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 <bl...@pnmac.com> 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 <bl...@pnmac.com>
> 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.watson@pnmac.com
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
Re: Conditional Formatting issue
Posted by Blake Watson <bl...@pnmac.com>.
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 <bl...@pnmac.com>
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.watson@pnmac.com
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Re: Conditional Formatting issue
Posted by Blake Watson <bl...@pnmac.com>.
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
Posted by Blake Watson <bl...@pnmac.com>.
I may not be, let me double-check.
On Fri, Oct 20, 2017 at 11:06 AM, Greg Woolsey <gr...@gmail.com>
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 <bl...@pnmac.com>
> 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-unsubscribe@poi.apache.org
> > For additional commands, e-mail: user-help@poi.apache.org
>
--
*Blake Watson*
*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.watson@pnmac.com
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Re: Conditional Formatting issue
Posted by Greg Woolsey <gr...@gmail.com>.
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 <bl...@pnmac.com> 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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
Re: Conditional Formatting issue
Posted by Blake Watson <bl...@pnmac.com>.
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
Re: Conditional Formatting issue
Posted by Blake Watson <bl...@pnmac.com>.
>
>
> 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
Posted by Nick Burch <ap...@gagravarr.org>.
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Conditional Formatting issue
Posted by Blake Watson <bl...@pnmac.com>.
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.
Re: Conditional Formatting issue
Posted by Blake Watson <bl...@pnmac.com>.
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
Posted by Greg Woolsey <gr...@gmail.com>.
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 <ap...@gagravarr.org> 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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Conditional Formatting issue
Posted by Nick Burch <ap...@gagravarr.org>.
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Conditional Formatting issue
Posted by Blake Watson <bl...@pnmac.com>.
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: CellUtil setting fonts not working?
Posted by Javen O'Neal <on...@apache.org>.
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" <ja...@gmail.com> 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
>
>
>
Re: CellUtil setting fonts not working?
Posted by Javen O'Neal <ja...@gmail.com>.
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
Re: CellUtil setting fonts not working?
Posted by Javen O'Neal <on...@apache.org>.
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 <
alexander.hoernlein@uni-wuerzburg.de> 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 • casetrain@uni-wuerzburg.de
>
> Rechenzentrum der Universität Würzburg • 1U10
> Mail hoernlein@uni-wuerzburg.de
> Tel. +49 931 31-*86738*
> Mobil +49 176 49887115
> WWW http://casetrain.uni-wuerzburg.de/hoernlein
> Map http://casetrain.uni-wuerzburg.de/map
>
Re: CellUtil setting fonts not working?
Posted by Alexander Hörnlein <al...@uni-wuerzburg.de>.
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 • casetrain@uni-wuerzburg.de
Rechenzentrum der Universität Würzburg • 1U10
Mail hoernlein@uni-wuerzburg.de
Tel. +49 931 31-*86738*
Mobil +49 176 49887115
WWW http://casetrain.uni-wuerzburg.de/hoernlein
Map http://casetrain.uni-wuerzburg.de/map
CellUtil setting fonts not working?
Posted by Alexander Hörnlein <al...@uni-wuerzburg.de>.
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
Re: Conditional Formatting issue
Posted by Greg Woolsey <gr...@gmail.com>.
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 <bl...@pnmac.com> wrote:
> I'll give it a shot!
>
> On Tue, Oct 10, 2017 at 11:48 PM, Nick Burch <ap...@gagravarr.org> 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-unsubscribe@poi.apache.org
> > For additional commands, e-mail: user-help@poi.apache.org
> >
> >
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742 <(805)%20330-4911>
> blake.watson@pnmac.com
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
Re: Conditional Formatting issue
Posted by Blake Watson <bl...@pnmac.com>.
I'll give it a shot!
On Tue, Oct 10, 2017 at 11:48 PM, Nick Burch <ap...@gagravarr.org> 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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
*Blake Watson*
*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.watson@pnmac.com
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Re: Conditional Formatting issue
Posted by Nick Burch <ap...@gagravarr.org>.
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org