RE: Please Help! Change Axis scaling without using 'setMajorUnit' from XDDFValueAxis

[Murphy, Mark]

No, that's new.

-Original Message-
From: ranzhang35 [mailto:rzh...@mkprod.com] 
Sent: Friday, October 05, 2018 11:53 AM
To: user@poi.apache.org
Subject: Please Help! Change Axis scaling without using 'setMajorUnit' from 
XDDFValueAxis

*Is there similar API from XSSFValueAxis class as the 'setMajorUnit' in 
'XDDFValueAxis' class?*  

XDDFValueAxis class is only available in POI 4.0.0. 

I need to change the scaling of axis using POI version prior to 4.0.0. 



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

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


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

RE: Urgent: using POI 3.17, how to change X-Axis scaling using xssf chart

[Murphy, Mark]

If it is really that urgent, why don't you look at how POI 4.0.0 does it, and 
try to work that into POI 3.17. Surely you can do that faster than someone here 
since we all work on this in our own free time, and it appears to be what you 
are working on at work right now. I'll give you a hint, you are going to have 
to dig down into the CT classes, and apply the correct ones in the correct 
order. You can find documentation on the spec at 
https://www.ecma-international.org/publications/standards/Ecma-376.htm

Be sure to look at the 1st edition spec as that is the one I think we are 
compiling at v3.17 (maybe we upgraded a bit in 4.0.0)

-Original Message-
From: Bob Cochran [mailto:r2cochr...@gmail.com] 
Sent: Thursday, October 04, 2018 11:41 AM
To: POI Users List 
Subject: Re: Urgent: using POI 3.17, how to change X-Axis scaling using xssf 
chart

It would be better for you to stop using the word “urgent” in your subject 
line. The fact is, your rush is not an emergency for mailing list readers. I 
consider it to have a bullying flavor. 

Why not use a little politeness instead of attempting to rush others for your 
personal purposes?

Thanks

Bob


> On Oct 4, 2018, at 11:34 AM, ranzhang35  wrote:
> 
> if there are 100 data points for X-axis, instead of showing 100 tick 
> marks, I just want to show 10 tick marks and have labels (10. 20. 30…) 
> under each tick mark.
> 
> can someone tell me how to do it in POI 3.17? 
> 
> I think in POI 4.0.0, there is xddf chart type which allows set the 
> scaling of axis, but xddf isn't available in POI 3.17.
> 
> 
> 
> --
> Sent from: 
> http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
> 
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional 
> commands, e-mail: user-h...@poi.apache.org
> 

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

RE: General Support for Percentage Values?

[Murphy, Mark]

Which version of the spec are you reading? We are using Version 1. Though we 
sometimes deal with later version artifacts, the CT and ST classes are all 
version 1.

-Original Message-
From: Eliot Kimber [mailto:ekim...@contrext.com] 
Sent: Thursday, August 16, 2018 11:00 AM
To: POI Users List 
Subject: Re: General Support for Percentage Values?

I'm reading section 17.4.88 Table Measurement (CT_TblWidth), which shows this 
example:

<… w:type="pct" w:w="100%"/>

And which refers to 17.18.107 ST_MeasurementOrPercent (Measurement or 
Percentage Value) as the definition of the @w:w attribute.

It says it's a union of two types: ST_DecimalNumberOrPercent and 
ST_UniversalMeasure.  ST_DecimalNumberOrPercent then says it's a union of 
ST_Percentage, 22.9.2.9 ST_Percentage (Percentage Value with Sign), which has 
this example:



I tried some experiements and if I modify the XML for my table to e.g.:



And open it, Word correctly reflects the 50% width BUT when I save from Word 
the @w:w value is set to 2500 (50 * 50).

So clearly Word prefers to store things as integers, which makes sense, but it 
will read the % string value as defined in the spec.

I'm curious--what part of the spec are you reading that indicates that value is 
always an integer? I'm not doubting that it says that, but I didn't get to that 
part of the spec starting from the definitions for table width.

But this does suggest that my code that lets you set and get the table width 
(and the table cell widths, as you indicate, which I also need) is appropriate, 
in that it conforms to the spec, works with Word, and provides the convenience 
of allowing direct specific of the literal decimal percentage strings to 
.setWidth().

At the same time, there's no reason not to have the POI code do the 
percentage-to-integer conversion when it sets the @w:w value.

Cheers,

Eliot

--
Eliot Kimber
http://contrext.com
 

On 8/16/18, 8:42 AM, "Murphy, Mark"  wrote:

According to the spec, w:val is always an integer value. What that integer 
represents depends on the w:type value. If w:type="auto" then it appears that 
w:val=0, or may even be ignored, but that will depend on the table width 
algorithm. I didn't look at that just now, it is very complex.

If w:type="dxa" then w:val is an integer that represents 20ths of a point. 
In that case I would take the value you specify in setCellWidth and multiply by 
20 to get the integer for val (don't make the user provide val in 20ths of a 
point).

If w:type="pct" then w:val is an integer that represents 50ths of a point. 
In that case I would take the value specified in setCellWidth and multiply by 
50 to get the integer for w:val (again don't make the user provide val in 50ths 
of a percent).

getCellWidth will have to honor these conventions as well. To retrieve cell 
width in points, you will have to divide w:val by 20, and to retrieve cell 
widths in percents, you will have to divide by 50. Note, there is no real way 
to convert between points and percent.

-Original Message-
From: Eliot Kimber [mailto:ekim...@contrext.com] 
Sent: Wednesday, August 15, 2018 7:02 PM
To: POI Users List 
Subject: Re: General Support for Percentage Values?

If I manually set the @w:w value to the percentage string then I can get 
back the percentage and convert it to a double when the width type is set to 
percentage.

But I think there really needs to be a method on CTTblWidth that can return 
an STPercentage, parallel to xgetWidth(), which returns a BigDecimal.

Cheers,

E.

--
Eliot Kimber
http://contrext.com
 

On 8/15/18, 5:39 PM, "Eliot Kimber"  wrote:

(This is in the context of setting the width on XWPFTable.)

I decided that the best (and simplest) approach would be to use strings 
to set the width value for "auto" and percentages, rather than creating a 
separate class that would just end up creating a percentage string anyway. This 
also matches the way the API is likely to be used, e.g., getting percentage 
value strings from some incoming source.

So I added a new setWidth(String widthValue) method to XWPFTable() 
which validates that it's a good value ("auto", an integer, or a percentage) 
and then if it's good, sets the value appropriately.

I also created a new method, getWidthDecimal(), to return the width 
value as a decimal (because percentages can be decimal values).

Using this test case:

XWPFDocument doc = new XWPFDocument();

XWPFTable xtab = doc.createTable();

assertEquals(0, xtab.getWidth());
assertEquals(STTblWidth.AUTO, xtab.getWidthType());
 

RE: Any Idea Why Word Does not Preserve Leading or Trailing Space?

[Murphy, Mark]

Maybe the space is preserved only at the end of the text in the node?

-Original Message-
From: Eliot Kimber [mailto:ekim...@contrext.com] 
Sent: Wednesday, August 15, 2018 4:21 PM
To: POI Users List 
Subject: Any Idea Why Word Does not Preserve Leading or Trailing Space?

In the DOCX I'm generating I have this pair of runs:

If an applicant under subsection 38-79

Notice the leading space in the second run (" 38-79").

When I view this with Word for Mac (16.16) the space is not rendered.

This seems like a bug, especially since space is preserve on the w:t element.

Any idea why Word behaves this way and if there's a way to avoid it (other than 
merging the two runs, which in this case have the same properties)?

This is DOCX being generated from XML source where the runs reflect separate 
source nodes (in this case a text node and then an inline element).

Thanks,

Eliot
--
Eliot Kimber
http://contrext.com
 



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


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

RE: General Support for Percentage Values?

[Murphy, Mark]

According to the spec, w:val is always an integer value. What that integer 
represents depends on the w:type value. If w:type="auto" then it appears that 
w:val=0, or may even be ignored, but that will depend on the table width 
algorithm. I didn't look at that just now, it is very complex.

If w:type="dxa" then w:val is an integer that represents 20ths of a point. In 
that case I would take the value you specify in setCellWidth and multiply by 20 
to get the integer for val (don't make the user provide val in 20ths of a 
point).

If w:type="pct" then w:val is an integer that represents 50ths of a point. In 
that case I would take the value specified in setCellWidth and multiply by 50 
to get the integer for w:val (again don't make the user provide val in 50ths of 
a percent).

getCellWidth will have to honor these conventions as well. To retrieve cell 
width in points, you will have to divide w:val by 20, and to retrieve cell 
widths in percents, you will have to divide by 50. Note, there is no real way 
to convert between points and percent.

-Original Message-
From: Eliot Kimber [mailto:ekim...@contrext.com] 
Sent: Wednesday, August 15, 2018 7:02 PM
To: POI Users List 
Subject: Re: General Support for Percentage Values?

If I manually set the @w:w value to the percentage string then I can get back 
the percentage and convert it to a double when the width type is set to 
percentage.

But I think there really needs to be a method on CTTblWidth that can return an 
STPercentage, parallel to xgetWidth(), which returns a BigDecimal.

Cheers,

E.

--
Eliot Kimber
http://contrext.com
 

On 8/15/18, 5:39 PM, "Eliot Kimber"  wrote:

(This is in the context of setting the width on XWPFTable.)

I decided that the best (and simplest) approach would be to use strings to 
set the width value for "auto" and percentages, rather than creating a separate 
class that would just end up creating a percentage string anyway. This also 
matches the way the API is likely to be used, e.g., getting percentage value 
strings from some incoming source.

So I added a new setWidth(String widthValue) method to XWPFTable() which 
validates that it's a good value ("auto", an integer, or a percentage) and then 
if it's good, sets the value appropriately.

I also created a new method, getWidthDecimal(), to return the width value 
as a decimal (because percentages can be decimal values).

Using this test case:

XWPFDocument doc = new XWPFDocument();

XWPFTable xtab = doc.createTable();

assertEquals(0, xtab.getWidth());
assertEquals(STTblWidth.AUTO, xtab.getWidthType());

xtab.setWidth(1000);
assertEquals(STTblWidth.DXA, xtab.getWidthType());
assertEquals(1000, xtab.getWidth());

xtab.setWidth("auto");
assertEquals(STTblWidth.AUTO, xtab.getWidthType());
assertEquals(0, xtab.getWidth());
assertEquals(0.0, xtab.getWidthDecimal(), 0.01);

xtab.setWidth("999");
assertEquals(STTblWidth.DXA, xtab.getWidthType());
assertEquals(999, xtab.getWidth());

xtab.setWidth("50.5%");
assertEquals(STTblWidth.PCT, xtab.getWidthType());
assertEquals(50.5, xtab.getWidthDecimal(), 0.01);
assertEquals(50.5, 
xtab.getCTTbl().getTblPr().getTblW().xgetW().getBigDecimalValue().doubleValue(),
 0.01);

Everything passes until the last test, where the value returned by 
getWidthDecimal() is 50.0, not 50.5.

It looks like the underlying CT class methods always set the value as an 
integer, even when using CTTblWidth.setBigDecimalValue():

} else if (widthValue.matches("[0-9]+(\\.[0-9]+)?%")) {
String numericPart = widthValue.substring(0, 
widthValue.length() - 1);
STDecimalNumber number = STDecimalNumber.Factory.newInstance();
number.setBigDecimalValue(new BigDecimal(numericPart));
ctWidth.xsetW(number);
ctWidth.setType(STTblWidth.PCT);

Debugging through this code I see that the @w:val attribute is "50" rather 
than "50.5" after the xsetW() method call.

This appears to be happening at a very low level.

Is there a way to fix this so that percentages can be decimals or have I 
misunderstood the specs and percentages should actually always be integers 
(although I'm pretty sure the spec allows fractional percentages).

Thanks,

Eliot
--
Eliot Kimber
http://contrext.com
 

On 8/15/18, 3:40 PM, "Eliot Kimber"  wrote:

I need to extend the options for setting table widths.

Per the spec, a table's width can be an absolute measurement, a 
percentage value, or the keyword "auto".

Right now XWPFTable.setWidth() only take

RE: How to Resolve "Indirectly referenced from required .class files" Issue?

[Murphy, Mark]

LOL, There is a lot of old stuff that doesn’t have unit tests. If you are 
regularly making use of CT classes, then you might want to consider using the 
full ooxml-schemas jar.

-Original Message-
From: Eliot Kimber [mailto:ekim...@contrext.com] 
Sent: Wednesday, August 01, 2018 7:40 AM
To: POI Users List 
Subject: Re: How to Resolve "Indirectly referenced from required .class files" 
Issue?

Note that I'm getting this error in the context of a standalone project, not 
the POI code itself.

However, I can do the work I'm doing in POI in order to extend the XWPFRun API 
and for that I would of course create unit tests (what kind of monster would 
not create a unit test for a new method?).

Cheers,

E.
--
Eliot Kimber
http://contrext.com
 

On 7/31/18, 10:59 PM, "Mark Murphy"  wrote:

CT Classes are added to the poi-ooxml-schemas jar based on unit tests. If
the class is used by a unit test, it is included in the jar. I generally
use the full schema in ooxml-schemas for development so I don't hit that
issue. And you should always add unit tests for new code, and examples so
that others can use your code.

On Tue, Jul 31, 2018 at 7:29 PM pj.fanning  wrote:

> poi-ooxml depends on poi-ooxml-schemas and this latter jar has the most
> commonly used XMLBeans generated classes for the OOXML xsds. CTEm appears
> to
> be missing from this jar.
> We can add it for the 4.0.0 release.
> ooxml-schemas has the full set of generated classes. We will be releasing
> v1.4 of this jar with poi 4.0.0.
> It is built with the rest of the poi jars (if you building locally).
>
>
>
> --
> Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
>
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
>




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


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

RE: XWPFFieldRun Doesn't Work for Creating DOCX

[Murphy, Mark]

Yea, there are a lot of half finished classes in XWPF. Fields are one of those. 
If you look at Stack Overflow here https://stackoverflow.com/a/40264237/2296441 
you will see how I add a table of contents as a simple field. This should be 
relatively easy to add as a method of a paragraph like this:

Public XWPFSimpleField createSimpleField(String instruction) {
CTP ctP = p.getCTP();
CTSimpleField field = ctP.addNewFldSimple();
field.setInstr(instruction);
return field;
}

Then you would add a table of contents like this: para.createSimpleField("TOC 
\\h");

-Original Message-
From: Eliot Kimber [mailto:ekim...@contrext.com] 
Sent: Tuesday, July 31, 2018 7:52 AM
To: POI Users List 
Subject: XWPFFieldRun Doesn't Work for Creating DOCX

When creating new DOCX the XWPFFieldRun class does not work to create 
paragraphs with CTSimpleFields.

The problem is that the field is not written out during commit. The current 
unit test only tests the read path but does not test the write path, which 
would fail.

I'm not sure what the right solution is (or I would have simply implemented it).

This code works:

CTSimpleField ctField = para.getCTP().addNewFldSimple();
((XmlObject)ctField).newCursor()
.setAttributeText(
QNAME_INSTR_ATT, 
fieldData);

So it's not a critical issue but it's definitely an annoyance in the API that 
you can't create simple fields directly.

I don't have time at the moment to puzzle through this so I was hoping somebody 
might have more cogent ideas about how best to address this in the API. 

But my fear is that addressing this would require serious adjustment or 
extension to the internals of XWPFParagraph.

Ideally I'd like something like:

XWPFSimpleField field = para.createSimpleField("PAGE");

I'd also like XWPF-level support for creating complex fields, but that's more 
involved due to the amorphous nature of complex fields, so I'm really not sure 
what design should be.

Thanks,

Eliot
--
Eliot Kimber
http://contrext.com
 



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

RE: General Mechanism in WXPF Code for Managing CT IDs?

[Murphy, Mark]

In fact, I think it would be good to change the TableCell behavior before 4.0.0 
goes live as that is a breaking change.

-Original Message-
From: Murphy, Mark [mailto:murphym...@metalexmfg.com] 
Sent: Friday, July 20, 2018 3:52 PM
To: 'POI Users List' 
Subject: RE: General Mechanism in WXPF Code for Managing CT IDs?

Those places that have this behavior are being adjusted as I get to them. So 
let's not create extra work for later.

-Original Message-
From: Eliot Kimber [mailto:ekim...@contrext.com] 
Sent: Friday, July 20, 2018 11:22 AM
To: POI Users List 
Subject: Re: General Mechanism in WXPF Code for Managing CT IDs?

I agree that having to treat the first paragraph separately is annoying, 
however, there are already several classes that do that (e.g., XWPFTableRow, 
which creates a new cell and that cell has a paragraph), so unless we modify 
them all to not create an initial child object, it seems like consistency of 
behavior argues for creating the initial paragraph.

What I have working at the moment is footnote creation where the footnote is 
created with an initial paragraph that contains the required footnote reference 
in the first run. 

As this reference is mandatory and otherwise requires dropping into the CT 
classes it seemed reasonable to add the paragraph that then contains the 
reference.

I also added createParagraph() and createTable() (and now that I think about it 
I also need the insert* methods as well).
I suppose those methods could add the footnoteref if it's not already present.

Here's my real-world code that uses my new methods (it consumes a simple XML 
structure that mirrors the OOXML structure and constraints):

private void makeFootnote(XWPFParagraph para, XmlCursor cursor) throws 
DocxGenerationException {
XWPFFootnote note = para.getDocument().createFootnote();

cursor.toFirstChild();
boolean isFirst = true;
do {
String tagName = cursor.getName().getLocalPart();
if ("p".equals(tagName)) {
XWPFParagraph p = 
isFirst ? 
note.getParagraphs().get(0) : note.createParagraph();
isFirst = false;
makeParagraph(p, cursor);
} else if ("table".equals(tagName)) {
makeTable(note, cursor);
} else {
System.out.println("- [WARN] Unexpected element 
'" + tagName + "' in . Ignored.");
}
} while (cursor.toNextSibling());

// Add footnote reference to the paragraph:
CTP paraCTP = para.getCTP();
CTR run = paraCTP.addNewR();
run.addNewRPr().addNewRStyle().setVal("FootnoteReference");
run.addNewFootnoteReference().setId(note.getId());  

}

Notice the "isFirst" Boolean--in the abstract it would be nice not to have to 
do that.

I will certainly defer to the direction from the project team on this aspect of 
the design.

Cheers,

Eliot
--
Eliot Kimber
http://contrext.com
 

On 7/20/18, 11:06 AM, "Mark Murphy"  wrote:

According to the spec, the id's for footnotes and end notes are unique to
the note type (footnote or end note). You may want to create this so that
it can handle footnotes or end notes as there are only cosmetic differences
between the two. Similar to header/footer. Id's should be handled
internally so the user does not have to concern themselves with them. If
you are looking at header/footer for examples, please do not generate an
empty paragraph when a footnote or end note is created. This is too
limiting because it forces treating the first paragraph differently than
any potential additional paragraphs when adding content.

Maybe for simplicity it would be nice, for someone creating a document, to
simply be able to insert a footnote or end note with some text at the
current location in the paragraph without having to specify anything else.
The InsertFootnote () method would then do all the necessary background
work (create part if necessary, generate the next id, insert the footnote
reference) and insert the footnote text in the footnote part. It could also
return the footnote for additional modification like stylizing or adding
additional paragraphs, tables, images, etc..

On Fri, Jul 20, 2018 at 5:33 AM Eliot Kimber  wrote:

> For footnotes I need to set a unique ID on each newly-created getCTFtnEdn.
> In my personal code I was maintaining my own global ID counter that I used
> to set IDs on things.
&

RE: Contributor Question: How Best to Create Pull Request Using a git-svn Clone?

[Murphy, Mark]

It depends on who goes about reviewing your changes. Most don't care, PJ 
prefers a git pull request. I generally do my real work in svn, and test stuff 
in a git clone.

-Original Message-
From: Eliot Kimber [mailto:ekim...@contrext.com] 
Sent: Friday, July 20, 2018 12:00 PM
To: POI Users List 
Subject: Contributor Question: How Best to Create Pull Request Using a git-svn 
Clone?

I've done my work in a git-svn clone of the project, but reviewing the 
contributor guidance it's not clear to me how best to create a pull request or 
patch submission in this case.

Maybe I should have used the read-only git repo on GitHub?

Thanks,

E.
--
Eliot Kimber
http://contrext.com
 



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

RE: General Mechanism in WXPF Code for Managing CT IDs?

[Murphy, Mark]

Those places that have this behavior are being adjusted as I get to them. So 
let's not create extra work for later.

-Original Message-
From: Eliot Kimber [mailto:ekim...@contrext.com] 
Sent: Friday, July 20, 2018 11:22 AM
To: POI Users List 
Subject: Re: General Mechanism in WXPF Code for Managing CT IDs?

I agree that having to treat the first paragraph separately is annoying, 
however, there are already several classes that do that (e.g., XWPFTableRow, 
which creates a new cell and that cell has a paragraph), so unless we modify 
them all to not create an initial child object, it seems like consistency of 
behavior argues for creating the initial paragraph.

What I have working at the moment is footnote creation where the footnote is 
created with an initial paragraph that contains the required footnote reference 
in the first run. 

As this reference is mandatory and otherwise requires dropping into the CT 
classes it seemed reasonable to add the paragraph that then contains the 
reference.

I also added createParagraph() and createTable() (and now that I think about it 
I also need the insert* methods as well).
I suppose those methods could add the footnoteref if it's not already present.

Here's my real-world code that uses my new methods (it consumes a simple XML 
structure that mirrors the OOXML structure and constraints):

private void makeFootnote(XWPFParagraph para, XmlCursor cursor) throws 
DocxGenerationException {
XWPFFootnote note = para.getDocument().createFootnote();

cursor.toFirstChild();
boolean isFirst = true;
do {
String tagName = cursor.getName().getLocalPart();
if ("p".equals(tagName)) {
XWPFParagraph p = 
isFirst ? 
note.getParagraphs().get(0) : note.createParagraph();
isFirst = false;
makeParagraph(p, cursor);
} else if ("table".equals(tagName)) {
makeTable(note, cursor);
} else {
System.out.println("- [WARN] Unexpected element 
'" + tagName + "' in . Ignored.");
}
} while (cursor.toNextSibling());

// Add footnote reference to the paragraph:
CTP paraCTP = para.getCTP();
CTR run = paraCTP.addNewR();
run.addNewRPr().addNewRStyle().setVal("FootnoteReference");
run.addNewFootnoteReference().setId(note.getId());  

}

Notice the "isFirst" Boolean--in the abstract it would be nice not to have to 
do that.

I will certainly defer to the direction from the project team on this aspect of 
the design.

Cheers,

Eliot
--
Eliot Kimber
http://contrext.com
 

On 7/20/18, 11:06 AM, "Mark Murphy"  wrote:

According to the spec, the id's for footnotes and end notes are unique to
the note type (footnote or end note). You may want to create this so that
it can handle footnotes or end notes as there are only cosmetic differences
between the two. Similar to header/footer. Id's should be handled
internally so the user does not have to concern themselves with them. If
you are looking at header/footer for examples, please do not generate an
empty paragraph when a footnote or end note is created. This is too
limiting because it forces treating the first paragraph differently than
any potential additional paragraphs when adding content.

Maybe for simplicity it would be nice, for someone creating a document, to
simply be able to insert a footnote or end note with some text at the
current location in the paragraph without having to specify anything else.
The InsertFootnote () method would then do all the necessary background
work (create part if necessary, generate the next id, insert the footnote
reference) and insert the footnote text in the footnote part. It could also
return the footnote for additional modification like stylizing or adding
additional paragraphs, tables, images, etc..

On Fri, Jul 20, 2018 at 5:33 AM Eliot Kimber  wrote:

> For footnotes I need to set a unique ID on each newly-created getCTFtnEdn.
> In my personal code I was maintaining my own global ID counter that I used
> to set IDs on things.
>
> However, I'm not finding a similar mechanism in the POI code--is there one
> or is there a reliable technique for constructing new IDs, e.g., based on
> the size of lists of things? I'm pretty sure footnote IDs only need to be
> unique across the footnotes but I'm not 100% sure on the general semantics
> of IDs in OOXML.
>
> Searching for "setId" I'm not finding any hits outside the test cases, so
> either this is somethi

RE: Long XSLF Table needs splitting

[Murphy, Mark]

This seems to be a new question, not even related to Excel. Can you please not 
hijack threads, but instead create a new one? Odds are PJ's answer won't even 
help because he answered for Excel, and the API's aren't really even similar.

-Original Message-
From: Nayan Zubeen Bayan [mailto:nayanjyoti...@gmail.com] 
Sent: Thursday, June 21, 2018 11:34 PM
To: POI Users List 
Subject: Re: Long XSLF Table needs splitting

Can someone help me with adding hyperlink to a particular string in an existing 
word document using Apache POI? By hyperlink I mean hyperlink to file, web 
address and all.
Thank you.

On Fri, Jun 22, 2018 at 2:33 AM, Andreas Beeker 
wrote:

> On 6/21/18 5:00 PM, lanpirot wrote:
> > Bump. Is there any workaround to find out the rendered height of a
> table, a
> > tablerow or a cell?
> >
> The height/width of a cell is fixed/independent from the content - 
> have a look at XSLFTable.getColumnWidth() / .getRowHeight().
>
> For resizing the cell around the content, I would start with
> XSLFTextShape.getTextHeight() and adapt it.
>
> Andi
>
>
>

RE: Is it POI error starting 3.14 version onwards

[Murphy, Mark]

You need to apply a style to the cell. The style can include a number format.

DataFormat format = workbook.createDataFormat();
short fmt = format.getFormat("#0.0");
CellStyle style = workbook.createCellStyle();
style.setDataFormat(fmt);

…
cell.setCellStyle(style);


From: Syed Mudassir Ahmed [mailto:syed.mudas...@gaianconsultants.com]
Sent: Wednesday, May 16, 2018 11:56 PM
To: POI Users List 
Subject: Re: Is it POI error starting 3.14 version onwards

And one more thing, this is my source code:

public static void main(String str[]) throws Exception {
Map input = new LinkedHashMap<>();
SXSSFWorkbook workbook = new SXSSFWorkbook();
OutputStream os = new FileOutputStream(
new File("/home/gaian/Desktop/Sal.xlsx"));
workbook.setCompressTempFiles(true);
SXSSFSheet sheet = workbook.createSheet("firstSheet");
SXSSFRow row = sheet.createRow(0);
SXSSFCell cell = row.createCell(0);
cell.setCellValue("salary");
cell.setCellType(CellType.STRING);
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue(new Double("4.0"));
cell.setCellType(CellType.NUMERIC);
workbook.write(os);
os.close();
workbook.close();
System.out.println("done creating excel workbook...");
}

I am creating a workbook and writing a value (4.0) as double in a sheet.  But 
when I open the sheet, I see the integral value 4 instead of decimal 4.0.

Any settings I need to do in my code to get the value 4.0?

Thanks.

Thanks,
[cid:ii_14fd4e82bb9756bc]

On Thu, May 17, 2018 at 9:07 AM, Syed Mudassir Ahmed 
mailto:syed.mudas...@gaianconsultants.com>> 
wrote:
Thanks so much Tim and Fanning.  Both of your suggestions are working out.  I 
would suggest better to have such example in the test case section of your 
source folder.

Thanks,
[cid:ii_14fd4e82bb9756bc]

On Wed, May 16, 2018 at 6:49 PM, Tim Allison 
mailto:talli...@apache.org>> wrote:
You need to make your SAXReader namespace aware:

saxFactory.setNamespaceAware(true);


On Wed, May 16, 2018 at 8:59 AM, Tim Allison 
mailto:talli...@apache.org>> wrote:

> Sorry for my delay.  I just tested your file with Apache Tika 1.18 which
> uses POI 3.17..., and I got:
>
>
> Sheet1
>   Salary
> 99.965432
> 
> 
> Sheet2
> 
> 
> Sheet3
> 
> 
> 
>
> That's promising...  Let me take a look at your example code.
>
> On Wed, May 16, 2018 at 1:31 AM, Syed Mudassir Ahmed mailto:syed.mudassir@%0b>> gaianconsultants.com> 
wrote:
>
>> any update on this pls?  This is blocking me.
>>
>> Thanks,
>>
>>
>> On Tue, May 15, 2018 at 3:45 PM, Syed Mudassir Ahmed <
>> syed.mudas...@gaianconsultants.com>
>>  wrote:
>>
>>> Yes, pls find the file attached here.
>>>
>>> Thanks,
>>>
>>>
>>> On Tue, May 15, 2018 at 3:43 PM, Tim Allison 
>>> mailto:talli...@apache.org>>
>>> wrote:
>>>
 Any chanc you can share the file?

 On Tue, May 15, 2018 at 3:19 AM Syed Mudassir Ahmed <
 syed.mudas...@gaianconsultants.com>
  wrote:

 > Hi,
 >   I am trying to read data from a XLSX sheet via
 XSSFSheetXMLHandler.  The
 > source code is below.
 >
 >   public static void main(String str[]) throws Exception {
 > String filePath
 > = "/home/gaian/Desktop/salary.xlsx";
 > File file = new File(filePath);
 > InputStream inputStream = new FileInputStream(file);
 > OPCPackage pkg = OPCPackage.open(inputStream);
 >
 > SheetContentsHandler sheetContentsHandler = new
 > SheetContentsHandler() {
 > @Override
 > public void startRow(int rowIndex) {
 > }
 >
 > @Override
 > public void endRow(int i) {
 > }
 >
 > @Override
 > public void cell(String cell, String formattedValue,
 > XSSFComment c) {
 > System.out.println("cell encountered with addess:<" +
 cell
 > + "> and value:<" + formattedValue + ">");
 > }
 >
 > @Override
 > public void headerFooter(String text, boolean isHeader,
 String
 > tagName) {
 > System.out.println("headerFooter()");
 > }
 > };
 >
 > ReadOnlySharedStringsTable strings = new
 > ReadOnlySharedStringsTable(pkg);
 > XSSFReader xssfReader = new XSSFReader(pkg);
 > StylesTable styles = xssfReader.getStylesTable();
 > XSSFReader.SheetIterator worksheets =
 (XSSFReader.SheetIterator)
 > xssfReader.getSheetsData();
 > InputStream stream = worksheets.next();
 > SAXParserFactory saxFactory = SAXParserFactory.newInstance();
 > X

RE: POI 3.17 Problems with border and fill styles in generating XSSF/xlsx files, viewed in MS Office 365 ProPlus

[Murphy, Mark]

How did you sort the rows?

-Original Message-
From: Dagnon, William [mailto:william.dag...@wpsic.com] 
Sent: Thursday, March 08, 2018 8:53 AM
To: POI Users List 
Subject: RE: POI 3.17 Problems with border and fill styles in generating 
XSSF/xlsx files, viewed in MS Office 365 ProPlus

As a follow-on: I created a cell style with a bottom and right border plus a 
background, then applied it to selective rows.  It looks good when I generate 
the file.

If I sort the rows, the background color remains for the content rows - but the 
border style randomly disappears.  In fact the last 2 columns only have content 
for rows with this style (w borders), but after sorting most of those columns 
just lose their borders: 7 rows keep borders, 4 lose the last 2 columns' 
borders, 14 lose all borders after the sort.  Total 163 content rows.

The border dialog within Excel shows this isn't just a visual artifact: missing 
borders are no longer set on those content rows.

The rows which keep their borders after the sort doesn't correlate with the 
original position of rows with borders.

I don't know if this is an Excel-ism or how much POI affects it.

CONFIDENTIALITY NOTICE: This e-mail, including any attachments, may contain 
confidential, privileged and/or proprietary information which is solely for the 
use of the intended recipient(s). Any review, use, disclosure, or retention by 
others is strictly prohibited. If you are not an intended recipient, please 
contact the sender and delete this e-mail, any attachments, and all copies.
B�CB��[��X��ܚX�KK[XZ[
�\�\�][��X��ܚX�P�K�\X�K�ܙ�B��܈Y][ۘ[��[X[��K[XZ[
�\�\�Z[�K�\X�K�ܙ�B

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

RE: showInPane does not work

[Murphy, Mark]

Not working is a very broad statement, what is happening, what did you expect? 
What does your code look like?

-Original Message-
From: arun.ka [mailto:thimsa...@gmail.com] 
Sent: Wednesday, February 07, 2018 2:44 AM
To: user@poi.apache.org
Subject: Re: showInPane does not work

Dear POI team and Mark,

Please help me out of that issue.
showInPane not working for HSSF.

BR,
ARUN



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

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


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

RE: Paragraph runs are wrongly considered to be deleted in review

[Murphy, Mark]

Simon, if you could attach a simple document that exhibits this issue to 
bugzilla issue #61787 (https://bz.apache.org/bugzilla/show_bug.cgi?id=61787), I 
would appreciate it.

-Original Message-
From: Murphy, Mark [mailto:murphym...@metalexmfg.com] 
Sent: Monday, November 20, 2017 8:45 AM
To: 'POI Users List' 
Subject: RE: Paragraph runs are wrongly considered to be deleted in review

It appears, based on my reading of the spec, that the fix for bug 58067 is 
incorrect as it is keying on rsidDel which is a revision session id not a 
deleted element mark. Deletions are indicated by several tags but most 
important to this particular bug is  which is the tag for deleted text 
in a run. I will create a bug for you.

-Original Message-
From: Simon Gaeremynck [mailto:gaeremyn...@gmail.com] 
Sent: Sunday, November 19, 2017 7:23 AM
To: POI Users List 
Subject: Re: Paragraph runs are wrongly considered to be deleted in review

Hi Mark,

The file was not produced in POI but authored by another user (presumably in 
Word or another office editor).

All content displays fine in Word. When checking the review stats in Word there 
are 0 deletions, in fact, the file hasn’t been reviewed at all.

Thanks!
Simon

On 11/19/17, 2:19 AM, "Mark Murphy"  wrote:

Is POI producing this? What happens if you display the file with Word?

On Thu, Nov 16, 2017 at 10:47 AM, Simon Gaeremynck 
wrote:

> Hello all,
>
> I think this is a bug but wanted to run it past the email list first
> before I open a bug ticket.
>
> I have a document that contains paragraphs and runs that all have zeroed
> out rsidDel attributes. [1]
> I’m having a bit of trouble trying to find the right reference
> documentation to help discern what the expected behavior should be, but
> from just eyeballing that XML, it looks like w:rsidRDefault declares the
> value  to be the default ID and therefore ignorable?
>
> When doing a paragraph.getText() the runs are filtered out as they have an
> rsidDel attribute.
> https://github.com/apache/poi/blob/trunk/src/ooxml/java/org/
> apache/poi/xwpf/usermodel/XWPFParagraph.java#L192
>
> This was done as a fix for https://bz.apache.org/
> bugzilla/show_bug.cgi?id=58067 (getText() of XWPFParagraph returns
> deleted text if in "review" mode) but I wonder whether the right behavior
> is to compare the value of rsidDel to rsidRDefault (or filter out 
> values)?
>
> You can find the source .docx file at https://s3.amazonaws.com/ally-
> dev/files/essay.docx
>
> Let me know what you think.
>
> Thanks!
> Simon
>
>
> [1] Example of paragraph runs
>  w:rsidRDefault="" w:rsidRPr="">
> 
> 
> 
> 
> 
>  w:name="_u0zbcgllb07d"/>
> 
> 
> 
> 
> 
> Personal Worldview Essay
> 
> 
>  w:rsidRDefault="" w:rsidRPr="">
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
>
>
>
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
>




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

B�CB��[��X��ܚX�KK[XZ[
�\�\�][��X��ܚX�P�K�\X�K�ܙ�B��܈Y][ۘ[��[X[��K[XZ[
�\�\�Z[�K�\X�K�ܙ�B

RE: Paragraph runs are wrongly considered to be deleted in review

[Murphy, Mark]

It appears, based on my reading of the spec, that the fix for bug 58067 is 
incorrect as it is keying on rsidDel which is a revision session id not a 
deleted element mark. Deletions are indicated by several tags but most 
important to this particular bug is  which is the tag for deleted text 
in a run. I will create a bug for you.

-Original Message-
From: Simon Gaeremynck [mailto:gaeremyn...@gmail.com] 
Sent: Sunday, November 19, 2017 7:23 AM
To: POI Users List 
Subject: Re: Paragraph runs are wrongly considered to be deleted in review

Hi Mark,

The file was not produced in POI but authored by another user (presumably in 
Word or another office editor).

All content displays fine in Word. When checking the review stats in Word there 
are 0 deletions, in fact, the file hasn’t been reviewed at all.

Thanks!
Simon

On 11/19/17, 2:19 AM, "Mark Murphy"  wrote:

Is POI producing this? What happens if you display the file with Word?

On Thu, Nov 16, 2017 at 10:47 AM, Simon Gaeremynck 
wrote:

> Hello all,
>
> I think this is a bug but wanted to run it past the email list first
> before I open a bug ticket.
>
> I have a document that contains paragraphs and runs that all have zeroed
> out rsidDel attributes. [1]
> I’m having a bit of trouble trying to find the right reference
> documentation to help discern what the expected behavior should be, but
> from just eyeballing that XML, it looks like w:rsidRDefault declares the
> value  to be the default ID and therefore ignorable?
>
> When doing a paragraph.getText() the runs are filtered out as they have an
> rsidDel attribute.
> https://github.com/apache/poi/blob/trunk/src/ooxml/java/org/
> apache/poi/xwpf/usermodel/XWPFParagraph.java#L192
>
> This was done as a fix for https://bz.apache.org/
> bugzilla/show_bug.cgi?id=58067 (getText() of XWPFParagraph returns
> deleted text if in "review" mode) but I wonder whether the right behavior
> is to compare the value of rsidDel to rsidRDefault (or filter out 
> values)?
>
> You can find the source .docx file at https://s3.amazonaws.com/ally-
> dev/files/essay.docx
>
> Let me know what you think.
>
> Thanks!
> Simon
>
>
> [1] Example of paragraph runs
>  w:rsidRDefault="" w:rsidRPr="">
> 
> 
> 
> 
> 
>  w:name="_u0zbcgllb07d"/>
> 
> 
> 
> 
> 
> Personal Worldview Essay
> 
> 
>  w:rsidRDefault="" w:rsidRPr="">
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
>
>
>
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
>




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

RE: XSSFWorkbook isHidden implementation

[Murphy, Mark]

For sheets, once again Excel 2016, I cannot hide the last visible sheet. So it 
appears that Excel handles Sheets and workbooks differently. One potential 
reason to have hidden workbooks is if you have a workbook with just macros in 
it, and you have that in your XLSTART library [User 
library]/AppData/Roaming/Microsoft/Excel/XLSTART, then that workbook will open 
every time you open Excel, and you really want that workbook to be hidden, but 
open with no visible window views.

-Original Message-
From: Javen O'Neal [mailto:one...@apache.org] 
Sent: Monday, October 30, 2017 10:46 PM
To: POI Users List 
Subject: Re: XSSFWorkbook isHidden implementation

Yudai Yamaguchi-san,

How are you planning on using workbook visibility?

My understanding of workbook visibility, using Excel 2013 (no longer a multiple 
document interface) is that at least one workbook view must be visible (Excel 
disallows saving an all-hidden workbook), and each sheet defines a sheetviews 
list with one entry per workbook view.

There are quite a few gotchas to getting book views working in POI without 
creating xlsx files that Excel will declare are corrupt.

If your use case is a workbook that already contains multiple workbook views 
and you need to change the visibility of the first workbook view, then we can 
skip adding and removing workbook views to a workbook.
Otherwise, some research is needed to implement the dependent features before 
implementing boolean isHidden(int) and void setHidden(int, boolean), or 
possibly Enum WorbookViewVisibility { visible, hidden, very_hidden }.

RE: XSSFWorkbook isHidden implementation

[Murphy, Mark]

In my testing, Excel 2016, I can hide a workbook by selecting View->Hide. I can 
do that even if it is the only workbook open. The XML is in workbook.xml and 
looks like this:


   


I can close out of Excel with the only workbook hidden, and it allows me to 
save it as hidden. When I re-open that workbook, it starts out hidden.

-Original Message-
From: Javen O'Neal [mailto:one...@apache.org] 
Sent: Monday, October 30, 2017 10:46 PM
To: POI Users List 
Subject: Re: XSSFWorkbook isHidden implementation

Yudai Yamaguchi-san,

How are you planning on using workbook visibility?

My understanding of workbook visibility, using Excel 2013 (no longer a multiple 
document interface) is that at least one workbook view must be visible (Excel 
disallows saving an all-hidden workbook), and each sheet defines a sheetviews 
list with one entry per workbook view.

There are quite a few gotchas to getting book views working in POI without 
creating xlsx files that Excel will declare are corrupt.

If your use case is a workbook that already contains multiple workbook views 
and you need to change the visibility of the first workbook view, then we can 
skip adding and removing workbook views to a workbook.
Otherwise, some research is needed to implement the dependent features before 
implementing boolean isHidden(int) and void setHidden(int, boolean), or 
possibly Enum WorbookViewVisibility { visible, hidden, very_hidden }.

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

RE: corruption in xlsx spreadsheet generated by poi?

[Murphy, Mark]

I believe that the JVM still uses Unicode internally. I believe character data 
is probably converted when the string is created. I know that on IBM i, I do 
not have to do anything special to EBCDIC character data, and POI documents are 
written correctly.

-Original Message-
From: Robert Cochran [mailto:r2cochr...@gmail.com] 
Sent: Tuesday, July 11, 2017 12:59 AM
To: POI Users List 
Subject: Re: corruption in xlsx spreadsheet generated by poi?

I'm not sure what the reference to "IBM JVM" really means in this context.
If one means "The Java Virtual Machine that runs under IBM System Z on a 
z-Series mainframe", then character encoding issues to and from EBCDIC could be 
a problem. IBM's Tier 1 implementation of the JVM (under Unix System Services) 
is natively EBCDIC. It is just another possibility,

Thanks

Bob

On Mon, Jul 10, 2017 at 2:20 PM, Javen O'Neal  wrote:

> Sounds like this is a difference in incompatible zip library 
> implementations. POI uses java.util.zip.ZipFile, for which the JVM 
> provides the implementation.
>
> There may be some hints that POI can provide to the JVM to get 
> compatible files on IBM Java. If not, this may be a bug in the JVMs 
> not confirming to the same zip spec.
>
> On Jul 10, 2017 7:16 PM, "Robert Cochran"  wrote:
>
> Hi, does it make a difference that the Results2.xlsx file contains a 
> drawing1.xml? With the understanding that I myself am just learning 
> how to use the POI to generate Excel workbooks and sheet data.
>
> Thanks
>
> Bob Cochran
> Greenbelt, Maryland
>
>
> On Mon, Jul 10, 2017 at 12:33 PM, Carl Buxbaum 
> 
> wrote:
>
> > Thank you.  I think the difference is that my local environment on 
> > Tomcat is using the Oracle JVM, while the environment where this is 
> > an issue is using the IBM JVM.  So the zip implementation must be different.
> >
> > I will go ahead an open up a bug ticket.
> >
> > Carl Buxbaum
> > Senior Software Architect
> > 17 Rogers Street
> > Gloucester / Massachusetts / 01930 / USA
> > 1-978-515-5128
> >
> > Bamboo Rose
> > The only B2B marketplace powered by proven trade engines.
> > www.BambooRose.com
> >
> > Join Bamboo Rose for STARS 2017, our user conference in Boston this 
> > October! Register Today! > com/e/stars-2017-bamboo-rose-user-conference-tickets-31684750943>
> >
> > Make Retail. Fun. Connected. Easier. Smarter. Together. Better.
> >
> >
> > From: Andreas Beeker 
> > mailto:kiwiwi...@apache.org>>
> > Reply-To: POI Users List 
> > mailto:user@poi.apache.org
> >>
> > Date: Monday, July 10, 2017 at 11:48 AM
> > To: POI Users List mailto:user@poi.apache.org>>
> > Subject: Re: corruption in xlsx spreadsheet generated by poi?
> >
> >
> > If I create a ³corrupt² excel, unzip it, and then zip it back up (on 
> > my mac using zip command), the resulting zip file opens without 
> > issue.  If a colleague on Windows generates the same excel, and does 
> > the same
> probably
> > using windup, the corruption remains.
> >
> >
> > Please check if you see a difference with zipinfo:
> > http://www.info-zip.org/mans/zipinfo.html
> >
> > See below [1] - for your "Results (2).xlsx".
> >
> > I think there was something with the compression method, i.e. not 
> > all compression methods might be supported - also defN = "deflate 
> > normal" sound like a default compression.
> > IIRC there's also a file in our test-corpus with some strange 
> > compression method, which poi can't open.
> >
> >
> > Hmm, for some reason my attached files did not make it into the thread.
> > Is there a UI where I can attach them?
> > Just open a ticket in the bugzilla:
> > https://bz.apache.org/bugzilla/enter_bug.cgi?product=POI&component=X
> > SSF
> >
> >
> > Andi
> >
> >
> > [1]
> >
> > $ zipinfo Results2.xlsx
> > Archive:  Results2.xlsx
> > Zip file size: 6744 bytes, number of entries: 11
> > -rw 2.0 fat  596 bl defN 17-Jul-06 10:02 _rels/.rels
> > -rw 2.0 fat 1195 bl defN 17-Jul-06 10:02 [Content_Types].xml
> > -rw 2.0 fat  184 bl defN 17-Jul-06 10:02 docProps/app.xml
> > -rw 2.0 fat  441 bl defN 17-Jul-06 10:02 docProps/core.xml
> > -rw 2.0 fat  131 bl defN 17-Jul-06 10:02
> > xl/drawings/drawing1.xml
> > -rw 2.0 fat  138 bl defN 17-Jul-06 10:02 xl/sharedStrings.xml
> > -rw 2.0 fat 3601 bl defN 17-Jul-06 10:02 xl/styles.xml
> > -rw 2.0 fat  350 bl defN 17-Jul-06 10:02 xl/workbook.xml
> > -rw 2.0 fat  574 bl defN 17-Jul-06 10:02
> > xl/_rels/workbook.xml.rels
> > -rw 2.0 fat22610 bl defN 17-Jul-06 10:02
> > xl/worksheets/sheet1.xml
> > -rw 2.0 fat  303 bl defN 17-Jul-06 10:02
> > xl/worksheets/_rels/sheet1.xml.rels
> > 11 files, 30123 bytes uncompressed, 5226 bytes compressed:  82.7%
> >
> >
> >
> > 
> >
> > DISCLAIMER: E-mails and attachments from Bamboo Rose, LLC are 
> > confidential. If you are not the intended recipient, 

RE: Intended Cell Type

[Murphy, Mark]

I don't think you can restrict what goes in a cell in Excel without using VBA. 
If you need to do that you are better off with a database anyway. Besides, VBA 
cannot be parsed by POI, so even if you do restrict the contents of a cell that 
way, you can't detect it with POI.

-Original Message-
From: Blake Watson [mailto:blake.wat...@pnmac.com] 
Sent: Tuesday, June 27, 2017 12:26 PM
To: POI Users List 
Subject: Re: Intended Cell Type

>> but doesn't mean that's what will be written there.

Not in Excel-land. This presents some challenges when you're trying to actually 
restrict what might go there.

>>You could try entering a date and format it as "#,##0.00".

Yeah. And I could format the values according to whats in there, which would 
remove the scientific notation aspect, but which would also put commas in 
exported numbers.

Excel's a hell of a thing.

RE: new XWPFDocument(fis) is blocked

[Murphy, Mark]

What OS are you running on?

-Original Message-
From: xjtuzxh [mailto:xiaohui.zh...@transwarp.io] 
Sent: Wednesday, May 17, 2017 4:49 AM
To: user@poi.apache.org
Subject: new XWPFDocument(fis) is blocked

hi all,
This is my first topic on POI, I am from china so my English is a little 
poor.

I am trying  extract text from *.docx file which can be opened using the 
following code, but it is blocked when executing this statement:
document = new XWPFDocument(is);
no exception or error is reported. so how to debug.


CODE:
InputStream is = new FileInputStream(file); System.out.println(is.available());
document = new XWPFDocument(is);
extractor = new XWPFWordExtractor(document); writer.write(extractor.getText());
writer.flush();


version of poi jars: 3.16



--
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/new-XWPFDocument-fis-is-blocked-tp5727565.html
Sent from the POI - User mailing list archive at Nabble.com.

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


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

RE: How it is possible to get the Alt Text from a Table in Word

[Murphy, Mark]

As I mentioned on SO, this feature is not in the 2006 schema that POI uses. In 
order to do what you want to do, you will have to retrieve the XML for the 
table, and manually parse the tblCaption node out of the table Parameters. 
Start with XWPFTable.getCTTLB(). Then you may be able to use XMLBeans methods 
to retrieve the node you are looking for.

-Original Message-
From: fusulbashi [mailto:fusul.ba...@gmail.com] 
Sent: Wednesday, May 03, 2017 5:26 AM
To: user@poi.apache.org
Subject: How it is possible to get the Alt Text from a Table in Word

Hi,
how it's possilbe to geht the Alt Text, eg. Description and Title, from a Table 
in Word ?
In the Debugg Mode, i can see the Information, but i don't find any method to 
use them.

Hopefully some can help me. 

Thnx, for help.



--
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/How-it-is-possible-to-get-the-Alt-Text-from-a-Table-in-Word-tp5727440.html
Sent from the POI - User mailing list archive at Nabble.com.

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


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

RE: I want create a xls workbook that the sheets are greater than 64 and every sheet have comment, but it not work at the version 3.12 and later version.

[Murphy, Mark]

Please Open an issue at https://bz.apache.org/bugzilla/buglist.cgi?product=POI

From: Zouchengguang (Ben) [mailto:ben@huawei.com]
Sent: Thursday, April 06, 2017 2:28 AM
To: user@poi.apache.org
Subject: I want create a xls workbook that the sheets are greater than 64 and 
every sheet have comment, but it not work at the version 3.12 and later version.


Hello All
   I want create a xls workbook that the sheets number are greater than 64 
and every sheet has comment, it work at the version 3.10, but it not work at 
3.12 and later version. The following code is my test code. The first 63 sheets 
is ok, but when the sheets are greater than 64 , it throw the exception "The 
exception is Cannot add more than 65535 shapes. "

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class CellComments {

  public static void main(String[] args) {
HSSFWorkbook wb = new HSSFWorkbook();
for (int i = 0; i < 256; i++) {
  HSSFSheet sheet = wb.createSheet("Cell comments in POI HSSF" + (i + 1));
  // Create the drawing patriarch. This is the top level container for all 
shapes including cell comments.
  HSSFPatriarch patr = sheet.createDrawingPatriarch();
  try {
//create a cell in row 3
HSSFCell cell = sheet.createRow(0).createCell(0);
cell.setCellValue(new HSSFRichTextString("Hello, World"));
//anchor defines size and position of the comment in worksheet
HSSFComment comment = patr.createComment(new HSSFClientAnchor(0, 0, 0, 
0, (short) 1,  1, (short) 6, 4));
// set text in the comment
comment.setString(new HSSFRichTextString("We can set comments in POI" + 
0));
//set comment author.
//you can see it in the status bar when moving mouse over the commented 
cell
comment.setAuthor("Apache Software Foundation");
// The first way to assign comment to a cell is via 
HSSFCell.setCellComment method
cell.setCellComment(comment);
  } catch (Exception e) {
System.out.printf("Add comment fail at sheet%d. The exception is %s%n", 
i+1, e.getMessage());
  }
}
try (FileOutputStream out = new FileOutputStream("poi_comment.xls")) {
  wb.write(out);
} catch (IOException e) {
  e.printStackTrace();
}
  }
}

   I have checked the bug list. At the 3.12 version,  to fix the bug 56380, 
added the following code. The bug of 56380 is fixed, but when the sheet number 
is greater than 64, the sharpId will greater than 65535.
[http://hi3ms-image.huawei.com/hi/showimage-1430757821-49904-d36a984ffb4f0d897adedc492147c1f9.jpg]


[http://hi3ms-image.huawei.com/hi/showimage-1430757965-49904-a4e3bb6a39f32c188812741600a33bae.jpg]

RE: Prevent of creation new sheets

[Murphy, Mark]

How about just don't create a new sheet. Saving does not create new sheets in 
POI.

-Original Message-
From: Artem Telizhenko [mailto:bito4e...@gmail.com] 
Sent: Wednesday, April 05, 2017 11:07 AM
To: user@poi.apache.org
Subject: Prevent of creation new sheets

Hi,
I am tried to find out functionality to prevent creation new sheets.
So in my case i need to prevent to create new sheets after saving to Excel.
In VBA it resolve something like as "ThisWorkBook.Protect Structure:=True".
Could you tell me, how to do it in poi?

Thanks.

Best regatds,
Artem Telizhenko

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

RE: HSMF (Outlook .msg) updates

[Murphy, Mark]

Not real sure what Aspose is. It is likely not associated with POI. If it does 
what you need, maybe you should use it unless you can make the necessary 
modifications to POI.

Or

> There are usage examples in the poi-examples [2] package, in the unit 
> tests, and on the quick guide, [3], which is currently in need of 
> contributions to help people get started.
>
> [2] poi-examples HSMF
> http://svn.apache.org/viewvc/poi/trunk/src/examples/src/org/apache/poi/hsmf/examples/
>  
>
> [3] HSMF Quick Guide 
> https://poi.apache.org/hsmf/index.html

-Original Message-
From: rkchilukuri [mailto:rkchiluk...@gmail.com] 
Sent: Monday, February 06, 2017 3:32 AM
To: user@poi.apache.org
Subject: Re: HSMF (Outlook .msg) updates

Hi Javen,

My requirement is to create new outlook message / new draft message by using 
the api.

So please suggest in those lines. At present Aspose api has that feature which 
is a paid version.

Regards,
Ramakrishna Chilukuri

On Mon, 6 Feb 2017 at 11:57 AM, Javen O'Neal-2 [via Apache POI] <
ml-node+s1045710n5726480...@n5.nabble.com> wrote:

> The API is the JavaDocs. [1] The module you're looking for to read MSG 
> files is likely HSMF. This is a relatively immature module, so may be 
> missing certain functionality. Modules become more mature due to 
> contributions and interest from users and developers.
>
> There are usage examples in the poi-examples [2] package, in the unit 
> tests, and on the quick guide, [3], which is currently in need of 
> contributions to help people get started.
>
> If you're willing to contribute some examples of common things someone 
> may want to do with an Outlook message database or a single message, 
> such as extract an attachment, get message headers, extract message 
> body, or accumulate a list of all recipients, we'd be happy to add 
> them to the quick guide, as this is probably the first thing people 
> look for when trying to learn how to solve their problem using POI.
>
> I hope this helps!
> Javen
>
> [1] Javadocs https://poi.apache.org/apidocs/index.html
> [2] poi-examples HSMF
>
> http://svn.apache.org/viewvc/poi/trunk/src/examples/src/org/apache/poi
> /hsmf/examples/ [3] HSMF Quick Guide 
> https://poi.apache.org/hsmf/index.html
>
> On Feb 5, 2017 6:51 PM, "rkchilukuri" <[hidden email] 
> > wrote:
>
> > Hi Dominik,
> >
> > I am sorry for posting spelling mistake. What I want to ask is "api 
> > to create/ modify outlook .msg file"
> >
> > Regards,
> > Ramakrishna
> >
> > On Mon, 6 Feb 2017 at 2:18 AM, Dominik Stadler [via Apache POI] < 
> > [hidden email] 
> > >
> wrote:
> >
> > > Hi,
> > >
> > > I am sorry, but I don't fully understand what is a "pito 
> > > create/modify .msg file" is?
> > >
> > > General contributing guidelines are at 
> > > http://poi.apache.org/guidelines.html,
> > > but it would be good to discuss first on the dev@ mailing list so 
> > > we
> can
> > > understand what you are trying to do.
> > >
> > > Thanks... Dominik.
> > >
> > > On Thu, Feb 2, 2017 at 7:01 PM, rkchilukuri <[hidden email] 
> > > > wrote:
> > >
> > > > Can you please add a pito create/modify .msg file. Otherwise 
> > > > please
> > > guide
> > > > me
> > > > how I can do that so that I can contribute to apache poi aa pi.
> > > >
> > > >
> > > >
> > > > --
> > > > View this message in context: http://apache-poi.1045710.n5.
> > > > nabble.com/HSMF-Outlook-msg-updates-tp2281372p5726455.html
> > > > Sent from the POI - User mailing list archive at Nabble.com.
> > > >
> > > >
> -
> > > > To unsubscribe, e-mail: [hidden email]
> > > 
> > > > For additional commands, e-mail: [hidden email]
> > > 
> > > >
> > > >
> > >
> > >
> > > --
> > > If you reply to this email, your message will be added to the
> discussion
> > > below:
> > >
> > > http://apache-poi.1045710.n5.nabble.com/HSMF-Outlook-msg-
> > updates-tp2281372p5726477.html
> > > To unsubscribe from HSMF (Outlook .msg) updates, click here <
> http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro
> =
> > unsubscribe_by_code&node=2281372&code=cmtjaGlsdWt1cmlAZ21haWwuY29tfD
> > IyODEzNzJ8LTEyNjc2NzA4OTA=>
> > > .
> > > NAML
> > >  > NamlServlet.jtp?macro=macro_viewer&id=instant_html%
> > 21nabble%3Aemail.naml&base=nabble.naml.namespaces.
> > BasicNamespace-nabble.view.web.template.NabbleNamespace-
> > nabble.view.web.template.NodeNamespace&breadcrumbs=
> > notify_subscribers%21nabble%3Aemail.naml-instant_emails%
> > 21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
> > >
> >
> >
> >
> >
> > --
> > View this message in context: http://apache-poi.1045710

RE: Problem with default font size when reading from docx file

[Murphy, Mark]

Please attach a minimal document, and some code that exhibits the issue. Then 
we can probably answer better. But if there is no style directly associated 
with the paragraph or run, it should be getting its style from the default run 
style.

This is found in XWPFStyles.

XWPFStyles styles = doc.getStyles();
XWPFDefaultRunStyle defRun = styles.getDefaultRunStyle();
int size = defRun.getFontSize();

-Original Message-
From: apache_user [mailto:p.feizab...@gmail.com] 
Sent: Thursday, January 26, 2017 1:45 AM
To: user@poi.apache.org
Subject: Problem with default font size when reading from docx file

Hello All,

I am trying to read the font size of some runs, but sometimes, I get -1 as the 
font size. I know that it shows the default font size. But I need the actual 
font size. I found a couple of solutions to extract the default font size and 
what I do right now is like the following:

XWPFRun first_run = getFirstRun(paragraph); first_run.getCTR();

using the CTR, I can see something like the following:

http://schemas.microsoft.com/office/word/2010/wordprocessingCanvas";
xmlns:cx="http://schemas.microsoft.com/office/drawing/2014/chartex";
xmlns:cx1="http://schemas.microsoft.com/office/drawing/2015/9/8/chartex";
xmlns:cx2="http://schemas.microsoft.com/office/drawing/2015/10/21/chartex";
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006";
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships";
xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math";
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordprocessingDrawing";
xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing";
xmlns:w10="urn:schemas-microsoft-com:office:word"
xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main";
xmlns:w14="http://schemas.microsoft.com/office/word/2010/wordml";
xmlns:w15="http://schemas.microsoft.com/office/word/2012/wordml";
xmlns:w16se="http://schemas.microsoft.com/office/word/2015/wordml/symex";
xmlns:wpg="http://schemas.microsoft.com/office/word/2010/wordprocessingGroup";
xmlns:wpi="http://schemas.microsoft.com/office/word/2010/wordprocessingInk";
xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml";
xmlns:wps="http://schemas.microsoft.com/office/word/2010/wordprocessingShape";> first run: Manage and font is: 10 new try: 
http://schemas.microsoft.com/office/word/2010/wordprocessingCanvas";
xmlns:cx="http://schemas.microsoft.com/office/drawing/2014/chartex";
xmlns:cx1="http://schemas.microsoft.com/office/drawing/2015/9/8/chartex";
xmlns:cx2="http://schemas.microsoft.com/office/drawing/2015/10/21/chartex";
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006";
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships";
xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math";
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordprocessingDrawing";
xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing";
xmlns:w10="urn:schemas-microsoft-com:office:word"
xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main";
xmlns:w14="http://schemas.microsoft.com/office/word/2010/wordml";
xmlns:w15="http://schemas.microsoft.com/office/word/2012/wordml";
xmlns:w16se="http://schemas.microsoft.com/office/word/2015/wordml/symex";
xmlns:wpg="http://schemas.microsoft.com/office/word/2010/wordprocessingGroup";
xmlns:wpi="http://schemas.microsoft.com/office/word/2010/wordprocessingInk";
xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml";
xmlns:wps="http://schemas.microsoft.com/office/word/2010/wordprocessingShape";>

The last line is what shows the font information. Right? But the point in my 
case is that, that is not true! The font size of the text in the .docx file is 
12, getFontSize() returns -1 (that is it uses the default font), and the xml 
part says its 10!

In the meantime, I tried use getStyleID() for the paragraph and getStyles() for 
the document. But it didn't help as the paragraph had no style and
getStyleID() returned null.

If it helps, I'm reading the data from cells of a table and I know that the 
paragraphs and runs are extracted correctly.

Can anybody help me?

Thanks in advance,




--
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Problem-with-default-font-size-when-reading-from-docx-file-tp5726353.html
Sent from the POI - User mailing list archive at Nabble.com.

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


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

RE: add spacing to footer when generating .docx

[Murphy, Mark]

Does adding a couple extra blank paragraphs in the footer work for you?

From: Olga Bondareva [mailto:obondar...@objectstyle.com]
Sent: Thursday, January 19, 2017 11:11 AM
To: user@poi.apache.org
Subject: add spacing to footer when generating .docx

Hi all,
Could you tell me please how to add spacing to footer when generating .docx.
Here is the image what I need.

Best regards,
Olga

RE: shiftrows() in xls document containing hidden (zero height) rows

[Murphy, Mark]

Check the link at the bottom of the email?

-Original Message-
From: discreta discrecion [mailto:ecorr...@hotmail.com] 
Sent: Sunday, January 08, 2017 5:53 AM
To: POI Users List 
Subject: Re: shiftrows() in xls document containing hidden (zero height) rows

unsuscribe, please?


De: Spyros Rokopanos 
Enviado: sábado, 7 de enero de 2017 15:04:48
Para: user@poi.apache.org
Asunto: Re: shiftrows() in xls document containing hidden (zero height) rows

I don't know if its universal. I have faced the issue in one workbook in which 
one sheet has hidden rows... I have not tried with other workbooks/sheets..
Also from stackoverflow  I found this which sounds the same problem:
"Somehow I manage to create new rows between two rows in an existing excel 
file. The problem is, some of the formatting were not include along the 
shifting of the rows.

One of this, is the row that are hide are not relatively go along during the 
shift. What I mean is(ex.), rows from 20 to 30 is hidden, but when a create new 
rows the formating still there. The hidden rows must also move during the 
insertion/creation of new rows, it should be 21 to 31.

Another thing is, the other object in the sheet that are not in the cell. Like 
the text box are not move along after the new row is created. Its like the 
position of these object are fixed. But I want it to move, the same thing as I 
insert a new row or paste row in excel. If there is a function of inserting a 
new row, please let me know."

Although there are many answers, it seems that problem is unresolved... The 
link is here:
http://stackoverflow.com/questions/5785724/how-to-insert-a-row-between-two-rows-in-an-existing-excel-with-hssf-apache-poi

I will also attach my workbook to have a look...
On 2017-01-06 06:50 (+0200), "Javen O'Neal"  wrote:
> Does this happen on all XLS workbooks or just a few?
>
> If it isn't universal, could you also attach a small workbook to go 
> along with your code sample so that we can reproduce the problem?
>
> On Jan 5, 2017 20:47, "Javen O'Neal"  wrote:
>
> Sounds like a bug in the shiftRows code.
>
> Could you post a self-contained example of what you're doing that's 
> causing the error, including a stack trace of the error if you're 
> getting an exception instead of a mangled workbook?
>
> On Jan 5, 2017 15:40, "Spyros Rokopanos"  wrote:
>
> > Hello all,
> >
> >
> > I would like to ask if behavior of shiftrows() changes if a 
> > documents contains hidden (zero height) rows. I just ask because I 
> > am trying to insert some new rows (using shiftrows()) and if I have 
> > hidden rows in the document I face issues. If I unhide them, shiftrows() 
> > works as expected.
> >
> >
> > Has anyone faced a similar issue?
> >
> >
> > Thanks in advance for any hint/help!
> >
> >
> > Kind regards,
> >
> > Spyros
> >
>

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


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

RE: Writing workbooks with linked workbooks using XSSFWorkbook

[Murphy, Mark]

If you create an issue in bugzilla with a unit test, It will be easier to get 
that fixed.

-Original Message-
From: Jörn Franke [mailto:jornfra...@gmail.com] 
Sent: Tuesday, December 27, 2016 9:53 AM
To: user@poi.apache.org
Subject: Writing workbooks with linked workbooks using XSSFWorkbook

Dear all,

I am currently writing an open source library for reading/writing office files 
on Hadoop/Spark/Hive/etc. The code is on Github and an official release in form 
of a library on maven central will follow soon.
The Excel files will be supported by the Apache POI library (3.15).
So far everything works quiet well, such as reading from linked workbooks (HSSF 
& XSSF), writing workbooks with linked workbooks (HSSF). However, I have some 
issues with writing workbooks with linked workbooks using XSSF. I receive the 
following exception (when calling linkexternalworkbooks):

RuntimeException
("Not
Implemented - see bug #57184");


The bug (https://bz.apache.org/bugzilla/show_bug.cgi?id=57184) refers to bug 
60219, which is supposed to be fixed in 3.15. However, it does not seem that 
this bug really solves the issue I have.

Does anyone know when external workbooks for XSSFWorkbooks will be supported?


Thank you a lot.


best regards

RE: How do you code cell striping?

[Murphy, Mark]

Ok, I thought it was earlier than that. But as I said earlier, it will still do 
the same thing with the boarder and fill blocks in styles.xml. It is based on 
setCellStyleProperties() It will reduce the number of styles though vs. 
setCellStyleProperty().

-Original Message-
From: Eric Douglas [mailto:edoug...@blockhouse.com] 
Sent: Tuesday, December 20, 2016 2:15 PM
To: POI Users List 
Subject: Re: How do you code cell striping?

I found a PropertyTemplate class.  It is in the poi-3.16-beta1.jar.  It is not 
in the 3.15 current download.

On Tue, Dec 20, 2016 at 11:44 AM, Murphy, Mark 
wrote:

> PropertyTemplate is new for 3.15. It is in package 
> org.apache.poi.ss.util
>
> Here are the javadocs: https://poi.apache.org/ 
> apidocs/index.html?org/apache/poi/ss/util/PropertyTemplate.html
>
> Both PropertyTemplate and setCellStyleProperties() will help in 
> reducing the number of styles generated. But neither will reduce the 
> number of fills or boarders that are defined. That issue is deeper in 
> the API. However, I have not seen nearly the number of boarders and 
> fills defined as I have styles from using setCellStyleProperty(). You 
> are right, the HashMap does not have all the CellStyle properties in 
> it, just the ones you are trying to change. It uses a putAll() behind 
> the scenes which does a merge with the existing cell's style.
>
> -Original Message-
> From: Eric Douglas [mailto:edoug...@blockhouse.com]
> Sent: Tuesday, December 20, 2016 10:23 AM
> To: POI Users List 
> Subject: Re: How do you code cell striping?
>
> Yeah that doesn't work.
> I wrote a test program that generates a .xlsx file and the borders 
> section looks like this:
>  count="1"><
> diagonal/>
>
> I change nothing except for adding this default border method:
>   BorderStyle DEFAULTBORDER = BorderStyle.HAIR;
>   Map properties = new HashMap();
>   // border around a cell
>   properties.put(CellUtil.BORDER_TOP, DEFAULTBORDER);
>   properties.put(CellUtil.BORDER_BOTTOM, DEFAULTBORDER);
>   properties.put(CellUtil.BORDER_LEFT, DEFAULTBORDER);
>   properties.put(CellUtil.BORDER_RIGHT, DEFAULTBORDER);
>   // Give it a color (AUTOMATIC)
>   properties.put(CellUtil.TOP_BORDER_COLOR,
> IndexedColors.AUTOMATIC.getIndex());
>   properties.put(CellUtil.BOTTOM_BORDER_COLOR,
> IndexedColors.AUTOMATIC.getIndex());
>   properties.put(CellUtil.LEFT_BORDER_COLOR,
> IndexedColors.AUTOMATIC.getIndex());
>   properties.put(CellUtil.RIGHT_BORDER_COLOR,
> IndexedColors.AUTOMATIC.getIndex());
> Then the call to that util under my cell:
>   r = curSheet.createRow(1);
>   c = r.createCell(0);
>   CellUtil.setCellStyleProperties(c, properties);
>
> Then the borders section looks like this:
>  count="9"><
> diagonal/> style="hair"/> style="hair"/> style="hair"/> style="hair"/> style="hair"/> style="hair"/> indexed="64"/> indexed="64"/> style="hair"> style="hair"> indexed="64"/> indexed="64"/> indexed="64"/> indexed="64"/> indexed="64"/> indexed="64"/>
>
> That method example also creates a new HashMap, so it's not pulling 
> sheet defaults.  The font on that cell is different from the other cells.
>
> The next method described on that page references a PropertyTemplate.  
> I Googled that and found org.apache.poi.ss.util.PropertyTemplate, 
> searched that and found POI 3.9.  Whatever that was appears to be gone in POI 
> 3.15.
>
> On Mon, Dec 19, 2016 at 10:57 PM, Mark Murphy 
> wrote:
>
> > BTW, if you look at the quick guide
> > https://poi.apache.org/spreadsheet/quick-guide.html#CellProperties 
> > you will find two methods of drawing borders without creating all 
> > those unused intermediate styles.
> >
> > On Mon, Dec 19, 2016 at 4:04 PM, Eric Douglas 
> > 
> > wrote:
> >
> > > Yeah, I wouldn't expect really fast response from a list like this.
> > > My previous message was 6 days ago.
> > > I didn't see an easy way to get alternate row styling with regular 
> > > styles (to color only even numbered rows, and keep only even 
> > > numbered rows
> > colored
> > > if the user sorts by a different column) but I did get the 
> > > conditional formatting working.  The bad part is the order of 
> > > precedence.  The application applies the individual cell colors 
> > > then the conditional format.  I wanted cell co

RE: How do you code cell striping?

[Murphy, Mark]

I just tried what you want to do in Excel. Conditional formats always override 
the cell's formatting. You can't set a cell's style to override the conditional 
formatting. The only thing you can do is omit the cells from conditional 
formatting that you want to leave alone.

-Original Message-----
From: Murphy, Mark [mailto:murphym...@metalexmfg.com] 
Sent: Tuesday, December 20, 2016 11:45 AM
To: 'POI Users List' 
Subject: RE: How do you code cell striping?

Can you do the color thing in Excel? If not, you probably can't do it in POI 
either.

-Original Message-
From: Eric Douglas [mailto:edoug...@blockhouse.com]
Sent: Tuesday, December 20, 2016 10:34 AM
To: POI Users List 
Subject: Re: How do you code cell striping?

I did get the conditional formatting to work for row striping, other than when 
individual cells need color that should override the color in the conditional 
format.
The data table idea sounds interesting but the first example Google found 
doesn't seem to work.
https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java

On Tue, Dec 20, 2016 at 12:01 AM, Javen O'Neal  wrote:

> Looks like Microsoft suggests [1] conditional formatting or a data 
> table (XSSFTable), so you were on the right track to solve this with 
> POI's conditional formatting.
>
> Either way, it sounds like we should cover this somewhat common 
> scenario either in poi-examples or the spreadsheet quick guide. Pull 
> requests greatly appreciated.
>
> [1]
> https://support.office.com/en-us/article/Apply-shading-to-
> alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880
>
>
> On Dec 19, 2016 19:57, "Mark Murphy"  wrote:
>
> > BTW, if you look at the quick guide
> > https://poi.apache.org/spreadsheet/quick-guide.html#CellProperties
> > you will find two methods of drawing borders without creating all 
> > those unused intermediate styles.
> >
> > On Mon, Dec 19, 2016 at 4:04 PM, Eric Douglas 
> > 
> > wrote:
> >
> > > Yeah, I wouldn't expect really fast response from a list like 
> > > this.  My previous message was 6 days ago.
> > > I didn't see an easy way to get alternate row styling with regular
> styles
> > > (to color only even numbered rows, and keep only even numbered 
> > > rows
> > colored
> > > if the user sorts by a different column) but I did get the 
> > > conditional formatting working.  The bad part is the order of 
> > > precedence.  The application applies the individual cell colors 
> > > then the conditional format.  I wanted cell colors in the 
> > > individual cell styles to override
> > the
> > > conditional.  The only thing I've found that I think would work is
> > coding a
> > > VBA method into the conditional formatting to tell the "ISEVEN(ROW())"
> to
> > > apply only if the cell does not already have individual coloring, 
> > > but
> the
> > > syntax for applying VBA sounds ugly.
> > >
> > > I did unzip some xlsx files and read through the schema and we can
> easily
> > > see why some people send us really large files.  Saving 
> > > spreadsheets
> from
> > > LibreOffice wants to write a lot of styles it doesn't need.  Some 
> > > just
> > code
> > > duplicate styles and end up with hundreds or thousands, where I 
> > > got
> mine
> > > only writing unique so there's about 11.  Some of the POI API 
> > > doesn't
> > make
> > > sense, as I tried to apply a thin border around every populated 
> > > cell
> and
> > > ended up with 9 border styles, with cell styles only referencing 3 
> > > of
> > them.
> > > ie:
> > >
> > >  > > auto="true"/> auto="true"/> > > style="hair"> > > auto="true"/> > > style="hair"/> > > style="hair"/> > > style="hair"/> > > style="hair"/> > > style="hair"/> > > style="hair"/> > > indexed="64"/> > > indexed="64"/> > > style="hair"/> > > indexed="64"/> > > style="hair"> > > style="hair"> > > style="hair"/> > > indexed="64"/> > > indexed="64"/> > indexed="64"/> > > style="hair"> > > style="hair">
> > >
> > >  > > borderId

RE: How do you code cell striping?

[Murphy, Mark]

Can you do the color thing in Excel? If not, you probably can't do it in POI 
either.

-Original Message-
From: Eric Douglas [mailto:edoug...@blockhouse.com] 
Sent: Tuesday, December 20, 2016 10:34 AM
To: POI Users List 
Subject: Re: How do you code cell striping?

I did get the conditional formatting to work for row striping, other than when 
individual cells need color that should override the color in the conditional 
format.
The data table idea sounds interesting but the first example Google found 
doesn't seem to work.
https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java

On Tue, Dec 20, 2016 at 12:01 AM, Javen O'Neal  wrote:

> Looks like Microsoft suggests [1] conditional formatting or a data 
> table (XSSFTable), so you were on the right track to solve this with 
> POI's conditional formatting.
>
> Either way, it sounds like we should cover this somewhat common 
> scenario either in poi-examples or the spreadsheet quick guide. Pull 
> requests greatly appreciated.
>
> [1]
> https://support.office.com/en-us/article/Apply-shading-to-
> alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880
>
>
> On Dec 19, 2016 19:57, "Mark Murphy"  wrote:
>
> > BTW, if you look at the quick guide
> > https://poi.apache.org/spreadsheet/quick-guide.html#CellProperties 
> > you will find two methods of drawing borders without creating all 
> > those unused intermediate styles.
> >
> > On Mon, Dec 19, 2016 at 4:04 PM, Eric Douglas 
> > 
> > wrote:
> >
> > > Yeah, I wouldn't expect really fast response from a list like 
> > > this.  My previous message was 6 days ago.
> > > I didn't see an easy way to get alternate row styling with regular
> styles
> > > (to color only even numbered rows, and keep only even numbered 
> > > rows
> > colored
> > > if the user sorts by a different column) but I did get the 
> > > conditional formatting working.  The bad part is the order of 
> > > precedence.  The application applies the individual cell colors 
> > > then the conditional format.  I wanted cell colors in the 
> > > individual cell styles to override
> > the
> > > conditional.  The only thing I've found that I think would work is
> > coding a
> > > VBA method into the conditional formatting to tell the "ISEVEN(ROW())"
> to
> > > apply only if the cell does not already have individual coloring, 
> > > but
> the
> > > syntax for applying VBA sounds ugly.
> > >
> > > I did unzip some xlsx files and read through the schema and we can
> easily
> > > see why some people send us really large files.  Saving 
> > > spreadsheets
> from
> > > LibreOffice wants to write a lot of styles it doesn't need.  Some 
> > > just
> > code
> > > duplicate styles and end up with hundreds or thousands, where I 
> > > got
> mine
> > > only writing unique so there's about 11.  Some of the POI API 
> > > doesn't
> > make
> > > sense, as I tried to apply a thin border around every populated 
> > > cell
> and
> > > ended up with 9 border styles, with cell styles only referencing 3 
> > > of
> > them.
> > > ie:
> > >
> > >  > > auto="true"/> auto="true"/> > > style="hair"> > > auto="true"/> > > style="hair"/> > > style="hair"/> > > style="hair"/> > > style="hair"/> > > style="hair"/> > > style="hair"/> > > indexed="64"/> > > indexed="64"/> > > style="hair"/> > > indexed="64"/> > > style="hair"> > > style="hair"> > > style="hair"/> > > indexed="64"/> > > indexed="64"/> > indexed="64"/> > > style="hair"> > > style="hair">
> > >
> > >  > > borderId="0"/> > > xfId="1"/> > xfId="2"/> > > borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="3"/> > borderId="0"
> > > fillId="0" fontId="0" numFmtId="0" xfId="4"/> fillId="0"
> > > fontId="0" numFmtId="0" xfId="5"/> fontId="0"
> > > numFmtId="0" xfId="6"/> > numFmtId="0"
> > > xfId="7"/>
> > >
> > >  > > xfId="0" applyFont="true" applyBorder="true"> > > horizontal="left"/> > > numFmtId="0" applyBorder="true" applyNumberFormat="true"
> applyFill="true"
> > > applyFont="true"> > > vertical="bottom" wrapText="false"/> > > locked="true"/> > > numFmtId="0"
> > applyBorder="true"
> > > applyNumberFormat="true" applyFill="true" 
> > > applyFont="true"> > > vertical="bottom"
> > > wrapText="false"/> > borderId="8"
> > > fillId="3" fontId="0" numFmtId="0" applyBorder="true"
> > > applyNumberFormat="true" applyFill="true" 
> > > applyFont="true"> > > vertical="bottom"
> > > wrapText="false"/> > borderId="8"
> > > fillId="2" fontId="0" numFmtId="164" applyBorder="true"
> > > applyNumberFormat="true" applyFill="true" 
> > > applyFont="true"> > > vertical="bottom"
> > > wrapText="false"/> > borderId="8"
> > > fillId="3" fontId="0" numFmtId="165" applyBorder="true"
> > > applyNumberFormat="true" applyFill="true" 
> > > applyFont="true"> > > vertical="bottom"
> > > wrapText="false"/> > borderId="8"
> > > fillId="3" fontId="0" numFmtId="165" applyBorder="true"
> > > applyNumberFormat="true" applyFill="true" 
> > > applyFont="true"> > > vertical="bot

RE: How do you code cell striping?

[Murphy, Mark]

PropertyTemplate is new for 3.15. It is in package org.apache.poi.ss.util

Here are the javadocs: 
https://poi.apache.org/apidocs/index.html?org/apache/poi/ss/util/PropertyTemplate.html

Both PropertyTemplate and setCellStyleProperties() will help in reducing the 
number of styles generated. But neither will reduce the number of fills or 
boarders that are defined. That issue is deeper in the API. However, I have not 
seen nearly the number of boarders and fills defined as I have styles from 
using setCellStyleProperty(). You are right, the HashMap does not have all the 
CellStyle properties in it, just the ones you are trying to change. It uses a 
putAll() behind the scenes which does a merge with the existing cell's style.

-Original Message-
From: Eric Douglas [mailto:edoug...@blockhouse.com] 
Sent: Tuesday, December 20, 2016 10:23 AM
To: POI Users List 
Subject: Re: How do you code cell striping?

Yeah that doesn't work.
I wrote a test program that generates a .xlsx file and the borders section 
looks like this:


I change nothing except for adding this default border method:
  BorderStyle DEFAULTBORDER = BorderStyle.HAIR;
  Map properties = new HashMap();
  // border around a cell
  properties.put(CellUtil.BORDER_TOP, DEFAULTBORDER);
  properties.put(CellUtil.BORDER_BOTTOM, DEFAULTBORDER);
  properties.put(CellUtil.BORDER_LEFT, DEFAULTBORDER);
  properties.put(CellUtil.BORDER_RIGHT, DEFAULTBORDER);
  // Give it a color (AUTOMATIC)
  properties.put(CellUtil.TOP_BORDER_COLOR,
IndexedColors.AUTOMATIC.getIndex());
  properties.put(CellUtil.BOTTOM_BORDER_COLOR,
IndexedColors.AUTOMATIC.getIndex());
  properties.put(CellUtil.LEFT_BORDER_COLOR,
IndexedColors.AUTOMATIC.getIndex());
  properties.put(CellUtil.RIGHT_BORDER_COLOR,
IndexedColors.AUTOMATIC.getIndex());
Then the call to that util under my cell:
  r = curSheet.createRow(1);
  c = r.createCell(0);
  CellUtil.setCellStyleProperties(c, properties);

Then the borders section looks like this:


That method example also creates a new HashMap, so it's not pulling sheet 
defaults.  The font on that cell is different from the other cells.

The next method described on that page references a PropertyTemplate.  I 
Googled that and found org.apache.poi.ss.util.PropertyTemplate, searched that 
and found POI 3.9.  Whatever that was appears to be gone in POI 3.15.

On Mon, Dec 19, 2016 at 10:57 PM, Mark Murphy  wrote:

> BTW, if you look at the quick guide
> https://poi.apache.org/spreadsheet/quick-guide.html#CellProperties you 
> will find two methods of drawing borders without creating all those 
> unused intermediate styles.
>
> On Mon, Dec 19, 2016 at 4:04 PM, Eric Douglas 
> 
> wrote:
>
> > Yeah, I wouldn't expect really fast response from a list like this.  
> > My previous message was 6 days ago.
> > I didn't see an easy way to get alternate row styling with regular 
> > styles (to color only even numbered rows, and keep only even 
> > numbered rows
> colored
> > if the user sorts by a different column) but I did get the 
> > conditional formatting working.  The bad part is the order of 
> > precedence.  The application applies the individual cell colors then 
> > the conditional format.  I wanted cell colors in the individual cell 
> > styles to override
> the
> > conditional.  The only thing I've found that I think would work is
> coding a
> > VBA method into the conditional formatting to tell the 
> > "ISEVEN(ROW())" to apply only if the cell does not already have 
> > individual coloring, but the syntax for applying VBA sounds ugly.
> >
> > I did unzip some xlsx files and read through the schema and we can 
> > easily see why some people send us really large files.  Saving 
> > spreadsheets from LibreOffice wants to write a lot of styles it 
> > doesn't need.  Some just
> code
> > duplicate styles and end up with hundreds or thousands, where I got 
> > mine only writing unique so there's about 11.  Some of the POI API 
> > doesn't
> make
> > sense, as I tried to apply a thin border around every populated cell 
> > and ended up with 9 border styles, with cell styles only referencing 
> > 3 of
> them.
> > ie:
> >
> >  > auto="true"/> > auto="true"/> > auto="true"/> > auto="true"/> > style="hair"/> > style="hair"/> > style="hair"/> > style="hair"/> > style="hair"/> > style="hair"/> > indexed="64"/> > indexed="64"/> > style="hair"> > style="hair"> > indexed="64"/> > indexed="64"/> > indexed="64"/> indexed="64"/> > style="hair"> > indexed="64"/>
> >
> >  > borderId="0"/> > xfId="1"/> xfId="2"/> > borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="3"/> borderId="0"
> > fillId="0" fontId="0" numFmtId="0" xfId="4"/> > fontId="0" numFmtId="0" xfId="5"/> > numFmtId="0" xfId="6"/> numFmtId="0"
> > xfId="7"/>
> >
> >  > xfId="0" applyFont="true" applyBorder="true"> > horizontal="left"/> > numFmtId="0" applyBorder="true" applyNumberFormat=

RE: How do you code cell striping?

[Murphy, Mark]

I am pretty sure this is the way it works in Excel. Conditional formatting 
overrides the stated cell style. In order to get any other colors you need to 
add additional rules to the conditional formatting.

-Original Message-
From: Eric Douglas [mailto:edoug...@blockhouse.com] 
Sent: Monday, December 19, 2016 4:04 PM
To: POI Users List 
Subject: Re: How do you code cell striping?

Yeah, I wouldn't expect really fast response from a list like this.  My 
previous message was 6 days ago.
I didn't see an easy way to get alternate row styling with regular styles (to 
color only even numbered rows, and keep only even numbered rows colored if the 
user sorts by a different column) but I did get the conditional formatting 
working.  The bad part is the order of precedence.  The application applies the 
individual cell colors then the conditional format.  I wanted cell colors in 
the individual cell styles to override the conditional.  The only thing I've 
found that I think would work is coding a VBA method into the conditional 
formatting to tell the "ISEVEN(ROW())" to apply only if the cell does not 
already have individual coloring, but the syntax for applying VBA sounds ugly.

I did unzip some xlsx files and read through the schema and we can easily see 
why some people send us really large files.  Saving spreadsheets from 
LibreOffice wants to write a lot of styles it doesn't need.  Some just code 
duplicate styles and end up with hundreds or thousands, where I got mine only 
writing unique so there's about 11.  Some of the POI API doesn't make sense, as 
I tried to apply a thin border around every populated cell and ended up with 9 
border styles, with cell styles only referencing 3 of them.
ie:







The conditional formatting ends up in sheet1.xml after the sheetData, and I 
coded an option to skip heading rows:
AND(ISEVEN(ROW()),ROW()>1)
which of course links to dxf in the styles.xml 

On Mon, Dec 19, 2016 at 3:36 PM, Javen O'Neal  wrote:

> I think there's a way to apply alternate row styling within regular 
> styles (not conditional formatting), but have never used POI to do 
> this. Rather than tell you the wrong answer, I'd rather stay quiet to 
> avoid unnecessary confusion. Read through the OOXML schemas or create 
> a file in Excel with alternate row styling, unzip the xlsx file, and 
> read the XML to figure out how POI needs to create the same file.
>
> > How many POI developers actively monitor this list?
> At least half a dozen. Nick is one of them.
> Some of us don't live in your timezone, so same-day responses are unlikely.
> We use a minimum window of 72 hours whenever we vote on a release.
>
> Not all of us are experts at the feature you're needing help with, 
> conditional formatting, which may be another reason for low response.
>
> We volunteer our time, working on POI between our day jobs and 
> personal lives. Given the holiday season is close, I would expect 
> developers to be particularly busy, finishing projects at work before 
> the holiday closure and preparing for travel to relatives.
>
> Personally, I spend less time on POI when work at my day job gets busy.
> This is to avoid coming down with a cold due to lack of sleep or 
> prolonged stress, or burning out.
>
> We appreciate your patience.
>
> On Dec 19, 2016 11:24, "Eric Douglas"  wrote:
>
> How many POI developers actively monitor this list?  I haven't gotten 
> any answers to those questions.
> Meanwhile I have a new question.  If I create a conditional formatting 
> which is linked to the entire document and colors every other row, how 
> can I get individual colors in cell styles to override that, or how 
> can I get the conditional formatting to only color cells that have no 
> color in their individual cell styles?
>
> On Tue, Dec 13, 2016 at 9:59 AM, Eric Douglas 
> 
> wrote:
>
> > So I took another stab at it.  This seems to work, but I have questions.
> >
> > import java.awt.Color;
> > import java.io.File;
> > import java.io.FileOutputStream;
> > import java.io.IOException;
> >
> > import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
> > import org.apache.poi.ss.usermodel.FillPatternType;
> > import org.apache.poi.ss.usermodel.PatternFormatting;
> > import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
> > import org.apache.poi.ss.util.CellRangeAddress;
> > import org.apache.poi.xssf.usermodel.XSSFCell;
> > import org.apache.poi.xssf.usermodel.XSSFCellStyle;
> > import org.apache.poi.xssf.usermodel.XSSFColor;
> > import org.apache.poi.xssf.usermodel.XSSFSheet;
> > import org.apache.poi.xssf.usermodel.XSSFWorkbook;
> >
> > public class TestExcel {
> >
> >  public static void main(String[] args) throws IOException {
> >   XSSFWorkbook wb = new XSSFWorkbook();
> >   XSSFSheet curSheet = wb.createSheet("Sheet " +
> > (wb.getNumberOfSheets() + 1));
> >   XSSFCell c1 = curSheet.createRow(0).createCell(0);
> >   c1.setCellValue(wb.getCreationHelper().
> creat

RE: How do you code cell striping?

[Murphy, Mark]

I can take a stab at one of these. 

#1) Why does Conditional formatting use setBackgroundColor to set the fill 
color for Solid Fill while styles use setForgroundColor?

I noticed this discrepancy myself. To answer the question I generated a simple 
spreadsheet in MS Excel with one cell colored via style, and a second colored 
with a conditional format. It turned out that MS Excel uses Foreground Color 
for styles, and Background Color for conditional formats. I don't know why, and 
it only seems to apply to the Solid Fill pattern. For other fill patterns, 
Excel uses Foreground Color and Background Color consistently. You can validate 
this yourself. Create a simple spreadsheet with various fills using conditional 
formatting, and styles. Save it as an XSLX. Rename the resulting file to .ZIP, 
and look at the resulting XML. You will see the discrepancy. Why? I don't know, 
maybe ask on MSDN? MS is unlikely to fix this as it is pervasive at this point.

-Original Message-
From: Eric Douglas [mailto:edoug...@blockhouse.com] 
Sent: Tuesday, December 13, 2016 10:00 AM
To: POI Users List 
Subject: Re: How do you code cell striping?

So I took another stab at it.  This seems to work, but I have questions.

import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.PatternFormatting;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestExcel {

 public static void main(String[] args) throws IOException {
  XSSFWorkbook wb = new XSSFWorkbook();
  XSSFSheet curSheet = wb.createSheet("Sheet " +
(wb.getNumberOfSheets() + 1));
  XSSFCell c1 = curSheet.createRow(0).createCell(0);
  c1.setCellValue(wb.getCreationHelper().createRichTextString("No
color"));
  XSSFCell c2 = curSheet.createRow(1).createCell(0);

c2.setCellValue(wb.getCreationHelper().createRichTextString("Color this cell"));
  XSSFCell c3 = curSheet.createRow(2).createCell(0);
  c3.setCellValue(wb.getCreationHelper().createRichTextString("No
color"));
  XSSFCell c4 = curSheet.createRow(3).createCell(0);

c4.setCellValue(wb.getCreationHelper().createRichTextString("Color this cell"));
  // set one cell's color
  final XSSFCellStyle style1 = wb.createCellStyle();
  style1.setFillForegroundColor(new XSSFColor(new Color(123,124,125)));
  style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  c4.setCellStyle(style1);
  // set all cells' color, every other row
  CellRangeAddress[] regions =
{CellRangeAddress.valueOf("A1:AMJ1048576")};
  SheetConditionalFormatting sheetCF = 
curSheet.getSheetConditionalFormatting();
  ConditionalFormattingRule rule1 = 
sheetCF.createConditionalFormattingRule("ISEVEN(ROW())");
  PatternFormatting fill1 = rule1.createPatternFormatting();
  final XSSFColor customColor = new XSSFColor(new Color(228, 247, 247));
  fill1.setFillBackgroundColor(customColor);
  fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
  sheetCF.addConditionalFormatting(regions, rule1);
  File fi = new File("output.xlsx");
  if (fi.exists()) {
   fi.delete();
  }
  FileOutputStream output = new FileOutputStream(fi);
  wb.write(output);
  wb.close();
  output.flush();
  output.close();
 }
}

1) Why does it change the background of one cell using setFillForegroundColor, 
but to change the background of many cells with a condition I have to call 
setFillBackgroundColor??
2) Why does this create a generic format?  When I call
createPatternFormatting() it shows up in the LibreOffice Calc Styles and 
Formatting section as ConditionalStyle_1.  Can I name this style?
3) Why does this look like a regular style in the Calc program with many 
attributes I can set, but the API only allows me to set the fill color?

On Mon, Dec 12, 2016 at 4:33 PM, Nick Burch  wrote:

> On Mon, 12 Dec 2016, Eric Douglas wrote:
>
>> I found one sample that shows how to code the condition using
>> org.apache.poi.ss.usermodel.SheetConditionalFormatting.addConditional
>> Formatting() to put in the formula that would color each cell if it's 
>> in an even numbered row, but I'm having trouble figuring out the API 
>> to apply the formula to every cell on the worksheet.
>>
>
> For every cell on a sheet, just give a cellrangeaddress that covers 
> the whole extent
>
> For every formula cell, you'd need to loop over a

RE: Where is the Eclipse Project file?

[Murphy, Mark]

The easiest way is to:

1. Add the repository in eclipse
2. In your Java perspective, File->Import from the repository trunk, and during 
the import have eclipse find the project file. This is an option in one of the 
import wizard dialogs.

-Original Message-
From: Nadav Benedek [mailto:nadavbene...@gmail.com] 
Sent: Tuesday, December 13, 2016 3:33 PM
To: POI Users List 
Subject: Where is the Eclipse Project file?

I checked out the svn project.
Here it says: https://poi.apache.org/howtobuild.html that there is an Eclipse 
project there, but I could find any .project file

RE: Too much memory is used when reading a xlsx-file whose size is just 7.3M

[Murphy, Mark]

If we replaced that, and stored the document as Java objects only, we could 
then conceivably write the document as either XLS or XLSX, or whatever format 
comes down the pike simply by selecting a write format. Someone would have to 
create the writers. Expanding on that, we could also read whatever format we 
wanted by creating an appropriate reader. However that would lose the ability 
for a user to just use the CT classes to support a bit of the standard that 
hasn't yet been surfaced by POI. But, it would make swapping out XMLBeans for 
something else easier.

Still thousands of hours of work.

-Original Message-
From: Javen O'Neal [mailto:javenon...@gmail.com] 
Sent: Friday, December 02, 2016 2:21 PM
To: POI Users List 
Subject: Re: Too much memory is used when reading a xlsx-file whose size is 
just 7.3M

Those numbers sound about right. I'm used to 4 MB balloning to 1 GB.

We could significantly reduce memory consumption if we didn't maintain the XML 
DOM in memory, but replacing that requires thousands of hours of work.

On Dec 2, 2016 09:44, "Blake Watson"  wrote:

> Well, this is interesting: I've also seen a ballooning of RAM usage 
> (though I wouldn't call it "violent" =P) when loading a spreadsheet 
> and I didn't know about the input-stream vs. file distinction. 
> Although, come to think of it, I'm pulling out of a database so that might 
> not help me.
>
> The excess usage is only temporary, though, right? My small (<100K) 
> spreadsheets seem to take about 50MB, and my large ones (5MB) seem to 
> take about 500MB.
>
> On Fri, Dec 2, 2016 at 7:54 AM, Gordon Pettey 
> wrote:
>
> > On Fri, Dec 2, 2016 at 2:41 AM, Javen O'Neal 
> wrote:
> >
> > > CPU pegged due the the Java garbage collector trying to clean up 
> > > unused memory. The out of memory error is raised when the garbage 
> > > collector
> > cannot
> > > free the necessary memory for new objects to be allocated.
> > >
> > > Have you read the FAQ yet? https://urldefense.proofpoint.
> > com/v2/url?u=https-3A__poi.apache.org_faq.html-23faq-
> 2DN10165&d=DgIFaQ&c=
> > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3
> > 3stXXb3Psthjw&m=K-mZqLKcau3v-2mO1e7Es8pmREXI9_WkDMs3UxiVEsE&s=_
> > gk9WyMdApng32mJ3IDsTgjct7dpw_BenPZP-wdE0fA&e=
> > >
> > > On Dec 2, 2016 00:11, "do...@126.com"  wrote:
> > >
> > > > Hi!
> > > >
> > > > I am trying to read a xlsx-file whose size is 7.3M with POI 
> > > > version
> > 3.15,
> > > > here is the code：
> > > >
> > > >
> > > > *InputStream is = new FileInputStream(new File(path));//line
> 1Workbook
> > > book = new XSSFWorkbook(in);// line 2*
> > > > When running to line 2，the memory of javaw rised violently from 
> > > > 700M to 1200M，cpu from 10% to 90%，the following is the 
> > > > screenshot of memory dump:
> > > >
> > >
> >
> > See https://urldefense.proofpoint.com/v2/url?u=http-3A__poi.
> > apache.org_spreadsheet_quick-2Dguide.html-23Files-2Bvs-
> > 2BInputStreams&d=DgIFaQ&c=dmLomitc30UP5j2qU8E1rg&r=
> > p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Psthjw&m=K-mZqLKcau3v-
> > 2mO1e7Es8pmREXI9_WkDMs3UxiVEsE&s=Q2dr3Xc2iSpx8HHAAj0kQkGXZYpdBK
> > 9yZFifR-mQo64&e= ,
> > also.
> >
>
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742
> blake.wat...@pnmac.com  
> www.PennyMacUSA.com 
>

RE: Getting all ContentControls

[Murphy, Mark]

Not real sure what you mean by this. Ask you question, and maybe someone can 
help you. Be sure to include what you did, what you expected to happen, and 
what actually happened instead. Showing your code is a good idea, and maybe 
attach the document as well.

-Original Message-
From: Shivam148 [mailto:jainshivam...@gmail.com] 
Sent: Wednesday, November 23, 2016 12:45 AM
To: user@poi.apache.org
Subject: Re: Getting all ContentControls

Hi ,
I am having some issue s with content control in POI . Can you help me with 
that ?



--
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Getting-all-ContentControls-tp5718805p5725733.html
Sent from the POI - User mailing list archive at Nabble.com.

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


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

RE: RE: XWPFSDT

[Murphy, Mark]

You can't, you have to navigate to it from the container.

-Original Message-
From: Nadav Benedek [mailto:nadavbene...@gmail.com] 
Sent: Sunday, November 20, 2016 4:38 PM
To: POI Users List 
Subject: Re: RE: XWPFSDT

Thanks Mark,

The problem is that unlike other elements in the apache poi, the XWPFSDT object 
doesn't give me back the CT classes!

How can I get these classes out of the XWPFSDT element so I can modify them?

On 2016-11-15 15:09 (+0200), "Murphy, Mark"  wrote:
> It depends on where the SDT is. You are going to have to use the CT
classes to do it though. The SDT part of XWPF is pretty much read only at the 
moment. You can find the spec here 
http://www.ecma-international.org/publications/standards/Ecma-376.htm.
POI uses the 1st edition. Parts 3, and 4 will likely be the most informative. 
You should be able to get to the CT classes from most of the document parts 
that can contain an SDT, but you can't get there from XWPFSDT.>;
>
>  >
>
> -Original Message->
>
> From: Nadav Benedek [mailto:nadavbene...@gmail.com] >
>
> Sent: Tuesday, November 15, 2016 6:42 AM>
>
> To: POI Users List >
>
> Subject: XWPFSDT>
>
>
>
> Hi team,>
>
>
>
> Do you know how can I change the content of XWPFSDT?>
>
>
>
> When I do XWPFSDT.getContent() and I get a XWPFSDTContent object, all 
> the
paragraphs and runs in it are private...>
>
>
>
> Thanks,>
>
> Nadav.>
>
>

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

RE: XLS and XLSX with XSSF

[Murphy, Mark]

Yes, but David is correct. Even if you use SS, behind the scenes you are using 
either HSSF or XSSF depending on how you created your Worksheet object. And 
there is no way to switch the Worksheet object to the other.

-Original Message-
From: MARK GOLDEN [mailto:mark_gol...@btopenworld.com] 
Sent: Wednesday, November 16, 2016 12:41 PM
To: POI Users List 
Subject: Re: XLS and XLSX with XSSF

Hi,
I think HSSF is for XLS, XSSF is for XLSX and you can use SS for either.
R,Mark.  

On Wednesday, 16 November 2016, 6:45, David North 
 wrote:
 

 Hi Markus,

This question would be best asked on the POI user list
(user@poi.apache.org) - moving thread there.

> is there a way to write the same file as xls and xlsx file ?
> For example with XSSF.

The APIs require you to pick one or the other - unfortunately although the two 
share a set of common interfaces, we don't have code to clone one 
implementation into the other.

> I tried writing a xlsx file with HSSF and it works.

That's surprising. Are you sure it's actually written an .xlsx file (a ZIP with 
XML inside it)? Or is it the case that Excel happily opens an .xls with the 
.xlsx extension?

> Can i use XSSF for XLS and XLSX without any problems?
> 
> Thanks a lot.
> 
> -
> To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org For additional 
> commands, e-mail: dev-h...@poi.apache.org
> 

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



   

RE: XWPFSDT

[Murphy, Mark]

It depends on where the SDT is. You are going to have to use the CT classes to 
do it though. The SDT part of XWPF is pretty much read only at the moment. You 
can find the spec here 
http://www.ecma-international.org/publications/standards/Ecma-376.htm. POI uses 
the 1st edition. Parts 3, and 4 will likely be the most informative. You should 
be able to get to the CT classes from most of the document parts that can 
contain an SDT, but you can't get there from XWPFSDT.
 
-Original Message-
From: Nadav Benedek [mailto:nadavbene...@gmail.com] 
Sent: Tuesday, November 15, 2016 6:42 AM
To: POI Users List 
Subject: XWPFSDT

Hi team,

Do you know how can I change the content of XWPFSDT?

When I do XWPFSDT.getContent() and I get a XWPFSDTContent object, all the 
paragraphs and runs in it are private...

Thanks,
Nadav.

RE: XSSF for XLS usinh multimap

[Murphy, Mark]

Show us code, what did you try, and what happened?

-Original Message-
From: Tripathi, Shiv Deepak [mailto:shiv.deepak.tripa...@philips.com] 
Sent: Monday, November 14, 2016 1:06 PM
To: POI Users List 
Subject: RE: XSSF for XLS usinh multimap

Hi ,

As I said I am having these values in multimap

{Deepak=F1, F4, Ajinkya=F2, F5} And want to read a existing 
excel file and replace the value available in F1 and F4 with Deepak.

So I need to read the excel too.

I tried some ways In java , but got stucked.



-Original Message-
From: Javen O'Neal [mailto:javenon...@gmail.com]
Sent: Monday, November 14, 2016 12:00 PM
To: POI Users List 
Subject: Re: XSSF for XLS usinh multimap

Is this a question about how to iterate over a multimap or how to set a cell 
value in POI?

Read the javadocs for the multimap implementation you are using.
For example, org.apache.commons.collections4.MultiValuedMap is here:
https://commons.apache.org/proper/commons-collections/apidocs/org/apache/commons/collections4/MultiValuedMap.html

Read the POI HSSF and XSSF quick guide to learn how to set a cell value as a 
string.
https://poi.apache.org/spreadsheet/quick-guide.html#Creating+Cells


The pseudo-code/python answer:

# Getting a Cell object at a CellAddress addr on a Sheet sheet def 
getCell(sheet, addr):
r = addr.getRow()
c = addr.getColumn()
# create row if it does not exist
row = sheet.getRow(r) or sheet.createRow(r)
cell = row.getCell(c) or row.createCell(c)
return cell

# Iterating over a multi-map
for name, addresses in amultimap.items():
for addr in addresses:
getCell(sheet, addr).setCellValue(name)
   


On 11/13/16 10:34, Tripathi, Shiv Deepak wrote:
> Hi All,
>
>
> I have a multimap:
>
> {Deepak=F1, F4, Ajinkya=F2, F5}
>
> In this F1,F4 are nothing but cell Adress.
>
>
> I want to write These values in given cell number like:
>
> F1 and F4 should have Deepak as value.
>
> Can you please let me know how can I write it.
>
> I am using XSSF.
>
> Thanks in advance.
>
>
> Regards,
> Deepak
>
> 
> The information contained in this message may be confidential and legally 
> protected under applicable law. The message is intended solely for the 
> addressee(s). If you are not the intended recipient, you are hereby notified 
> that any use, forwarding, dissemination, or reproduction of this message is 
> strictly prohibited and may be unlawful. If you are not the intended 
> recipient, please contact the sender by return e-mail and destroy all copies 
> of the original message.
>
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional 
> commands, e-mail: user-h...@poi.apache.org



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

RE: Restarting styled numbered/lettered lists

[Murphy, Mark]

XWPF is a work in process, and the API is not really stable. The goal is that 
it not be simply a surrogate for the CT classes, but rather an intuitive 
interface to create word documents from Java. I am working through a basic 
architecture right now so that what we have doesn’t feel so disjointed. Then I 
will start adding in convenience methods for missing parts. Your comments will 
be helpful in deciding what to work on. Documentation is also very important, 
but I want to have an underlying architecture in place before I publish how to 
use it.

From: Jim Klo [mailto:jim@sri.com]
Sent: Thursday, November 03, 2016 12:45 PM
To: Nick Burch 
Cc: user@poi.apache.org
Subject: Re: Restarting styled numbered/lettered lists


On Nov 3, 2016, at 4:24 AM, Nick Burch 
mailto:apa...@gagravarr.org>> wrote:

On Thu, 3 Nov 2016, Jim Klo wrote:

As I toiled away trying to figure out how to restart styled 
bulleted/numbered/lettered lists without messing with the applied numbering 
style - as there seems to be a lack of reasonable examples (in both the unit 
tests, and existing documentation and tutorials)

Where would you have expected to find a method to do this in XWPF, and what 
would you have expected it to look like?

Given the XWPF classes are more of a DAO to the underlying Office Open XML, I 
don’t know that I would expect a specific convenience method that does this. 
However, I would hope that the sequence of how to do common tasks such as 
restarting numbered lists to exist in some sort of FAQ/tutorial that explains 
how styles, lists and numbering work.

FWIW, there’s very little on the XWPF side of things that document these sorts 
of things… maybe the intent is that the ECMA-376 standard is to supplant that?  
However there isn’t really a pointer to which specific version of ECMA-376 that 
the POI project supports, nor a very good explanation of how the POI API maps 
to the spec.  Only now have I located the ECMA Office Open XML Part 1 [1], 
section 17.9 Numbering (page 682) which covers how numbering works:
The basis for all numbering in WordprocessingML is specified via two structures:
•  abstract numbering definitions
•  numbering definition instances

Abstract numbering definitions define the appearance and behavior of a specific 
set of numbered paragraphs in a document. Because this construct is abstract, 
they are not be directly referenced by document content, but rather they shall 
be inherited by a numbering definition instance, which itself is referenced by 
document content.

There are a lot of additional details it goes into but I think most would get 
the gist.

In some respects I would also think there would be at least an exemplar unit 
test that maps and validates according to the spec.

Note this isn’t a rant - you asked what I would expect - that’s kind of what I 
would expect to find.

I think most challenges that I’ve found with POI so far would have been solved 
sooner if references to the ECMA standard were present in the Quick Start and 
overview. IMO, part 1 of the ECMA standard is very readable and understandable, 
and parts of it should be a prerequisite to working with POI effectively.



I finally found a solution - and I kind of understand it… So I published a full 
working sample here: 
https://github.com/jimklo/apache-poi-sample/blob/master/src/main/java/com/sri/jklo/StyledDocument.java

Based on the investigations you've done shown in that code, we might be able to 
add a friendly method, especially if you can suggest what that method should 
look like... :)

Again, I’m not sure a “friendly” method is necessarily needed given the XWPF 
classes are really just an API to the OpenDocXML DOM.

A “friendly” method would be a separate project that encapsulates POI as a 
wrapper with shortcut methods for authoring docs / editing docs… I’m just not 
aware of any open source/non-commercial projects that do this.

But if I were to ask to add anything, it would be property getters on protected 
fields; i.e. XWPFNumbering.getAbstractNums() and XWPFNumbering.getNums(). 
Overall, I’m not sure why all these fields have protected visibility and have 
no property getters.  Protected visibility in Java is just one of those things 
that was sort of done “wrong” IMO, as it can cause some package pollution - 
such as in this instance.

I’ve updated my sample [2]  which leverages the use of those protected fields 
by defining a utility class in the same package as XWPFNumbering.

While I understand that this is probably the acceptable ‘Java’ way of doing 
this, it feels like a “dirty” way of doing this as in practice, an update of 
the POI libraries could cause a conflict with my newly defined NumberingUtil 
class, if the project were to add the same named class in the future.

[1] http://www.ecma-international.org/publications/standards/Ecma-376.htm

[2] 
https://github.com/jimklo/apache-poi-sample/commit/a2833583e801eb9fbdb708fabdeeb88f18b05a25


- JK


Nick

RE: POI-3.15: Commons-Collections-4 vs. Jasper Reports' dependency on Commons-Collections-3

[Murphy, Mark]

This issue seems to have worked out well for Andreas, but I am wondering, if 
Jasper Reports depends on POI, can a user upgrade POI without breaking 
dependencies such as Jasper Reports? Can a user run with two versions of POI in 
their classpath? I don’t think so. While it is good to remove deprecations, I 
believe we need to do so in an orderly manner. I have brought this up before, 
but semantic versioning could help solve that problem by putting some rules 
around when breaking changes happen.

Semantic version numbers look something like POI 3.15.1
Major version is 3 – This changes when breaking changes or major functional 
changes are made
Minor version is 15 – This changes when features are added, but no breaking 
changes are allowed
Patch version is 1 – This changes for bug fixes that do not involve breaking 
changes

If we were to adopt semantic versioning, we could also put the major version in 
the namespace so that incompatible versions of POI could remain in the 
classpath together. It would also let us apply a bug fix to an older version 
while still developing a newer version. That improves stability of the project 
for everyone using it. Folks don’t have to refactor their code just to get bug 
fixes. On the other side, we would be able to deliver bug fixes to a branch 
other than the current production branch. As it stands now, users are stuck 
with upgrading to the latest-greatest, maybe breaking version if they want any 
bug fix. And we are quick on the draw at removing deprecated features.

A lot of projects are moving to semantic versioning for just these reasons. 
Maybe it is time for us to do so as well. Even tools like Maven provide a 
richer experience for packages that use semantic versioning by letting you 
specify a Major version, or Major and Minor version, and then retrieving the 
latest minor or patch version. With our current scheme of versioning, the user 
has to specify the specific version they want to use, and there are no patches 
forthcoming without the pain of breaking changes.

From: Andreas Reichel [mailto:andr...@manticore-projects.com]
Sent: Wednesday, October 26, 2016 5:13 AM
To: user@poi.apache.org
Subject: Re: POI-3.15: Commons-Collections-4 vs. Jasper Reports' dependency on 
Commons-Collections-3


On Wed, 2016-10-26 at 01:40 -0700, Javen O'Neal wrote:
The Commons collection project has namespaced their library so that
collections3 and collections4 can run side by side.[1]

Javen, thank you a lot. This saved my day!
We put both libraries commons-collections-3.2.1.jar and 
commons-collections4-4.1.jar into the classpath and were able to build a Jasper 
Report, but also to export data into an Excel File.

Thank you again for kind support, cheers.

RE: Suspected Spam:Re: altChunk inside paragraph or table call

[Murphy, Mark]

This also needs to be done through the CT classes. You can break into this low 
level interface using XWPFGetDocument. From there you need to navigate the XML 
to add a new paragraph in a specific location. Once again, an issue in Bugzilla 
will help this occur a bit faster.

-Original Message-
From: Алексей Ушаровский [mailto:usha...@mail.ru.INVALID] 
Sent: Thursday, October 20, 2016 1:17 PM
To: POI Users List 
Subject: Suspected Spam:Re: altChunk inside paragraph or table call

And another question, how to insert a paragraph after certain paragraph?


>Четверг, 20 октября 2016, 16:25 +03:00 от Алексей Ушаровский 
>:
>
>Hi!
>
>Is it possible to put altChunk element into paragraph or table cell element in 
>docx?

RE: altChunk inside paragraph or table call

[Murphy, Mark]

Not yet through the usermodel API, but if you do some research on what it 
should look like in the underlying XML, you should be able to accomplish it 
with the CT classes. You should add an issue to the Bugzilla issue tracker for 
this.

-Original Message-
From: Алексей Ушаровский [mailto:usha...@mail.ru.INVALID] 
Sent: Thursday, October 20, 2016 9:25 AM
To: POI Users List 
Subject: altChunk inside paragraph or table call

Hi!

Is it possible to put altChunk element into paragraph or table cell element in 
docx?

RE: How to insert table inside tablerowcell using apache poi

[Murphy, Mark]

Now I am going to give you the keys to the kingdom here. The XML formats are 
really easy to deal with. Simply create a document in Word that contains the 
element you want to duplicate. Then if POI does not expose that element, rename 
your document from XXX.docx to xxx.zip. You can now look at the XML. Most 
relevant XML is in word/document.xml. There are other parts like headers and 
footers, but you can test most things in the main document body. Then look at 
the element you want to be able to produce with POI. If you make you document 
small and simple, this will be easier. The CT classes track very closely to the 
XML node names with CT tacked on the front and namespaces removed. Add and set 
methods are the most useful followed by isset. Once you use a few of them you 
will get the hang of it, and be able to look up and write methods to duplicate 
missing functionality with ease.

-Original Message-
From: Murphy, Mark [mailto:murphym...@metalexmfg.com] 
Sent: Thursday, September 01, 2016 11:58 AM
To: 'Krishna Vyas Majji' 
Cc: user@poi.apache.org
Subject: RE: How to insert table inside tablerowcell using apache poi

This should do it. Note: the inner table has to be handled with the CT classes 
since cells containing anything other than paragraphs does not seem to be 
available yet. Also, every table cell must end with a paragraph. For now, when 
a table cell is created, a blank paragraph is added automatically. I don't 
really like this behavior, but given the requirement that a cell must contain 
at least one paragraph, and the last element in a cell must be a paragraph, I 
am going to have to adjust the code to deal with this in a more user friendly 
manner.

You will, of course, want to add loops, as appropriate to your situation, to 
minimize redundant code :)

Please ask these things on the list in the future so others can benefit from 
your questions.

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigInteger;
import java.util.List;

import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTBorder;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTP;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTR;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTRow;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTbl;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblBorders;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTc;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTText;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STBorder;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STTblWidth;

public class TableTest {

   /**
   * @param args
   */
   public static void main(String[] args) {
  // Blank Document
  XWPFDocument doc = new XWPFDocument();
  XWPFTable tbl = doc.createTable(2,2);
  XWPFTableCell cell;
  XWPFParagraph p;
  List pList;
  CTTbl ctTable;
  CTTblPr ctTPr;
  CTTblWidth ctTW;
  CTTblBorders ctBorders;
  CTBorder ctB;
  CTRow ctRow;
  CTTc ctCell;
  CTP ctP;
  CTR ctR;
  CTText ctTx;


  cell = tbl.getRow(0).getCell(0);
  pList = cell.getParagraphs();
  if (pList.size() == 0) {
 p = cell.addParagraph();
  }
  p = pList.get(0);
  p.createRun().setText("Cell 1, Row 1");

  cell = tbl.getRow(0).getCell(1);
  pList = cell.getParagraphs();
  if (pList.size() == 0) {
 p = cell.addParagraph();
  }
  p = pList.get(0);
  p.createRun().setText("Cell 2, Row 1");

  cell = tbl.getRow(1).getCell(0);
  pList = cell.getParagraphs();
  if (pList.size() == 0) {
 p = cell.addParagraph();
  }
  p = pList.get(0);
  p.createRun().setText("Cell 1, Row 2");

  cell = tbl.getRow(1).getCell(1);
  pList = cell.getParagraphs();
  if (pList.size() == 0) {
 p = cell.addParagraph();
  }
  p = pList.get(0);
  p.createRun().setText("Cell 2, Row 2");
  ctCell = cell.getCTTc();

RE: How to insert table inside tablerowcell using apache poi

[Murphy, Mark]

  ctTx = ctR.addNewT();
  ctTx.setStringValue("Cell 2, Row 1");
  ctRow = ctTable.addNewTr();
  ctCell = ctRow.addNewTc();
  ctP = ctCell.addNewP();
  ctR = ctP.addNewR();
  ctTx = ctR.addNewT();
  ctTx.setStringValue("Cell 1, Row 2");
  ctCell = ctRow.addNewTc();
  ctP = ctCell.addNewP();
  ctR = ctP.addNewR();
  ctTx = ctR.addNewT();
  ctTx.setStringValue("Cell 2, Row 2");

  // A paragraph has to be the last element in a table cell.
  p = cell.addParagraph();
  p.createRun().addCarriageReturn();


  // Write the Document in file system
  try {
 FileOutputStream out = new FileOutputStream(new File(
  "tabletest.docx"));
 doc.write(out);
 out.close();
 doc.close();
 System.out.println("tabletest.docx written successully");
  } catch (FileNotFoundException e) {
 e.printStackTrace();
  } catch (IOException e) {
 e.printStackTrace();
  }
   }

}


From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com]
Sent: Thursday, September 01, 2016 10:49 AM
To: Murphy, Mark 
Subject: RE: How to insert table inside tablerowcell using apache poi

I tried mark I am not getting exactly like this. Do you have any sample code 
snippet?

From: Murphy, Mark [mailto:murphym...@metalexmfg.com]
Sent: Thursday, September 01, 2016 7:03 PM
To: Krishna Vyas Majji
Subject: RE: How to insert table inside tablerowcell using apache poi

This is allowed, and should be possible.

From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com]
Sent: Thursday, September 01, 2016 7:45 AM
To: Murphy, Mark mailto:murphym...@metalexmfg.com>>
Subject: How to insert table inside tablerowcell using apache poi

Hi Mark,

Is it possible to create table inside cell using XWPF document
As shown below. Thanks in advance.


Cell 1, Row 1

Cell 2, Row 1

Cell 1, Row 2

Cell 2, Row 2

Cell 1, Row 1

Cell 2, Row 1

Cell 1, Row 2

Cell 2, Row 2





Regards,
Krishna
---Disclaimer-- This e-mail contains PRIVILEGED AND 
CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If 
you are not the intended recipient, please notify the sender by e-mail and 
delete the original message. Opinions, conclusions and other information in 
this transmission that do not relate to the official business of QuEST Global 
and/or its subsidiaries, shall be understood as neither given nor endorsed by 
it. Any statements made herein that are tantamount to contractual obligations, 
promises, claims or commitments shall not be binding on the Company unless 
followed by written confirmation by an authorized signatory of the Company. 
---
---Disclaimer-- This e-mail contains PRIVILEGED AND 
CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If 
you are not the intended recipient, please notify the sender by e-mail and 
delete the original message. Opinions, conclusions and other information in 
this transmission that do not relate to the official business of QuEST Global 
and/or its subsidiaries, shall be understood as neither given nor endorsed by 
it. Any statements made herein that are tantamount to contractual obligations, 
promises, claims or commitments shall not be binding on the Company unless 
followed by written confirmation by an authorized signatory of the Company. 
---

RE: how to split a cell vertically in XWPFTableCell

[Murphy, Mark]

I don't understand what you mean.

-Original Message-
From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com] 
Sent: Tuesday, August 23, 2016 10:45 AM
To: POI Users List 
Subject: how to split a cell vertically in XWPFTableCell

Hi Team,

How to split a cell vertically in XWPFTableCell?

Thanks,
Krishna
---Disclaimer-- This e-mail contains PRIVILEGED AND 
CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If 
you are not the intended recipient, please notify the sender by e-mail and 
delete the original message. Opinions, conclusions and other information in 
this transmission that do not relate to the official business of QuEST Global 
and/or its subsidiaries, shall be understood as neither given nor endorsed by 
it. Any statements made herein that are tantamount to contractual obligations, 
promises, claims or commitments shall not be binding on the Company unless 
followed by written confirmation by an authorized signatory of the Company. 
---

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

RE: Remove space before and after paragraph in table i.e. word document

[Murphy, Mark]

The problem is that when creating table, a row containing a cell containing a 
paragraph containing a blank run is already created. Those are the extra blanks 
you see. When you do cell.addParagraph(), you are adding a second paragraph. 
This is not really appropriate behavior, and is likely to change in the future 
as the Word API is worked through. However, you need to code to look for those 
empty parts in the table cell and just update then if you find them rather than 
automatically create new.

-Original Message-
From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com] 
Sent: Monday, August 22, 2016 3:07 AM
To: POI Users List 
Subject: Rmove space before and after paragraph in table i.e. word document

Hi Team,

Good morning,

I am adding a paragraph to a TableRowCell . But it is generating spaces before 
and after the paragraph. Please help me how to remove those extra blank lines.
This is my code snippet:


 //Blank Document
 XWPFDocument document= new XWPFDocument();

 //Write the Document in file system
 FileOutputStream out = new FileOutputStream(new 
File("create_table.docx"));

 //create table
 XWPFTable table = document.createTable();
 //create first row
 XWPFTableRow tableRowOne = table.getRow(0);
 XWPFParagraph paragraph00 = 
tableRowOne.getCell(0).addParagraph();
 XWPFRun cell00= paragraph00.createRun();
 cell00.setBold(true);
 cell00.setFontFamily("Lao UI");
 cell00.setText("Hello World");

 tableRowOne.addNewTableCell().setText("col two, row one");

 //create second row
 XWPFTableRow tableRowTwo = table.createRow();
 tableRowTwo.getCell(0).setText("col one, row two");
 tableRowTwo.getCell(1).setText("col two, row two");

 document.write(out);
 out.close();

Thanks,
Krishna


---Disclaimer-- This e-mail contains PRIVILEGED AND 
CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If 
you are not the intended recipient, please notify the sender by e-mail and 
delete the original message. Opinions, conclusions and other information in 
this transmission that do not relate to the official business of QuEST Global 
and/or its subsidiaries, shall be understood as neither given nor endorsed by 
it. Any statements made herein that are tantamount to contractual obligations, 
promises, claims or commitments shall not be binding on the Company unless 
followed by written confirmation by an authorized signatory of the Company. 
---

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

RE: How to add diagonal up border to a tablecell in word document using XWPF

[Murphy, Mark]

Ok, you were talking about Word. Word doesn't behave the way you want either, 
but I can have some text left aligned, and other text right aligned to simulate 
the effect you are looking for. I just have to manually ensure the text doesn't 
overlap the diagonal line.

-Original Message-
From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com] 
Sent: Friday, August 19, 2016 3:23 AM
To: POI Users List 
Subject: RE: How to add diagonal up border to a tablecell in word document 
using XWPF

Mark,

I think my attachment is not visible. I added "diagonal up border"  in "Row 
One, Column One" Cell.
Now I want to display first string in the top and second string in the bottom 
of the "diagonal up border".
Can you please help me on that.

Thanks and Regards,
Krishna

-Original Message-
From: Krishna Vyas Majji
Sent: Friday, August 19, 2016 12:10 PM
To: POI Users List
Subject: Bulk Mail : RE: How to add diagonal up border to a tablecell in word 
document using XWPF

Mark,

Does apache-poi  supports text to be display in the cell in the format I 
attached in the screen-shot.

Thanks,
Krishna

-Original Message-
From: Krishna Vyas Majji
Sent: Thursday, August 18, 2016 7:08 POk, You were talking about Word
To: POI Users List
Subject: Bulk Mail : RE: How to add diagonal up border to a tablecell in word 
document using XWPF

Mark,

Thanks a lot. Your code snippet is working.

Regards,
Krishna

-Original Message-
From: Murphy, Mark [mailto:murphym...@metalexmfg.com]
Sent: Thursday, August 18, 2016 6:48 PM
To: 'POI Users List'
Subject: Bulk Mail : RE: How to add diagonal up border to a tablecell in word 
document using XWPF

Ok, I didn't go all the way through it. You need to add attributes to the 
border.

CTTc ctTc = cell.getCTTc();
CTTcPr ctTcPr = ctTc.isSetTcPr() ? ctTc.getTcPr() : 
ctTc.addNewTcPr();
CTTcBorders ctTcBorders = ctTcPr.isSetTcBorders() ? 
ctTcPr.getTcBorders() : ctTcPr.addNewTcBorders();
CTBorder ctBorder = ctTcBorders.isSetTr2Bl() ? 
ctTcBorders.getTr2Bl() : ctTcBorders.addNewTr2Bl();
ctBorder.setVal(STBorder.Enum.forString("single"));
ctBorder.setSz(new BigInteger("1"));
ctBorder.setSpace(new BigInteger("0"));
ctBorder.setColor("FF");

This adds a thin red diagonal border.

-Original Message-
From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com]
Sent: Thursday, August 18, 2016 8:55 AM
To: POI Users List 
Subject: RE: How to add diagonal up border to a tablecell in word document 
using XWPF

Mark,

Thanks for the reply. I tried your code snippet but it didn't work. 
Alternatively I will go throw the reference to you provide i.e. " Office Open 
XML Part 4: Markup Language Reference, December 2006 (1st edition)"
My code snippet:

  //Blank Document
   XWPFDocument document= new XWPFDocument();

   //create table
   XWPFTable table = document.createTable();
   //create first row
   XWPFTableRow tableRowOne = table.getRow(0);
   tableRowOne.getCell(0).setText("");
   tableRowOne.addNewTableCell().setText("col two, row one");
   tableRowOne.addNewTableCell().setText("col three, row one");

  //create second row
   XWPFTableRow tableRowTwo = table.createRow();
   tableRowTwo.getCell(0).setText("col one, row two");
   tableRowTwo.getCell(1).setText("col two, row two");
   tableRowTwo.getCell(2).setText("col three, row two");

   //Diagonal Border
   XWPFTableCell cellRow00 = table.getRow(0).getCell(0);
   CTTc ctTc = cellRow00.getCTTc();
   CTTcPr ctTcPr = ctTc.isSetTcPr() ? ctTc.getTcPr() : 
ctTc.addNewTcPr();
   CTTcBorders ctTcBorders = ctTcPr.isSetTcBorders() ? 
ctTcPr.getTcBorders() : ctTcPr.addNewTcBorders();
   if (!ctTcBorders.isSetTr2Bl()) {
   ctTcBorders.addNewTr2Bl();
   }

   //Write the Document in file system
   FileOutputStream out = new FileOutputStream(new 
File("create_table_example.docx"));
   document.write(out);
   out.close();

Thanks,
Krishna

-Original Message-
From: Murphy, Mark [mailto:murphym...@metalexmfg.com]
Sent: Thursday, August 18, 2016 5:27 PM
To: 'POI Users List'
Subject: Bulk Mail : RE: How to add diagonal up border to a tablecell in word 
document using XWPF

Here is some code that works its way from the table cell ob

RE: How to add diagonal up border to a tablecell in word document using XWPF

[Murphy, Mark]

Excel doesn't do that. You can put a string in there, but it does not wrap 
around the border. You can simulate it by aligning the text to the top and 
left, then adding a couple newlines, and spacing the second text string out to 
the right edge of the cell. Try it in Excel first to see just what you have to 
do to get the text the way you want it. I can't even get a tab into the cell 
text using Excel.

-Original Message-
From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com] 
Sent: Friday, August 19, 2016 3:23 AM
To: POI Users List 
Subject: RE: How to add diagonal up border to a tablecell in word document 
using XWPF

Mark,

I think my attachment is not visible. I added "diagonal up border"  in "Row 
One, Column One" Cell.
Now I want to display first string in the top and second string in the bottom 
of the "diagonal up border".
Can you please help me on that.

Thanks and Regards,
Krishna

-Original Message-
From: Krishna Vyas Majji
Sent: Friday, August 19, 2016 12:10 PM
To: POI Users List
Subject: Bulk Mail : RE: How to add diagonal up border to a tablecell in word 
document using XWPF

Mark,

Does apache-poi  supports text to be display in the cell in the format I 
attached in the screen-shot.

Thanks,
Krishna

-Original Message-
From: Krishna Vyas Majji
Sent: Thursday, August 18, 2016 7:08 PM
To: POI Users List
Subject: Bulk Mail : RE: How to add diagonal up border to a tablecell in word 
document using XWPF

Mark,

Thanks a lot. Your code snippet is working.

Regards,
Krishna

-Original Message-
From: Murphy, Mark [mailto:murphym...@metalexmfg.com]
Sent: Thursday, August 18, 2016 6:48 PM
To: 'POI Users List'
Subject: Bulk Mail : RE: How to add diagonal up border to a tablecell in word 
document using XWPF

Ok, I didn't go all the way through it. You need to add attributes to the 
border.

CTTc ctTc = cell.getCTTc();
CTTcPr ctTcPr = ctTc.isSetTcPr() ? ctTc.getTcPr() : 
ctTc.addNewTcPr();
CTTcBorders ctTcBorders = ctTcPr.isSetTcBorders() ? 
ctTcPr.getTcBorders() : ctTcPr.addNewTcBorders();
CTBorder ctBorder = ctTcBorders.isSetTr2Bl() ? 
ctTcBorders.getTr2Bl() : ctTcBorders.addNewTr2Bl();
ctBorder.setVal(STBorder.Enum.forString("single"));
ctBorder.setSz(new BigInteger("1"));
ctBorder.setSpace(new BigInteger("0"));
ctBorder.setColor("FF");

This adds a thin red diagonal border.

-Original Message-
From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com]
Sent: Thursday, August 18, 2016 8:55 AM
To: POI Users List 
Subject: RE: How to add diagonal up border to a tablecell in word document 
using XWPF

Mark,

Thanks for the reply. I tried your code snippet but it didn't work. 
Alternatively I will go throw the reference to you provide i.e. " Office Open 
XML Part 4: Markup Language Reference, December 2006 (1st edition)"
My code snippet:

  //Blank Document
   XWPFDocument document= new XWPFDocument();

   //create table
   XWPFTable table = document.createTable();
   //create first row
   XWPFTableRow tableRowOne = table.getRow(0);
   tableRowOne.getCell(0).setText("");
   tableRowOne.addNewTableCell().setText("col two, row one");
   tableRowOne.addNewTableCell().setText("col three, row one");

  //create second row
   XWPFTableRow tableRowTwo = table.createRow();
   tableRowTwo.getCell(0).setText("col one, row two");
   tableRowTwo.getCell(1).setText("col two, row two");
   tableRowTwo.getCell(2).setText("col three, row two");

   //Diagonal Border
   XWPFTableCell cellRow00 = table.getRow(0).getCell(0);
   CTTc ctTc = cellRow00.getCTTc();
   CTTcPr ctTcPr = ctTc.isSetTcPr() ? ctTc.getTcPr() : 
ctTc.addNewTcPr();
   CTTcBorders ctTcBorders = ctTcPr.isSetTcBorders() ? 
ctTcPr.getTcBorders() : ctTcPr.addNewTcBorders();
   if (!ctTcBorders.isSetTr2Bl()) {
   ctTcBorders.addNewTr2Bl();
   }

   //Write the Document in file system
   FileOutputStream out = new FileOutputStream(new 
File("create_table_example.docx"));
   document.write(out);
   out.close();

Thanks,
Krishna

-Original Message-
From: Murphy, Mark [mailto:murphym...@metalexmfg.com]
Sent: Thursday, August 18, 2016 5:27 PM
To: 'POI Users List'
Subject: Bulk Mail : RE: How to add diagonal 

RE: How to add diagonal up border to a tablecell in word document using XWPF

[Murphy, Mark]

Ok, I didn't go all the way through it. You need to add attributes to the 
border.

CTTc ctTc = cell.getCTTc();
CTTcPr ctTcPr = ctTc.isSetTcPr() ? ctTc.getTcPr() : 
ctTc.addNewTcPr();
CTTcBorders ctTcBorders = ctTcPr.isSetTcBorders() ? 
ctTcPr.getTcBorders() : ctTcPr.addNewTcBorders();
CTBorder ctBorder = ctTcBorders.isSetTr2Bl() ? 
ctTcBorders.getTr2Bl() : ctTcBorders.addNewTr2Bl();
ctBorder.setVal(STBorder.Enum.forString("single"));
ctBorder.setSz(new BigInteger("1"));
ctBorder.setSpace(new BigInteger("0"));
ctBorder.setColor("FF");

This adds a thin red diagonal border.

-Original Message-
From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com] 
Sent: Thursday, August 18, 2016 8:55 AM
To: POI Users List 
Subject: RE: How to add diagonal up border to a tablecell in word document 
using XWPF

Mark,

Thanks for the reply. I tried your code snippet but it didn't work. 
Alternatively I will go throw the reference to you provide i.e. " Office Open 
XML Part 4: Markup Language Reference, December 2006 (1st edition)"
My code snippet:

  //Blank Document
   XWPFDocument document= new XWPFDocument();

   //create table
   XWPFTable table = document.createTable();
   //create first row
   XWPFTableRow tableRowOne = table.getRow(0);
   tableRowOne.getCell(0).setText("");
   tableRowOne.addNewTableCell().setText("col two, row one");
   tableRowOne.addNewTableCell().setText("col three, row one");

  //create second row
   XWPFTableRow tableRowTwo = table.createRow();
   tableRowTwo.getCell(0).setText("col one, row two");
   tableRowTwo.getCell(1).setText("col two, row two");
   tableRowTwo.getCell(2).setText("col three, row two");

   //Diagonal Border
   XWPFTableCell cellRow00 = table.getRow(0).getCell(0);
   CTTc ctTc = cellRow00.getCTTc();
   CTTcPr ctTcPr = ctTc.isSetTcPr() ? ctTc.getTcPr() : 
ctTc.addNewTcPr();
   CTTcBorders ctTcBorders = ctTcPr.isSetTcBorders() ? 
ctTcPr.getTcBorders() : ctTcPr.addNewTcBorders();
   if (!ctTcBorders.isSetTr2Bl()) {
   ctTcBorders.addNewTr2Bl();
   }

   //Write the Document in file system
   FileOutputStream out = new FileOutputStream(new 
File("create_table_example.docx"));
   document.write(out);
   out.close();

Thanks,
Krishna

-Original Message-
From: Murphy, Mark [mailto:murphym...@metalexmfg.com]
Sent: Thursday, August 18, 2016 5:27 PM
To: 'POI Users List'
Subject: Bulk Mail : RE: How to add diagonal up border to a tablecell in word 
document using XWPF

Here is some code that works its way from the table cell object - cell to add 
an up diagonal border:

CTTc ctTc = cell.getCTTc();
CTTcPr ctTcPr = ctTc.isSetTcPr() ? ctTc.getTcPr() : ctTc.addNewTcPr();
CTTcBorders ctTcBorders = ctTcPr.isSetTcBorders() ? 
ctTcPr.getTcBorders() : ctTcPr.addNewTcBorders();
if (!ctTcBorders.isSetTr2Bl()) {
ctTcBorders.addNewTr2Bl();
}

You will need to use the documentation found in Office Open XML Part 4: Markup 
Language Reference, December 2006 (1st edition)

-Original Message-
From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com]
Sent: Thursday, August 18, 2016 7:16 AM
To: user@poi.apache.org
Subject: How to add diagonal up border to a tablecell in word document using 
XWPF

Hello Team,

I created a table with 2 rows and 3 columns using XWPF. Now I want to know if I 
can add "diagonal up border" to a cell in my table.
Something like this.



col two, row one

col three, row one

col one, row two

col two, row two

col three, row two


My code looks like this.

 //Blank Document
 XWPFDocument document= new XWPFDocument();

 //Write the Document in file system
 FileOutputStream out = new FileOutputStream(new 
File("create_table_example.docx"));

 //create table
 XWPFTable table = document.createTable();

 //create first row
 XWPFTableRow tableRowOne = table.getRow(0);
 tableRowOne.getCell(0).setText("");
 tableRowOne.addNewTableCell().setText("col two, row one");
 tableRowOne.addNewTableCell().setText("col

RE: How to add diagonal up border to a tablecell in word document using XWPF

[Murphy, Mark]

Here is some code that works its way from the table cell object - cell to add 
an up diagonal border:

CTTc ctTc = cell.getCTTc();
CTTcPr ctTcPr = ctTc.isSetTcPr() ? ctTc.getTcPr() : ctTc.addNewTcPr();
CTTcBorders ctTcBorders = ctTcPr.isSetTcBorders() ? 
ctTcPr.getTcBorders() : ctTcPr.addNewTcBorders();
if (!ctTcBorders.isSetTr2Bl()) {
ctTcBorders.addNewTr2Bl();
}

You will need to use the documentation found in Office Open XML Part 4: Markup 
Language Reference, December 2006 (1st edition)

-Original Message-
From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com] 
Sent: Thursday, August 18, 2016 7:16 AM
To: user@poi.apache.org
Subject: How to add diagonal up border to a tablecell in word document using 
XWPF

Hello Team,

I created a table with 2 rows and 3 columns using XWPF. Now I want to know if I 
can add "diagonal up border" to a cell in my table.
Something like this.



col two, row one

col three, row one

col one, row two

col two, row two

col three, row two


My code looks like this.

 //Blank Document
 XWPFDocument document= new XWPFDocument();

 //Write the Document in file system
 FileOutputStream out = new FileOutputStream(new 
File("create_table_example.docx"));

 //create table
 XWPFTable table = document.createTable();

 //create first row
 XWPFTableRow tableRowOne = table.getRow(0);
 tableRowOne.getCell(0).setText("");
 tableRowOne.addNewTableCell().setText("col two, row one");
 tableRowOne.addNewTableCell().setText("col three, row one");

 //create second row
 XWPFTableRow tableRowTwo = table.createRow();
 tableRowTwo.getCell(0).setText("col one, row two");
 tableRowTwo.getCell(1).setText("col two, row two");
 tableRowTwo.getCell(2).setText("col three, row two");

 document.write(out);
 out.close();


Thanks,
Krishna

---Disclaimer-- This e-mail contains PRIVILEGED AND 
CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If 
you are not the intended recipient, please notify the sender by e-mail and 
delete the original message. Opinions, conclusions and other information in 
this transmission that do not relate to the official business of QuEST Global 
and/or its subsidiaries, shall be understood as neither given nor endorsed by 
it. Any statements made herein that are tantamount to contractual obligations, 
promises, claims or commitments shall not be binding on the Company unless 
followed by written confirmation by an authorized signatory of the Company. 
---

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

RE: How to add diagonal up border to a tablecell in word document using XWPF

[Murphy, Mark]

Diagonal borders have not been surfaced from the CT interface yet. You can do 
it, but you will have to manipulate the CT structures of the table directly.

-Original Message-
From: Krishna Vyas Majji [mailto:krishna.ma...@quest-global.com] 
Sent: Thursday, August 18, 2016 7:16 AM
To: user@poi.apache.org
Subject: How to add diagonal up border to a tablecell in word document using 
XWPF

Hello Team,

I created a table with 2 rows and 3 columns using XWPF. Now I want to know if I 
can add "diagonal up border" to a cell in my table.
Something like this.



col two, row one

col three, row one

col one, row two

col two, row two

col three, row two


My code looks like this.

 //Blank Document
 XWPFDocument document= new XWPFDocument();

 //Write the Document in file system
 FileOutputStream out = new FileOutputStream(new 
File("create_table_example.docx"));

 //create table
 XWPFTable table = document.createTable();

 //create first row
 XWPFTableRow tableRowOne = table.getRow(0);
 tableRowOne.getCell(0).setText("");
 tableRowOne.addNewTableCell().setText("col two, row one");
 tableRowOne.addNewTableCell().setText("col three, row one");

 //create second row
 XWPFTableRow tableRowTwo = table.createRow();
 tableRowTwo.getCell(0).setText("col one, row two");
 tableRowTwo.getCell(1).setText("col two, row two");
 tableRowTwo.getCell(2).setText("col three, row two");

 document.write(out);
 out.close();


Thanks,
Krishna

---Disclaimer-- This e-mail contains PRIVILEGED AND 
CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If 
you are not the intended recipient, please notify the sender by e-mail and 
delete the original message. Opinions, conclusions and other information in 
this transmission that do not relate to the official business of QuEST Global 
and/or its subsidiaries, shall be understood as neither given nor endorsed by 
it. Any statements made herein that are tantamount to contractual obligations, 
promises, claims or commitments shall not be binding on the Company unless 
followed by written confirmation by an authorized signatory of the Company. 
---

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

RE: Trouble building 3.15

[Murphy, Mark]

Is this a test he has created which is failing, or is it a current test that is 
already working for us?

-Original Message-
From: Andreas Beeker [mailto:kiwiwi...@apache.org] 
Sent: Monday, August 15, 2016 10:25 AM
To: POI Users List 
Subject: Re: Trouble building 3.15

>  [java] java.lang.AssertionError: Expected a column width greater 
> than
> 5000 but found 4971

At first glance, this looks like a font issue - why don't you simply decrease 
the assertion to something above 4900 instead of 5000?



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

RE: Trouble building 3.15

[Murphy, Mark]

In the past when I have had trouble with a build, it has been a configuration 
error in my environment. I use Eclipse for development and build, so that may 
be different, but you really didn't give us much to go on. Are you using an IDE 
or just plain Ant? 

-Original Message-
From: Matt Barron [mailto:mfb...@gmail.com] 
Sent: Monday, August 15, 2016 9:38 AM
To: user@poi.apache.org
Subject: Trouble building 3.15

Hi,
I am fairly new to building POI, but I have set up all of the dependencies on 
my machine and I have yet to have it build successfully.  So I am just looking 
for some pointers to get it kicked off.

Build environment information:
 [echo] Using Ant: Apache Ant(TM) version 1.9.7 compiled on April 9
2016 from /home/matt/build_tools/apache-ant-1.9.7, Ant detected Java 1.8 (may 
be different than actual Java sometimes...)
 [echo] Using Java: 1.8.0_101/1.8.0_101-b13/25.101-b13/OpenJDK 64-Bit 
Server VM from Oracle Corporation on Linux: 4.7.0-1-default
 [echo] Using Findbugs: 3.0.1 for Java 1.8.0_101


The compile target runs with no issue, but the test target fails with the 
following message under test-ooxml:
[junit] Running org.apache.poi.xssf.streaming.TestSXSSFSheetAutoSizeColumn
[junit] Tests run: 26, Failures: 1, Errors: 0, Skipped: 0, Time
elapsed: 0.149 sec

BUILD FAILED
/home/matt/javaLibraries/apache-poi/trunk/build.xml:1443: The following error 
occurred while executing this line:
/home/matt/javaLibraries/apache-poi/trunk/build.xml:1396: Tests failed

If I run the jar target, it similarly fails under compile-ooxml-lite with the 
following message:
 [java] Time: 113.927
 [java] There was 1 failure:
 [java] 1) test_WindowSizeEqualsOne_flushedRowHasMergedCell[0:
useMergedCells=false](org.apache.poi.xssf.streaming.TestSXSSFSheetAutoSizeColumn)
 [java] java.lang.AssertionError: Expected a column width greater than
5000 but found 4971
 [java] at org.junit.Assert.fail(Assert.java:88)
 [java] at org.junit.Assert.assertTrue(Assert.java:41)
 [java] at
org.apache.poi.xssf.streaming.TestSXSSFSheetAutoSizeColumn.assertColumnWidthStrictlyWithinRange(TestSXSSFSheetAutoSizeColumn.java:373)
 [java] at
org.apache.poi.xssf.streaming.TestSXSSFSheetAutoSizeColumn.test_WindowSizeEqualsOne_flushedRowHasMergedCell(TestSXSSFSheetAutoSizeColumn.java:216)
(...truncated lines...)
 [java] at org.apache.poi.util.OOXMLLite.build(OOXMLLite.java:129)
 [java] at org.apache.poi.util.OOXMLLite.main(OOXMLLite.java:88)
 [java]
 [java] FAILURES!!!
 [java] Tests run: 2022,  Failures: 1
 [java]
 [java] Exception in thread "main" java.lang.RuntimeException: Tests did 
not succeed, cannot build ooxml-lite jar
 [java] at org.apache.poi.util.OOXMLLite.build(OOXMLLite.java:131)
 [java] at org.apache.poi.util.OOXMLLite.main(OOXMLLite.java:88)


Thanks in advance,
Matt

RE: Merge Word docx into another Word dox via XWPFDocument - preserving formatting

[Murphy, Mark]

So what you need to do is end the run (and paragraph, and potentially table, 
row, and cell) then start inserting the new document, then reopen the 
table/paragraph/run with the original properties. You still have to be 
concerned with sections though because "Document B" will likely contain a 
section at the end which must be excluded, or you need to copy the section from 
"Document A" to the properties of the last paragraph before "Document B" is 
inserted, and move "Document B's" section to the last paragraph properties from 
that document. In this case you will have three sections: first half of 
"Document A", "Document B", and last half of "Document A". Any of these options 
must be done using the CT_ interface and XMLBeans as the features do not exist 
in the XWPF usermodel.

-Original Message-
From: Cory Newey [mailto:cory.ne...@gmail.com] 
Sent: Monday, August 08, 2016 11:11 AM
To: POI Users List 
Subject: Re: Merge Word docx into another Word dox via XWPFDocument - 
preserving formatting

Thanks for the replies.

Unfortunately, I don't just want to tack one document onto the end of another 
document. I want to search for a particular word/phrase in the first document 
and replace that with the entire contents of the second document. That means 
that if I find the text to be replaced inside of a particular run, I need to 
preserve all of the contents of the runs before that run, insert all of the 
contents from the merged-in document at the place where run that contains the 
text-to-be-replaced resides, and then preserve all of the contents of the runs 
that come after.

Thanks again.
~Cory


On Mon, Aug 8, 2016 at 5:19 AM, Murphy, Mark 
wrote:

> That depends on what you mean by merge. Are you just trying to append 
> document B onto the end of Document A, or are you trying to mix them 
> together in some specific way?
>
> Appending should be just a matter of reading through the document body 
> of Document B, and copying the elements to Document A. I wouldn't look 
> for just paragraphs, but all elements. That way you shouldn't lose anything.
> The main thing you will have to watch out for is sections. These 
> aren't handled well right now (ok, not handled at all). If there is 
> one section in the document, the section element will be found at the 
> end of the document, and must remain there. If there are multiple 
> sections, all but the last section element will be found in the last 
> paragraph properties element of the section.
>
> Just remember, the Word interface is still unstable, and subject to 
> significant changes. And there is still much that has to be 
> accomplished down in the weeds of the CT_ interfaces.
>
> -Original Message-
> From: Cory Newey [mailto:cory.ne...@gmail.com]
> Sent: Sunday, August 07, 2016 4:30 PM
> To: user@poi.apache.org
> Subject: Merge Word docx into another Word dox via XWPFDocument - 
> preserving formatting
>
> Hello All:
>
> I've tried to check the FAQ for this question and was unable to find 
> anything. I'm not sure if this should be a question for the developers'
> list; I couldn't really tell from the brief description of the various 
> lists. Anyway, I figured I'd ask the question here and you guys could 
> bounce me over to the developers' list if that is the appropriate 
> place for this question.
>
> I have written a program that will merge changes (replace certain 
> words with other words/phrases) into a Word (docx) document using 
> XWPFDocument.
> But now I want to replace words/sections of one Word document with an 
> entire other Word document. I've tried to update my program so that it 
> iterates through the paragraphs/runs of the document to be merged in. 
> It merges the text just fine but it loses all formatting, tables, etc. 
> I've googled the question and found a few Stack-Overflow posts that 
> talked about it, but nothing was of any use to me.
>
> My question is: is it possible to merge one Word document into another 
> Word document, such that it copies all formatting, tables, etc from 
> the merged-in document (minus any headers/footers - that would be a 
> little impossible I think) into the merged-into document - using the 
> XWPFDocument object?
>
> Thanks in advance for any help.
> ~Cory
>

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

RE: Merge Word docx into another Word dox via XWPFDocument - preserving formatting

[Murphy, Mark]

That depends on what you mean by merge. Are you just trying to append document 
B onto the end of Document A, or are you trying to mix them together in some 
specific way?

Appending should be just a matter of reading through the document body of 
Document B, and copying the elements to Document A. I wouldn't look for just 
paragraphs, but all elements. That way you shouldn't lose anything. The main 
thing you will have to watch out for is sections. These aren't handled well 
right now (ok, not handled at all). If there is one section in the document, 
the section element will be found at the end of the document, and must remain 
there. If there are multiple sections, all but the last section element will be 
found in the last paragraph properties element of the section.

Just remember, the Word interface is still unstable, and subject to significant 
changes. And there is still much that has to be accomplished down in the weeds 
of the CT_ interfaces.

-Original Message-
From: Cory Newey [mailto:cory.ne...@gmail.com] 
Sent: Sunday, August 07, 2016 4:30 PM
To: user@poi.apache.org
Subject: Merge Word docx into another Word dox via XWPFDocument - preserving 
formatting

Hello All:

I've tried to check the FAQ for this question and was unable to find anything. 
I'm not sure if this should be a question for the developers'
list; I couldn't really tell from the brief description of the various lists. 
Anyway, I figured I'd ask the question here and you guys could bounce me over 
to the developers' list if that is the appropriate place for this question.

I have written a program that will merge changes (replace certain words with 
other words/phrases) into a Word (docx) document using XWPFDocument.
But now I want to replace words/sections of one Word document with an entire 
other Word document. I've tried to update my program so that it iterates 
through the paragraphs/runs of the document to be merged in. It merges the text 
just fine but it loses all formatting, tables, etc. I've googled the question 
and found a few Stack-Overflow posts that talked about it, but nothing was of 
any use to me.

My question is: is it possible to merge one Word document into another Word 
document, such that it copies all formatting, tables, etc from the merged-in 
document (minus any headers/footers - that would be a little impossible I 
think) into the merged-into document - using the XWPFDocument object?

Thanks in advance for any help.
~Cory

RE: Reading xlsx file using poi3.7

[Murphy, Mark]

In addition, you are going to have to upgrade your Java as POI 3.14 requires 
Java 1.6.

-Original Message-
From: Nick Burch [mailto:apa...@gagravarr.org] 
Sent: Tuesday, June 14, 2016 6:59 AM
To: POI Users List 
Subject: Re: Reading xlsx file using poi3.7

On Tue, 14 Jun 2016, pavanikureti wrote:
> I will change the poi jars with the suggested version. I am using few 
> more jars also as below. javax.xml.stream-1.0.1, xmlbeans-2.3.0.jar, 
> dom4j.jar, ooxml-schemas-1.0.jar Are these fine?

See http://poi.apache.org/overview.html#components for the details of what 
dependent jars are required by the various POI components. (Some of your jars 
aren't needed, some need upgrading, the components page will tell you
what)

Or switch to something like Gradle or Maven, which takes care of all of this 
sort of stuff for you!

Nick

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


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

RE: CTLongHexNumber

[Murphy, Mark]

I just thought of that. It is the small one.

-Original Message-
From: Nick Burch [mailto:apa...@gagravarr.org] 
Sent: Thursday, June 02, 2016 3:02 PM
To: POI Users List
Subject: Re: CTLongHexNumber

On Thu, 2 Jun 2016, Murphy, Mark wrote:
> Is there a way to use CTLongHexNumber without it causing a type cannot 
> be resolved error?
>
> The error is: The type
> org.openxmlformats.schemas.wordprocessingml.x2006.main.CTLongHexNumber
> cannot be resolved. It is indirectly referenced from required .class 
> files.

Are you using the full ooxml-schemas file, or the small poi-ooxml one? (If so, 
see the faq for the fix!)

Nick

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


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

CTLongHexNumber

[Murphy, Mark]

Is there a way to use CTLongHexNumber without it causing a type cannot be 
resolved error?

The error is: The type 
org.openxmlformats.schemas.wordprocessingml.x2006.main.CTLongHexNumber cannot 
be resolved. It is indirectly referenced from required .class files.

I don't think this is an eclipse bug as the file won't compile either.

This is not a classpath error as 
org.openxmlformats.schemas.wordprocessingml.x2006.main.CTDecimalNumber is fine.

RE: Difference of getLastRowNum() and getPhysicalNumberOfRows()

[Murphy, Mark]

getLastRowNum() retrieves the index of the last row in a spreadsheet. These 
indexes are 0 based, and if you have no missing rows, row 1104 the last row 
will have index 1103. getPhysicalNumberOfRows() returns the number of rows 
actually used in the spreadsheet. Unused rows are just missing from the 
spreadsheet. So you could have a situation where you only have one row at row 
1104. In that case getPhysicalNumberOfRows() would return 1, but 
getLastRowNum() would return 1103.

-Original Message-
From: Michael [mailto:michae...@electronicscience.com] 
Sent: Wednesday, June 01, 2016 2:50 AM
To: POI Users List
Subject: Difference of getLastRowNum() and getPhysicalNumberOfRows()

Hi All,

Would like to ask the difference between getLastRowNum() and
getPhysicalNumberOfRows() on a XSSFSheet.
Both method returns different number of row.

I have an Excel file with 1103 number of row. Using getLastRowNum() returns 
1103 and using getPhysicalNumberOfRows() returns 1104.

Regards,
Michael.A

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


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

RE: Display embedded files on the document (.docx)

[Murphy, Mark]

I don't know the answer to your question, but what happens when you try to 
embed the same document using Word? What does the XML look like? What does the 
XML look like when you try to embed it with POI? Give us a minimal example of 
the problem so we can easily see what is happening. Then someone may be able to 
help you.

-Original Message-
From: marc yet [mailto:deflot@gmail.com] 
Sent: Wednesday, May 25, 2016 11:15 AM
To: user@poi.apache.org
Subject: Re: Display embedded files on the document (.docx)

Up
does someone have a solution ? I'm really stuck. :/

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

RE: Unable to understand how to merge Rows in POI

[Murphy, Mark]

You are not calling the merge method after your for loop when finalIndex > 0.

Immediately following the for loop you need to add:
If (finalIndex > 0) {
sendRangeToMergeCells(startIndex, finalIndex, sheet);
}

-Original Message-
From: sunnykeerthi [mailto:sunnykeer...@gmail.com] 
Sent: Tuesday, May 24, 2016 5:04 AM
To: user@poi.apache.org
Subject: Unable to understand how to merge Rows in POI

Hi,

I'm writing a program where I need to merge rows in Excel sheet. Currently, I'm 
able to merge the starting rows, but when coming to the end, I'm unable to know 
where it is going wrong. Below is my code.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class RowsMerge {

public static void main(String[] args) throws IOException {
FileInputStream fin = new FileInputStream(
new File("C:\\D\\Sheets\\Sample Sheets\\dummy.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fin);
HSSFSheet sheet = workbook.getSheetAt(0);
int row = sheet.getPhysicalNumberOfRows();
String currentLawName, currentCountry, currentAssociate, 
previousLawName, previousCountry, previousAssociate;
String currentPages, previousPages;
int startIndex = 1, finalIndex = 0, tempNum = 0;
System.out.println(row);
for (int i = 2; i < (row - 1); i++) {
currentAssociate = sheet.getRow(i).getCell(0).toString();
currentLawName = sheet.getRow(i).getCell(1).toString();
currentCountry = sheet.getRow(i).getCell(2).toString();
currentPages = sheet.getRow(i).getCell(3).toString();

previousAssociate = sheet.getRow(i - 1).getCell(0).toString();
previousLawName = sheet.getRow(i - 1).getCell(1).toString();
previousCountry = sheet.getRow(i - 1).getCell(2).toString();
previousPages = sheet.getRow(i - 1).getCell(3).toString();

if (currentAssociate.equals(previousAssociate) &&
currentCountry.equals(previousCountry)
&& currentLawName.equals(previousLawName) &&
currentPages.equals(previousPages)) {
finalIndex += 1;
} else {
sendRangeToMergeCells(startIndex, finalIndex, sheet);
startIndex = i;
finalIndex = 0;

}

}
FileOutputStream fileOut = new
FileOutputStream("C:\\D\\Sheets\\Sample Sheets\\dummy.xls");
workbook.write(fileOut);
fileOut.close();
}

private static void sendRangeToMergeCells(int startIndex, int finalIndex, 
HSSFSheet sheet) {
System.out.println(startIndex + "\t" + (startIndex + finalIndex));
CellRangeAddress region = CellRangeAddress
.valueOf("D" + (startIndex + 1) + ":D" + ((startIndex +
finalIndex) + 1));
sheet.addMergedRegion(region);
}

}
Below is my Excel Sheet
SourceExcel:

 

Current O/P: 

Expected o/p: 

 


please let me know where am I going wrong and how can I fix this.

Thanks



--
View this message in context: 
http://apache-poi.1045710.n5.nabble.com/Unable-to-understand-how-to-merge-Rows-in-POI-tp5723107.html
Sent from the POI - User mailing list archive at Nabble.com.

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


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

RE: How to "clone" values of a workbook

[Murphy, Mark]

Hmm... I'm not real sure what you mean by "read the data from excel and 
transform them to an output format" are you using POI or an alternate tool to 
do that? It seems to me that this might be the bottleneck rather than the 
create the workbook part. But if "create a new instance of the workbook" is the 
problem, maybe you just need to preload some workbooks, and when you use one, 
spawn a thread to load another from the database. That way you don't have to go 
through the trouble of doing a reset, and the create happens before the request 
is made. I am saying this based on the assumption that your pooling solution 
eventually has all the documents loaded in the pool at least once. If this is 
the case you might as well preload them and avoid the long wait for the 
requester of the first document of each type.

-Original Message-
From: Kai Grabfelder [mailto:nos...@kaigrabfelder.de] 
Sent: Wednesday, May 11, 2016 6:05 PM
To: POI Users List
Subject: Re: How to "clone" values of a workbook

Hi Mark,

in our system we have a limited number of workbooks that we mainly use for 
calculation. The process withouth pooling looks like this

- create a new instance of the workbook (one of about 20 different workbook 
stored in the database)
- fill in some data (entered by the user)
- recalculate formula (incl. creating a new formula evaluator)
- read the data from excel and transform them to an output format (the workbook 
in the database is not changed it's kind of a template and the main purpose of 
poi/excel is to perform calculations)

The creation of the workbook takes 200ms till 15 seconds which is too slow for 
the required use case (it's online processing in a web application).

The process with pooling looks like this:

- ask the pool to either acquire an instance of the workbook
- the pool checks if there is an idle instance of the workbook available in the 
pool
-- if not a new instance of the workbook (and the formula evaluator) is created 
and put in the pool
-- if yes the idle instance is returned
- fill in some data (entered by the user)
- recalculate formula (without creating a new formula evaluator
- read the data from excel and transform them to an output format
- ==> "reset" the instance to it's state when acquired from the pool <== I'm 
currently thinking how to implement this - I guess I'll have to "remember" all 
changes to the workbook and "revert" them at this step.
- return the instance to the pool

I think SXSSF is not an option as imho it doesn't support formula evaluation. 
In any way memory is not a big constraint here - the issue is the time that the 
system requires for instantiating the workbook instances.

Best Regards

Kai

Murphy, Mark schrieb am 11.05.16 um 16:19:
> I am trying to understand just what it is you are trying to do
> 
>>> because the creation of workbook instances is pretty costly (about 200ms to 
>>> 15 seconds for the workbooks we are dealing with) we implemented a pooling 
>>> of workbook instances which seems to work pretty well.
> 
> What do you mean by this? How does it work?
> 
>>> Since we are only performing limited write-operations on the workbook 
>>> instances (basically only calling Cell.setCellType() and 
>>> Cell.setCellValue()) we need a way of "cloning" the original workbook 
>>> values when retrieving a workbook instance from the pool, remembering them 
>>> and "reseting" the workbook instance when returning it to the pool by 
>>> setting all those remembered values to the workbook instance.
> 
> Are you holding a group of workbooks in memory, then using them as templates 
> to write new workbooks with changed values? Or are you changing the values 
> and saving it with a new file name?
> 
>>> I'm wondering if there is already a functionality like this in poi or if 
>>> somebody already did sth. similar? Otherwhise I'll have to write sth. by my 
>>> own (which shouldn't be too hard) - but I thought that I'll ask anyway.
> 
>>> Performing a deep clone of the complete workbook is not an option as this 
>>> would make the whole pooling idea pointless.
> 
> It may be possible to write your new workbook using SXSSF which does not hold 
> everything in memory so long. You can specify how many rows to keep in memory 
> to help control the amount of memory used.
> 
> 
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional 
> commands, e-mail: user-h...@poi.apache.org
> 
> 


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


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

RE: How to "clone" values of a workbook

[Murphy, Mark]

I am trying to understand just what it is you are trying to do

>> because the creation of workbook instances is pretty costly (about 200ms to 
>> 15 seconds for the workbooks we are dealing with) we implemented a pooling 
>> of workbook instances which seems to work pretty well.

What do you mean by this? How does it work?

>> Since we are only performing limited write-operations on the workbook 
>> instances (basically only calling Cell.setCellType() and 
>> Cell.setCellValue()) we need a way of "cloning" the original workbook values 
>> when retrieving a workbook instance from the pool, remembering them and 
>> "reseting" the workbook instance when returning it to the pool by setting 
>> all those remembered values to the workbook instance.

Are you holding a group of workbooks in memory, then using them as templates to 
write new workbooks with changed values? Or are you changing the values and 
saving it with a new file name?

>> I'm wondering if there is already a functionality like this in poi or if 
>> somebody already did sth. similar? Otherwhise I'll have to write sth. by my 
>> own (which shouldn't be too hard) - but I thought that I'll ask anyway.

>> Performing a deep clone of the complete workbook is not an option as this 
>> would make the whole pooling idea pointless.

It may be possible to write your new workbook using SXSSF which does not hold 
everything in memory so long. You can specify how many rows to keep in memory 
to help control the amount of memory used.


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

RE: SSPerformanceTest: Is the FAQ still accurate?

[Murphy, Mark]

XSSF is an XML document. Given that XML is generally about 70-80% overhead vs. 
data, it is not surprising that binary spreadsheets (which can be optimized, 
and have very little overhead) are more memory efficient. In addition, XML must 
be parsed, but binary documents can frequently be accessed using pointers and 
data structures. That gives the binary formats a performance edge, which can be 
significant. I'm not sure how Microsoft handles spreadsheets internally, but 
maybe they keep an internal binary format, and then write it to whatever format 
is requested on save rather than using an internal XML representation for an 
XML spreadsheet, which I what POI is doing.

-Original Message-
From: Jack of Shadows [mailto:somerandomlo...@gmail.com] 
Sent: Monday, April 11, 2016 7:46 AM
To: POI Users List
Subject: Re: SSPerformanceTest: Is the FAQ still accurate?

XSSF is basically unusable. 25000 or 5 isn't that many rows. Memory 
consumption is pretty high too.
That's really confusing, I wouldn't have been surprised if HSSF performed 
poorly -- but it actually works better.
Ohh well, whatever, I guess I'd have to use SXSSF instead.

On Mon, Apr 11, 2016 at 12:04 AM, Dominik Stadler 
wrote:

> Hi,
>
> Not sure which exact machine spec the information in the FAQ is based 
> on, maybe there is something that can have quite a big influence on 
> runtime of this sample for XSSF, e.g. which actual JDK is used, 
> Linux/Windows, ... ?!
>
> I did a quick run of it across various versions of POI to see if we 
> degraded performance at some point, but for me it rather was always 
> this way, i.e. HSSF very quick, SXSSF fairly quick (with being very 
> slow in early releases) and XSSF quite a bit slower, maybe we need to 
> adjust the FAQ entry some more here to set correct expectations?
>
> (Exact numbers here are not that relevant as I used my 6+ year old 
> laptop where I was doing other things at the same time, albeit no CPU 
> intensive things, JVM was Sun 6.0, Linux Ubuntu, 25000 rows, 25 cols)
>
>
> latest-2016-04-10:
>
> Elapsed 2 seconds
> Elapsed 15 seconds
> Elapsed 5 seconds
>
>
> 2014-03-22 (the FAQ-Entry was added)
>
> Elapsed 1 seconds
> Elapsed 14 seconds
> Elapsed 3 seconds
>
>
> 3.10:
>
> Elapsed 2 seconds
> Elapsed 14 seconds
> Elapsed 3 seconds
>
>
> 3.9:
>
> Elapsed 1 seconds
> Elapsed 12 seconds
> Elapsed 3 seconds
>
>
> 3.8:
>
> Elapsed 2 seconds
> Elapsed 15 seconds
> Elapsed 3 seconds
>
>
> initial checkin of SSPerformanceTest:
>
> Elapsed 1 seconds
> Elapsed 14 seconds
> Elapsed 47 seconds
>
>
> Dominik.
>
>
>
>
> On Sun, Apr 10, 2016 at 5:59 PM, Jack  wrote:
>
> > I'm having the exact same issue, I've tracked down this message from 
> > StackOverflow.
> > I've tested read performance on two XLS and XLSX with identical 
> > content (around 75000 rows, 25 columns).
> > HSSF takes under 5 sec; XSSF takes 15-20 sec.
> >
> > Any idea what is the issue with XSSF performance?
> >
> >
> > On 15.02.2016 17:00, Drew Spencer wrote:
> >
> >> Mike DeHaan  mikeandzoya.com> writes:
> >>
> >> As a followup, a user has replied to my stack overflow post with 
> >> some
> >>> information that might be helpful in tracking this issue down. 
> >>> Here is
> >>>
> >> the
> >>
> >>> link to his post:
> >>>
> >>> http://stackoverflow.com/a/34266795/4471563
> >>>
> >>> I ran the same tests in my environments and came up with similar
> >>>
> >> numbers.
> >>
> >>> -Mike DeHaan
> >>>
> >>> I have also asked the same question. Would love to get an answer 
> >>> to
> this
> >> either way. My similar post on StackOverflow is here:
> >> http://stackoverflow.com/questions/34995058/apache-poi-much-quicker
> >> -
> >> using-hssf-than-xssf-what-next
> >>
> >> I received an good answer with the link to the streaming reader, 
> >> but unfortunately I don't think I can use it because my code runs 
> >> on app engine.
> >>
> >> Thanks to anyone that can help.
> >>
> >> Drew Spencer
> >>
> >>
> >> ---
> >> -- To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For 
> >> additional commands, e-mail: user-h...@poi.apache.org
> >>
> >>
> >>
> >
> > 
> > - To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For 
> > additional commands, e-mail: user-h...@poi.apache.org
> >
> >
>

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

RE: How to open word document in android studio

[Murphy, Mark]

This all makes sense up until the point that you say: However this code is 
working in eclipse... 

What code is working in eclipse? The app you are trying to create? Code you 
wrote (using the POI API) to retrieve the contents of a word doc and print it 
to the console? Or are you talking about going into eclipse, finding the 
document, and opening it from the project tree like you would open a source 
file? If the latter, then it looks like Eclipse is actually using Word as an 
external editor to open the document, and rendering it in a window within the 
eclipse workspace. You are going to have to talk to the Android Studio guys to 
see how Android Studio handles that, if indeed Android Studio is able to use 
Word as an external editor at all. I would be willing to bet that this only 
works on Windows for Eclipse, though Eclipse may be able to use some other 
editor like Open Office on Linux.

So bottom line, what exactly are you looking for? Such as: I am doing _, or 
I am executing the following code: __, and  
happens, but I expect __ to happen. Be as specific as you can. 
Include code or navigation paths. Then maybe someone here can help you.

-Original Message-
From: Pratibha Mache [mailto:pratibha_ma...@symantec.com] 
Sent: Tuesday, March 15, 2016 11:59 PM
To: user@poi.apache.org
Subject: How to open word document in android studio

Thank you for your quick response!
Yes i'm trying to create an app, which actually view the doc file contents.
But,as below you said that Apache POI doesn't provide any library to  view the 
doc file.
So,now I'm trying to only read the doc file content and print on console in 
android studio version 1.5.
I want this to work in android studio version 1.5, but whenever I'm trying it 
is giving me an error which I am not able to understand.
However this code is working in eclipse so maybe this problem is specific to 
android studio. I just want to know if this library is compatible with android 
studio or only eclipse.
If it is compatible with android studio then please tell me the steps for 
reading the doc file. Awaiting your response.

Thanking you,
Yours faithfully,
Pratibha Mache



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

RE: [Bug 59106] - WorkdayFunction does not read correctly the area with holidays to calculate work days

[Murphy, Mark]

You also need to create unit tests that validate the patch, and possibly also 
documentation.

From: Артем Бондарь [mailto:bonda...@gmail.com]
Sent: Friday, March 11, 2016 2:46 AM
To: user@poi.apache.org
Subject: [Bug 59106] - WorkdayFunction does not read correctly the area with 
holidays to calculate work days

Hi Team,

I have found issue in the apache poi (3.13 - Final) and raised the ticket (Bug 
59106) .
Also, I have created patch (attached).

Could you please have a look at the ticket and advice, what should I do next?
Should I rename ticket to [PATCH] TICKET NAME and attach patch there?
Or any other steps are required?

Thanks,
--
Artem Bondar
fatalyt11989 | skype

RE: [Bug 59106] - WorkdayFunction does not read correctly the area with holidays to calculate work days

[Murphy, Mark]

Yes, rename the ticket and attach the patch there.

From: Артем Бондарь [mailto:bonda...@gmail.com]
Sent: Friday, March 11, 2016 2:46 AM
To: user@poi.apache.org
Subject: [Bug 59106] - WorkdayFunction does not read correctly the area with 
holidays to calculate work days

Hi Team,

I have found issue in the apache poi (3.13 - Final) and raised the ticket (Bug 
59106) .
Also, I have created patch (attached).

Could you please have a look at the ticket and advice, what should I do next?
Should I rename ticket to [PATCH] TICKET NAME and attach patch there?
Or any other steps are required?

Thanks,
--
Artem Bondar
fatalyt11989 | skype

RE: Ability to set solid color on DataBarFormatting

[Murphy, Mark]

I asked if anyone could tell me how to do a gradient fill a couple months ago. 
I do not believe the API supports it yet.

-Original Message-
From: Maheshwar Jayaraman [mailto:maheshwar_...@hotmail.com] 
Sent: Tuesday, January 26, 2016 1:14 PM
To: user@poi.apache.org
Subject: Ability to set solid color on DataBarFormatting

In using XSSFSheet and programmatically setting a conditional formatting rule 
with DataBar on a range of cells, the resulting excel always has a gradient for 
the databar. I have searched long and hard and have been unable to really see 
API level support for this. Has there been any asks for this in the past? I 
would like to attempt an patch if this can have more wide spread use.

Mahesh 

RE: CellStyle Help

[Murphy, Mark]

Very close,

First create your workbook, then create your base styles that include your 
fonts and basic styles. Then when you create a cell you apply the style you 
want to it.

Workbook wb = WorkbookFactory.create(new File(""));

CellStyle cs1 = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();

I usually have about a dozen of them: Title, SubTitle, Headers, left adjusted 
text, centered text, right adjusted numbers (a style for each number format I 
will be using), centered numbers, dates, and maybe a few others.

After creating a cell, I add the appropriate style: 

Sheet s = wb.CreateSheet("Sheet1");
Row r = s.createRow(0);
Cell c = r.createCell(0);
c.setStyle(cs1);

Javen pointed you to CellUtil. There are some methods in there to help you 
apply the same style to a bunch of cells. I usually do borders that way. You 
can use CellUtil.setCellProperty, and coming soon setCellProperties so that you 
don't have to set them one at a time (which adds a bunch of unused CellStyles 
to your spreadsheet).

-Original Message-
From: Melody [mailto:475573...@qq.com] 
Sent: Friday, January 22, 2016 4:16 AM
To: user
Subject: CellStyle Help

Hi !
Thank you very much for creating such a wonderful project of Apache POI which 
helps a lot. And I really appreciate it.
However, I have encountered a problem when using CellStyle.
At present, to create a CellStyle, the common way is:
Workbook wb = WorkbookFactory.create(new File("")); CellStyle cellStyle = 
wb.createCellStyle(); // first create a Workbook then create a CellStyle


That means, all cells share the same CellStyle. So the problem is:
How can I set different CellStyle for different Cells ?
Say,
CellStyle cellStyle1;
CellStyle cellStyle2;
cell1.setCellStyle(cellStyle1);
cell2.setCellStyle(cellStyle2);


Very much appreciated for your reply.

 Yours Sincerely

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

RE: Documentation for XSSF

[Murphy, Mark]

I don't know. I will have to think on this one.

-Original Message-
From: Javen O'Neal [mailto:javenon...@gmail.com] 
Sent: Wednesday, December 30, 2015 4:09 AM
To: POI Users List
Subject: Re: Documentation for XSSF

Mark,

Is this a request to modify the website documentation, comments or java docs in 
the source code, or Java code/functionality, or unit test behavior, or a 
combination thereof? I'm a bit unclear what needs done here. Could you open a 
bug and if you have a suggestion for how to fix the bug, attach a patch?

The website documentation is generated from xml sources (don't worry about 
providing html diffs) 
https://svn.apache.org/viewvc/poi/site/src/documentation/content/xdocs/

The Java code for XSSFWorkbooks is
https://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/
and https://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/ss/

The unit test code for XSSFWorkbooks is
https://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/
and 
https://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/ss/

Thanks for helping and happy holidays!
Javen

On Fri, Dec 18, 2015 at 10:14 AM, Murphy, Mark  
wrote:
> Wow, that got totally hosed. Not sure where all those  references came 
> from, but here it is without them.
>
> -----Original Message-
> From: Murphy, Mark [mailto:murphym...@metalexmfg.com]
> Sent: Friday, December 18, 2015 9:48 AM
> To: user@poi.apache.org
> Subject: Documentation for XSSF
>
> Excel has an inconsistency in its fills. A solid fill defined for a cell 
> needs a foreground color:
>
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
> This first fill shows in Excel as a cell with a yellow background, the second 
> shows in excel with a white background (neither have borders). In the excel 
> Format Cells dialog, the first happens by selecting Yellow as the background 
> color, and leaving Pattern Color and Style alone, the second fill shows No 
> Color for the background color with Yellow Pattern Color and blank Pattern 
> Style.
>
> In the Conditional Formatting space, however, the resulting xml and behavior 
> is reversed for patternType='solid':
>
> 
> 
> 
>
> 
> 
> 
>
> This was arrived at in the Format Cells dialog by selecting Yellow as the 
> Background Color, and leaving Pattern Color and Style alone. Note that 
> bgColor is set here instead of Foreground color. With testing, it appears 
> that in almost all instances bgColor corresponds with Background Color in the 
> dialog, and fgColor corresponds with Pattern Color. The only exception is the 
> initial example. It should be documented somewhere that 
> setFillBackgroundColor sets the background color of the cell, and 
> setForegroundColor sets the color of the pattern except when the fill is 
> applied to a CellStyle and the pattern is SOLID_FOREGROUND. That being the 
> case SOLID_FOREGROUND is somewhat a misnomer when dealing with Conditional 
> Formats as you need to set the background color not the foreground color when 
> you select pattern SOLID_FOREGROUND. I don't know whether the solution is to 
> add a new constant such as SOLID that exhibits the appropriate bug or just to 
> document the issue so that folks using it know to use setForegroundColor when 
> it applies to CellStyle and SOLID_FOREGROUND, and setBackgroundColor at all 
> other times.
>
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional 
> commands, e-mail: user-h...@poi.apache.org
>

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

RE: Documentation for XSSF

[Murphy, Mark]

Wow, that got totally hosed. Not sure where all those  references came 
from, but here it is without them.

-Original Message-
From: Murphy, Mark [mailto:murphym...@metalexmfg.com] 
Sent: Friday, December 18, 2015 9:48 AM
To: user@poi.apache.org
Subject: Documentation for XSSF

Excel has an inconsistency in its fills. A solid fill defined for a cell needs 
a foreground color:














This first fill shows in Excel as a cell with a yellow background, the second 
shows in excel with a white background (neither have borders). In the excel 
Format Cells dialog, the first happens by selecting Yellow as the background 
color, and leaving Pattern Color and Style alone, the second fill shows No 
Color for the background color with Yellow Pattern Color and blank Pattern 
Style.

In the Conditional Formatting space, however, the resulting xml and behavior is 
reversed for patternType='solid':




   




This was arrived at in the Format Cells dialog by selecting Yellow as the 
Background Color, and leaving Pattern Color and Style alone. Note that bgColor 
is set here instead of Foreground color. With testing, it appears that in 
almost all instances bgColor corresponds with Background Color in the dialog, 
and fgColor corresponds with Pattern Color. The only exception is the initial 
example. It should be documented somewhere that setFillBackgroundColor sets the 
background color of the cell, and setForegroundColor sets the color of the 
pattern except when the fill is applied to a CellStyle and the pattern is 
SOLID_FOREGROUND. That being the case SOLID_FOREGROUND is somewhat a misnomer 
when dealing with Conditional Formats as you need to set the background color 
not the foreground color when you select pattern SOLID_FOREGROUND. I don't know 
whether the solution is to add a new constant such as SOLID that exhibits the 
appropriate bug or just to document the issue so that folks using it know to 
use setForegroundColor when it applies to CellStyle and SOLID_FOREGROUND, and 
setBackgroundColor at all other times.

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

Documentation for XSSF

[Murphy, Mark]

Excel has an inconsistency in its fills. A solid fill defined for a cell needs 
a foreground color:














This first fill shows in Excel as a cell with a yellow background, the second 
shows in excel with a white background (neither have borders). In the excel 
Format Cells dialog, the first happens by selecting Yellow as the background 
color, and leaving Pattern Color and Style alone, the second fill shows No 
Color for the background color with Yellow Pattern Color and blank Pattern 
Style.

In the Conditional Formatting space, however, the resulting xml and behavior is 
reversed for patternType='solid':





   




This was arrived at in the Format Cells dialog by selecting Yellow as the 
Background Color, and leaving Pattern Color and Style alone. Note that bgColor 
is set here instead of Foreground color. With testing, it appears that in 
almost all instances bgColor corresponds with Background Color in the dialog, 
and fgColor corresponds with Pattern Color. The only exception is the initial 
example. It should be documented somewhere that setFillBackgroundColor sets the 
background color of the cell, and setForegroundColor sets the color of the 
pattern except when the fill is applied to a CellStyle and the pattern is 
SOLID_FOREGROUND. That being the case SOLID_FOREGROUND is somewhat a misnomer 
when dealing with Conditional Formats as you need to set the background color 
not the foreground color when you select pattern SOLID_FOREGROUND. I don't know 
whether the solution is to add a new constant such as SOLID that exhibits the 
appropriate bug or just to document the issue so that folks using it know to 
use setForegroundColor when it applies to CellStyle and SOLID_FOREGROUND, and 
setBackgroundColor at all other times.

RE: Drawing Borders is SLOW

[Murphy, Mark]

Would you prefer just overriding setCellStyleProperty with a secondary set of 
parameters, or creating a new method named setCellStyleProperties? In either 
case, setCellStyleProperty would change to call the new method for a single 
property.

-Original Message-
From: Nick Burch [mailto:apa...@gagravarr.org] 
Sent: Thursday, November 05, 2015 9:48 AM
To: POI Users List
Subject: RE: Drawing Borders is SLOW

On Thu, 5 Nov 2015, Murphy, Mark wrote:
> My thought was to build the borders on their own, then once the 
> borders are created, apply them to the cell styles in a single step. 
> This would require a few new objects.
>
> You are going to have to change 
> poi.apache.ss.util.CellUtil.setCellStyleProperty

Oh, you're using CellUtil! That does change things

I don't see why we couldn't add a method onto CellUtil which took muliple 
properties, and found/created a style with all of those on in one go

That's different to working on the CellStyle objects directly, which is what I 
thought you were doing


Please feel free to create an enhancement in bugzilla for this (stressing 
it's for CellUtil not CellStyle, to avoid others getting confused), then 
even better work up a patch if you can!

Nick

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


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

RE: Drawing Borders is SLOW

[Murphy, Mark]

 
style. This is necessary
 * because Excel has an upper limit on the number of Styles that it 
supports.
 *
 *@param workbook The workbook that is being worked with.
 *@param propertyName The name of the property that is to be changed.
 *@param propertyValue The value of the property that is to be changed.
 *@param cell The cell that needs it's style changes
 */
public static void setCellStyleProperty(Cell cell, Workbook workbook, 
String propertyName,
Object propertyValue) {
HashMap property = new HashMap(propertyName, 
propertyValue);
setCellStyleProperty(cell, workbook, property); 
}

I think the above change is the key. But it needs a lot of support built around 
it. The classes and methods to build the borders, and the method to apply those 
borders to the styles by calling setCellStyleProperty(Cell, Workbook, Map). 
This will prevent creating all those intermediate styles.


-Original Message-
From: Dominik Stadler [mailto:dominik.stad...@gmx.at] 
Sent: Wednesday, November 04, 2015 3:25 PM
To: POI Users List
Subject: Re: Drawing Borders is SLOW

Hi,

I think there is a related bug entry at
https://bz.apache.org/bugzilla/show_bug.cgi?id=54593 where we did some 
investigation and discussed caching things, but we could not create a complete 
fix yet... Ideas/Patches welcome!

Thanks... Dominik.

On Wed, Nov 4, 2015 at 3:18 PM, Murphy, Mark  wrote:
> I do create a bunch of styles at the front, but they do not have borders. I 
> would need 3 styles for column headers (just because of borders), and the 
> data portion of the table would require at least 9 styles, just because of 
> borders. And that assumes that the data in each cell is formatted the same 
> way. Each different type of formatting, number formats, special highlighting, 
> bolding, alignment would require multiple styles, and the program complexity 
> needed to add table data location awareness to the mix (just to ensure the 
> correct border goes with the correct cell) is unnecessary. The logic is much 
> simpler, and you have to preload fewer styles, if you just leave the borders 
> off, and draw them after the spreadsheet is built. Unfortunately that is a 
> very slow option. It is POI that creates unnecessary styles due to its one 
> property at a time processing of borders.
>
> What's easier to read and maintain (this is RPG that I am calling the POI 
> methods from):
>
> for rowNum = 1 to something;
>  row = ssSheet_createRow(sheet: rowNum);
>  ss_num(row: 1: customerNumber: sty_num);
>  ss_text(row: 2: customerName: sty_text);
>  ss_text(row: 3: status: sty_code);
>  ss_date(row: 4: createDate: *MDY): sty_date);
> endfor;
>
> ss_drawBorders(book: sheet: 1: rowNum-1: 1: 4:
> BORDER_THIN: EXTENT_INSIDE);
> ss_drawBorders(book: sheet: 1: rowNum-1: 1: 4:
> BORDER_MEDIUM: EXTENT_OUTSIDE);
>
> Or
>
> for rowNum = 1 to something;
>  row = ssSheet_createRow(sheet: rowNum);
>  if rowNum = 1;
>  style = sty_num_tl;
>  else;
>  style = sty_num_l;
>  endif;
>  ss_num (row: 1: customerNumber: style);
>  if rowNum = 1;
>  style = sty_text_t;
>  else;
>  style = sty_text;
>  endif;
>  ss_text(row: 2: customerName: sty_text);
>  if rowNum = 1;
>  style = sty_code_t;
>  else;
>  style = sty_code;
>  endif;
>  ss_text(row: 3: status: sty_code);
>  if rowNum = 1;
>  style = sty_date_tr;
>  else;
>  style = sty_date_r;
>  endif;
>  ss_date(row: 4: createDate: *MDY): sty_date);
> endfor;
>
> // Still need more code to replace the styles on the last row of 
> cells to get the borders right
>
> This is the simplest of examples. Most of my spreadsheets are far more 
> complex with some having styles set based on the data, is the part 
> discontinued, is the process late, etc. This just makes for nested if's in 
> trying to decide which style should be applied to each cell. The first code 
> is simpler, and if all the border properties could be set for a cell all at 
> once, much quicker. Just a single search, just a single setCellStyleProperty, 
> and no unused intermediate styles created.
>
> -Original Message-
> From: Nick Burch [mailto:apa...@gagravarr.org]
> Sent: Tuesday, November 03, 2015 3:59 PM
> To: POI Users List
> Subject: Re: Drawing Borders is SLOW
>
> On Tue, 3 Nov 2015, Murphy, Mark wrote:
>> I am sure you all know this. 

RE: Drawing Borders is SLOW

[Murphy, Mark]

I do create a bunch of styles at the front, but they do not have borders. I 
would need 3 styles for column headers (just because of borders), and the data 
portion of the table would require at least 9 styles, just because of borders. 
And that assumes that the data in each cell is formatted the same way. Each 
different type of formatting, number formats, special highlighting, bolding, 
alignment would require multiple styles, and the program complexity needed to 
add table data location awareness to the mix (just to ensure the correct border 
goes with the correct cell) is unnecessary. The logic is much simpler, and you 
have to preload fewer styles, if you just leave the borders off, and draw them 
after the spreadsheet is built. Unfortunately that is a very slow option. It is 
POI that creates unnecessary styles due to its one property at a time 
processing of borders.

What's easier to read and maintain (this is RPG that I am calling the POI 
methods from):

for rowNum = 1 to something;
 row = ssSheet_createRow(sheet: rowNum);
 ss_num(row: 1: customerNumber: sty_num);
 ss_text(row: 2: customerName: sty_text);
 ss_text(row: 3: status: sty_code);
 ss_date(row: 4: createDate: *MDY): sty_date);
endfor;

ss_drawBorders(book: sheet: 1: rowNum-1: 1: 4:
BORDER_THIN: EXTENT_INSIDE);
ss_drawBorders(book: sheet: 1: rowNum-1: 1: 4:
BORDER_MEDIUM: EXTENT_OUTSIDE);

Or

for rowNum = 1 to something;
 row = ssSheet_createRow(sheet: rowNum);
 if rowNum = 1;
 style = sty_num_tl;
 else;
 style = sty_num_l;
 endif;
 ss_num (row: 1: customerNumber: style);
 if rowNum = 1;
 style = sty_text_t;
 else;
 style = sty_text;
 endif; 
 ss_text(row: 2: customerName: sty_text);
 if rowNum = 1;
 style = sty_code_t;
 else;
 style = sty_code;
 endif;   
 ss_text(row: 3: status: sty_code);
 if rowNum = 1;
 style = sty_date_tr;
 else;
 style = sty_date_r;
 endif;
 ss_date(row: 4: createDate: *MDY): sty_date);
endfor;

// Still need more code to replace the styles on the last row of cells to 
get the borders right

This is the simplest of examples. Most of my spreadsheets are far more complex 
with some having styles set based on the data, is the part discontinued, is the 
process late, etc. This just makes for nested if's in trying to decide which 
style should be applied to each cell. The first code is simpler, and if all the 
border properties could be set for a cell all at once, much quicker. Just a 
single search, just a single setCellStyleProperty, and no unused intermediate 
styles created.

-Original Message-
From: Nick Burch [mailto:apa...@gagravarr.org] 
Sent: Tuesday, November 03, 2015 3:59 PM
To: POI Users List
Subject: Re: Drawing Borders is SLOW

On Tue, 3 Nov 2015, Murphy, Mark wrote:
> I am sure you all know this. But the problem increases as the number 
> of styles grows. In looking at the code, I am convinced that the 
> problem can be found in the fact that when borders are drawn, the cell 
> style is retrieved, the border is applied, and all styles are searched 
> for a matching style. I one is not found, then a new one is created

This is the bit where I'm loosing you. Surely you create a dozen or so styles 
at the start of creating your workbook, with the various colours and borders 
that you want, then you simply apply them to your cells as you work through 
creating your workbook. You shouldn't need to be creating styles as you go, 
adding various bits of borders in to them.

Styles in Excel are, due to how the file format works, scoped at the workbook 
level and not the cell level. You shouldn't therefore be creating styles as you 
go, or you'll run out of available styles!

Nick

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


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

Drawing Borders is SLOW

[Murphy, Mark]

I am sure you all know this. But the problem increases as the number of styles 
grows. In looking at the code, I am convinced that the problem can be found in 
the fact that when borders are drawn, the cell style is retrieved, the border 
is applied, and all styles are searched for a matching style. I one is not 
found, then a new one is created (even if it is not going to ultimately be used 
because this happens for each border segment individually), and if colors are 
included, the process will take 2 to 4 times as long because only one style 
property is applied at a time. You can see the exponential growth in time as 
borders are added around ranges.

If you were to create a setCellProperty(Cell, Workbook, Map) 
where the map is a set of cell style properties, you could then use the 
Map.putAll method to apply a whole set of border properties all in one step, 
and then search for, and create if necessary, the final style, rather than 
searching and creating each intermediate style. The benefit, if you discount 
the fact that the number of styles is growing all the time you are drawing 
borders is that you are going to get up to a 75% time reduction, and another 
50% beyond that if border colors are being used. Now empirical evidence 
suggests that I end up with 70 to 80% unused cell styles due to my border 
drawing activities. And since the real time in drawing borders is in searching 
for a style match, if we simply avoid creating all those intermediate styles, 
we will see additional gains.

A second method of speeding things up would be to hash and cache the styles so 
that instead of comparing the entire map, we just compare hash values.

Maybe a combination of the two methods would be the best of both worlds.

Am I crazy?

Gradient Fill

[Murphy, Mark]

Has anyone been able to do a gradient fill, and would be willing to show me the 
magic code? I can see methods for pattern fill, but not gradient fill. An XSSF 
only solution is acceptable.

RE: Strange behavior when Math.min() in code

[Murphy, Mark]

It doesn't get there, the first line of my test program is Workbook wb = new 
XSSFWorkbook(); It fails there throwing a NulPointerException. My final code 
(which works for me) is:
try {
copyRow(sh, n1, n2);
sh.removeRow(sh.getRow(n1));
copyRow(sh, n2+1, n1);
sh.removeRow(sh.getRow(n2+1));
if (n2+2 <= sh.getLastRowNum()) {
sh.shiftRows(n2+2, sh.getLastRowNum(), -1);
}
} catch (Exception e) {
e.printStackTrace();
}

-Original Message-
From: Javen O'Neal [mailto:javenon...@gmail.com] 
Sent: Wednesday, September 23, 2015 2:53 PM
To: POI Users List
Subject: Re: Strange behavior when Math.min() in code

Math.min/StrictMath.min versus your ternary computation look equivalent to me.
What is the value of last just before sh.shiftRows if you use Math.min, 
StrictMath.min, or the ternary operator?

On a side-note, I've submitted an implementation of copyRows in bug
58348 (https://bz.apache.org/bugzilla/show_bug.cgi?id=58348).
Javen O'Neal


On Wed, Sep 23, 2015 at 5:59 AM, Murphy, Mark  wrote:
> POI 3.12 - Windows - IBM JVM
>
> Here is some code (CopyRow is my own concoction based on some code I found on 
> StackOverflow):
>
>   try {
>  copyRow(sh, n1, n2);
>  sh.removeRow(sh.getRow(n1));
>  copyRow(sh, n2+1, n1);
>  sh.removeRow(sh.getRow(n2+1));
> //   int last = Math.min(sh.getLastRowNum(), n2+2);
> //   int last = StrictMath.min(sh.getLastRowNum(), n2+2);
>  int last = sh.getLastRowNum();
>  last = (last < n2+2 ? n2+2 : last);
>  sh.shiftRows(n2+2, last, -1);
>   } catch (Exception e) {
>  e.printStackTrace();
>   }
>
> If I comment out
>
> int last = sh.getLastRowNum();
>last = (last < n2+2 ? n2+2 : last);
>
> and restore either of the two lines above it,
>
>Workbook wb = new XSSFWorkbook();
>
> In my main method (different class) throws a NullPointerException, but the 
> code as stated above works, but even so, the logic is incorrect because I was 
> confused about what was happening. Now that my fog is clearing, I know how to 
> fix the logic, but still remain unclear why Math.min() would cause new 
> XSSFWorkbook() to throw a NullPointerError. Any ideas?

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

Strange behavior when Math.min() in code

[Murphy, Mark]

POI 3.12 - Windows - IBM JVM

Here is some code (CopyRow is my own concoction based on some code I found on 
StackOverflow):

  try {
 copyRow(sh, n1, n2);
 sh.removeRow(sh.getRow(n1));
 copyRow(sh, n2+1, n1);
 sh.removeRow(sh.getRow(n2+1));
//   int last = Math.min(sh.getLastRowNum(), n2+2);
//   int last = StrictMath.min(sh.getLastRowNum(), n2+2);
 int last = sh.getLastRowNum();
 last = (last < n2+2 ? n2+2 : last);
 sh.shiftRows(n2+2, last, -1);
  } catch (Exception e) {
 e.printStackTrace();
  }

If I comment out

int last = sh.getLastRowNum();
   last = (last < n2+2 ? n2+2 : last);

and restore either of the two lines above it,

   Workbook wb = new XSSFWorkbook();

In my main method (different class) throws a NullPointerException, but the code 
as stated above works, but even so, the logic is incorrect because I was 
confused about what was happening. Now that my fog is clearing, I know how to 
fix the logic, but still remain unclear why Math.min() would cause new 
XSSFWorkbook() to throw a NullPointerError. Any ideas?

Fill and Border efficiency for XSSF format

[Murphy, Mark]

POI 3.12

In looking at the styles.xml file generated by POI, It appears that a lot of 
borders and fills are created that are not used. Maybe POI needs a method to 
trim off those unused fills and borders on save. This method would also be able 
to normalize the fills and borders to remove duplicates and system colors (i.e. 
fgColor = 64 or bgColor = 65). I notice that Excel does this when it saves a 
spreadsheet created by POI. Here is an example of the fills, first as created 
by POI, second as saved by Excel (same spreadsheet):

POI 3.12
   
  
 
  
  
 
  
  
 
   
 
  
  
 
   
   
 
  
  
 
   
   
 
  
  
 
   
 
  
  
 
   
   
 
  
  
 
   
   
 
  
  
 
   
 
  
   

Excel 2010 Save As (same spreadsheet)

  
 
  
  
 
  
  
 
  
  
 
   
 
  
   

Note, not even Excel was perfect as it left a duplicate fill (0, and 2). 
Borders was even more dramatic POI generated 94, Excel saved 13. And now that I 
look at it, POI generated 42 styles while Excel kept 23. I understand that you 
are keeping all the fills borders and styles as they are specified, and 
re-using them as you get to a final setting, but normalizing and trimming seem 
to be what you are missing. That can also be applied to fonts and formats.

Some additional thoughts, based on not yet looking at all the code. I can see 
three steps to collecting all the garbage: Normalization, Duplicate removal, 
Orphan removal. Normalization would involve setting default values, and 
removing system colors. On the fill above, the POI generated fill would look 
like this after normalization:

   
  
 
  
  
 
  
  
 
  
  
 
  
  
 
  
  
 
   
 
  
  
 
   
 
  
  
 
   
 
  
  
 
  
   

Duplicate removal would remove the many duplicates left by normalization. This 
would have to be carried back to the styles themselves and potentially to 
individual cells in the spreadsheet that contain fill id's. After duplicate 
removal you would have something like the following in the fills:

   
  
 
  
  
 
  
  
 
   
 
  
  
 
   
 
  
   

And I just happen to know that my spreadsheet never uses fill "none" - 44, so 
that could d be stripped out during orphan removal to bring the fill to:

   
  
 
  
  
 
  
  
 
   
 
  
   

I suspect that the same process could be used for borders, fonts, formats and 
styles where the order of operation would be normalization and duplicate 
removal for fills, borders, fonts, formats, then styles. This followed by 
orphan removal for styles then fills, borders, fonts, and formats.

RE: Cannot see text in cell after F2

[Murphy, Mark]

Setting the background color to 65 works for me.

-Original Message-
From: Dominik Stadler [mailto:dominik.stad...@gmx.at] 
Sent: Friday, September 18, 2015 4:18 PM
To: POI Users List
Subject: RE: Cannot see text in cell after F2

Hi,

there is a similar bug related to background fill color at
https://bz.apache.org/bugzilla/show_bug.cgi?id=56548

A workaround might be to set the background fill color of the cell style to
65 in this case, let us know if it works for you so we can note it in the bug 
report.

Dominik
Am 18.09.2015 18:25 schrieb "Murphy, Mark" :

> One other observation, because I don't have time to look further into 
> the code right now, that third fill which provides a blue cell 
> background is the only one I created, and I only added a foreground 
> color to it. So somewhere, something is just adding  indexed="64" /> to fills whether it is called for or not. For a solid 
> fill, there is no problem with that, but for other fills, it apparently will 
> cause some problems.
>
> -Original Message-
> From: Murphy, Mark [mailto:murphym...@metalexmfg.com]
> Sent: Friday, September 18, 2015 11:56 AM
> To: user@poi.apache.org
> Subject: Cannot see text in cell after F2
>
> POI 3.12 - XSSF workbook
>
> I am having trouble with spreadsheets created by POI. When I open them 
> in Excel, they look just fine, but when I try to edit the contents of 
> a cell, I cannot see the value of the cell, it all turns black. I 
> think it might be something having to do with the my style, as only 
> cells that I have created, and therefore added a style to have this 
> issue. Let me be more specific.
>
> On my spreadsheet, if I select a cell with data in it and press F2, 
> the cell turns black. If I drag the mouse through the cell, it all turns 
> white.
> I tried this on a cell where the text is not black, but red. In this 
> case when I press F2, I can see the red text against the black 
> background of the cell, and if I drag the mouse through the cell, the 
> text turns light blue on a white background. Somewhere the "edit" 
> background color is being set to black, but I don't know how I am doing this.
>
> Unfortunately I am not having this trouble for all of my cells. And to 
> make matters more difficult, I am using a particular style with two 
> different cells, and one exhibits the behavior, and the other does 
> not. The only difference between these two cells is that I am drawing 
> borders around one, and not around the other. Yet not all of the cells 
> I am drawing a border around have the problem either.
>
> Anyway, I still believe it has something to do with the style, and how 
> it is being selected or created. Dozens of styles are created behind 
> the scenes, exacerbated by the drawing of cell borders, and that could 
> be my problem. But, I found in styles.xml, a fill that I did not 
> expect. Here is the bad fill:
>
>
>   
>  
>   
>   
>  
>   
>   
>  
>
>
>  
>   
>   
>  
>
>  
>   
>
>
> Every style that used that last fill had the issue, I validated this 
> by removing the bgColor node from that last fill, and now the 
> spreadsheet is working as expected. Still not real sure where it came 
> from though. Maybe the use of the border drawing utility, though I 
> have other cells with borders from that utility that do not use the 
> faulty fill. Maybe because they already had an explicit fill assigned to them.
>
> Any idea what I can do to solve this?
>
> -
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional 
> commands, e-mail: user-h...@poi.apache.org
>
>

RE: Cannot see text in cell after F2

[Murphy, Mark]

One other observation, because I don't have time to look further into the code 
right now, that third fill which provides a blue cell background is the only 
one I created, and I only added a foreground color to it. So somewhere, 
something is just adding  to fills whether it is called 
for or not. For a solid fill, there is no problem with that, but for other 
fills, it apparently will cause some problems.

-Original Message-
From: Murphy, Mark [mailto:murphym...@metalexmfg.com] 
Sent: Friday, September 18, 2015 11:56 AM
To: user@poi.apache.org
Subject: Cannot see text in cell after F2

POI 3.12 - XSSF workbook

I am having trouble with spreadsheets created by POI. When I open them in 
Excel, they look just fine, but when I try to edit the contents of a cell, I 
cannot see the value of the cell, it all turns black. I think it might be 
something having to do with the my style, as only cells that I have created, 
and therefore added a style to have this issue. Let me be more specific.

On my spreadsheet, if I select a cell with data in it and press F2, the cell 
turns black. If I drag the mouse through the cell, it all turns white. I tried 
this on a cell where the text is not black, but red. In this case when I press 
F2, I can see the red text against the black background of the cell, and if I 
drag the mouse through the cell, the text turns light blue on a white 
background. Somewhere the "edit" background color is being set to black, but I 
don't know how I am doing this.

Unfortunately I am not having this trouble for all of my cells. And to make 
matters more difficult, I am using a particular style with two different cells, 
and one exhibits the behavior, and the other does not. The only difference 
between these two cells is that I am drawing borders around one, and not around 
the other. Yet not all of the cells I am drawing a border around have the 
problem either.

Anyway, I still believe it has something to do with the style, and how it is 
being selected or created. Dozens of styles are created behind the scenes, 
exacerbated by the drawing of cell borders, and that could be my problem. But, 
I found in styles.xml, a fill that I did not expect. Here is the bad fill:

   
  
 
  
  
 
  
  
 
   
   
 
  
  
 
   
 
  
   

Every style that used that last fill had the issue, I validated this by 
removing the bgColor node from that last fill, and now the spreadsheet is 
working as expected. Still not real sure where it came from though. Maybe the 
use of the border drawing utility, though I have other cells with borders from 
that utility that do not use the faulty fill. Maybe because they already had an 
explicit fill assigned to them.

Any idea what I can do to solve this?

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

Cannot see text in cell after F2

[Murphy, Mark]

POI 3.12 - XSSF workbook

I am having trouble with spreadsheets created by POI. When I open them in 
Excel, they look just fine, but when I try to edit the contents of a cell, I 
cannot see the value of the cell, it all turns black. I think it might be 
something having to do with the my style, as only cells that I have created, 
and therefore added a style to have this issue. Let me be more specific.

On my spreadsheet, if I select a cell with data in it and press F2, the cell 
turns black. If I drag the mouse through the cell, it all turns white. I tried 
this on a cell where the text is not black, but red. In this case when I press 
F2, I can see the red text against the black background of the cell, and if I 
drag the mouse through the cell, the text turns light blue on a white 
background. Somewhere the "edit" background color is being set to black, but I 
don't know how I am doing this.

Unfortunately I am not having this trouble for all of my cells. And to make 
matters more difficult, I am using a particular style with two different cells, 
and one exhibits the behavior, and the other does not. The only difference 
between these two cells is that I am drawing borders around one, and not around 
the other. Yet not all of the cells I am drawing a border around have the 
problem either.

Anyway, I still believe it has something to do with the style, and how it is 
being selected or created. Dozens of styles are created behind the scenes, 
exacerbated by the drawing of cell borders, and that could be my problem. But, 
I found in styles.xml, a fill that I did not expect. Here is the bad fill:

   
  
 
  
  
 
  
  
 
   
   
 
  
  
 
   
 
  
   

Every style that used that last fill had the issue, I validated this by 
removing the bgColor node from that last fill, and now the spreadsheet is 
working as expected. Still not real sure where it came from though. Maybe the 
use of the border drawing utility, though I have other cells with borders from 
that utility that do not use the faulty fill. Maybe because they already had an 
explicit fill assigned to them.

Any idea what I can do to solve this?

RE: How to hide formula bar on spreadsheet

[Murphy, Mark]

Turns out that this flag is not stored in the workbook like the Display 
Gridlines attribute is, so I don't think this is possible.

-Original Message-
From: Dominik Stadler [mailto:dominik.stad...@gmx.at] 
Sent: Tuesday, July 14, 2015 10:08 AM
To: POI Users List
Subject: Re: How to hide formula bar on spreadsheet

Hi,

I don't know of a ready-made interface being available right now, but you can 
likely set it via some of the lower-level APIs in POI.

If yes I suggest you create two very similar files in Excel which just differ 
in this setting and save them as XLSX, then you can unzip both files (XLSX is 
actually a ZIP) and do a file-compare on them to find out where this is stored, 
with this information we can likely guide you to how you can use the lower 
level API to set this.

Thanks... Dominik.

On Tue, Jul 14, 2015 at 1:52 PM, Murphy, Mark  wrote:
> In Excel under the View menu, I can uncheck the Formula Bar checkbox to 
> remove the formula bar from the display. Is there a way I can do that with 
> POI?

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

How to hide formula bar on spreadsheet

[Murphy, Mark]

In Excel under the View menu, I can uncheck the Formula Bar checkbox to remove 
the formula bar from the display. Is there a way I can do that with POI?

RE: Borders and ranges

[Murphy, Mark]

I found the answer in RegionUtil. This API is still new to me. I am finding new 
things every time I use it.

-Original Message-
From: Dominik Stadler [mailto:dominik.stad...@gmx.at] 
Sent: Wednesday, July 01, 2015 8:35 AM
To: POI Users List
Subject: Re: Borders and ranges

Yes, unfortunately that is the way the Excel developers decided to put Styles 
into the format way back then (i.e. each different style needs to be defined 
and should be re-used across cells if possible) and it is not easy to shield 
the developer who is using POI from these complexities as it would likely cause 
big performance problems with larger spreadsheets with many different styles.

Unfortunately for now at least you have to decide on your own how you best 
re-use styles in your specific application of POI, but we are open to take 
suggestions (in code form with unit-test coverage, naturally :( ) of how you 
think it would be easier to manage these on top of POI.

Dominik.

On Tue, Jun 30, 2015 at 4:55 PM, Murphy, Mark  wrote:
> Ok, but I don't see where I can apply those properties to a cell or range, 
> Only to a CellStyle. So I then have to create a bunch of cell styles to 
> handle Upper left corner, upper right corner, upper border, right border, 
> left border, bottom border, bottom right corner, and bottom left corner, not 
> to mention the special cases where I want to do a single cell, or a range 
> that is one cell wide or one cell deep. And If I want to give some of these 
> bordered cells a background, and others not, it becomes even more complex no? 
> In Excel I can simply grab a range, and apply the borders I want. How do I do 
> that with POI?
>
> -Original Message-
> From: Dominik Stadler [mailto:dominik.stad...@gmx.at]
> Sent: Monday, June 29, 2015 3:05 PM
> To: POI Users List
> Subject: Re: Borders and ranges
>
> Not sure I understand the question correctly, but wouldn't you just apply 
> top/left/bottom/right borders as appropriate to get the larger box? At least 
> that is what you can do in Excel to get such an effect...
>
> Dominik
> Am 29.06.2015 20:19 schrieb "Murphy, Mark" :
>
>> I understand how to apply a border to an individual cell using a cell 
>> style, and I found documentation on how to apply a border to a merged 
>> range of cells, but how do I apply an outside border to a range of unmerged 
>> cells.
>>

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

RE: Borders and ranges

[Murphy, Mark]

Ok, but I don't see where I can apply those properties to a cell or range, Only 
to a CellStyle. So I then have to create a bunch of cell styles to handle Upper 
left corner, upper right corner, upper border, right border, left border, 
bottom border, bottom right corner, and bottom left corner, not to mention the 
special cases where I want to do a single cell, or a range that is one cell 
wide or one cell deep. And If I want to give some of these bordered cells a 
background, and others not, it becomes even more complex no? In Excel I can 
simply grab a range, and apply the borders I want. How do I do that with POI?

-Original Message-
From: Dominik Stadler [mailto:dominik.stad...@gmx.at] 
Sent: Monday, June 29, 2015 3:05 PM
To: POI Users List
Subject: Re: Borders and ranges

Not sure I understand the question correctly, but wouldn't you just apply 
top/left/bottom/right borders as appropriate to get the larger box? At least 
that is what you can do in Excel to get such an effect...

Dominik
Am 29.06.2015 20:19 schrieb "Murphy, Mark" :

> I understand how to apply a border to an individual cell using a cell 
> style, and I found documentation on how to apply a border to a merged 
> range of cells, but how do I apply an outside border to a range of unmerged 
> cells.
>

Borders and ranges

[Murphy, Mark]

I understand how to apply a border to an individual cell using a cell style, 
and I found documentation on how to apply a border to a merged range of cells, 
but how do I apply an outside border to a range of unmerged cells.