| Summary: | Problem importing function GETPIVOTDATA from Excel | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Rossetti Danilo <rossetti.danilo> |
| Component: | Calc | Assignee: | Kohei Yoshida <kohei> |
| Status: | RESOLVED FIXED | ||
| Severity: | major | CC: | rossetti.danilo |
| Priority: | low | ||
| Version: | unspecified | ||
| Hardware: | x86 (IA32) | ||
| OS: | Windows (All) | ||
| Whiteboard: | target:3.4 | ||
| Crash report or crash signature: | Regression By: | ||
| Attachments: | example of conversion error | ||
Ok. Now I understand. The last argument is missing in Excel, and when importing into Calc, Calc omits the last argument, and because that argument is required, Calc throws a #REF! erorr. Just fixed this in the 3.4 branch. Calc append an imaginary parameter in case the last parameter is missing, to make the number of parameters to GETPIVOTDATA even. Actually, that doesn't work on resave. I have to add a real parameter on import. Interesting. Excel only accepts empty parameter as valid empty parameter, but Calc doesn't accept empty parameter, but requires empty string "" to be there.
This is what Excel requires
=GETPIVOTDATA("AMOUNT",$G$1,"OFFICE","B","YEAR",2006,"VOICE",)
this expression chokes Calc's formula parser because it thinks the formula expression is invalid due to ',' followed by ')'.
Sigh...
Ok. Luckily it was much simpler to support missing parameters the way Excel does than I thought. With that change in place, this test document now imports correctly, and the round-trip works too. |
Created attachment 43392 [details] example of conversion error When in pivot table a item in a field is empty, if is present a pivot function that takes a value insite pivot, the conversion of this function isn't correct. The syntax for this Excel function is (italian version): =INFO.DATI.TAB.PIVOT("AMOUNT";$G$1;"OFFICE";"B";"YEAR";2006;"VOICE";) it is converted as: =GETPIVOTDATA("AMOUNT";$G$1;"OFFICE";"B";"YEAR";2006;"VOICE") The conversion should put "" (2 contiguous quotes) to indicate empty value because CALC function use this method to rappresent it. For example I write the correct conversion that CALC should do: =GETPIVOTDATA("AMOUNT";$G$1;"OFFICE";"B";"YEAR";2006;"VOICE";"")