Re: Handling of worksheet functions from Excel 2010 and newer
Hi Winfried, On Thursday, 2013-11-21 13:40:25 +0100, Winfried Donkers wrote: Fixed with https://gerrit.libreoffice.org/#/c/6751/ I misread the background information and didn't have an original xlsx-document containing the function at the time I submitted the first patch. Thanks a lot! Eike -- LibreOffice Calc developer. Number formatter stricken i18n transpositionizer. GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A Support the FSFE, care about Free Software! https://fsfe.org/support/?erack pgpF1yz3NgsAk.pgp Description: PGP signature ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
Hi Winfried, On Thursday, 2013-11-21 11:38:36 +0100, Winfried Donkers wrote: Do you intend to push this document with the tests disabled once I submit a patch with the tests for all functions currently present, based on the new document? Done in http://cgit.freedesktop.org/libreoffice/core/commit/?id=52999789258aa7cfde8d01ff7e8a03a0f53278db with the document version https://bugs.freedesktop.org/attachment.cgi?id=89635 of https://bugs.freedesktop.org/show_bug.cgi?id=70798 All implemented functions' tests are activated and pass. I will then add lines to the test with each function I add. And when all functions are added, I will change to test to check if all are OK and if not, check all functions one by one for a usefull error message. To activate a test, in sc/qa/unit/subsequent_filters-test.cxx ScFiltersTest::testFunctionsExcel2010() in struct aTests simply change the corresponding row's bEvaluate member from false to true. Eike -- LibreOffice Calc developer. Number formatter stricken i18n transpositionizer. GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A Support the FSFE, care about Free Software! https://fsfe.org/support/?erack pgp5CM87E0NW5.pgp Description: PGP signature ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
RE: Handling of worksheet functions from Excel 2010 and newer
Hi Eike, To activate a test, in sc/qa/unit/subsequent_filters-test.cxx ScFiltersTest::testFunctionsExcel2010() in struct aTests simply change the corresponding row's bEvaluate member from false to true. Thank you! :) I have already included sc/qa/unit/subsequent_filters-test.cxx in my checklist for coming Excel 2010 functions patches. Next one will be Ceiling and Floor functions. Winfried ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
RE: Handling of worksheet functions from Excel 2010 and newer
Hi Eike, Attached to https://bugs.freedesktop.org/show_bug.cgi?id=70798 as https://bugs.freedesktop.org/attachment.cgi?id=89539 Note that T.DIST.2T and T.INV.2T weren't correct, see bug comment. I will work on the unit test to include all functions that are currently present. I added one more function to the document and attached to https://bugs.freedesktop.org/show_bug.cgi?id=70798 as https://bugs.freedesktop.org/attachment.cgi?id=89583 Do you intend to push this document with the tests disabled once I submit a patch with the tests for all functions currently present, based on the new document? I will then add lines to the test with each function I add. And when all functions are added, I will change to test to check if all are OK and if not, check all functions one by one for a usefull error message. Winfried ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
RE: Handling of worksheet functions from Excel 2010 and newer
Hi Eike, Which reveals that our CONFIDENCE.T calculation seems to be wrong, there it results in 0.0837211385 whereas Excels calculates it as 1.5223611251 Could you please investigate? Fixed with https://gerrit.libreoffice.org/#/c/6751/ I misread the background information and didn't have an original xlsx-document containing the function at the time I submitted the first patch. Winfried ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
Hi Winfried, On Monday, 2013-11-18 07:55:22 +0100, Winfried Donkers wrote: Attachment 89360 (https://bugs.freedesktop.org/attachment.cgi?id=89360) of bug 70798, see comment 9 for explanations. Note that the new xlsx-document may make the current tests fail because of relocation of functions; I can submit a patch for that (and add functions to the test) when you tell me the xlsx-document is ready for pushing. Attached to https://bugs.freedesktop.org/show_bug.cgi?id=70798 as https://bugs.freedesktop.org/attachment.cgi?id=89539 Note that T.DIST.2T and T.INV.2T weren't correct, see bug comment. Eike -- LibreOffice Calc developer. Number formatter stricken i18n transpositionizer. GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A Support the FSFE, care about Free Software! https://fsfe.org/support/?erack pgpQhKWuuPrda.pgp Description: PGP signature ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
Hi Winfried, On Wednesday, 2013-11-20 18:18:02 +0100, Eike Rathke wrote: Attached to https://bugs.freedesktop.org/show_bug.cgi?id=70798 as https://bugs.freedesktop.org/attachment.cgi?id=89539 Actually now https://bugs.freedesktop.org/attachment.cgi?id=89540 with result and test columns. Eike -- LibreOffice Calc developer. Number formatter stricken i18n transpositionizer. GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A Support the FSFE, care about Free Software! https://fsfe.org/support/?erack pgpOQ5PhtIP3i.pgp Description: PGP signature ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
Hi Winfried, On Wednesday, 2013-11-20 18:25:49 +0100, Eike Rathke wrote: https://bugs.freedesktop.org/attachment.cgi?id=89539 Actually now https://bugs.freedesktop.org/attachment.cgi?id=89540 with result and test columns. Which reveals that our CONFIDENCE.T calculation seems to be wrong, there it results in 0.0837211385 whereas Excels calculates it as 1.5223611251 Could you please investigate? Thanks Eike -- LibreOffice Calc developer. Number formatter stricken i18n transpositionizer. GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A Support the FSFE, care about Free Software! https://fsfe.org/support/?erack pgpbl7sQCkqU4.pgp Description: PGP signature ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
RE: Handling of worksheet functions from Excel 2010 and newer
Hi Eike, Attached to https://bugs.freedesktop.org/show_bug.cgi?id=70798 as https://bugs.freedesktop.org/attachment.cgi?id=89539 Note that T.DIST.2T and T.INV.2T weren't correct, see bug comment. My typing error (which didn't show as I haven't added these functions yet). I will work on the unit test to include all functions that are currently present. Has the xlsx file been pushed to master so that I can submit the test patch? Winfried ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
RE: Handling of worksheet functions from Excel 2010 and newer
Hi Eike, Should we add all new/renamed Excel2010 functions to that xlsx-document? It isn't too much trouble for me, but I wouldn't like to give 'one of you with Excel' work that is not necessary. :) I'd appreciate if you could add all the functions, send the document to me, I'd check it in Excel if all works and then save it again to commit. Attachment 89360 (https://bugs.freedesktop.org/attachment.cgi?id=89360) of bug 70798, see comment 9 for explanations. Note that the new xlsx-document may make the current tests fail because of relocation of functions; I can submit a patch for that (and add functions to the test) when you tell me the xlsx-document is ready for pushing. Winfried ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
Hi Winfried, On Friday, 2013-11-15 08:42:29 +0100, Winfried Donkers wrote: No problem, only AFAICS, the current test is more of a 'do _xlfn.-functions import correctly' kind. That specific test case yes. We can add other tests to check correctness of results (within bounds of rounding errors) or whatever we want. Should we add all new/renamed Excel2010 functions to that xlsx-document? It isn't too much trouble for me, but I wouldn't like to give 'one of you with Excel' work that is not necessary. :) I'd appreciate if you could add all the functions, send the document to me, I'd check it in Excel if all works and then save it again to commit. BTW, I would put all functions in the xlsx-document at once, and only add the test when adding a set of functions. Please do so. Eike -- LibreOffice Calc developer. Number formatter stricken i18n transpositionizer. GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A Support the FSFE, care about Free Software! https://fsfe.org/support/?erack pgpeBzlhtHMyW.pgp Description: PGP signature ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
Hi Winfried, On Tuesday, 2013-11-12 10:00:15 +0100, Winfried Donkers wrote: The tests Kohei added are of some functions I recently added (plus one that I'm still working on), so I will expand both the xlsx-file and the ScFiltersTest::testFunctionsExcel2010(). I did that for the recently added CONFIDENCE.NORM and CONFIDENCE.T funcions. (The xlsx-file will have to checked by someone with Excel though, before getting pushed. I don't have Excel) Best if at the same time you submit the unit test to gerrit you attach the .xlsx file edited to contain the additional tests to the BugZilla RFE with a short comment to mark it as interim. Only an import test case file written by Excel should be committed to the repository, which one of us with Excel can do then. Eike -- LibreOffice Calc developer. Number formatter stricken i18n transpositionizer. GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A Support the FSFE, care about Free Software! https://fsfe.org/support/?erack pgp5LQto2XpwC.pgp Description: PGP signature ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
RE: Handling of worksheet functions from Excel 2010 and newer
Hi Eike, Best if at the same time you submit the unit test to gerrit you attach the .xlsx file edited to contain the additional tests to the BugZilla RFE with a short comment to mark it as interim. Only an import test case file written by Excel should be committed to the repository, which one of us with Excel can do then. No problem, only AFAICS, the current test is more of a 'do _xlfn.-functions import correctly' kind. Should we add all new/renamed Excel2010 functions to that xlsx-document? It isn't too much trouble for me, but I wouldn't like to give 'one of you with Excel' work that is not necessary. :) BTW, I would put all functions in the xlsx-document at once, and only add the test when adding a set of functions. Winfried ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
On Wed, 2013-11-13 at 07:59 +0100, Winfried Donkers wrote: as I seem to read that you offer help in creating the test file, that may not be a problem after all.:) So... Our hope was to raise awareness of the importance of increasing test cases (which includes creating test files) and attract more people to write more tests because there are only so much test cases Markus and myself can write. I can tell you upfront that I cannot to offer help any more simply because I physically can't. But, thanks for the discussion. Kohei ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
RE: Handling of worksheet functions from Excel 2010 and newer
Hi Markus, Kohei, Eike, Kohei added the tests with http://cgit.freedesktop.org/libreoffice/core/commit/sc/qa?id=c40cbf189750f623e065b6ed9596d90977a2f631 but at least IMO it makes more sense to add test cases similar to testFunctionODS just for XLSX where you add corner cases for new functions. I suppose that you already have a set of corner cases for your tests and you can just use these for the import and export tests. The tests Kohei added are of some functions I recently added (plus one that I'm still working on), so I will expand both the xlsx-file and the ScFiltersTest::testFunctionsExcel2010(). (The xlsx-file will have to checked by someone with Excel though, before getting pushed. I don't have Excel) Winfried ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
Hey Winfried, 2013/11/12 Winfried Donkers w.donk...@dci-electronics.nl Hi Markus, Kohei, Eike, Kohei added the tests with http://cgit.freedesktop.org/libreoffice/core/commit/sc/qa?id=c40cbf189750f623e065b6ed9596d90977a2f631 but at least IMO it makes more sense to add test cases similar to testFunctionODS just for XLSX where you add corner cases for new functions. I suppose that you already have a set of corner cases for your tests and you can just use these for the import and export tests. The tests Kohei added are of some functions I recently added (plus one that I'm still working on), so I will expand both the xlsx-file and the ScFiltersTest::testFunctionsExcel2010(). (The xlsx-file will have to checked by someone with Excel though, before getting pushed. I don't have Excel) So Kohei just tested that the functions are imported without an error (which made sense as he was writing a test for a bug). What I was suggesting for your case is that you take some of the corner cases that you know and write a test for them. This is what I started once for ODS with testFunctionsODS. That way we can test that we import the function and that the function behaves correctly in the corner cases. Hopefully this helps to make sure that nobody ever breaks this function. Btw if your only problem with writing test cases is that you don't have access to Excel just ask for help. Kohei, Eike and I have access to Excel and can surely create the test file that you need or check that your file imports correctly. Regards, Markus ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
RE: Handling of worksheet functions from Excel 2010 and newer
Hi Markus, So Kohei just tested that the functions are imported without an error (which made sense as he was writing a test for a bug). What I was suggesting for your case is that you take some of the corner cases that you know and write a test for them. This is what I started once for ODS with testFunctionsODS. That way we can test that we import the function and that the function behaves correctly in the corner cases. Hopefully this helps to make sure that nobody ever breaks this function. The new Excel 2010 functions are mostly renamed existing functions and new functions with minor differences, so far all quite straightforward. IMHO I don't think there are corner cases specifically for most of these functions, as the 'corners' are already handled by the existing calc functions. Testing will mostly be limited to the correct handling of the import. Btw if your only problem with writing test cases is that you don't have access to Excel just ask for help. Kohei, Eike and I have access to Excel and can surely create the test file that you need or check that your file imports correctly. I was thinking to create an xlsx document with all the renamed and new Excel functions (http://office.microsoft.com/en-us/excel-help/what-s-new-changes-made-to-excel-functions-HA010355760.aspx) and add a test line in /sc/qa/unit/subsequent_filters-test.cxx, ScFiltersTest::testFunctionsExcel2010() each time I add new functions. That way the xlsx document is changed only once (don't know how well binary changes look in git/gerrit), but this approach necessitates the presence of Excel 2010/2013. On the other hand, as I seem to read that you offer help in creating the test file, that may not be a problem after all.:) Once I have a function with real corner cases that ought to be tested, I can add these corner cases myself. OTOH if you think only corner cases need to be tested, I can put them in an xlsx document (exported from calc) myself and ask for an Excel-check of the document when submitting. Winfried ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
Hey, 2013/11/11 Kohei Yoshida kohei.yosh...@collabora.com Hi there, Eike brought to my attention that my recent change to use the internal API to import formulas from xlsx broke the extra handling that the UNO API was doing to translate some of the newer Excel functions from Excel 2010 and newer. These new functions are typically prefixed with _xlfn. in the XML stream so that when the function name is e.g. BETA.DIST, it would appear as _xlfn.BETA.DIST in the sheet stream where the formula cell is stored. Long story short, the way I've decided to solve this was to add another set of function names and associate it with formula language XL_ENGLISH, which is, for now, only used for importing xlsx in a significant way. So, when you guys have implemented these new functions in Calc core, but have trouble importing them from xlsx, please take a look at resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML in formula/source/core/resource/core_resource.src and see if the stored function name matches what's in the XML stream. Hopefully this fixes the old functionality from the UNO formar parser that we were missing in ScCompiler. And a small request by me. Could we maybe add always directly an import/export test when we add a new function? Adding an import/export test for a function is normally a task of a few minutes and should help us prevent regressions with these functions. Regards, Markus ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
On Tue, 2013-11-12 at 00:30 +0100, Markus Mohrhard wrote: Hey, 2013/11/11 Kohei Yoshida kohei.yosh...@collabora.com Hi there, Eike brought to my attention that my recent change to use the internal API to import formulas from xlsx broke the extra handling that the UNO API was doing to translate some of the newer Excel functions from Excel 2010 and newer. These new functions are typically prefixed with _xlfn. in the XML stream so that when the function name is e.g. BETA.DIST, it would appear as _xlfn.BETA.DIST in the sheet stream where the formula cell is stored. Long story short, the way I've decided to solve this was to add another set of function names and associate it with formula language XL_ENGLISH, which is, for now, only used for importing xlsx in a significant way. So, when you guys have implemented these new functions in Calc core, but have trouble importing them from xlsx, please take a look at resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML in formula/source/core/resource/core_resource.src and see if the stored function name matches what's in the XML stream. Hopefully this fixes the old functionality from the UNO formar parser that we were missing in ScCompiler. And a small request by me. Could we maybe add always directly an import/export test when we add a new function? I did my share for my change. The ones that were missing from the test which went unnoticed by my change, I added them. That said, I can't write all necessary tests on my own, so I'd much like to get more help from others who work in this area. Adding an import/export test for a function is normally a task of a few minutes and should help us prevent regressions with these functions. Agreed, though I have to say that a task of a few minutes is a bit of a stretch. ;-) For more complex functions, understanding the functions may require a bit of a trial and error, and coming up with a suitable test case may take a lot more than just a few minutes But I do agree with trying to make the writing of tests more a normal part of our day to day development. Kohei ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
And before anybody starts lecturing me on the importance of snipping when replying on a thread (which I do understand very well), I do apologize for not snipping the unrelated parts of the previous post. I just realize that now... :-/ Kohei ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
RE: Handling of worksheet functions from Excel 2010 and newer
Hi Kohei, And a small request by me. Could we maybe add always directly an import/export test when we add a new function? I did my share for my change. The ones that were missing from the test which went unnoticed by my change, I added them. That said, I can't write all necessary tests on my own, so I'd much like to get more help from others who work in this area. Could you give me a code pointer to one of the tests you made (or a commit-ID)? That would help me to remain within the 'couple of minutes' when adding tests for the Excel-functions I am currently adding ;) Winfried ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Handling of worksheet functions from Excel 2010 and newer
Hey Winfried, 2013/11/12 Winfried Donkers w.donk...@dci-electronics.nl Hi Kohei, And a small request by me. Could we maybe add always directly an import/export test when we add a new function? I did my share for my change. The ones that were missing from the test which went unnoticed by my change, I added them. That said, I can't write all necessary tests on my own, so I'd much like to get more help from others who work in this area. Could you give me a code pointer to one of the tests you made (or a commit-ID)? That would help me to remain within the 'couple of minutes' when adding tests for the Excel-functions I am currently adding ;) Kohei added the tests with http://cgit.freedesktop.org/libreoffice/core/commit/sc/qa?id=c40cbf189750f623e065b6ed9596d90977a2f631but at least IMO it makes more sense to add test cases similar to testFunctionODS just for XLSX where you add corner cases for new functions. I suppose that you already have a set of corner cases for your tests and you can just use these for the import and export tests. Regards, Markus ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
