Created attachment 109198 [details] orginal execel sheet view (Picture) I do have a very angry issue - and I am not sure, where the reasons are. Background: Amazon delivers to there sellers a monthly report in Format *.xls. This report is the base of the invoice. Well, it is a simple Excel-sheet with some formulas but without any spezilists. If you open the file in Excel (all versions) or in an excel-Viewer (im am using the Panmaker Viewer) - everything ist ok (see Pic Org_Excel_20141110.png). If I open the file with LibreOffice Calc, most things looks like in Excel - but there are two exeptions: - Date-Values where not imported or interpretation failed. - Bigger issue: Instead of the corret Sum -Formulas the value 4,500 is displayed and all further formulas use this value as base. See picture Calc_Import_20141110.png). Looking detailed to the Issue: The formua of the sum-cells is imported correct (see picture Calc_Formula_20141110.png). The formula uses rangenames which where also imported correct. I do not know, where the value "4.500" is coming from?? Interesting: If I copy the formula of the cell, delete then the formula in the cell and paste the formula again - the correct value will be displayed and provided. So - the formula works correct even in LibO. Nevertheless it is a lot of work to correct manual all formulas. For futher tests I will provide one original xls-file. Be aware, that if you open the file in calc and change something and save the file (xls or ods) you may have new issues ang changes! so please work with copies.
Created attachment 109199 [details] file opened in calc (picture)
Created attachment 109200 [details] correct formula in calc - wrong result (picture)
Created attachment 109201 [details] original excel file
Still in 4.4.
Does hard refresh work (Ctrl+Shft+F9) ? Did this work properly before? thanks - Cor
Hard refresh works for me loading the excel file. At least I see this problem in 4.1 as in master. The problem is that this is not really an XLS file - it's an Excel 2003 XML thing: <?xml version='1.0' encoding='UTF-8'?><?mso-application progid='Excel.Sheet'?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> More amusingly the file contains: <Row ss:AutoFitHeight="0" ss:Height="16.5"> <Cell ss:StyleID="s46"> <Data ss:Type="String">VERKAUFSBERICHT</Data> </Cell> <Cell ss:StyleID="s47"/> <Cell ss:StyleID="s47"/> <Cell ss:StyleID="s47"/> <Cell ss:StyleID="s47"/> <Cell ss:StyleID="s47"/> <Cell ss:StyleID="s47"/> <Cell ss:StyleID="s47"/> <Cell ss:Formula="=IF(ISERROR(SUM(SA_Amount)),0,SUM(SA_Amount))" ss:StyleID="s48"> <Data ss:Type="Number">4.5</Data> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ :-) <NamedCell ss:Name="SALES"/> <TotalPaymentsKey>SA</TotalPaymentsKey> </Cell> ie. that cell has the data "4.5" inside it. To be sure, Microsoft Excel doesn't appear to trust that data, or even show it - but re-calculates it on-load; but we (it seems) do. So - Thomas - where did you get this file ? ;-) Of course ctrl-alt-F9 forces a nice re-calc that works well. Failing that - we can add a feature to clobber re-calc mode to force it on for these files; since we load via XSLT (IIRC) it is deadly slow anyway so no real perf. loss I suppose [ unless I'm confused wrt. the format ]. Thanks.
@ Michael: The file is provided from Amazon - and every seller (part of the advantage programm) will recieve a similar file every month. so - I wonder if non of the millions of amazon sellers works with LibreOffice - except me??? OK, nevertheless: ctlr + shift + F9 works and you discribe one of the possible reasons - so I will open now a ticket by amazon - whenever I do not have any hope, that they will change the problem;) Guess, we can close the ticket here.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.1.5 or 5.2.1 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20160920
Unfortunately the bug is back. So - the bug has changed a little bit. While contacting Amazon they have correct their file-output and a short time everything was ok. then - with new libO version - again a error occurs. this happened with 5.0.? and it is still alive. Problem now: Calc shows #name? in calculated cells. I have made some investigations: Amazon worked with named ranges in formulas (see picture 1 - 20160920a.png ) But looking to the definition in LibO - all names ended with a bracked (see picture2 - 20160920b.png) - and I believe, this is am error of the import (migration xlsx to calc?) actual system: Version: 5.2.1.2 (x64) Build-ID: 31dd62db80d4e60af04904455ec9c9219178d620 CPU-Threads: 4; BS-Version: Windows 6.19; UI-Render: Standard; Gebietsschema: de-DE (de_DE); Calc: group
Created attachment 127483 [details] example of used formulas in calc
Created attachment 127484 [details] 20160920b.png - definition of named ranges (import!!)
Comment on attachment 127483 [details] example of used formulas in calc this is picture 20160920a.png as mentioned in comment
(In reply to Thomas Krumbein from comment #9) > Unfortunately the bug is back. > > So - the bug has changed a little bit. please close this one and open a new one. In the summary (description) mention the named range. And please refer to https://bugs.documentfoundation.org/show_bug.cgi?id=102272 ;)
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.4.1 or 5.3.6 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20170929
still an issue in Version: 6.0.0.0.alpha0+ Build ID: c5a93cad149618bbd43632f1660a558c34bdbf7e CPU threads: 4; OS: Linux 4.10; UI render: default; VCL: gtk2; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-10-07_01:12:25 Locale: nl-NL (nl_NL.UTF-8); Calc: group
Opening the original excel file give my err:507 Saving the spreadsheet as a ods file give me the right calculation and no errors Err:507 => The 507 error may be caused by windows system files damage. The corrupted windows system files entries can be a real threat to the well being of your computer. There can be many events which may have resulted in the system files errors. An incomplete installation, an incomplete uninstall, improper deletion of applications or hardware. It can also be caused if your computer is recovered from a virus or adware/spyware attack or by an improper shutdown of the computer. All the above actives may result in the deletion or corruption of the entries in the windows registry. This corrupted registry will lead to the missing and wrongly linked information and files needed for the proper working of the application. tested with Version: 5.3.8.0.0+ Build ID: a0fae00a2d52960eebbb14f08d2de251e0a8ff3f CPU Threads: 8; OS Version: Linux 4.9; UI Render: default; VCL: kde4; Layout Engine: new; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-5-3, Time: 2017-10-05_05:58:12 Locale: nl-BE (en_US.UTF-8); Calc: group and Version: 6.0.0.0.alpha0+ Build ID: fbfe55e58c4b14f86cbb2c7b822f727e5b2e4a66 CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group Best regards
This i get when saving xml file with LO =of:]=IF(ISERROR(SUM(sa_amount));0;SUM(sa_amount)) and the manage name range is empty Version: 5.4.3.0.0+ Build ID: cc70f137cd853bc22281edb94f15ebe48da871e7 CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group Best regards
in reply to comment 16: sorry Xavier, your comment on corrupted computers, virus and all this stuff may be correct general, but it doesn´t help here. Those file where provided from Amazon as monthly bills to sellers - and this bug is still present since summer. I guess, those files are not generated with Excel, but programmly written. All users with Excel do not have any problem of opening those file and they do not have any error. As clearly descriped LO do not interpret the range-definitions correct becouse of adding or not cutting this "[" bracket. And this is not becouse a user have a corrupped device! Your comment leads in a wrong direction - this will not solve the problem.
The original Excel now shows the correct sum in cell I12 on open. There are some other bugs on formatting, but I guess new bugs should be filed on that, if they haven't been reported already I don't encounter the issues mentioned in comment #9, is that with a new version of the Amazon file? If so, a new bug should be opened Version: 6.1.0.0.alpha0+ Build ID: a488c7ad2763b944713997911c1ddb0315d8c93f CPU threads: 2; OS: Mac OS X 10.12.6; UI render: default; TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2018-03-26_00:38:29 Locale: en-US (en_US.UTF-8); Calc: group