I have manually created an Excel XLSX file and put in the following formula in one cell:
=FILTER(gw_col_gwuPMBo;(MONAT(gw_col_DATUM)=1)*(gw_col_gwuPMBo<>0);"_empty")
The related xml-file for the sheet shows:
<c r="A3" cm="1">
<f t="array" ref="A3:A1429">_xlfn._xlws.FILTER(gw_col_gwuPMBo,(MONTH(gw_col_DATUM)=1)*(gw_col_gwuPMBo<>0),"_empty")</f>
<v>-3.016</v>
</c>
O.K. so far.
Now I have created the file via Excel::Writer::XLSX and wrote the formula this way:
$av_tmp_STRING = '=_xlfn.FILTER(gw_col_gwuPMBo,(MONTH(gw_col_DATUM)=1)*(gw_col_gwuPMBo<>0),"_empty")';
$av_tmp_STRING = decode( 'UTF-8', $av_tmp_STRING );
$av_obj_excel_worksheet_DATA->write_formula( 'a3', $av_tmp_STRING );
The result in the xlsx-file is:
=@FILTER(gw_col_gwuPMBo;(MONAT(gw_col_DATUM)=1)*(gw_col_gwuPMBo<>0);"_empty")
The result in the related xml-file is:
<c r="A3">
<f>_xlfn._xlws.FILTER(gw_col_gwuPMBo,(MONTH(gw_col_DATUM)=1)*(gw_col_gwuPMBo<>0),"_empty")</f>
<v>0</v>
</c>
What am I doing wrong to get the same result as in the file manually setup?
By the way! I was suspect about the "array" in the xml-statement of the manually setup file. So I tried to create an "Array-Formula" this way:
$av_tmp_STRING = '{=_xlfn.FILTER(gw_col_gwuPMBo,(MONTH(gw_col_DATUM)=1)*(gw_col_gwuPMBo<>0),"_empty")}';
$av_tmp_STRING = decode( 'UTF-8', $av_tmp_STRING );
$av_obj_excel_worksheet_DATA->write_array_formula( 'a3:a1429', $av_tmp_STRING );
But this causes the module to throw some errors which I am not able to analyze!
Any help appreciated!