Perl Excel::Writer::XLSX write formula
averlon opened this issue · 5 comments
Hi,
I have created manually an EXCEL 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._xlws.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._xlws.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!
Use of uninitialized value $type in string eq at /home/xxx/perl5/lib/perl5/Excel/Writer/XLSX/Worksheet.pm line 7823. at /home/xxx/perl5/lib/perl5/Excel/Writer/XLSX/Worksheet.pm line 7823.
Any help apreciated!
There are probably 2 issues here.
The first is that FILTER() is a new class of Excel function called a dynamic function. That is currently only supported on the HEAD of the Excel::Writer::XLSX main branch.
You can see an example here: https://github.com/jmcnamara/excel-writer-xlsx/blob/main/examples/dynamic_arrays.pl
The second issue is that function names must be in English and MONAT() should be MONTH() in that case. You spotted this I think by looking at the formula in the Excel file. See this section of the docs: https://metacpan.org/pod/Excel::Writer::XLSX#Non-US-Excel-functions-and-syntax
And this translator: http://en.excel-translator.de/language/
Anyway, try your program with the latest code on HEAD and let me know how you get on.
Hi @jmcnamara ,
"Monat()" was used in the manually setup excel file via the desktop version of excel (GERMAN Version).
And as you see in the examples all statements done in perl use "Month".
I will try to understand you HEAD example and try to implement it. I will come back and report.
I will try to understand you HEAD example and try to implement it. I will come back and report.
There is no implementation required. You just need to install the version of Excel::Writer::XLSX from GitHub and try it with your existing code.
The example is there just for reference.
@jmcnamara
Hi,
probably I am missing something but -
your example shows:
$worksheet1->write('F2', '=FILTER(A1:D17,C1:C17=K2)');
I have setup a test script and the statement is:
$av_tmp_STRING = '=FILTER(n1:n15,n1:n15=0,"_empty")'; $av_obj_excel_worksheet_DATA->write( 'A1', $av_tmp_STRING );
As the result the file brings up an error when opening.
The xml-file shows:
<c r="A1"> <f>FILTER(n1:n15,n1:n15=0,"_empty")</f> <v>0</v>
Maybe I am still doing something wrong!
There is no implementation required. You just need to install the version of Excel::Writer::XLSX from GitHub and try it with your existing code.
I will give it a try!