If a worksheet containing a CEILING function is saved in .xlsx format, the function gets saved with 4 arguments instead of the 3 originals, resulting in Err.504 when the file is opened again. If the format is .ods, the arguments remain being 3, and the behaviour is the expected when reopening
Reproduce. When saving in .xlsx, Calc added an argument instead of removing the last argument. In Excel, CEILING have only 2 arguments.
When saved to xlsx all 3 parameters of CEILING are exported. Resulting xlsx file cannot be opened with MS Office. (I tried MS Office 2003 with the compatibility pack.) On import Calc adds a 4th parameter -> Error:504. FLOOR has the same bug.
Let me have a look at it.
Need some rework in the formula API. I don't think it is something for the 3.4 release cycle. I have to talk to Kohei if we really want change the API or if we want to change the export code for xlsx. Code Pointers: FormulaCompiler::CreateStringFromTokenArray XclExpFormulaCell::SaveXml
*** Bug 39615 has been marked as a duplicate of this bug. ***
Saw the duplicate post of this bug (39615). I created an .ods file and used the Floor function with 2 arguments and saved as .xlsx format with no problem. On MS Excel it only demands 2 arguments for the floor function, so perhaps error only occurs when using the three argument format in calc? On LibreOffice 3.4 340m1(Build:103) for OpenSuse Linux.
As far as i have noticed the "error" also occurs when using the two argument format in calc. Then during save a third parameter is added. After saving a reloading and saving a second time the result will be four arguments.
The problem is that the functions in Excel and in ODF are not full compatible. Some thoughts on that in http://wiki.services.openoffice.org/wiki/Calc/Drafts/Treatment_of_new_Excel_2010_functions#CEILING.PRECISE
Created attachment 89551 [details] Minimal test case for reproducing this bug consistently I noticed this behaviour in 3.5 and upgraded to 4.1 to see if it would go away but it is still present. In my case the function was: CEILING(A1/B1,1) which was replaced on the first save and open cycle with: CEILING(A1/B1,1,1) The next time I opened the file and edited the formula to add a space at the end and saved and then opened the file again another 1 was added: CEILING(A1/B1,1,1,1) Ditto for the next cycle: CEILING(A1/B1,1,1,1,1) etc. CEILING(A1/B1,1,1,1,1,1) It seems to add an endless number of extra parameters as long as I make a null edit each time between saving. I have only ever saved the file as XLSX.
http://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/formulaparser.cxx#384 : void FormulaFinalizer::appendCalcOnlyParameter( const FunctionInfo& rFuncInfo, size_t nParam ) { (void)nParam; // prevent 'unused' warning switch( rFuncInfo.mnBiff12FuncId ) { case BIFF_FUNC_FLOOR: case BIFF_FUNC_CEILING: OSL_ENSURE( nParam == 2, "FormulaFinalizer::appendCalcOnlyParameter - unexpected parameter index" ); maTokens.append< double >( OPCODE_PUSH, 1.0 ); maTokens.append( OPCODE_SEP ); break; } } I am not sure what OSL_ENSURE does, but if it doesn't fail at that line, it looks like this function will add extra parameters to both CEILING and FLOOR without strictly relying on nParam. Naively, it looks like there should be an if(nParam == 2) {} wrapper instead of just OSL_ENSURE. The bug tracking this issue for the FLOOR function is Bug 69072
Patch submitted to Gerrit for review: https://gerrit.libreoffice.org/6746
*** Bug 69072 has been marked as a duplicate of this bug. ***
A different change pending review for 4-1 https://gerrit.libreoffice.org/6764
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1f38670b11f7d49505603852f6e259cfc2b7710e&h=libreoffice-4-1 resolved fdo#38592 do not insert extraneous parameter in import It will be available in LibreOffice 4.1.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
@Eike can we label this bug as FIXED after your patch?
Yes, done.