OOXML writes @ and # as calls and a reference union as a parenthesised
list, so for each of them we need to know how far the sub-expression
under it reaches. CreateStringFromTokenArray worked that out from the
text it had already written. The problem with this is that every pair
it spliced in shifted all the offsets that were still waiting behind
it, so it kept breaking.
Instead of patching the text, OOXMLRewriter builds a second
token array in the shape OOXML spells out and the writer walks that as
it stands. The call names and the parentheses are ordinary tokens, so
there is nothing to splice and no offsets to keep track of. It is the
same trick RewriteMissing already plays a few lines further up, and it
means the input and the output can be looked at side by side.
The # changes place, from behind its operand to a call in front of it,
so it gets an opcode of its own. _xlfn.ANCHORARRAY spells ocAnchorArray
now, and ocSpill keeps the native postfix spelling with no OOXML one at
all, so the postfix form cannot end up in a file by accident. The
import already rewrote the call back into the postfix operator and now
recognises it by opcode instead of by position. The @ stays
ocSingleValue, it is already in front of its operand so only the
parentheses are new.
The OFFSET() literal argument is swapped for the reference error in
the same pass, instead of the writing loop jumping over tokens.
A unit test drives the rewrite on its own: token array in, token array
out, one case per shape. The shapes that end up with nothing to wrap
are in there too - a lone @, one with a missing argument, and text that
never parsed - since those are the ones where dropping a token is the
only change.
Also drop the dead "@" and "#" rows from the OOXML symbol table, the
rows above them already have _xlfn.SINGLE and _xlfn.ANCHORARRAY.
Signed-off-by: Tomaž Vajngerl <tomaz.vajngerl@collabora.co.uk>
Change-Id: I63944fb85b5c4388f304f7d94afd0b4f347d7782
Reviewed-on: https://gerrit.collaboraoffice.com/c/online/+/10155
Tested-by: Tomaž Vajngerl <tomaz.vajngerl@collabora.com>
Reviewed-by: Miklos Vajna <vmiklos@collabora.com>
(cherry picked from commit 4feac84950f5b524d99e277481de50f7b45a8d2f)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/210292
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>
Tested-by: Jenkins
The @ operand walk on OOXML save took the token behind the @ as the
whole operand, so any operand longer than one token was cut apart by
the wrapper: a leading sign, a structured table reference, a range
built with the range operator, and a nested @ or # inside an
argument list all left parts of themselves outside.
The @ now writes no text of its own. The main token loop notes where
the wrapper has to go and which parenthesis scope the operand lives
in, and splices _xlfn.SINGLE(...) in once the operand ends. The #
splice already worked that way, so there is one walk with one set of
special cases and no recursion.
Prefix signs and whitespace go inside the wrapper with the operand,
and a sign moves the factor start past itself, so a # wraps the
reference it follows and leaves the sign outside. Table reference
brackets scope like parentheses, which keeps a multi-item reference
whole. AND and OR are written as a name with an argument list, so
such a name does not end the operand either and the whole call stays
inside.
An operand that writes no text leaves the wrapper out. One that is
exactly one parenthesised group lends its parentheses to the
wrapper, so a saved formula keeps its shape over round trips. Text
that never parsed takes no wrapper and drops a # behind it, the way
an ODF save leaves the @ out.
The error strings for tokens the writer cannot spell out sit in
AppendTokenOrError, which tests the cheap conditions first. The
whitespace opcode test moved to opcode.hxx as isWhitespaceOpCode,
replacing the private copies, and the operator range tests join it
there as isUnaryOperatorOpCode and isBinaryOperatorOpCode. Unit
tests pin the saved output for each operand shape.
Signed-off-by: Tomaž Vajngerl <tomaz.vajngerl@collabora.co.uk>
Change-Id: I9b6c12bbc8d0ddb9efa043a2364d13c4699e7bd5
Reviewed-on: https://gerrit.collaboraoffice.com/c/online/+/9290
Tested-by: Tomaž Vajngerl <tomaz.vajngerl@collabora.com>
Reviewed-by: Mike Kaganski <mike.kaganski@collabora.com>
(cherry picked from commit 2b8d9c58e8f7b5f138d44f41d756421c3f7932cd)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/209393
Tested-by: Jenkins
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>
ARRAYTOTEXT returns a cell range or array as one text string,
taking the range and an optional format number.
Format 0 (or omitted) joins the values in row order with ", ".
Format 1 gives an array constant: braces around the array, ","
between columns, ";" between rows, and text quoted with any
inner double quote doubled. Any other format returns #VALUE!.
A number keeps its input-line text, a boolean reads TRUE or
FALSE in the current locale, an empty cell adds nothing, and an
error in a range lists as its error text while a reference to a
single error cell returns that error.
The function is wired up with its name tables, wizard entry,
parameter classification, and OOXML and binary filter mappings.
A self-checking OOXML workbook is verified after import and
after an XLSX and an ODS round trip, each with a recalc, and the
exported files are checked for the ARRAYTOTEXT formula. A ucalc
test covers the locale-dependent boolean text for a value and
a range.
Signed-off-by: Tomaž Vajngerl <tomaz.vajngerl@collabora.co.uk>
Change-Id: I0cdd9737a51f402e39cf9a1346f4c5326680fe8f
Reviewed-on: https://gerrit.collaboraoffice.com/c/online/+/7219
Reviewed-by: Miklos Vajna <vmiklos@collabora.com>
Tested-by: Jenkins CPCI <releng@collaboraoffice.com>
(cherry picked from commit f66673d65ee307f17512a4239dd4b52ac189ac20)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/208395
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>
Tested-by: Jenkins
A1# is the postfix counterpart of the @ prefix. When A1 is
the origin of a dynamic-array formula spilled into A1:A4, A1#
refers to the whole spill range, so SUM(A1#) totals the four
values and ROWS(A1#) returns 4.
The new opcode ocSpill sits in the unary-operator range. The
tokenizer produces it from a bare # after a reference or
value. Otherwise # stays the start of a #REF! or #NAME!
token. The compiler routes it through PostOpLine so it
follows its operand in the RPN.
ScSpilledRange pushes the spill range as an ScMatrixRangeToken
so aggregating functions see the whole range, and ISREF
reports it as a reference. A master yields its matrix. A
plain number, string, or non-master matrix slot yields the
the cell value.
A freshly typed =A1# counts as an array result, so it
promotes to a dynamic-array master and spills again. A cell
that does not promote collapses the range by implicit
intersection to the row-aligned value, or #VALUE! outside the
range, keeping that cell's number format.
Signed-off-by: Tomaž Vajngerl <tomaz.vajngerl@collabora.co.uk>
Change-Id: Idf2752af3f7575f6bc95b940344aa365415e4a2f
Reviewed-on: https://gerrit.collaboraoffice.com/c/online/+/5785
Reviewed-by: Mike Kaganski <mike.kaganski@collabora.com>
Tested-by: Tomaž Vajngerl <tomaz.vajngerl@collabora.com>
(cherry picked from commit 9b97b0fc91bce4b8726ea210347e8ea84270e148)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/207904
Tested-by: Xisco Fauli <xiscofauli@libreoffice.org>
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>
Tested-by: Jenkins
Add BYCOL(tdf#164508), BYROW(tdf#164509), MAKEARRAY(tdf#164511),
MAP(tdf#164512), REDUCE(tdf#164513), SCAN(tdf#161428) formula
functions, which are usually used together with LAMBDA and are
important for interoperability with other Office suites.
Change-Id: I7eb5a992a113638fdadf0cf61592c082094fb44f
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/205979
Tested-by: Xisco Fauli <xiscofauli@libreoffice.org>
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>
Removed LET from sc/README.md's list of functions supporting
wildcards and regular expressions, since it doesn't interact
with either.
Changed LET's return type from ForceArrayReturn to Value, since
it usually doesn't return an array.
Change-Id: I99168d274483ddea5ba338d4e019cf433fe88c45
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/205977
Tested-by: Jenkins
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>
The @ operator collapses an array operand to its upper-left value.
It is the implicit-intersection prefix used to opt a formula out
of the dynamic-array spill behaviour. =@SEQUENCE(4) returns 1
instead of the four-row spill.
The new ocSingleValue opcode joins the unary-operator range so the
existing compile and interpret machinery treats it like any other
unary prefix. The @ character stops being part of name syntax in
the OOXML grammar, so sheet names that contain @ have to be quoted
from now on.
In-session only for now. The operator is parsed and interpreted
but not yet serialised into XLSX or ODS.
Signed-off-by: Tomaž Vajngerl <tomaz.vajngerl@collabora.co.uk>
Change-Id: I18cf56e34f933ebb7f6e43c157618c9fe28dbe0f
Reviewed-on: https://gerrit.collaboraoffice.com/c/online/+/4172
Tested-by: Tomaž Vajngerl <tomaz.vajngerl@collabora.com>
Reviewed-by: Miklos Vajna <vmiklos@collabora.com>
(cherry picked from commit fbd04d7c394ad7445a667bf63820f88551b176e1)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/206673
Tested-by: Jenkins
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>
Introduce calculated field infrastructure across the pivot table stack:
cache (ScDPCache::CalculatedField), filtered cache, data sources
(ScSheetDPData, ScDatabaseDPData, ScDPGroupTableData), result
calculation (ScDPTabRes), dimension supplier (ScDPSource), and
save/restore (ScDPDimCalcSaveData). Add new ocDPFieldName opcode
and formula compiler support for pivot field references.
Handle OOXML import/export of pivot calculated fields.
Change-Id: I42d094122d547b8ee9a2b63f809bdb13127f8f01
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/197913
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga@collabora.com>
I note in moving comments around to preserve them that ocXor
is in the wrong place, but it has always been that way, so I will
leave it alone.
Change-Id: Ia5fd59e96c478fe5f30788554cc260059a1c46e3
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/201510
Tested-by: Jenkins
Reviewed-by: Noel Grandin <noel.grandin@collabora.co.uk>
https://issues.oasis-open.org/browse/OFFICE-4154
What is working already: xlookup with normal forward,
backward search in columns/rows. Binary search in rows with
real binary search algorithm, in columns only works with linear search yet.
Linear forward backward wildcard/regex search in columns/rows.
Looking for the first smaller or greater value with linear and binary search
ALso all the combination of all these options. Except XLOOKUP
not supperted wildcard/regex search with binary search.
TODO in next patches:
- add the binary search option for searching in columns.
- Evaluate Formula calculation not working in general.
Co-authored-by: Balazs Varga <balazs.varga.extern@allotropia.de>
Change-Id: I15fd4479b63ec13b093d269760d1bbb5957553e8
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/131905
Tested-by: Jenkins
Tested-by: Gabor Kelemen <gabor.kelemen.extern@allotropia.de>
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>
Allowed whitespace in ODFF and OOXML are
U+0020 SPACE
U+0009 CHARACTER TABULATION
U+000A LINE FEED
U+000D CARRIAGE RETURN
Line feed and carriage return look a bit funny in the Function Wizard if
part of a function's argument but work. Once a formula is edited, CR are
converted to LF though, probably already in EditEngine, didn't
investigate.
Change-Id: I6278f6be48872e0710a3d74212db391dda249ed2
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/119635
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins
Same as RAND() and RANDBETWEEN() but not recalculating on every
change, just the normal expression recalculation.
Change-Id: I8ba7099125e487a78bd3d91db8b666c2f36b22fd
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/92994
Tested-by: Jenkins
Reviewed-by: Eike Rathke <erack@redhat.com>
FOURIER(Array, GroupedByColumns, Inverse, Polar)
is a matrix formula that computes discrete Fourier transform[DFT]
of input array(first argument) via a radix-2, decimation-in-time
fast Fourier transform algorithm.
Unit test for this is coming up in a new commit.
The data in input array(first argument) can be :-
1) grouped by columns (needs to be indicated by flag GroupedByColumns = TRUE)
In this case the array can contain 1 or 2 columns, where the first column
contains the real part of input series and second column if present contains
the imaginary part of the input series. If there is only 1 column, the input
series is treated as purely real. If the number of rows is not a power of 2,
zeroes are appended to the input series internally to make the series length
equal to the next nearest power of 2.
2) grouped by rows (needs to be indicated by flag GroupedByColumns = FALSE)
In this case the array can contain 1 or 2 rows, where the first row
contains the real part of input series and second row if present contains
the imaginary part of the input series. If there is only 1 row, the input
series is treated as purely real. If the number of columns is not a power of 2,
zeroes are appended to the input series internally to make the series length
equal to the next nearest power of 2.
The third argument "Inverse" is a boolean flag to indicate whether an inverse
DFT needs to be computed. This argument is optional and the default value is
FALSE.
The fourth argument Polar is a boolean flag to indicate whether the final output
needs to be in polar coordinates. This argument is optional and the default value
is FALSE.
The result of DFT consists of two columns - first column contains the real parts (or
the magnitudes if Polar=TRUE) and second column contains the imaginary parts (or
the phases if Polar=TRUE).
Implementation:
A fairly straighforward non-recursive implementation of radix-2 FFT algorithm is
written from scratch.
Reference:
Heckbert, P., 1995. Fourier transforms and the fast Fourier transform (FFT) algorithm.
Computer Graphics, 2, pp.15-463.
The normalization factor used in DFT / and inverse DFT in this implementation matches that
of fft() and ifft() functions of Matlab/Octave. It also matches the one used in Wikipedia
article on DFT: https://en.wikipedia.org/wiki/Discrete_Fourier_transform.
Change-Id: If4a40a6ef62bce1f03c589ae5357b2049f66fe64
Reviewed-on: https://gerrit.libreoffice.org/67938
Tested-by: Jenkins
Reviewed-by: Eike Rathke <erack@redhat.com>
... instead of a low precedence unary operator with an odd
behaviour. This wasn't documented nor specified but maybe needed
for old(est) binary file format compatibility. Generate an error
for anything else than a context of a function with one argument.
There might be some corner cases of documents where some old usage
leads to error now, of which some may have worked by accident, but
some not as intended.
Related, the internal not exposed (but available) NEG was classified
similar as a unary operator but corectly handled as function at all
places. Classified as an ordinary 1-parameter function as well.
Change-Id: I3d84a6382243c8d64313e37346f81c857e71be95
Reviewed-on: https://gerrit.libreoffice.org/50055
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>
Tomaž Vajngerl