search

awalker89 / openxlsx

R package for .xlsx file reading and writing.
Other
364 stars 79 forks source link

Array formulas: Feature request #392

Open rexmacey opened 6 years ago

rexmacey commented 6 years ago

As you may know, Excel allows for "array formulas" which are required for formulas using certain functions such as matrix multiplication (mmult). In Excel one might enter "=mmult(a1:d4,f1:i4)" and then press ctrl-shift-Enter. I'm using openxlsx to show some work out of R including matrix operations, so this functionality would be useful. Thanks.

The workaround for now is to use openxlsx to create the formula. Then I have to manually edit the array formulas by pressing the ctrl-shift-enter. Thank you.

kainhofer commented 6 years ago

Technically, from what I can see array formulas are really simple to implement in the XML. Only the left upper cell contains the formula, with the additional ref and t elements of the tag:

<row r="1" x14ac:dyDescent="0.2" spans="1:2">
    <c r="A1" s="1">
        <f ref="A1:B2" t="array">MMULT(A4:B5,A7:B8)</f>
        <v>19</v>
    </c>
    <c r="B1" s="2">
        <v>22</v>
    </c>
</row>
<row r="2" x14ac:dyDescent="0.25" spans="1:2" thickBot="1" ht="13.5">
    <c r="A2" s="3">
        <v>43</v>
    </c>
    <c r="B2" s="4">
        <v>50</v>
    </c>
</row>

The xl/calcChain.xml file also contains those cells, but I'm not sure whether Excel / OpenOffice relly needs that information or whether it will create the calculation chain if missing.