Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Hulbert, Robert Douglas
Hi,

I'm a summer student at Lawrence Livermore National Laboratory and was hired to find or implement POI's table formulas and matrix functions.

Over the last week or so, I have checked the POI page/Contributor guidelines and have looked through the source code for handling this functionality.

Is anyone still interested in this functionality? If not, is there any documentation on where this aspect left off?

Thank you so much for any help you can give!

Best Regards,
Robert Hulbert
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Javen O'Neal-2
Greg Woolsey has provided quite a few improvements on Table support for
XSSF recently (last 6-12 months).

Question to the devs: Are tables part of the XLS binary file format, and if
so are users interested in a common SS Table interface?

Question to Robert: Is LLNL particularly interested in using POI to read
and write workbooks containing tables and matrix (table or array?)
functions? Or were they more interested in having an intern help out on an
open source project and table support was one idea they had?


On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <[hidden email]>
wrote:

Hi,

I'm a summer student at Lawrence Livermore National Laboratory and was
hired to find or implement POI's table formulas and matrix functions.

Over the last week or so, I have checked the POI page/Contributor
guidelines and have looked through the source code for handling this
functionality.

Is anyone still interested in this functionality? If not, is there any
documentation on where this aspect left off?

Thank you so much for any help you can give!

Best Regards,
Robert Hulbert
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Greg Woolsey
In reply to this post by Hulbert, Robert Douglas
I implemented the current table formula handling, based on a patch I found
languishing in the bug database.  It's been a year or so, but here's what I
remember:

Data Table syntax works in most cases, but there is definitely room for
more test cases looking for bugs in less common scenarios - Unicode in
table header names, characters requiring quoting, references to the current
or external workbooks are some I can think of off the top.

There are exceptions and JavaDoc notes in many places stating array
formulas are not supported, and some open bugs about functions that work
only partially or incorrectly because of incomplete array formula support.
For example you can set array formulas for a sheet, but POI doesn't
evaluate them when calculating.

I don't think there is much support at all currently for anything matrix
related.  Parsing array syntax will be a tricky challenge, but if done
would likely allow a good portion of array/matrix function implementations
without much further fuss.

Table formula syntax is not allowed by Excel in places like chart
definitions.  If someone wanted to work on functionality similar to that in
Excel that can attempt to infer table/chart relationships, and thus offer
some support for automatically adjusting chart definitions based on table
additions/deletions, that would be an amazingly helpful feature to many
users, I suspect (and myself for sure! :)  )

Somewhere on one of the mailing lists was a good collection of links to
pages and videos related to implementing functions in POI, but I'm failing
to find them right now.

On Tue, Jun 27, 2017 at 1:01 PM Hulbert, Robert Douglas <[hidden email]>
wrote:

> Hi,
>
> I'm a summer student at Lawrence Livermore National Laboratory and was
> hired to find or implement POI's table formulas and matrix functions.
>
> Over the last week or so, I have checked the POI page/Contributor
> guidelines and have looked through the source code for handling this
> functionality.
>
> Is anyone still interested in this functionality? If not, is there any
> documentation on where this aspect left off?
>
> Thank you so much for any help you can give!
>
> Best Regards,
> Robert Hulbert
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Hulbert, Robert Douglas
In reply to this post by Hulbert, Robert Douglas
LLNL currently uses POI for reading and writing workbooks and would like to extend that functionality so that their engineers could use the matrix operations for configuration purposes.
They also want me to learn and relay my experiences in helping an open source project.

CLARIFICATION: I believe I meant array formulas not table formulas. Specifically, when I attempted testing component binary operations (+,-,*,/) using array formulas (formula over an area w/ CTRL+SHIFT+ENTER), it
                                evaluated to [!VALUE] in the cell where the formula was and empty string in subsequent cells.


On 2017-06-27 12:38 (-0700), "Hulbert, Robert Douglas" <[hidden email]> wrote:
> Hi,>
>
> I'm a summer student at Lawrence Livermore National Laboratory and was hired to find or implement POI's table formulas and matrix functions.>

>
> Over the last week or so, I have checked the POI page/Contributor guidelines and have looked through the source code for handling this functionality.>

>
> Is anyone still interested in this functionality? If not, is there any documentation on where this aspect left off?>
>
> Thank you so much for any help you can give!>
>
> Best Regards,>
> Robert Hulbert>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Robert Hulbert
In reply to this post by Javen O'Neal-2
LLNL currently uses POI for reading and writing workbooks and would like to extend that functionality so that their engineers could use the matrix operations for configuration purposes.

They also want me to learn and relay my experiences in helping an open source project.

On 2017-06-27 15:51 (-0700), "Javen O'Neal" <[hidden email]> wrote:

> Greg Woolsey has provided quite a few improvements on Table support for
> XSSF recently (last 6-12 months).
>
> Question to the devs: Are tables part of the XLS binary file format, and if
> so are users interested in a common SS Table interface?
>
> Question to Robert: Is LLNL particularly interested in using POI to read
> and write workbooks containing tables and matrix (table or array?)
> functions? Or were they more interested in having an intern help out on an
> open source project and table support was one idea they had?
>
>
> On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <[hidden email]>
> wrote:
>
> Hi,
>
> I'm a summer student at Lawrence Livermore National Laboratory and was
> hired to find or implement POI's table formulas and matrix functions.
>
> Over the last week or so, I have checked the POI page/Contributor
> guidelines and have looked through the source code for handling this
> functionality.
>
> Is anyone still interested in this functionality? If not, is there any
> documentation on where this aspect left off?
>
> Thank you so much for any help you can give!
>
> Best Regards,
> Robert Hulbert
>

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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Robert Hulbert
In reply to this post by Greg Woolsey
Correction, I meant to say array formulas. For example, when I attempted testing component binary operations (+,-,*,/) using array formulas (formula over an area w/ CTRL+SHIFT+ENTER), it evaluated to [!VALUE] in the cell where the formula was and empty string in subsequent cells.

On 2017-06-27 15:59 (-0700), Greg Woolsey <[hidden email]> wrote:

> I implemented the current table formula handling, based on a patch I found
> languishing in the bug database.  It's been a year or so, but here's what I
> remember:
>
> Data Table syntax works in most cases, but there is definitely room for
> more test cases looking for bugs in less common scenarios - Unicode in
> table header names, characters requiring quoting, references to the current
> or external workbooks are some I can think of off the top.
>
> There are exceptions and JavaDoc notes in many places stating array
> formulas are not supported, and some open bugs about functions that work
> only partially or incorrectly because of incomplete array formula support.
> For example you can set array formulas for a sheet, but POI doesn't
> evaluate them when calculating.
>
> I don't think there is much support at all currently for anything matrix
> related.  Parsing array syntax will be a tricky challenge, but if done
> would likely allow a good portion of array/matrix function implementations
> without much further fuss.
>
> Table formula syntax is not allowed by Excel in places like chart
> definitions.  If someone wanted to work on functionality similar to that in
> Excel that can attempt to infer table/chart relationships, and thus offer
> some support for automatically adjusting chart definitions based on table
> additions/deletions, that would be an amazingly helpful feature to many
> users, I suspect (and myself for sure! :)  )
>
> Somewhere on one of the mailing lists was a good collection of links to
> pages and videos related to implementing functions in POI, but I'm failing
> to find them right now.
>
> On Tue, Jun 27, 2017 at 1:01 PM Hulbert, Robert Douglas <[hidden email]>
> wrote:
>
> > Hi,
> >
> > I'm a summer student at Lawrence Livermore National Laboratory and was
> > hired to find or implement POI's table formulas and matrix functions.
> >
> > Over the last week or so, I have checked the POI page/Contributor
> > guidelines and have looked through the source code for handling this
> > functionality.
> >
> > Is anyone still interested in this functionality? If not, is there any
> > documentation on where this aspect left off?
> >
> > Thank you so much for any help you can give!
> >
> > Best Regards,
> > Robert Hulbert
> >
>

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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Robert Hulbert
In reply to this post by Javen O'Neal-2
Following up, there are two external Matrix Libraries that I am familiar with (JAMA and Commons.Math3.Linear). Both of these libraries provide all the functionality necessary to emulate the Excel Matrix functionality. The Linear library is 2MB and JAMA is ~20KB. I understand this would be adding a dependency to the project. Is there a preference on which library would be used or is the preferred solution to implement the functionality directly in POI?

On 2017-06-27 15:51 (-0700), "Javen O'Neal" <[hidden email]> wrote:

> Greg Woolsey has provided quite a few improvements on Table support for
> XSSF recently (last 6-12 months).
>
> Question to the devs: Are tables part of the XLS binary file format, and if
> so are users interested in a common SS Table interface?
>
> Question to Robert: Is LLNL particularly interested in using POI to read
> and write workbooks containing tables and matrix (table or array?)
> functions? Or were they more interested in having an intern help out on an
> open source project and table support was one idea they had?
>
>
> On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <[hidden email]>
> wrote:
>
> Hi,
>
> I'm a summer student at Lawrence Livermore National Laboratory and was
> hired to find or implement POI's table formulas and matrix functions.
>
> Over the last week or so, I have checked the POI page/Contributor
> guidelines and have looked through the source code for handling this
> functionality.
>
> Is anyone still interested in this functionality? If not, is there any
> documentation on where this aspect left off?
>
> Thank you so much for any help you can give!
>
> Best Regards,
> Robert Hulbert
>

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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Greg Woolsey
We aren't averse to dependency changes, we did one in the past year.  My
personal preference is the Commons Math library, as it is also an Apache
project and more importantly still under active development.  JAMA appears
dead, and calls itself a straw-man implementation.

We could note that if a user doesn't need Excel matrix function evaluation
they would not need to include that library at run time.  Until we start
using the Commons Math functionality for more stuff :)

Actually, there are likely statistical functions we could implement using
it as well, among others.

On Wed, Jul 26, 2017 at 9:18 AM Robert Hulbert <[hidden email]> wrote:

> Following up, there are two external Matrix Libraries that I am familiar
> with (JAMA and Commons.Math3.Linear). Both of these libraries provide all
> the functionality necessary to emulate the Excel Matrix functionality. The
> Linear library is 2MB and JAMA is ~20KB. I understand this would be adding
> a dependency to the project. Is there a preference on which library would
> be used or is the preferred solution to implement the functionality
> directly in POI?
>
> On 2017-06-27 15:51 (-0700), "Javen O'Neal" <[hidden email]> wrote:
> > Greg Woolsey has provided quite a few improvements on Table support for
> > XSSF recently (last 6-12 months).
> >
> > Question to the devs: Are tables part of the XLS binary file format, and
> if
> > so are users interested in a common SS Table interface?
> >
> > Question to Robert: Is LLNL particularly interested in using POI to read
> > and write workbooks containing tables and matrix (table or array?)
> > functions? Or were they more interested in having an intern help out on
> an
> > open source project and table support was one idea they had?
> >
> >
> > On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <[hidden email]>
> > wrote:
> >
> > Hi,
> >
> > I'm a summer student at Lawrence Livermore National Laboratory and was
> > hired to find or implement POI's table formulas and matrix functions.
> >
> > Over the last week or so, I have checked the POI page/Contributor
> > guidelines and have looked through the source code for handling this
> > functionality.
> >
> > Is anyone still interested in this functionality? If not, is there any
> > documentation on where this aspect left off?
> >
> > Thank you so much for any help you can give!
> >
> > Best Regards,
> > Robert Hulbert
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Javen O'Neal
+1 to Apache Commons Math.

Can we write tests to verify that this dependency is only needed for
certain packages? I hope I'm wrong, but 20 MB is large enough that some
people may not want to update to newer versions of POI.

On Jul 26, 2017 10:28 AM, "Greg Woolsey" <[hidden email]> wrote:

> We aren't averse to dependency changes, we did one in the past year.  My
> personal preference is the Commons Math library, as it is also an Apache
> project and more importantly still under active development.  JAMA appears
> dead, and calls itself a straw-man implementation.
>
> We could note that if a user doesn't need Excel matrix function evaluation
> they would not need to include that library at run time.  Until we start
> using the Commons Math functionality for more stuff :)
>
> Actually, there are likely statistical functions we could implement using
> it as well, among others.
>
> On Wed, Jul 26, 2017 at 9:18 AM Robert Hulbert <[hidden email]>
> wrote:
>
> > Following up, there are two external Matrix Libraries that I am familiar
> > with (JAMA and Commons.Math3.Linear). Both of these libraries provide all
> > the functionality necessary to emulate the Excel Matrix functionality.
> The
> > Linear library is 2MB and JAMA is ~20KB. I understand this would be
> adding
> > a dependency to the project. Is there a preference on which library would
> > be used or is the preferred solution to implement the functionality
> > directly in POI?
> >
> > On 2017-06-27 15:51 (-0700), "Javen O'Neal" <[hidden email]> wrote:
> > > Greg Woolsey has provided quite a few improvements on Table support for
> > > XSSF recently (last 6-12 months).
> > >
> > > Question to the devs: Are tables part of the XLS binary file format,
> and
> > if
> > > so are users interested in a common SS Table interface?
> > >
> > > Question to Robert: Is LLNL particularly interested in using POI to
> read
> > > and write workbooks containing tables and matrix (table or array?)
> > > functions? Or were they more interested in having an intern help out on
> > an
> > > open source project and table support was one idea they had?
> > >
> > >
> > > On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <[hidden email]>
> > > wrote:
> > >
> > > Hi,
> > >
> > > I'm a summer student at Lawrence Livermore National Laboratory and was
> > > hired to find or implement POI's table formulas and matrix functions.
> > >
> > > Over the last week or so, I have checked the POI page/Contributor
> > > guidelines and have looked through the source code for handling this
> > > functionality.
> > >
> > > Is anyone still interested in this functionality? If not, is there any
> > > documentation on where this aspect left off?
> > >
> > > Thank you so much for any help you can give!
> > >
> > > Best Regards,
> > > Robert Hulbert
> > >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [hidden email]
> > For additional commands, e-mail: [hidden email]
> >
> >
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Dominik Stadler
Hi,

we already run some of the tests without the "scratchpad" dependency to
verify that we don't introduce unwanted dependencies, see
https://builds.apache.org/view/P/view/POI/job/POI-DSL-no-scratchpad/. It
should be easy to do something similar for commons-math, i.e. exclude it
during the test-run and exclude some specific tests that are known to
depend on commons-math functionality.

Dominik.

On Wed, Jul 26, 2017 at 8:20 PM, Javen O'Neal <[hidden email]> wrote:

> +1 to Apache Commons Math.
>
> Can we write tests to verify that this dependency is only needed for
> certain packages? I hope I'm wrong, but 20 MB is large enough that some
> people may not want to update to newer versions of POI.
>
> On Jul 26, 2017 10:28 AM, "Greg Woolsey" <[hidden email]> wrote:
>
> > We aren't averse to dependency changes, we did one in the past year.  My
> > personal preference is the Commons Math library, as it is also an Apache
> > project and more importantly still under active development.  JAMA
> appears
> > dead, and calls itself a straw-man implementation.
> >
> > We could note that if a user doesn't need Excel matrix function
> evaluation
> > they would not need to include that library at run time.  Until we start
> > using the Commons Math functionality for more stuff :)
> >
> > Actually, there are likely statistical functions we could implement using
> > it as well, among others.
> >
> > On Wed, Jul 26, 2017 at 9:18 AM Robert Hulbert <[hidden email]>
> > wrote:
> >
> > > Following up, there are two external Matrix Libraries that I am
> familiar
> > > with (JAMA and Commons.Math3.Linear). Both of these libraries provide
> all
> > > the functionality necessary to emulate the Excel Matrix functionality.
> > The
> > > Linear library is 2MB and JAMA is ~20KB. I understand this would be
> > adding
> > > a dependency to the project. Is there a preference on which library
> would
> > > be used or is the preferred solution to implement the functionality
> > > directly in POI?
> > >
> > > On 2017-06-27 15:51 (-0700), "Javen O'Neal" <[hidden email]> wrote:
> > > > Greg Woolsey has provided quite a few improvements on Table support
> for
> > > > XSSF recently (last 6-12 months).
> > > >
> > > > Question to the devs: Are tables part of the XLS binary file format,
> > and
> > > if
> > > > so are users interested in a common SS Table interface?
> > > >
> > > > Question to Robert: Is LLNL particularly interested in using POI to
> > read
> > > > and write workbooks containing tables and matrix (table or array?)
> > > > functions? Or were they more interested in having an intern help out
> on
> > > an
> > > > open source project and table support was one idea they had?
> > > >
> > > >
> > > > On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <
> [hidden email]>
> > > > wrote:
> > > >
> > > > Hi,
> > > >
> > > > I'm a summer student at Lawrence Livermore National Laboratory and
> was
> > > > hired to find or implement POI's table formulas and matrix functions.
> > > >
> > > > Over the last week or so, I have checked the POI page/Contributor
> > > > guidelines and have looked through the source code for handling this
> > > > functionality.
> > > >
> > > > Is anyone still interested in this functionality? If not, is there
> any
> > > > documentation on where this aspect left off?
> > > >
> > > > Thank you so much for any help you can give!
> > > >
> > > > Best Regards,
> > > > Robert Hulbert
> > > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: [hidden email]
> > > For additional commands, e-mail: [hidden email]
> > >
> > >
> >
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Greg Woolsey
In reply to this post by Javen O'Neal
The runtime JARs for Commons Math are 2.2MB, the rest of the release
package is source and docs.  If users really need the minimum required
packaging, they can use something like Maven Shade [1].

I like the idea of running tests without this dependency and just skipping
tests that need it.

Perhaps we could use an annotation to identify tests to skip in a
particular run, so we don't need to maintain a master list?

1. https://maven.apache.org/plugins/maven-shade-plugin/

On Wed, Jul 26, 2017 at 11:20 AM Javen O'Neal <[hidden email]> wrote:

> +1 to Apache Commons Math.
>
> Can we write tests to verify that this dependency is only needed for
> certain packages? I hope I'm wrong, but 20 MB is large enough that some
> people may not want to update to newer versions of POI.
>
> On Jul 26, 2017 10:28 AM, "Greg Woolsey" <[hidden email]> wrote:
>
> > We aren't averse to dependency changes, we did one in the past year.  My
> > personal preference is the Commons Math library, as it is also an Apache
> > project and more importantly still under active development.  JAMA
> appears
> > dead, and calls itself a straw-man implementation.
> >
> > We could note that if a user doesn't need Excel matrix function
> evaluation
> > they would not need to include that library at run time.  Until we start
> > using the Commons Math functionality for more stuff :)
> >
> > Actually, there are likely statistical functions we could implement using
> > it as well, among others.
> >
> > On Wed, Jul 26, 2017 at 9:18 AM Robert Hulbert <[hidden email]>
> > wrote:
> >
> > > Following up, there are two external Matrix Libraries that I am
> familiar
> > > with (JAMA and Commons.Math3.Linear). Both of these libraries provide
> all
> > > the functionality necessary to emulate the Excel Matrix functionality.
> > The
> > > Linear library is 2MB and JAMA is ~20KB. I understand this would be
> > adding
> > > a dependency to the project. Is there a preference on which library
> would
> > > be used or is the preferred solution to implement the functionality
> > > directly in POI?
> > >
> > > On 2017-06-27 15:51 (-0700), "Javen O'Neal" <[hidden email]> wrote:
> > > > Greg Woolsey has provided quite a few improvements on Table support
> for
> > > > XSSF recently (last 6-12 months).
> > > >
> > > > Question to the devs: Are tables part of the XLS binary file format,
> > and
> > > if
> > > > so are users interested in a common SS Table interface?
> > > >
> > > > Question to Robert: Is LLNL particularly interested in using POI to
> > read
> > > > and write workbooks containing tables and matrix (table or array?)
> > > > functions? Or were they more interested in having an intern help out
> on
> > > an
> > > > open source project and table support was one idea they had?
> > > >
> > > >
> > > > On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <
> [hidden email]>
> > > > wrote:
> > > >
> > > > Hi,
> > > >
> > > > I'm a summer student at Lawrence Livermore National Laboratory and
> was
> > > > hired to find or implement POI's table formulas and matrix functions.
> > > >
> > > > Over the last week or so, I have checked the POI page/Contributor
> > > > guidelines and have looked through the source code for handling this
> > > > functionality.
> > > >
> > > > Is anyone still interested in this functionality? If not, is there
> any
> > > > documentation on where this aspect left off?
> > > >
> > > > Thank you so much for any help you can give!
> > > >
> > > > Best Regards,
> > > > Robert Hulbert
> > > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: [hidden email]
> > > For additional commands, e-mail: [hidden email]
> > >
> > >
> >
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Javen O'Neal
At 2.2MB and with more projects using better dependency resolvers (Gradle),
I'm less worried about this hurting adoption.

On Jul 26, 2017 11:58, "Greg Woolsey" <[hidden email]> wrote:

> The runtime JARs for Commons Math are 2.2MB, the rest of the release
> package is source and docs.  If users really need the minimum required
> packaging, they can use something like Maven Shade [1].
>
> I like the idea of running tests without this dependency and just skipping
> tests that need it.
>
> Perhaps we could use an annotation to identify tests to skip in a
> particular run, so we don't need to maintain a master list?
>
> 1. https://maven.apache.org/plugins/maven-shade-plugin/
>
> On Wed, Jul 26, 2017 at 11:20 AM Javen O'Neal <[hidden email]>
> wrote:
>
> > +1 to Apache Commons Math.
> >
> > Can we write tests to verify that this dependency is only needed for
> > certain packages? I hope I'm wrong, but 20 MB is large enough that some
> > people may not want to update to newer versions of POI.
> >
> > On Jul 26, 2017 10:28 AM, "Greg Woolsey" <[hidden email]> wrote:
> >
> > > We aren't averse to dependency changes, we did one in the past year.
> My
> > > personal preference is the Commons Math library, as it is also an
> Apache
> > > project and more importantly still under active development.  JAMA
> > appears
> > > dead, and calls itself a straw-man implementation.
> > >
> > > We could note that if a user doesn't need Excel matrix function
> > evaluation
> > > they would not need to include that library at run time.  Until we
> start
> > > using the Commons Math functionality for more stuff :)
> > >
> > > Actually, there are likely statistical functions we could implement
> using
> > > it as well, among others.
> > >
> > > On Wed, Jul 26, 2017 at 9:18 AM Robert Hulbert <[hidden email]>
> > > wrote:
> > >
> > > > Following up, there are two external Matrix Libraries that I am
> > familiar
> > > > with (JAMA and Commons.Math3.Linear). Both of these libraries provide
> > all
> > > > the functionality necessary to emulate the Excel Matrix
> functionality.
> > > The
> > > > Linear library is 2MB and JAMA is ~20KB. I understand this would be
> > > adding
> > > > a dependency to the project. Is there a preference on which library
> > would
> > > > be used or is the preferred solution to implement the functionality
> > > > directly in POI?
> > > >
> > > > On 2017-06-27 15:51 (-0700), "Javen O'Neal" <[hidden email]>
> wrote:
> > > > > Greg Woolsey has provided quite a few improvements on Table support
> > for
> > > > > XSSF recently (last 6-12 months).
> > > > >
> > > > > Question to the devs: Are tables part of the XLS binary file
> format,
> > > and
> > > > if
> > > > > so are users interested in a common SS Table interface?
> > > > >
> > > > > Question to Robert: Is LLNL particularly interested in using POI to
> > > read
> > > > > and write workbooks containing tables and matrix (table or array?)
> > > > > functions? Or were they more interested in having an intern help
> out
> > on
> > > > an
> > > > > open source project and table support was one idea they had?
> > > > >
> > > > >
> > > > > On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <
> > [hidden email]>
> > > > > wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I'm a summer student at Lawrence Livermore National Laboratory and
> > was
> > > > > hired to find or implement POI's table formulas and matrix
> functions.
> > > > >
> > > > > Over the last week or so, I have checked the POI page/Contributor
> > > > > guidelines and have looked through the source code for handling
> this
> > > > > functionality.
> > > > >
> > > > > Is anyone still interested in this functionality? If not, is there
> > any
> > > > > documentation on where this aspect left off?
> > > > >
> > > > > Thank you so much for any help you can give!
> > > > >
> > > > > Best Regards,
> > > > > Robert Hulbert
> > > > >
> > > >
> > > > ------------------------------------------------------------
> ---------
> > > > To unsubscribe, e-mail: [hidden email]
> > > > For additional commands, e-mail: [hidden email]
> > > >
> > > >
> > >
> >
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Robert Hulbert
In reply to this post by Greg Woolsey
For personal clarification, tests will be generated for this functionality, however, these tests and the dependency will not be included in the main test set?

On 2017-07-26 11:58 (-0700), Greg Woolsey <[hidden email]> wrote:

> The runtime JARs for Commons Math are 2.2MB, the rest of the release
> package is source and docs.  If users really need the minimum required
> packaging, they can use something like Maven Shade [1].
>
> I like the idea of running tests without this dependency and just skipping
> tests that need it.
>
> Perhaps we could use an annotation to identify tests to skip in a
> particular run, so we don't need to maintain a master list?
>
> 1. https://maven.apache.org/plugins/maven-shade-plugin/
>
> On Wed, Jul 26, 2017 at 11:20 AM Javen O'Neal <[hidden email]> wrote:
>
> > +1 to Apache Commons Math.
> >
> > Can we write tests to verify that this dependency is only needed for
> > certain packages? I hope I'm wrong, but 20 MB is large enough that some
> > people may not want to update to newer versions of POI.
> >
> > On Jul 26, 2017 10:28 AM, "Greg Woolsey" <[hidden email]> wrote:
> >
> > > We aren't averse to dependency changes, we did one in the past year.  My
> > > personal preference is the Commons Math library, as it is also an Apache
> > > project and more importantly still under active development.  JAMA
> > appears
> > > dead, and calls itself a straw-man implementation.
> > >
> > > We could note that if a user doesn't need Excel matrix function
> > evaluation
> > > they would not need to include that library at run time.  Until we start
> > > using the Commons Math functionality for more stuff :)
> > >
> > > Actually, there are likely statistical functions we could implement using
> > > it as well, among others.
> > >
> > > On Wed, Jul 26, 2017 at 9:18 AM Robert Hulbert <[hidden email]>
> > > wrote:
> > >
> > > > Following up, there are two external Matrix Libraries that I am
> > familiar
> > > > with (JAMA and Commons.Math3.Linear). Both of these libraries provide
> > all
> > > > the functionality necessary to emulate the Excel Matrix functionality.
> > > The
> > > > Linear library is 2MB and JAMA is ~20KB. I understand this would be
> > > adding
> > > > a dependency to the project. Is there a preference on which library
> > would
> > > > be used or is the preferred solution to implement the functionality
> > > > directly in POI?
> > > >
> > > > On 2017-06-27 15:51 (-0700), "Javen O'Neal" <[hidden email]> wrote:
> > > > > Greg Woolsey has provided quite a few improvements on Table support
> > for
> > > > > XSSF recently (last 6-12 months).
> > > > >
> > > > > Question to the devs: Are tables part of the XLS binary file format,
> > > and
> > > > if
> > > > > so are users interested in a common SS Table interface?
> > > > >
> > > > > Question to Robert: Is LLNL particularly interested in using POI to
> > > read
> > > > > and write workbooks containing tables and matrix (table or array?)
> > > > > functions? Or were they more interested in having an intern help out
> > on
> > > > an
> > > > > open source project and table support was one idea they had?
> > > > >
> > > > >
> > > > > On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <
> > [hidden email]>
> > > > > wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I'm a summer student at Lawrence Livermore National Laboratory and
> > was
> > > > > hired to find or implement POI's table formulas and matrix functions.
> > > > >
> > > > > Over the last week or so, I have checked the POI page/Contributor
> > > > > guidelines and have looked through the source code for handling this
> > > > > functionality.
> > > > >
> > > > > Is anyone still interested in this functionality? If not, is there
> > any
> > > > > documentation on where this aspect left off?
> > > > >
> > > > > Thank you so much for any help you can give!
> > > > >
> > > > > Best Regards,
> > > > > Robert Hulbert
> > > > >
> > > >
> > > > ---------------------------------------------------------------------
> > > > To unsubscribe, e-mail: [hidden email]
> > > > For additional commands, e-mail: [hidden email]
> > > >
> > > >
> > >
> >
>

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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Dominik Stadler
Na, the main test-runs should still verify all functionality with all
required dependencies, we are talking about a separate build-config which
verifies this specific requirement to not have the dependency being
introduced in other functionality unintentionally.

Dominik.

On Wed, Jul 26, 2017 at 9:58 PM, Robert Hulbert <[hidden email]> wrote:

> For personal clarification, tests will be generated for this
> functionality, however, these tests and the dependency will not be included
> in the main test set?
>
> On 2017-07-26 11:58 (-0700), Greg Woolsey <[hidden email]> wrote:
> > The runtime JARs for Commons Math are 2.2MB, the rest of the release
> > package is source and docs.  If users really need the minimum required
> > packaging, they can use something like Maven Shade [1].
> >
> > I like the idea of running tests without this dependency and just
> skipping
> > tests that need it.
> >
> > Perhaps we could use an annotation to identify tests to skip in a
> > particular run, so we don't need to maintain a master list?
> >
> > 1. https://maven.apache.org/plugins/maven-shade-plugin/
> >
> > On Wed, Jul 26, 2017 at 11:20 AM Javen O'Neal <[hidden email]>
> wrote:
> >
> > > +1 to Apache Commons Math.
> > >
> > > Can we write tests to verify that this dependency is only needed for
> > > certain packages? I hope I'm wrong, but 20 MB is large enough that some
> > > people may not want to update to newer versions of POI.
> > >
> > > On Jul 26, 2017 10:28 AM, "Greg Woolsey" <[hidden email]>
> wrote:
> > >
> > > > We aren't averse to dependency changes, we did one in the past
> year.  My
> > > > personal preference is the Commons Math library, as it is also an
> Apache
> > > > project and more importantly still under active development.  JAMA
> > > appears
> > > > dead, and calls itself a straw-man implementation.
> > > >
> > > > We could note that if a user doesn't need Excel matrix function
> > > evaluation
> > > > they would not need to include that library at run time.  Until we
> start
> > > > using the Commons Math functionality for more stuff :)
> > > >
> > > > Actually, there are likely statistical functions we could implement
> using
> > > > it as well, among others.
> > > >
> > > > On Wed, Jul 26, 2017 at 9:18 AM Robert Hulbert <[hidden email]>
> > > > wrote:
> > > >
> > > > > Following up, there are two external Matrix Libraries that I am
> > > familiar
> > > > > with (JAMA and Commons.Math3.Linear). Both of these libraries
> provide
> > > all
> > > > > the functionality necessary to emulate the Excel Matrix
> functionality.
> > > > The
> > > > > Linear library is 2MB and JAMA is ~20KB. I understand this would be
> > > > adding
> > > > > a dependency to the project. Is there a preference on which library
> > > would
> > > > > be used or is the preferred solution to implement the functionality
> > > > > directly in POI?
> > > > >
> > > > > On 2017-06-27 15:51 (-0700), "Javen O'Neal" <[hidden email]>
> wrote:
> > > > > > Greg Woolsey has provided quite a few improvements on Table
> support
> > > for
> > > > > > XSSF recently (last 6-12 months).
> > > > > >
> > > > > > Question to the devs: Are tables part of the XLS binary file
> format,
> > > > and
> > > > > if
> > > > > > so are users interested in a common SS Table interface?
> > > > > >
> > > > > > Question to Robert: Is LLNL particularly interested in using POI
> to
> > > > read
> > > > > > and write workbooks containing tables and matrix (table or
> array?)
> > > > > > functions? Or were they more interested in having an intern help
> out
> > > on
> > > > > an
> > > > > > open source project and table support was one idea they had?
> > > > > >
> > > > > >
> > > > > > On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <
> > > [hidden email]>
> > > > > > wrote:
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I'm a summer student at Lawrence Livermore National Laboratory
> and
> > > was
> > > > > > hired to find or implement POI's table formulas and matrix
> functions.
> > > > > >
> > > > > > Over the last week or so, I have checked the POI page/Contributor
> > > > > > guidelines and have looked through the source code for handling
> this
> > > > > > functionality.
> > > > > >
> > > > > > Is anyone still interested in this functionality? If not, is
> there
> > > any
> > > > > > documentation on where this aspect left off?
> > > > > >
> > > > > > Thank you so much for any help you can give!
> > > > > >
> > > > > > Best Regards,
> > > > > > Robert Hulbert
> > > > > >
> > > > >
> > > > > ------------------------------------------------------------
> ---------
> > > > > To unsubscribe, e-mail: [hidden email]
> > > > > For additional commands, e-mail: [hidden email]
> > > > >
> > > > >
> > > >
> > >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Table Formula Manipulation and Matrix Function Implementation in HSSF/XSSF

Yegor Kozlov-4
Even if we go for the Commons Math route we need to recognize and support
the array formula syntax from the Formula Evaluator module.
Correct  me if I'm wrong, but POI still doesn't support arrays as formula
arguments and constructions like below will fail :

=SUM(C2:C11*D2:D11)
=SQRT({1,2;3,4})

Once we support the array formula operands we can plug in Commons Math to
do the actual computations.

There was a patch a few years back
https://bz.apache.org/bugzilla/show_bug.cgi?id=48292 which tried to address
it. Unfortunately it wasn't applied. The code needed  work and no one
volunteered to finish it. It might be a good start to revisit it and
integrate in trunk.

On Wed, Jul 26, 2017 at 11:40 PM, Dominik Stadler <[hidden email]>
wrote:

> Na, the main test-runs should still verify all functionality with all
> required dependencies, we are talking about a separate build-config which
> verifies this specific requirement to not have the dependency being
> introduced in other functionality unintentionally.
>
> Dominik.
>
> On Wed, Jul 26, 2017 at 9:58 PM, Robert Hulbert <[hidden email]>
> wrote:
>
> > For personal clarification, tests will be generated for this
> > functionality, however, these tests and the dependency will not be
> included
> > in the main test set?
> >
> > On 2017-07-26 11:58 (-0700), Greg Woolsey <[hidden email]>
> wrote:
> > > The runtime JARs for Commons Math are 2.2MB, the rest of the release
> > > package is source and docs.  If users really need the minimum required
> > > packaging, they can use something like Maven Shade [1].
> > >
> > > I like the idea of running tests without this dependency and just
> > skipping
> > > tests that need it.
> > >
> > > Perhaps we could use an annotation to identify tests to skip in a
> > > particular run, so we don't need to maintain a master list?
> > >
> > > 1. https://maven.apache.org/plugins/maven-shade-plugin/
> > >
> > > On Wed, Jul 26, 2017 at 11:20 AM Javen O'Neal <[hidden email]>
> > wrote:
> > >
> > > > +1 to Apache Commons Math.
> > > >
> > > > Can we write tests to verify that this dependency is only needed for
> > > > certain packages? I hope I'm wrong, but 20 MB is large enough that
> some
> > > > people may not want to update to newer versions of POI.
> > > >
> > > > On Jul 26, 2017 10:28 AM, "Greg Woolsey" <[hidden email]>
> > wrote:
> > > >
> > > > > We aren't averse to dependency changes, we did one in the past
> > year.  My
> > > > > personal preference is the Commons Math library, as it is also an
> > Apache
> > > > > project and more importantly still under active development.  JAMA
> > > > appears
> > > > > dead, and calls itself a straw-man implementation.
> > > > >
> > > > > We could note that if a user doesn't need Excel matrix function
> > > > evaluation
> > > > > they would not need to include that library at run time.  Until we
> > start
> > > > > using the Commons Math functionality for more stuff :)
> > > > >
> > > > > Actually, there are likely statistical functions we could implement
> > using
> > > > > it as well, among others.
> > > > >
> > > > > On Wed, Jul 26, 2017 at 9:18 AM Robert Hulbert <
> [hidden email]>
> > > > > wrote:
> > > > >
> > > > > > Following up, there are two external Matrix Libraries that I am
> > > > familiar
> > > > > > with (JAMA and Commons.Math3.Linear). Both of these libraries
> > provide
> > > > all
> > > > > > the functionality necessary to emulate the Excel Matrix
> > functionality.
> > > > > The
> > > > > > Linear library is 2MB and JAMA is ~20KB. I understand this would
> be
> > > > > adding
> > > > > > a dependency to the project. Is there a preference on which
> library
> > > > would
> > > > > > be used or is the preferred solution to implement the
> functionality
> > > > > > directly in POI?
> > > > > >
> > > > > > On 2017-06-27 15:51 (-0700), "Javen O'Neal" <[hidden email]>
> > wrote:
> > > > > > > Greg Woolsey has provided quite a few improvements on Table
> > support
> > > > for
> > > > > > > XSSF recently (last 6-12 months).
> > > > > > >
> > > > > > > Question to the devs: Are tables part of the XLS binary file
> > format,
> > > > > and
> > > > > > if
> > > > > > > so are users interested in a common SS Table interface?
> > > > > > >
> > > > > > > Question to Robert: Is LLNL particularly interested in using
> POI
> > to
> > > > > read
> > > > > > > and write workbooks containing tables and matrix (table or
> > array?)
> > > > > > > functions? Or were they more interested in having an intern
> help
> > out
> > > > on
> > > > > > an
> > > > > > > open source project and table support was one idea they had?
> > > > > > >
> > > > > > >
> > > > > > > On Jun 27, 2017 1:01 PM, "Hulbert, Robert Douglas" <
> > > > [hidden email]>
> > > > > > > wrote:
> > > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I'm a summer student at Lawrence Livermore National Laboratory
> > and
> > > > was
> > > > > > > hired to find or implement POI's table formulas and matrix
> > functions.
> > > > > > >
> > > > > > > Over the last week or so, I have checked the POI
> page/Contributor
> > > > > > > guidelines and have looked through the source code for handling
> > this
> > > > > > > functionality.
> > > > > > >
> > > > > > > Is anyone still interested in this functionality? If not, is
> > there
> > > > any
> > > > > > > documentation on where this aspect left off?
> > > > > > >
> > > > > > > Thank you so much for any help you can give!
> > > > > > >
> > > > > > > Best Regards,
> > > > > > > Robert Hulbert
> > > > > > >
> > > > > >
> > > > > > ------------------------------------------------------------
> > ---------
> > > > > > To unsubscribe, e-mail: [hidden email]
> > > > > > For additional commands, e-mail: [hidden email]
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [hidden email]
> > For additional commands, e-mail: [hidden email]
> >
> >
>
Loading...