There doesn't appear to be an equivalent yet for HSSF. You are on the
right track for XSSF, I use that myself when stripping down a workbook to
bare bones.
I'm not at all versed in the HSSF format, so I have no idea what it might
take to implement there. As far as I can tell the binary format sh
Is the sheet to delete referenced in any formulas, names, chart series,
etc. On other sheets?
Is it XLSX? If so, you may be able to just open it as a zip file and delete
the sheet by manipulating the zip contents directly, without bothering to
parse the xml.
I would take a sample, open it in Exce
>From the Javadoc for evaluate(Cell):
"This method should be preferred over evaluateInCell() when the call should
not modify the contents of the original cell."
You need to use the result of this call, a CellValue instance, like the POI
example page shows, as this call doesn't save the formula ev
Did you explicitly evaluate the cell? The example page on the website
shows how to do that. The cached numeric value would only be useful
_after_ you explicitly call evaluate(cell). At that point you also would
be able to just use the evaluation result directly.
On Wed, Apr 10, 2019 at 10:30 AM
The Apache POI project is pleased to announce the release of POI 4.1.0.
Featured are a handful of new areas of functionality, and numerous bug fixes.
See the downloads page for binary and source distributions:
https://poi.apache.org/download.html
Release Notes
Changes
The most notab
The Apache POI project is pleased to announce the release of POI 4.1.0.
Featured are a handful of new areas of functionality, and numerous bug fixes.
See the downloads page for binary and source distributions:
https://poi.apache.org/download.html
Release Notes
Changes
The most notab
First try with the latest POI, 4.1.0. The version you are using, 3.8, is
very out of date. Much has changed, and I doubt you will get any help for
such an old version.
On Wed, Apr 10, 2019, 05:24 Dhanaraja, R (R.) wrote:
> Hi Team,
>
> We use below 3 POI jars in in our organization for Excel pr
The Apache POI project is pleased to announce the release of POI 4.1.0.
Featured are a handful of new areas of functionality, and numerous bug fixes.
See the downloads page for binary and source distributions:
https://poi.apache.org/download.html
Release Notes
Changes
The most notab
I see your problem. XSSFCell.toString() returns the formula String for a
formula cell type, not the cached cell value.
With POI, you need your own method that does a bit more work based on the
reported CellType for each cell. Even more if you want the value formatted
as it would appear in Excel
Hidden sheets aren't external, they are just marked to not be displayed in
a UI. POI doesn't care if sheets are hidden or not when calculating
formulas, I do this all the time.
How are you looking for the calculated value of the cell? What you are
getting back is a formula value, which is just a
The Apache POI project is pleased to announce the release of Apache
XMLBeans 3.1.0.
This is the 4th release since the POI team took over the ownership of
XMLBeans.
Featured are a handful of memory, stability, coverage, and security fixes.
The release package organization has also been updated to a
this release possible.
On behalf of the Apache POI PMC,
Greg Woolsey
[1] Release notes:
https://www.apache.org/dyn/closer.lua/poi/xmlbeans/release/dev/RELEASE-NOTES-3.1.0.txt
[2] Changelog: https://xmlbeans.apache.org/status.html#rel_310
What use case do you have in mind? Excel stores this as a String value,
what benefit would there be to accepting Object, and how would that then be
converted to String? Why not use use toString() on your object?
On Thu, Mar 21, 2019 at 4:52 AM osamafawzy...@gmail.com <
osamafawzy...@gmail.com> w
I often use a "template" document instead of creating a workbook from
scratch. This isn't an Excel template (XLST), just a plain XLSX file with
no data, only boilerplate like labels.
In that file I can define workbook default styles in Excel, table themes,
and, possibly most important for you, co
; workbook - which to me contradicts the helpful hint and b) I had been
> doing exactly that in the recent pass.
>
> If you still want my code which generates the error I can back up a
> bit. The partial stack trace I sent was all lines not in my code.
>
>
> On 12/20/18 3:55 PM, Greg W
Can you provide a test case, sample code, or full stacktrace? How is the
new sheet created? It looks to me from the code that the message means
exactly what it says - the style you are trying to set and the cell you are
trying to set it on come from different Workbook objects. Both the style
and
Just saw this - Gmail puts all emails from Yahoo in spam for some reason I
haven't investigated.
I like this, it was one of my first thoughts, but the display value
parameter is not always a String - it can be anything, including an error
result, in my testing.
So there would need to be Hyperlink
ipate only
implementing it in one path, perhaps as you suggest through the
FormulaEvaluator via instance specific getter/setter methods for hard-coded
or parameterized flags.
On Thu, Nov 29, 2018 at 11:51 PM Nick Burch wrote:
> On Thu, 29 Nov 2018, Greg Woolsey wrote:
> > The fi
This is a unique (as far as I can see) Excel function, in that the
evaluation result is one thing, but the function has a second value
required for implementation in a client.
The first argument to the function is the target URL, the second argument
is optional display text.
The current implement
et involved :)
On Mon, Nov 12, 2018, 19:54 Rob Sargent I believe I’ll have to resort to that, thanks. Is this not a missing
> feature?
>
> > On Nov 12, 2018, at 8:03 PM, Greg Woolsey
> wrote:
> >
> > Or copy the original file first, with standard Java tools, then open
Or copy the original file first, with standard Java tools, then open, edit,
and write the copy with POI.
On Mon, Nov 12, 2018, 13:34 Andreas Beeker Hi Rob,
>
> although this is more memory consuming, how about opening your template
> file via (File-)InputStream and using Workbook.write(new
> File
Take a look at the documentation for DataFormatter:
https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html
there are tons of examples available via web searches, plus the POI unit
test sources.
On Thu, Oct 11, 2018 at 3:46 AM Marvin He wrote:
> Hi all,
>
>I use this
You will have to use the underlying CT* classes, there is no good way to do
it with the POI API directly. This is partly because Excel determines the
axis label from either a formula or static text, each stored as different
properties in the underlying XML, and mutually exclusive.
Your best optio
See question 20 from the POI FAQ [1]. Accessing the same document
> objects
> > from multiple threads is not supported.
>
> Good, but can we make these objects thread safe at least?
>
> On Thu, 23 Aug 2018 at 19:22, Greg Woolsey wrote:
>
> > See question 20 from the
See question 20 from the POI FAQ [1]. Accessing the same document objects
from multiple threads is not supported.
[1] https://poi.apache.org/faq.html
On Thu, Aug 23, 2018 at 3:47 AM Pavel Drankov wrote:
> Hi,
>
> I have an instance of SXSSFSheet and when I'm trying to fill it with
> values con
That error means the formula references a different workbook. What version
of POI are you using? That stack trace doesn't match the latest release
version as far as I can see.
What you need to do is use the latest POI, 3.17, and register all external
workbooks referenced in formulas via
setupRe
If you manually add the space in Word for Mac, where it isn't being
rendered when already present in the source, and save it, what does the
resulting XML look like? Does Word then merge the runs, escape it somehow,
or something else?
On Thu, Aug 16, 2018 at 8:01 AM Eliot Kimber wrote:
> I tried
Also, please note that version 3.0.0 is extremely old, and the API has
changed significantly. Further, current source trunk is again much changed
from the current release, 3.17, especially wrt to replacing ints with Enums
when possible.
Any patch would need to be against the current SVN/Git sourc
There is very limited support for pivot tables yet in POI, as you've
found. The avenues I can think of for you are:
1. compare before and after XLSX output (it's zipped XML files) to see what
Excel is doing, then replicate that in POI - not typically a simple task.
2. Start with a "template" fil
The best way I've found to work through issues like this (I also modify
tables, but don't need merged cells) is to mock up what I want it to end up
like in Excel, save it to XLSX, mock up the same (or what I want to be the
same) content via POI and save it to XLSX. I then unzip the files and
compa
Documentation for the Office ML standard, ECMA-376, can be found online.
One link is
http://web.mit.edu/~stevenj/www/ECMA-376-new-merged.pdf
Theme part contents are discussed in section 14.2.7.
On Tue, Apr 3, 2018 at 5:39 PM QuinnChen wrote:
>
>
>
>
>
>
>
>
> What does
I can't find it in the archives, but I'm sure there was another issue
recently where adding a new part (not an image, but the mechanism was
similar - maybe tables?) caused an error like this because the algorithm
for picking the number to assign the package part file didn't properly
notice non-cont
>
> I'm gonna hope this comes in a lot lower on the priorities than some
> functions I need to add. =P
>
I hear that. When I first looked into it, I got very creative in finding
ways to do what we needed without using Pivot Tables :)
In the past, the only time I used them with POI was through a
The structure is actually quite convoluted, and involves multiple
components of the Zip structure, connected by relationship IDs (rId
attributes, _rels files):
Workbook.xml
Sheet#.xml
PivotTable#.xml
PivotCacheDefinition#.xml
PivotCacheRecords#.xml
If cell data is modified the corresponding Pivot
s, filling calculated
columns, updating dependent values, etc. But not much if any of that is
applicable or desired in the POI codebase in my opinion.
On Mon, Mar 19, 2018 at 1:33 PM Blake Watson wrote:
> On Fri, Mar 16, 2018 at 11:35 AM, Greg Woolsey
> wrote:
>
> > There still is
There still isn't much, if any, support beyond exposing the CT* classes.
That question also sounded like they wanted "live" manipulation, e.g.
applying sorting. That's an entirely different level of feature support
POI doesn't do for much beyond formula evaluation and recently shifting
cells by ro
XSSF files are zip files containing many XML files. Unzipping and parsing
these files is where the time is spent. The specifics depend on the
structure of the workbook, but past history makes it pretty certain the
time is in the XMLBeans library. Also, if you don't have enough RAM
allocated to th
Interesting! I'd love to see that as Java to run some comparisons, but I
don't have the time right now to investigate. If you do it, I'd evaluate
and commit it if appropriate.
On Tue, Jan 23, 2018 at 1:32 PM Blake Watson wrote:
> OK, FWIW, I did this:
>
> 1. I get all the rules on the sheet...
Haven't ever done it myself, but since it is possible in Excel [1] it is a
matter of working backward from a Workbook set up the way you want, seeing
what properties are set where and doing those things in POI to mimic or
duplicate the setup. Create an empty workbook set up per those
instructions
I can barely function with Maven, so I'm no help on that front,
unfortunately. I know folks have had various issues and questions about
XMLBeans and classpath issues on the mailing list and StackOverflow,
though, so you may find some answers in those archives.
On Tue, Jan 2, 2018 at 2:29 PM Blake
Could this be related to bug #61841 - Unnecessary long computation when
evaluating VLOOKUP on all column reference?
I fixed that earlier this month in trunk. Wasn't a problem with
conditional formatting itself, but rather an optimization of certain
formula constructs, especially ones using full-c
You could also wrap your function registration in code that first looks to
see if it is already defined, using
WorkbookEvaluator.getSupportedFunctionNames().contains("TREND")
On Thu, Dec 21, 2017 at 7:35 AM Yegor Kozlov wrote:
> UDFs are registered in a static map and JUnit does not reset stati
That formula uses "array syntax" (the curly braces). Until a commit to
trunk a few days ago POI didn't support any array syntax, so no, released
versions won't support that expression yet. A custom build from current
SVN/Git trunk might evaluate it - I don't think there is very broad unit
test co
This sounds like it should be a Bugzilla issue. The Excel documentation
[1] says "Text can be in any of the constant number, date, or time formats
recognized by Microsoft Excel." I think that means the function needs to be
rewritten in terms of the cell formats defined in
org.apache.poi.ss.usermod
>
> Kindly help, if any one has idea on it.
>
> Regards
> Thamodharan B
>
>
>
> On Nov 23, 2017 1:09 AM, "Greg Woolsey" wrote:
>
> The OOXML/Excel 2007+ limit is 2^20 (1,048,576) rows per sheet. If you are
> trying to put 5 million rows on a single sheet, Ex
m> wrote:
>
> On Wed, 2017-11-22 at 19:39 +0000, Greg Woolsey wrote:
> > POI streaming may not catch it (it should, but maybe that's the bug),
> > but
> >
> > OOXML compliant apps (Excel, OpenOffice/LibreOffice, etc.) will
> > consider it
> >
> >
The OOXML/Excel 2007+ limit is 2^20 (1,048,576) rows per sheet. If you are
trying to put 5 million rows on a single sheet, Excel will never open it.
POI streaming may not catch it (it should, but maybe that's the bug), but
OOXML compliant apps (Excel, OpenOffice/LibreOffice, etc.) will consider i
This is my busiest month of the year for my day job, but eventually I want
to look into the date code in POI, as I suspect there are some parts
handling this case explicitly and perhaps this path and a few others that
could benefit from some standardization around whatever that behavior is.
Unless
Sounds like an input validation issue with EDate and other similar
functions to me. With Conditional Formatting I tried to not swallow/ignore
errors in evaluation, exactly because I didn't want to hide bugs like this
appears to be.
Excel seems to assume a double of -1 equals a date of 0/0/1900, w
I just tried opening the file you attached in the last message. It opened
fine for me, no uncaught exceptions. When I opened it in Excel 2016, I
noticed the date formatted cells apparently resolve to empty values, which
are then treated as numeric 0 by Excel, and displayed as "1/0/1900".
I didn'
Also, to run all the tests, I use the Ant tasks in build.xml. Selecting a
package and trying to run all tests in Eclipse has trouble in some cases
because of the customized classpaths needed. It is possible to build a
test that runs in Eclipse but fails in the build because Eclipse puts more
stuf
It took me the better part of a week to get tests running the first time I
set it up. And I thought I was good with Ant and Eclipse, and passable
with Maven. Just a lot of moving parts.
Do you have a full stack trace for the error? Seeing where it comes from
may help me dig into it.
I checked a
Catching up after vacation, coming in late to this. What POI release is
this? I had a bug when shifting references for conditions that applied to
ranges of cells at one point. That should be fixed in 3.17, was probably
present in 3.16 and 3.15.
On Wed, Oct 11, 2017 at 8:40 AM Blake Watson wrot
The XML declaration, with the single quotes, looks a lot like this question
on SO:
https://stackoverflow.com/questions/43754776/java-transformer-adding-spaces-and-single-quotes-to-xml-header-and-not-encoding
The specific class generating the XML is
org.apache.poi.openxml4j.opc.StreamHelper, via
a
Like all open-source volunteer projects, the answer to "why isn't X
implemented?" is always "because no one needed it bad enough yet to write a
patch to do it." That said, IFERROR() _IS_ supported by POI, but in the
AnalysisToolPak functions, not the FunctionEval list. You can see the full
list o
You have to go down to the CT* classes for that info, POI doesn't have a
high-level API for it yet. Note this is a per-sheet setting, not
per-workbook.
XSSFWorksheet xw = getTheWorksheet();
if (xw.getCTWorksheet().isSetAutoFilter()) {
CTAutoFilter af = xw.getCTWorksheet().getAutoFilter();
}
Yo
Dominik is correct, there is no easy way to do it. The OOXML format for
these has document part relations, rule indexes, theme colors, shared
document style entries referenced by index, and other references that need
to be carefully managed along the way or the resulting zipped XML package
won't b
Even if you are attempting to just write a validator for some input that is
then used to update/add to a workbook, you may not get what you want
relying on cell formatting. Excel lets you format an entire column as
numbers, but enter text in whatever cells you want, for things like column
headers.
At this point, no. Data can be read from pivot tables, but dynamically
changing them is not implemented.
On Wed, Jun 14, 2017 at 7:13 AM Jörn Franke wrote:
> https://poi.apache.org/spreadsheet/limitations.html
>
> > On 14. Jun 2017, at 18:19, Matthew Stiles wrote:
> >
> > Hi there,
> >
> > I a
There is currently no api for creating charts. As someone working with
reading charts with POI, I can say it will be a major undertaking to create
from scratch. You might find it easier to start with a template workbook
with a chart defined in Open Office or Excel, and then just edit the data
wit
Rather involved to demonstrate, fortunately there is an open-source project
that does it using POI. Check out this method and the code it calls as a
starting point:
https://github.com/vaadin/spreadsheet/blob/4438c98c13a465ae3f78747ac812264358ee1091/vaadin-spreadsheet/src/main/java/com/vaadin/addo
Typically when I see this, it is an issue of font availability. Often a
file created on one system, such as Windows or Mac in the case of Excel,
use fonts that are not installed by default on the system reading and
rendering the file (often Linux based servers). Usually missing fonts are
handled
NPOI is not an Apache project, and as far as I know, has no ongoing
relationship with this project. You should ask your question in whatever
forum that project provides. I know in the Java Apache POI project those
functions had some updates in the latest 3.16 release, so it doesn't
surprise me th
overriding formatting.
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java
On Thu, Apr 20, 2017 at 9:53 AM Greg Woolsey wrote:
> Hadn't seen those example files. I'll think about what an example might
> look like. I&#x
Hadn't seen those example files. I'll think about what an example might
look like. I'm sure it would be useful to someone.
On Wed, Apr 19, 2017 at 10:46 PM Nick Burch wrote:
> On Wed, 19 Apr 2017, Greg Woolsey wrote:
> > Missed the 2nd half of the question. This class
as an example, I just got 25Mbps throughput downloading the zip from the
gatech.edu mirror:
http://www.gtlib.gatech.edu/pub/apache/poi/release/src/poi-src-3.16-20170419.zip
On Wed, Apr 19, 2017 at 10:22 PM Greg Woolsey
wrote:
> The download links use an array of mirror sites. Perhaps the
The download links use an array of mirror sites. Perhaps the one it sent
you to has an issue, or some ISP peering link in between has issues.
Whatever it is, it is outside the scope of the POI project, either with
Apache.org's dynamic mirroring, the mirror itself, some hop in the routing,
or the f
/issues/461#issuecomment-279091036
On Wed, Apr 19, 2017 at 3:26 PM Greg Woolsey wrote:
> The class is org.apache.poi.ss.formula.ConditionalFormattingEvaluator in
> poi-3.16.jar.
>
> Use is something like this:
>
> WorkbookEvaluatorProvider wbEvalProv = (Workboo
The class is org.apache.poi.ss.formula.ConditionalFormattingEvaluator in
poi-3.16.jar.
Use is something like this:
WorkbookEvaluatorProvider wbEvalProv = (WorkbookEvaluatorProvider)
workbook.getCreationHelper().createFormulaEvaluator();
ConditionalFormattingEvaluator cfEval = new
ConditionalForma
orks somewhere.
>
> On Fri, Apr 14, 2017 at 10:04 AM, Greg Woolsey
> wrote:
>
> > It's all open-source, done in the spare time of volunteers who have a
> need
> > and/or desire to work on it.
> >
> > In my case, I add features I need for work, and fix bugs
AM Blake Watson wrote:
> Thanks a lot! Is there a list somewhere to see what features are planned
> for 3.17+? I can't seem to find a POI roadmap that shows (e.g.) that
> conditional support was planned, or what's planned for future releases.
>
> On Fri, Apr 14, 2017 at 8:
This is in the nightly builds now and the upcoming 3.16 release. It
probably should go in the release notes/change log on the web site, but
hasn't yet.
The main change was revision r1782894 [1] in reference to issue 58131 [2].
There is now a ConditionalFormattingEvaluator class [3] to support the
72 matches
Mail list logo
