Description: Add dynamic arrays in to Calc like in Excel https://techcommunity.microsoft.com/t5/Excel-Blog/Preview-of-Dynamic-Arrays-in-Excel/ba-p/252944 Steps to Reproduce: - Actual Results: we can't make dynamic arrays in the Calc Expected Results: we can make dynamic arrays in the Calc Reproducible: Always User Profile Reset: No Additional Info: -
Makes sense. Moving to NEW
I had a look at how Excel saves the "dynamic array" feature. Here are my (hopefully correct) results. They may be helpful for those who implement the feature. The xlsx-file has a global switch in metadata.xml to determine whether the "dynamic array" feature should be used at all. It is located in the <futureMetadata> element and there in the <xda:dynamicArrayProperties> chil delement, namely its ´fDynamic´ attribute. This <futureMetadata> element is linked to a <metadataType> element via the ´name´ attribute. This <metadataType> element contains the ´minSupportedVersion´ attribute, among others. For the "dynamic array" feature to be used at all in the document, fDynamic="1" must be set. A formula is contained as <f> element in the top left cell of its output area, also for CSE (Ctrl+Shift+Enter) formulas. If the formula is to create a "dynamic array", this cell has a ´cm´ attribute (18.3.1.4, Part 1, OOXML standard) that refers to the <futureMetadata> element. The ´cm´ attribute uses a zero-based index. In the example I examined, there was also a <cellMetadata> element, which is probably assigned the index value 0. In any case, ´cm="1"´ had to be set for the cell which contains the formula to have a "dynamic array" as output in my example. The fact that a "dynamic array" is generated is not limited to the new functions SORT, RANDARRAY, etc. For example, =ISBLANK(D12:F16) also generates a "dynamic array". In addition to the missing {} - which are used in the CSE case - you can recognize a "dynamic array" in the UI in Excel by the special frame that is shown around the array when a cell in the array is the active cell. When importing with SheetDataContext::importCell(), the ´cm´ attribute is ignored and the ´CellModel´ struct used for ´maCellData´ does not even have a component for it. I don't know whether the <futureMetadata> element is currently evaluated at all. BTW: If someone is going to implement this feature, please contact the ODF TC as early as possible.
*** Bug 162581 has been marked as a duplicate of this bug. ***
Created attachment 196389 [details] Higher-Order Spreadsheets with Spilled Arrays Higher-Order Spreadsheets with Spilled Arrays Jack Williams, Nima Joharizadeh, Andy Gordon, Advait Sarkar European Symposium on Programming | April 2020 We develop a theory for two recently-proposed spreadsheet mechanisms: gridlets allow for abstraction and reuse in spreadsheets, and build on spilled arrays, where an array value spills out of one cell into nearby cells. We present the first formal calculus of spreadsheets with spilled arrays. Since spilled arrays may collide, the semantics of spilling is an iterative process to determine which arrays spill successfully and which do not. Our first theorem is that this process converges deterministically. To model gridlets, we propose the grid calculus, a higher-order extension of our calculus of spilled arrays with primitives to treat spreadsheets as values. We define a semantics of gridlets as formulas in the grid calculus. Our second theorem shows the correctness of a remarkably direct encoding of the Abadi and Cardelli object calculus into the grid calculus. This result is the first rigorous analogy between spreadsheets and objects; it substantiates the intuition that gridlets are an object-oriented counterpart to functional programming extensions to spreadsheets, such as sheet-defined functions. https://www.microsoft.com/en-us/research/publication/higher-order-spreadsheets-with-spilled-arrays/ https://www.microsoft.com/en-us/research/uploads/prod/2020/04/extended_gridlets_esop.pdf